MIME-Version: 1.0
References: <AM9PR02MB741013EB8C5698CD8C44387CE8A12@AM9PR02MB7410.eurprd02.prod.outlook.com>
In-Reply-To: <AM9PR02MB741013EB8C5698CD8C44387CE8A12@AM9PR02MB7410.eurprd02.prod.outlook.com>
From: =?UTF-8?Q?Torsten_F=C3=B6rtsch?= <tfoertsch123@gmail.com>
Date: Mon, 15 Jul 2024 18:59:04 +0200
Message-ID: <CAKkG4_nSEeOB4X-uThk6mTiBGU37swA2j=T8h82tnkSMTB+8aw@mail.gmail.com>
Subject: Re: Monitoring DB size
To: "Shenavai, Manuel" <manuel.shenavai@sap.com>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000089b74b061d4c2681"
Archived-At: <https://www.postgresql.org/message-id/CAKkG4_nSEeOB4X-uThk6mTiBGU37swA2j%3DT8h82tnkSMTB%2B8aw%40mail.gmail.com>
Precedence: bulk

--00000000000089b74b061d4c2681
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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) =3D# create temp table xx(dir text, sz bigint);
CREATE TABLE
Time: 2.587 ms
postgres(2454884) =3D# copy xx(sz, dir) from program 'du -s *';

COPY 21
Time: 3.793 ms
postgres(2454884) =3D# 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=E2=80=AFPM Shenavai, Manuel <manuel.shenavai@s=
ap.com>
wrote:

> Hi everyone,
>
>
>
> we currently capture the db size (pg_database_size) which gives the =E2=
=80=9CDisk
> space used by the database with the specified name=E2=80=9D. Is it possib=
le 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
>

--00000000000089b74b061d4c2681
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: base64

