Find the size of a table in Postgres

You often need to know the size of a table in postgres and there is no particularly easy way to get that information directly from the psql client.

Fortunately, all of the information is in there and is available through nice simple functions.

All you have to do is pull it together and a stock query will normally do.

The below query will get you a list of all of the tables in your database, it will show you the size of the associated indexes as well as the size of the associated TOAST objects.

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total,
    pg_size_pretty(pg_relation_size(relid)) AS internal,
    pg_size_pretty(pg_table_size(relid) - pg_relation_size(relid)) AS external,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes
     FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; 

If you want the size of your database, I would tend to just do a ‘\l+’ in psql but if you want to do it in SQL for some reason then you can just do:

select pg_size_pretty(pg_database_size('n2'));

 pg_size_pretty
----------------
 446 MB
(1 row) 

And then if you want the size of all of your databases then its a simple change so that it looks like:

select name, pg_size_pretty(pg_database_size(datname)) as size
postgres-# from pg_database;
      name | size
-----------+---------
  postgres | 7079 kB
 template1 | 7079 kB
 template0 | 6969 kB
        n2 | 446 MB 

And finally, if you want a size breakdown of all of the objects in your database – so that you can see all of the indexes as well as the tables then you can find that with:

SELECT
  schema_name, rel_name, table_size,
  pg_size_pretty(table_size) AS size
FROM (
  SELECT
    nspname AS schema_name,
    relname AS rel_name,
    pg_table_size(pg_class.oid) AS table_size
  FROM pg_class, pg_namespace
  WHERE pg_class.relnamespace = pg_namespace.oid
) _
WHERE schema_name NOT LIKE 'pg_%'
      AND schema_name != 'information_schema'
ORDER BY table_size DESC;