PostgreSQL Disk usage Size monitoring

Table size Information

postgres=# SELECT *, Pg_size_pretty (total_bytes) as Total
postgres-#, Pg_size_pretty (index_bytes) as Index
postgres-#, Pg_size_pretty (toast_bytes) as Toast
postgres-#, Pg_size_pretty (table_bytes) as table
postgres-# from (
Postgres (# SELECT *, Total_bytes-index_bytes-coalesce (toast_bytes,0) as Table_bytes from (
Postgres (# SELECT C.oid,nspname as TABLE_SCHEMA, relname as table_name
Postgres (#, C.reltuples as Row_estimate
Postgres (#, Pg_total_relation_size (c.oid) as Total_bytes
Postgres (#, Pg_indexes_size (c.oid) as Index_bytes
Postgres (#, Pg_total_relation_size (reltoastrelid) as Toast_bytes
Postgres (# from Pg_class C
Postgres (# Left JOIN pg_namespace n on n.oid = C.relnamespace
Postgres (# WHERE relkind = ' R '
Postgres (#) A
Postgres (#) A;
OID | Table_schema | table_name | Row_estimate | Total_bytes | Index_bytes | Toast_bytes | Table_bytes | Total
| Index | Toast | Table
-------+--------------------+-------------------------+--------------+-------------+-------------+------------- +-------------+--------
----+------------+------------+------------
24933 | Public | Pgbench_tellers | 1280 | 237568 | 73728 | | 163840 | 232 KB
| KB | | KB
24939 | Public | pgbench_branches | 128 | 65536 | 16384 | | 49152 | KB
| KB | | KB
24936 | Public | pgbench_accounts | 1.28e+07 | 2009858048 | 287531008 | | 1722327040 | 1917 MB
| 274 MB | | 1643 MB
24930 | Public | Pgbench_history | 20826 | 1261568 | 0 | | 1261568 | 1232 KB
| 0 bytes | | 1232 KB
2619 | Pg_catalog | pg_statistic | 402 | 286720 | 32768 | 73728 | 180224 | 280 KB
| KB | KB | 176 KB
1247 | Pg_catalog | Pg_type | 354 | 163840 | 57344 | | 106496 | KB
| KB | | 104 KB
1260 | Pg_catalog | Pg_authid | 1 | 73728 | 32768 | | 40960 | KB
| KB | | KB
1418 | Pg_catalog | pg_user_mapping | 0 | 16384 | 16384 | | 0 | KB
| KB | | 0 bytes
2613 | Pg_catalog | Pg_largeobject | 0 | 8192 | 8192 | | 0 | 8192 by
TES | 8192 bytes | | 0 bytes
1249 | Pg_catalog | Pg_attribute | 2399 | 598016 | 188416 | | 409600 | 584 KB
| 184 KB | | KB
1255 | Pg_catalog | Pg_proc | 2821 | 958464 | 327680 | 8192 | 622592 | 936 KB

The largest database

postgres=# SELECT D.datname as Name, Pg_catalog.pg_get_userbyid (D.DATDBA) as Owner,
postgres-# case when Pg_catalog.has_database_privilege (D.datname, ' CONNECT ')
postgres-# then Pg_catalog.pg_size_pretty (pg_catalog.pg_database_size (d.datname))
postgres-# ELSE ' No Access '
postgres-# END as SIZE
postgres-# from Pg_catalog.pg_database D
postgres-# ORDER by
postgres-# case when Pg_catalog.has_database_privilege (D.datname, ' CONNECT ')
postgres-# then Pg_catalog.pg_database_size (d.datname)
postgres-# ELSE NULL
postgres-# END DESC--nulls first
postgres-# LIMIT 20
postgres-#;
name | Owner | Size
--------------+--------------+---------
Benchmarksql | Benchmarksql | Ten GB
Postgres | Postgres | 1925 MB
Pg_monitor | Postgres | 7816 KB
Scott | Scott | 7632 KB
Cott | Cott | 7496 KB
template1 | Postgres | 7496 KB
Mgt | Mgt | 7496 KB
Template0 | Postgres | 7129 KB
(8 rows)

The largest table

postgres=# SELECT Nspname | | '. ' | | Relname as "relation",
postgres-# Pg_size_pretty (Pg_relation_size (c.oid)) as "size"
postgres-# from Pg_class C
postgres-# left JOIN pg_namespace N on (n.oid = c.relnamespace)
postgres-# WHERE Nspname not in (' Pg_catalog ', ' information_schema ')
postgres-# ORDER by Pg_relation_size (c.oid) DESC
postgres-# LIMIT 20;
Relation | Size
------------------------------+------------
public.pgbench_accounts | 1642 MB
Public.pgbench_accounts_pkey | 274 MB
Public.pgbench_history | 1208 KB
pg_toast.pg_toast_2618 | 376 KB
Public.pgbench_tellers | KB
Public.pgbench_tellers_pkey | KB
pg_toast.pg_toast_2619 | KB
Pg_toast.pg_toast_2618_index | KB
Public.pgbench_branches_pkey | KB
public.pgbench_branches | KB
Pg_toast.pg_toast_2619_index | KB
Pg_toast.pg_toast_2396_index | 8192 bytes
Pg_toast.pg_toast_2606_index | 8192 bytes
Pg_toast.pg_toast_2609_index | 8192 bytes
Pg_toast.pg_toast_2964_index | 8192 bytes
Pg_toast.pg_toast_2620_index | 8192 bytes
Pg_toast.pg_toast_2604_index | 8192 bytes
Pg_toast.pg_toast_1255_index | 8192 bytes
Pg_toast.pg_toast_3596_index | 8192 bytes
Pg_toast.pg_toast_3592_index | 8192 bytes
(rows)

Total size of table

postgres=# SELECT Nspname | | '. ' | | Relname as "relation",
postgres-# Pg_size_pretty (Pg_total_relation_size (c.oid)) as "Total_size"
postgres-# from Pg_class C
postgres-# left JOIN pg_namespace N on (n.oid = c.relnamespace)
postgres-# WHERE Nspname not in (' Pg_catalog ', ' information_schema ')
postgres-# and C.relkind <> ' i '
postgres-# and Nspname!~ ' ^pg_toast '
postgres-# ORDER by Pg_total_relation_size (c.oid) DESC
postgres-# LIMIT 20;
Relation | Total_size
---------------------------+------------
public.pgbench_accounts | 1917 MB
Public.pgbench_history | 1232 KB
Public.pgbench_tellers | 232 KB
public.pgbench_branches | KB
public.pg_stat_statements | 0 bytes
Public.pg_buffercache | 0 bytes
(6 rows)

Ref:https://wiki.postgresql.org/wiki/disk_usage

PostgreSQL disk use size monitoring