How to get table, index and database sizes in PostgreSQL
The query everyone actually wants — the biggest tables, indexes included — then the three size functions and what each one really measures.
-- Le 20 tabelle più grandi (indici e TOAST inclusi):
SELECT c.oid::regclass AS table_name,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
pg_size_pretty(pg_table_size(c.oid)) AS table_only,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;The three size functions, disambiguated
| Function | What it measures |
|---|---|
pg_relation_size('t') | The main heap only — no indexes, no TOAST (the out-of-line storage for large values). |
pg_table_size('t') | The table proper: heap + TOAST + auxiliary maps. Still no indexes. |
pg_total_relation_size('t') | Everything: table + TOAST + all its indexes. This is "how much disk does this table cost me". |
Wrap any of them in pg_size_pretty(...) for human units. A table with long text/JSONB columns can have most of its bytes in TOAST — if pg_table_size dwarfs pg_relation_size, that's where they are.
More one-liners
-- Dimensione di una singola tabella / indice / database:
SELECT pg_size_pretty(pg_total_relation_size('public.orders'));
SELECT pg_size_pretty(pg_relation_size('orders_created_at_idx'));
SELECT pg_size_pretty(pg_database_size(current_database()));
-- Tutti i database del cluster:
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
-- Gli indici più grandi:
SELECT c.oid::regclass AS index_name,
pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind = 'i'
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 20;Size on disk ≠ live data
These functions measure allocated disk, which includes bloat: space occupied by dead row versions that vacuum hasn't reclaimed (and that plain vacuum returns to the table, not to the OS). A 50 GB table might hold 20 GB of live rows. If a table looks far too big for its row count, that's a bloat investigation, not a storage upgrade.
Partitioned tables: the parent itself is empty — sum the partitions, e.g. joining pg_inherits, or eyeball them with \d+ parent_name in psql.