Largest tables on disk (PostgreSQL)

PostgreSQLRead onlyScripts Gallery

The tables that take the most disk space, indexes and TOAST data included.

Verification status. Written from the BroadSQL documentation and the PostgreSQL documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

Find what fills a disk.
Check the effect of a purge or a migration.

The Script

Download pg-largest-tables.sql

-- @description: Largest tables of the database, with their indexes and TOAST data (PostgreSQL)
-- @tags: postgresql, diagnostics, size, disk
-- @status: draft
-- @access: read
-- @dialect: PostgreSQL
-- @param top INTEGER = 20 "Number of tables listed"
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_total_relation_size(c.oid) AS total_bytes
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relkind = 'r'
   AND n.nspname NOT IN ('pg_catalog', 'information_schema')
 ORDER BY pg_total_relation_size(c.oid) DESC
 LIMIT ${top};

Parameters

Parameter	Type	Default	Meaning
`top`	`INTEGER`	`20`	Number of tables listed

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/pg-largest-tables.sql.
Connect to the PostgreSQL database to inspect.
Check what it declares: LIB SHOW gallery/pg-largest-tables.sql;
Run it: @gallery/pg-largest-tables.sql top=10;

What you get

The top largest tables, with a readable size (pg_size_pretty) and the size in bytes for sorting or export.

Limits

System schemas (pg_catalog, information_schema) are excluded.
Written from the PostgreSQL administration functions documentation.

Found something wrong or unclear on this page? Tell us, with the page address.