PGRpdiBkaXI9Imx0ciI+PGRpdj5TbGlnaHRseSBkaWZmZXJlbnQgYXBwcm9hY2ggdGhhbiB5b3Ug
bWlnaHQgZXhwZWN0LiBGb3IgbGFyZ2VyIERCcyB5b3UmIzM5O2QgbGlrZWx5IHdhbnQgdG8gZXhj
bHVkZSBiYXNlIGFuZCBpbnN0ZWFkIHVzZSBwZ19kYXRhYmFzZV9zaXplKCkgaW4gYWRkaXRpb24u
PC9kaXY+PGJyPjxkaXY+PHNwYW4gc3R5bGU9ImZvbnQtZmFtaWx5Om1vbm9zcGFjZSI+PHNwYW4g
c3R5bGU9ImNvbG9yOnJnYigwLDAsMCkiPnBvc3RncmVzKDI0NTQ4ODQpID0jIGNyZWF0ZSB0ZW1w
IHRhYmxlIHh4KGRpciB0ZXh0LCBzeiBiaWdpbnQpOyDCoMKgwqDCoMKgwqDCoMKgwqA8L3NwYW4+
PGJyPkNSRUFURSBUQUJMRQ0KPGJyPlRpbWU6IDIuNTg3IG1zDQo8YnI+PHNwYW4gc3R5bGU9ImNv
bG9yOnJnYigwLDAsMCkiPnBvc3RncmVzKDI0NTQ4ODQpID0jIGNvcHkgeHgoc3osIGRpcikgZnJv
bSBwcm9ncmFtICYjMzk7ZHUgLXMgKiYjMzk7OyDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKg
wqDCoMKgwqDCoMKgwqDCoMKgwqDCoDwvc3Bhbj48YnI+Q09QWSAyMQ0KPGJyPlRpbWU6IDMuNzkz
IG1zDQo8YnI+PHNwYW4gc3R5bGU9ImNvbG9yOnJnYigwLDAsMCkiPnBvc3RncmVzKDI0NTQ4ODQp
ID0jIHNlbGVjdCAqIGZyb20geHg7IMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDC
oMKgwqDCoMKgwqDCoMKgPC9zcGFuPjxicj4gwqDCoMKgwqDCoMKgwqDCoGRpciDCoMKgwqDCoMKg
wqDCoMKgwqB8IMKgc3ogwqDCoMKgPGJyPi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLQ0K
PGJyPiBiYXNlIMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgfCAyNjI4MA0KPGJyPiBn
bG9iYWwgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgwqDCoHwgwqDCoDU2OA0KPGJyPiBwZ19jb21t
aXRfdHMgwqDCoMKgwqDCoMKgwqDCoHwgwqDCoMKgMTINCjxicj4gcGdfZHluc2htZW0gwqDCoMKg
wqDCoMKgwqDCoMKgfCDCoMKgwqDCoDQNCjxicj4gcGdfbG9naWNhbCDCoMKgwqDCoMKgwqDCoMKg
wqDCoHwgwqDCoMKgMTYNCjxicj4gcGdfbXVsdGl4YWN0IMKgwqDCoMKgwqDCoMKgwqB8IMKgwqDC
oDI4DQo8YnI+IHBnX25vdGlmeSDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgfCDCoMKgwqDCoDQNCjxi
cj4gcGdfcmVwbHNsb3QgwqDCoMKgwqDCoMKgwqDCoMKgfCDCoMKgwqDCoDQNCjxicj4gcGdfc2Vy
aWFsIMKgwqDCoMKgwqDCoMKgwqDCoMKgwqB8IMKgwqDCoMKgNA0KPGJyPiBwZ19zbmFwc2hvdHMg
wqDCoMKgwqDCoMKgwqDCoHwgwqDCoMKgwqA0DQo8YnI+IHBnX3N0YXQgwqDCoMKgwqDCoMKgwqDC
oMKgwqDCoMKgwqB8IMKgwqDCoMKgNA0KPGJyPiBwZ19zdGF0X3RtcCDCoMKgwqDCoMKgwqDCoMKg
wqB8IMKgwqDCoMKgNA0KPGJyPiBwZ19zdWJ0cmFucyDCoMKgwqDCoMKgwqDCoMKgwqB8IMKgwqDC
oDEyDQo8YnI+IHBnX3RibHNwYyDCoMKgwqDCoMKgwqDCoMKgwqDCoMKgfCDCoMKgwqDCoDQNCjxi
cj4gcGdfdHdvcGhhc2UgwqDCoMKgwqDCoMKgwqDCoMKgfCDCoMKgwqDCoDQNCjxicj4gUEdfVkVS
U0lPTiDCoMKgwqDCoMKgwqDCoMKgwqDCoHwgwqDCoMKgwqA0DQo8YnI+IHBnX3dhbCDCoMKgwqDC
oMKgwqDCoMKgwqDCoMKgwqDCoMKgfCAxNjM5Mg0KPGJyPiBwZ194YWN0IMKgwqDCoMKgwqDCoMKg
wqDCoMKgwqDCoMKgfCDCoMKgwqAxMg0KPGJyPiBwb3N0Z3Jlc3FsLmF1dG8uY29uZiB8IMKgwqDC
oMKgNA0KPGJyPiBwb3N0bWFzdGVyLm9wdHMgwqDCoMKgwqDCoHwgwqDCoMKgwqA0DQo8YnI+IHBv
c3RtYXN0ZXIucGlkIMKgwqDCoMKgwqDCoHwgwqDCoMKgwqA0DQo8YnI+KDIxIHJvd3MpDQo8YnI+
DQo8YnI+VGltZTogMC4yODIgbXM8YnI+PC9zcGFuPjwvZGl2PjwvZGl2Pjxicj48ZGl2IGNsYXNz
PSJnbWFpbF9xdW90ZSI+PGRpdiBkaXI9Imx0ciIgY2xhc3M9ImdtYWlsX2F0dHIiPk9uIE1vbiwg
SnVsIDE1LCAyMDI0IGF0IDQ6NDLigK9QTSBTaGVuYXZhaSwgTWFudWVsICZsdDs8YSBocmVmPSJt
YWlsdG86bWFudWVsLnNoZW5hdmFpQHNhcC5jb20iPm1hbnVlbC5zaGVuYXZhaUBzYXAuY29tPC9h
PiZndDsgd3JvdGU6PGJyPjwvZGl2PjxibG9ja3F1b3RlIGNsYXNzPSJnbWFpbF9xdW90ZSIgc3R5
bGU9Im1hcmdpbjowcHggMHB4IDBweCAwLjhleDtib3JkZXItbGVmdDoxcHggc29saWQgcmdiKDIw
NCwyMDQsMjA0KTtwYWRkaW5nLWxlZnQ6MWV4Ij48ZGl2IGNsYXNzPSJtc2ctNDM3NDYwMTMyNzQx
MjU2NjYxNiI+DQoNCg0KDQoNCg0KPGRpdiBsYW5nPSJlbi1ERSIgc3R5bGU9Im92ZXJmbG93LXdy
YXA6IGJyZWFrLXdvcmQ7Ij4NCjxkaXYgY2xhc3M9Im1fLTQzNzQ2MDEzMjc0MTI1NjY2MTZXb3Jk
U2VjdGlvbjEiPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gbGFuZz0iRU4tVVMiPkhpIGV2
ZXJ5b25lLDx1PjwvdT48dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxz
cGFuIGxhbmc9IkVOLVVTIj48dT48L3U+wqA8dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJN
c29Ob3JtYWwiPjxzcGFuIGxhbmc9IkVOLVVTIj53ZSBjdXJyZW50bHkgY2FwdHVyZSB0aGUgZGIg
c2l6ZSAocGdfZGF0YWJhc2Vfc2l6ZSkgd2hpY2ggZ2l2ZXMgdGhlIOKAnERpc2sgc3BhY2UgdXNl
ZCBieSB0aGUgZGF0YWJhc2Ugd2l0aCB0aGUgc3BlY2lmaWVkIG5hbWXigJ0uIElzIGl0IHBvc3Np
YmxlIHRvIGZ1cnRoZXIgc3BsaXQgdGhpcyBkYXRhIGhvdyBtdWNoIHNwYWNlIGlzIG9jY3VwaWVk
IGJ5IGxpdmUgdHVwbGVzLCBkZWFkDQogdHVwbGVzIGFuZCBmcmVlIHNwYWNlPzx1PjwvdT48dT48
L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxzcGFuIGxhbmc9IkVOLVVTIj48
dT48L3U+wqA8dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxzcGFuIGxh
bmc9IkVOLVVTIj5XZSB3b3VsZCBsaWtlIHRvIGhhdmUgc29tZXRoaW5nIGxpa2U6PHU+PC91Pjx1
PjwvdT48L3NwYW4+PC9wPg0KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gbGFuZz0iRU4tVVMi
PkRCIFNpemU6IDIwMCBNQiwgbGl2ZSB0dXBsZSAxMDBNQiwgRGVhZCBUdXBsZTogMjAgTUIsIGZy
ZWUgc3BhY2UgODBNQjx1PjwvdT48dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3Jt
YWwiPjxzcGFuIGxhbmc9IkVOLVVTIj48dT48L3U+wqA8dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNs
YXNzPSJNc29Ob3JtYWwiPjxzcGFuIGxhbmc9IkVOLVVTIj5JcyB0aGlzIHBvc3NpYmxlPzx1Pjwv
dT48dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxzcGFuIGxhbmc9IkVO
LVVTIj48dT48L3U+wqA8dT48L3U+PC9zcGFuPjwvcD4NCjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxz
cGFuIGxhbmc9IkVOLVVTIj5CZXN0IHJlZ2FyZHMsPHU+PC91Pjx1PjwvdT48L3NwYW4+PC9wPg0K
PHAgY2xhc3M9Ik1zb05vcm1hbCI+PHNwYW4gbGFuZz0iRU4tVVMiPk1hbnVlbDx1PjwvdT48dT48
L3U+PC9zcGFuPjwvcD4NCjwvZGl2Pg0KPC9kaXY+DQoNCjwvZGl2PjwvYmxvY2txdW90ZT48L2Rp
dj4NCg==
--00000000000089b74b061d4c2681--