Size of User Tables
=# SELECT
relname as "Table",
pg_size_pretty(pg_total_relation_size(relid)) as "Total Size",
pg_size_pretty(pg_relation_size(relid)) as "Relation Size",
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC
;
Table | Total Size | Relation Size | External Size
------------------+------------+---------------+---------------
load_test | 1883 MB | 1776 MB | 107 MB
pgbench_accounts | 1121 MB | 961 MB | 161 MB
pgbench_history | 8656 kB | 8656 kB | 0 bytes
pgbench_tellers | 80 kB | 40 kB | 40 kB
pgbench_branches | 24 kB | 8192 bytes | 16 kB
(5 rows)
Size of User Indexes
=# SELECT
indexrelname as "Index",
pg_size_pretty(pg_total_relation_size(indexrelid)) as "Size",
pg_size_pretty(pg_total_relation_size(indexrelid) - pg_relation_size(indexrelid)) as "External Size"
FROM pg_catalog.pg_statio_user_indexes ORDER BY pg_total_relation_size(indexrelid) DESC;
Index | Size | External Size
-----------------------+--------+---------------
pgbench_accounts_pkey | 161 MB | 0 bytes
load_testidx | 107 MB | 0 bytes
pgbench_tellers_pkey | 40 kB | 0 bytes
pgbench_branches_pkey | 16 kB | 0 bytes
(4 rows)
Functions
pg_relation_size(oid) - returns disk space occupied by the table or index of the given oid.pg_total_relation_size(oid) - returns total disk space of the table of given oid, including indexes and toasted data
