You want a quick “which tables are eating my disk?” overview — usually before a vacuum, an archive purge, or a capacity-planning conversation. Here’s a one-shot query that joins information_schema.tables against pg_class to show every public table with its estimated row count and size:
1 2 3 4 5 6 7 8 | SELECT ist.table_name, reltuples AS "entries", pg_size_pretty(relpages::BIGINT * 8 * 1024) AS SIZE FROM information_schema.tables ist INNER JOIN pg_class c ON (ist.table_name = c.relname) WHERE ist.table_catalog = current_database() AND ist.table_schema = 'public' ORDER BY relpages DESC, ist.table_name ASC; |
The columns: reltuples is the planner’s row-count estimate (updated by ANALYZE / autovacuum), and relpages * 8 KB is the table’s heap size — Postgres pages are 8 KB by default. Tables come out biggest-first.
A few useful additions.
relpages is heap-only — there’s a built-in for the full picture. The query above only counts the main table heap. It does not include indexes, TOAST tables (where wide values overflow), or the FSM/VM bookkeeping. For “how big is this table really, on disk?” use pg_total_relation_size:
1 2 3 4 5 6 7 8 9 10 11 | SELECT n.nspname AS schema, c.relname AS TABLE, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS heap_size, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size, c.reltuples::BIGINT AS estimated_rows 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; |
Three sizes side by side:
- pg_relation_size — the main table heap only (what the original query approximates)
- pg_indexes_size — every index on the table, summed
- pg_total_relation_size — heap + indexes + TOAST + everything else attached to the relation
The gap between heap_size and total_size is often surprisingly large — a write-heavy table with several indexes can carry 3–5x its heap size in indexes. That’s where you start looking for indexes you don’t actually use (pg_stat_user_indexes with idx_scan = 0).
reltuples is an estimate, not a count. The number can be stale, and on a brand-new table it may even be -1 until ANALYZE runs. If you need the true row count, use SELECT count(*) FROM mytable — but that’s an exact scan, so reach for it only when you really need exact, on smaller tables. For a quick directional answer, reltuples is fine.
Skip the SQL entirely with \dt+ in psql. Inside psql, the meta-command \dt+ shows every table in the current schema along with its size:
1 | psql -d mydb -c "\dt+" |
Quick, readable, no joins to write. \dt+ schema.* for a specific schema; \di+ for indexes. 📐