Disk Space Does Not Give You a Warning
By the time PostgreSQL reports a disk full error, writes have stopped.
Monitoring size and growth is one of the most straightforward DBA habits — and one of the most commonly skipped. Tables grow. Indexes grow. Dead tuple bloat accumulates. WAL archives fill volumes that nobody is watching.
This script gives you a full picture: total database size, the largest tables and indexes, and the ratio of live data to dead bloat so you can see where vacuum is falling behind.
DatabaseSizeandGrowth.sql
Loading…
Reading the Results
Database Summary
| Column | What It Tells You |
|---|---|
datname | Database name |
pg_size_pretty | Total database size in human-readable format |
pg_database_size | Raw size in bytes — useful for trend tracking |
numbackends | Current active connections to this database |
Top Tables by Size
| Column | What It Tells You |
|---|---|
schemaname / tablename | Schema and table |
total_size | Table + all associated indexes and toast |
table_size | Table data only (excluding indexes) |
index_size | Total index size for the table |
live_tuples | Estimated live (visible) rows |
dead_tuples | Estimated dead rows not yet vacuumed |
bloat_ratio | Dead tuples as a percentage of total — above 20% warrants a look |
Top Indexes by Size
| Column | What It Tells You |
|---|---|
indexname | Index name |
index_size | Disk space used by this index |
idx_scan | Number of times this index has been used |
What to Watch For
| Signal | What It Means |
|---|---|
| Total size growing faster than expected | Check for missing vacuum, log tables with no retention, or batch job side effects |
High dead_tuples on a busy table | Autovacuum is not keeping up — tune autovacuum_vacuum_scale_factor for this table |
| Large indexes on rarely scanned columns | Unused indexes still cost writes and storage — consider dropping |
idx_scan = 0 on a large index | Index was never used since the last stats reset — candidate for removal |
index_size > table_size | Over-indexed table — review which indexes are actually needed |
| Many small databases each with significant size | Consolidation or archival opportunity |
Checking Size Over Time
To track growth trends, log pg_database_size() results into a monitoring table on a schedule:
CheckingSizeOverTime.sql
Loading…
Compare snapshots to project when you will hit capacity.
Gareth Winterman