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.
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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; |