PostgreSQL Object Size
From Wikistix
SQL to find the sizes of objects in PostgreSQL (postgres), with the following example taken from a mediawiki database. The `mib` (MiB) column is calculated with PostgreSQL's configured page size.
wikidb=# select t2.nspname as nspname, t1.relname, t1.relfilenode, t1.relpages, t1.relkind, t1.relpages * cast(current_setting('block_size') as bigint) / 2^20 as mib from pg_class t1, pg_namespace t2 where t1.relnamespace = t2.oid order by relpages desc limit 10;
nspname | relname | relfilenode | relpages | relkind | mib
------------+-------------------------+-------------+----------+---------+-----------
pg_toast | pg_toast_16958 | 16961 | 562 | t | 4.390625
mediawiki | objectcache_keyname_key | 18391 | 258 | i | 2.015625
mediawiki | text | 16958 | 196 | r | 1.53125
pg_catalog | pg_attribute | 0 | 99 | r | 0.7734375
pg_catalog | pg_proc | 0 | 98 | r | 0.765625
mediawiki | objectcacache_exptime | 18506 | 68 | i | 0.53125
pg_toast | pg_toast_2618 | 2838 | 65 | t | 0.5078125
pg_catalog | pg_statistic | 2619 | 58 | r | 0.453125
mediawiki | ts2_page_text | 18568 | 47 | i | 0.3671875
pg_catalog | pg_description | 2609 | 45 | r | 0.3515625
(10 rows)
