PostgreSQL query to determine the largest tables in the database

17 May 2011 · 3 min read

This is just a handy query I use from time to time to see which tables are growing madly, and how much of the growth is index bloat.

select
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as size_p,
pg_total_relation_size(schemaname || '.' || tablename) as siz,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size_p,
pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename) as index_size,
(100*(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)))/case when pg_total_relation_size(schemaname || '.' || tablename) = 0 then 1 else pg_total_relation_size(schemaname || '.' || tablename) end || '%' as index_pct
from pg_tables
order by siz desc limit 20;

This returns schema, table, size on disk (in human-readable and byte format - for sorting), and total size on disk including indices, and the percentage of the total size that comprises the indexes.

Sample result from our OpenFire Jabber server:

schemaname |      tablename       |   size_p   |    siz    | total_size_p | index_size | index_pct
------------+----------------------+------------+-----------+--------------+------------+-----------
 public     | ofconversation       | 71 MB      | 159236096 | 152 MB       |   84623360 | 53%
 public     | ofconparticipant     | 38 MB      |  95395840 | 91 MB        |   55394304 | 58%
 public     | ofpresence           | 10184 kB   |  10452992 | 10208 kB     |      24576 | 0%
 public     | ofpubsubitem         | 5336 kB    |   7733248 | 7552 kB      |    2269184 | 29%
 public     | ofid                 | 4008 kB    |   4120576 | 4024 kB      |      16384 | 0%
 public     | ofpubsubsubscription | 912 kB     |   1458176 | 1424 kB      |     524288 | 35%
 public     | ofpubsubaffiliation  | 728 kB     |   1196032 | 1168 kB      |     450560 | 37%
 public     | ofoffline            | 832 kB     |    942080 | 920 kB       |      90112 | 9%
 pg_catalog | pg_depend            | 320 kB     |    794624 | 776 kB       |     466944 | 58%
 pg_catalog | pg_attribute         | 384 kB     |    761856 | 744 kB       |     368640 | 48%
 pg_catalog | pg_proc              | 376 kB     |    753664 | 736 kB       |     368640 | 48%
 pg_catalog | pg_rewrite           | 72 kB      |    270336 | 264 kB       |     196608 | 72%
 pg_catalog | pg_description       | 136 kB     |    245760 | 240 kB       |     106496 | 43%
 pg_catalog | pg_operator          | 112 kB     |    237568 | 232 kB       |     122880 | 51%
 pg_catalog | pg_class             | 88 kB      |    212992 | 208 kB       |     122880 | 57%
 pg_catalog | pg_type              | 64 kB      |    155648 | 152 kB       |      90112 | 57%
 pg_catalog | pg_statistic         | 72 kB      |     98304 | 96 kB        |      24576 | 25%
 pg_catalog | pg_amop              | 24 kB      |     90112 | 88 kB        |      65536 | 72%
 pg_catalog | pg_conversion        | 16 kB      |     90112 | 88 kB        |      73728 | 81%
 pg_catalog | pg_constraint        | 8192 bytes |     81920 | 80 kB        |      73728 | 90%
(20 rows)

Time: 8.917 ms