Hosting Operations

Show PostgreSQL Database Sizes

Disk usage is rising and you need a quick database-level size breakdown.

Command

psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"

What changed

Nothing changes. PostgreSQL prints database names and sizes.

Danger

safe

When to use it

Use when storage alerts fire or backups suddenly take longer.

When not to use it

Do not use database size alone to identify the exact table or index responsible.

Undo or recovery

No undo needed because this command is read-only.

Expected output

A sorted list of database names and human-readable sizes.

demo script

Disposable terminal steps

df -h /var/lib/postgresql
psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"
psql -X -c "select datname from pg_database order by datname;"

Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel

::fixture-ready::
$ df -h /var/lib/postgresql
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   27G   11G  72% /var/lib/postgresql
::exit-code::0
$ psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"
 datname  |  size
----------+--------
 app_prod | 384 MB
 app_test | 42 MB
 postgres | 11 MB
(3 rows)
::exit-code::0
$ psql -X -c "select datname from pg_database order by datname;"
 datname
----------
 app_prod
 app_test
 postgres
(3 rows)
::exit-code::0

YouTube Short

Find the biggest Postgres DB.

When disk alerts fire, start with database sizes before hunting individual tables.

experiments

A/B tests to run

Metric: linkedin_click_through_rate

A: Disk pressure starts with knowing what grew.

B: Find the biggest Postgres database first.