Re: Monitoring DB size

Hi Manuel

Sorry for the late reply saw this email just today. Anyways how about using
something like?

postgres=# SELECT
postgres-# pg_size_pretty(pg_database_size('postgres')) AS
database_size,
postgres-# pg_size_pretty(pg_total_relation_size('accounts')) AS
table_size,
postgres-# pg_size_pretty(tuple_len) AS live_tuple_size,
postgres-# pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
postgres-# pg_size_pretty(free_space) AS free_space
postgres-# FROM
postgres-# pgstattuple('accounts');
database_size | table_size | live_tuple_size | dead_tuple_size |
free_space
---------------+------------+-----------------+-----------------+------------
8500 kB | 40 kB | 80 bytes | 80 bytes | 7988 bytes
(1 row)

Thanks and regards
Semab

On Tue, Jul 16, 2024 at 4:38 PM Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>
wrote:

> Thanks for the suggestion. I think this will not help us to differentiate
> between live tuples, dead tuples and free space.
>
>
>
> Best regards,
>
> Manuel
>
>
>
> *From:* Torsten Förtsch <tfoertsch123(at)gmail(dot)com>
> *Sent:* 15 July 2024 18:59
> *To:* Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Monitoring DB size
>
>
>
> Slightly different approach than you might expect. For larger DBs you'd
> likely want to exclude base and instead use pg_database_size() in addition.
>
>
>
> postgres(2454884) =# create temp table xx(dir text, sz bigint);
> CREATE TABLE
> Time: 2.587 ms
> postgres(2454884) =# copy xx(sz, dir) from program 'du -s *';
>
> COPY 21
> Time: 3.793 ms
> postgres(2454884) =# select * from xx;
> dir | sz
> ----------------------+-------
> base | 26280
> global | 568
> pg_commit_ts | 12
> pg_dynshmem | 4
> pg_logical | 16
> pg_multixact | 28
> pg_notify | 4
> pg_replslot | 4
> pg_serial | 4
> pg_snapshots | 4
> pg_stat | 4
> pg_stat_tmp | 4
> pg_subtrans | 12
> pg_tblspc | 4
> pg_twophase | 4
> PG_VERSION | 4
> pg_wal | 16392
> pg_xact | 12
> postgresql.auto.conf | 4
> postmaster.opts | 4
> postmaster.pid | 4
> (21 rows)
>
> Time: 0.282 ms
>
>
>
> On Mon, Jul 15, 2024 at 4:42 PM Shenavai, Manuel <manuel(dot)shenavai(at)sap(dot)com>
> wrote:
>
> Hi everyone,
>
>
>
> we currently capture the db size (pg_database_size) which gives the “Disk
> space used by the database with the specified name”. Is it possible to
> further split this data how much space is occupied by live tuples, dead
> tuples and free space?
>
>
>
> We would like to have something like:
>
> DB Size: 200 MB, live tuple 100MB, Dead Tuple: 20 MB, free space 80MB
>
>
>
> Is this possible?
>
>
>
> Best regards,
>
> Manuel
>
>