Skip to content

Instantly share code, notes, and snippets.

Embed
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;
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

To get more details, call:

SELECT
   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):

SELECT
   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

Refs: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database
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.