DBAtlas · Database operations atlas

List table size broken down into heap / index / TOAST, and the largest indexes

PostgreSQLStorage

Command

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid) - pg_relation_size(c.oid) - pg_indexes_size(c.oid)) AS toast_fsm_vm,
       round(100.0 * pg_total_relation_size(c.oid) / NULLIF(pg_database_size(current_database()), 0), 1) AS pct_of_db
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- Largest indexes and their usage counts
SELECT s.schemaname,
       s.relname AS table_name,
       s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan,
       i.indisunique,
       i.indisprimary
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 20;

What it does

Shows the largest tables broken down into total, heap, index and TOAST (+FSM/VM) with their share of the database; and the largest indexes with scan counts and unique/primary flags.

When to use it

To find where growth comes from (data, indexes or TOAST) as disk usage rises, and to spot large unused indexes.

Caution

Size functions touch the file system for every relation and can be slow in databases with tens of thousands of tables. A partitioned parent (relkind 'p') is 0 bytes itself and is excluded; for the total of a hierarchy: SELECT pg_size_pretty(sum(pg_total_relation_size(relid))) FROM pg_partition_tree('sales'); Large indexes with idx_scan = 0 are drop candidates, but counters cover only the time since the last statistics reset and only this server (usage on replicas is not visible); unique/primary indexes enforce constraints and are not dropped.

Open in DBAtlas →

Related