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(Coid)) AS “total_size”
FROM pg_class C
LEFT JOIN pg_namespace N ON (Noid = Crelnamespace)
WHERE nspname NOT IN (‘pg_catalog’, ‘information_schema’)
AND Crelkind <> ‘i’
AND nspname! ~ ‘^ pg_toast’
ORDER BY pg_total_relation_size(Coid) DESC
LIMIT twenty;

Similar Posts

Leave a Reply