radek.cc

psql from PostgreSQL 8.4.0 contains an useful addition: \d+ shows table sizes. That’s handy, but:

• doesn’t help much with 8.3 and 8.2 databases,
• doesn’t let me run aggregates on these,
• the view is cluttered by table comments,
• can’t be sorted by size.

So, my ~/.psqlrc contains these two lines:

$ tail -n2 .psqlrc
\set rtsize '(select table_schema, table_name, pg_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) as size, pg_total_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) as total_size  from information_schema.tables where table_type = \'BASE TABLE\' and table_schema not in (\'information_schema\', \'pg_catalog\') order by pg_relation_size( quote_ident( table_schema ) || \'.\' || quote_ident( table_name ) ) desc, table_schema, table_name)'
\set tsize '(select table_schema, table_name, pg_size_pretty(size) as size, pg_size_pretty(total_size) as total_size from (:rtsize) x order by x.size desc, x.total_size desc, table_schema, table_name)'

Both queries return the same data; “:rtsize” outputs raw numbers (mnemonic: raw table size), and “:tsize” formats them with pg_size_pretty().  Usage and the benefits will be best illustrated by examples; this one shows the 5 largest tables:

redmine=# :tsize limit 5;
 table_schema |      table_name       |  size  | total_size
--------------+-----------------------+--------+------------
 public       | journals              | 192 kB | 360 kB
 public       | issues                | 128 kB | 248 kB
 public       | wiki_content_versions | 48 kB  | 304 kB
 public       | journal_details       | 48 kB  | 152 kB
 public       | attachments           | 32 kB  | 88 kB
(5 rows)

Sum of table sizes from the “log” schema, without pricing-related tables; note the “x” after “:rtsize” — since the macro is expanded to “( select ... )“, it has to be aliased:

msawi11=# select pg_size_pretty( sum(size)::int ), pg_size_pretty( sum(total_size)::int )
          from :rtsize x where table_schema='log' and table_name !~ 'ceny';
 pg_size_pretty | pg_size_pretty
----------------+----------------
 18 MB          | 32 MB
(1 row)

Useful.

It’s a pity PostgreSQL doesn’t come with such views in the standard installation; psql’s code would have been much simpler, some incompatibilities of \X commands could have been avoided, and other client application developers wouldn’t have to copy/paste code from “psql -E” output…

Update: more ~/.psqlrc tricks.