PostgreSQL – how to check database sizes
Checking database sizes in PostgreSQL.
Find out the size of the database
We go to the postgres shell on the server:
Learn more about postgres commands here.
We check the size of the specified database:
SELECT pg_size_pretty (pg_database_size (‘database_name’));
Find out the sizes of tables in the database
Being in the postgres shell, enter the database we need:
We enter the command to sort the tables by the largest size:
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 twenty;