Read more

PostgreSQL: How to show table sizes

Arne Hartherz
April 13, 2018Software engineer at makandra GmbH

When you have a large PG database, you may want to find out which tables are consuming the most disk space.
You can easily check this using the following SQL statement from the PostgreSQL wiki.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 5;
Illustration UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
Read more Show archive.org snapshot

Example output:

       relation       | total_size 
----------------------+------------
 public.snapshots     | 823 MB
 public.invoice_items | 344 MB
 public.messages      | 267 MB
 public.topics        | 40 MB
 public.invoices      | 35 MB
(5 rows)

See the PostgreSQL wiki Data Usage Show archive.org snapshot page for more information on calculating sizes of databases or tables.

Connect to a Rails database with bin/rails dbconsole -p.

Posted by Arne Hartherz to makandra dev (2018-04-13 11:35)