Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
PostgreSQL: Show size of all databases

PostgreSQL: Show size of all databases

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

To get more details, call:

   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

It will show 3 columns:

Table – The name of the table Size – The total size that this table takes External Size – The size that related objects of this table like indices take If you want to dig in deeper and see the size of all objects (at least of 64kB):

   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;

This will show 4 columns:

objectname – The name of the object

objecttype – r for the table, i for an index, t for toast data, ...

#entries – The number of entries in the object (e.g. rows)

size – The size of the object

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.