Table sizes in PostgreSQL

Ever wanted to find out how much diskspace each table was taking in a database? Here’s how:

database=# SELECT
   tablename,
   pg_size_pretty(pg_relation_size(tablename)) AS table_size,
   pg_size_pretty(pg_total_relation_size(tablename)) AS total_table_size
FROM
   pg_tables
WHERE
   schemaname = 'public';
 tablename  | table_size | total_table_size
------------+------------+------------------
 deferrals  | 205 MB     | 486 MB
 errors     | 58 MB      | 137 MB
 deliveries | 2646 MB    | 10096 MB
 queue      | 7464 kB    | 22 MB
 unknown    | 797 MB     | 2644 MB
 messages   | 1933 MB    | 6100 MB
 rejects    | 25 GB      | 75 GB
(7 rows)

Table size is the size for the current data. Total table size includes indexes and data that is too large to fix in the main table store (things like large BLOB fields). You can find more information in the PostgreSQL manual.

Edit: changed to use pg_size_pretty(), which I thought existed, but couldn’t find in the docs. Brett Parker reminded me it did exist after all and I wasn’t just imagining it.