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.
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:
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:
schema_name, rel_name, table_size,
pg_size_pretty(table_size) AS size
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;