Page tree
Skip to end of metadata
Go to start of metadata

To get an overview about how much space is taken by what database, call:

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
  • objecttyper 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

What are 'toast data'?

From the documentation:

PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or "the best thing since sliced bread"). [...]

  • No labels