PostgreSQL ships with a rich set of statistics views, but knowing which view answers which question takes time to learn. Whether the database is slow, a disk is filling up, or the application team says "the database is locked", the first step is the same: look at the right numbers.
This post collects the queries I keep in my own toolkit, grouped by the question they answer. Every query is read-only and safe to run on production.
1. Database and object sizes
1. Database sizes
Lists every user database with its size on disk, largest first. Template databases are excluded.
SELECT datname AS database,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE NOT datistemplate
ORDER BY pg_database_size(datname) DESC;2. Tablespace sizes
Useful when data is spread across several disks. pg_global is skipped because it holds only shared system catalogs.
SELECT spcname AS tablespace,
pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace
WHERE spcname <> 'pg_global';3. Schema sizes (table vs index)
Splits each schema into table data and index data, so you can see where the space really goes. TOAST data is included in the total.
SELECT n.nspname AS schema,
pg_size_pretty(SUM(pg_relation_size(c.oid))) AS table_size,
pg_size_pretty(SUM(pg_indexes_size(c.oid))) AS index_size,
pg_size_pretty(SUM(pg_total_relation_size(c.oid))) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m')
AND n.nspname NOT IN ('pg_catalog','information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY n.nspname
ORDER BY SUM(pg_total_relation_size(c.oid)) DESC;4. Top 20 largest tables
The tables to watch for growth, with live row estimates from the statistics collector.
SELECT schemaname,
relname AS table_name,
pg_size_pretty(pg_relation_size(relid)) AS table_size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
n_live_tup AS est_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;2. Sessions, locks and transactions
5. Connections vs max_connections
One row that tells you how close you are to the connection limit, and how many sessions are sitting idle in transaction.
SELECT count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
current_setting('max_connections')::int AS max_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend';6. Long-running transactions
Old open transactions hold locks and stop VACUUM from cleaning up dead rows. Anything measured in hours deserves a closer look.
SELECT pid,
usename,
datname,
state,
now() - xact_start AS txn_age,
now() - query_start AS query_age,
left(query, 100) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 20;7. Blocked and blocking sessions
Uses pg_blocking_pids() (9.6+) to show exactly who is waiting on whom. This is far more reliable than self-joining pg_locks.
SELECT blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
now() - blocked.query_start AS waiting_for,
left(blocked.query, 80) AS blocked_query,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.state AS blocking_state,
left(blocking.query, 80) AS blocking_query
FROM pg_stat_activity blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
ORDER BY waiting_for DESC;8. Deadlock counter
A real deadlock is resolved automatically by PostgreSQL, which cancels one of the transactions. This counter (since the last stats reset) tells you how often that happens.
SELECT datname, deadlocks, stats_reset FROM pg_stat_database WHERE datname IS NOT NULL ORDER BY deadlocks DESC;
3. Performance indicators
9. Cache hit ratio per database
The share of block reads served from shared buffers instead of disk. For OLTP workloads aim for 99% or more.
SELECT datname,
round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY cache_hit_pct;10. Index cache hit ratio
The same idea, measured on index blocks only.
SELECT round(100.0 * SUM(idx_blks_hit)
/ NULLIF(SUM(idx_blks_hit) + SUM(idx_blks_read), 0), 2) AS index_hit_pct
FROM pg_statio_user_indexes;11. Tables with heavy sequential scans
Large tables that are mostly read by sequential scan are the best candidates for a new index, or for a look at the queries hitting them.
SELECT schemaname,
relname,
seq_scan,
COALESCE(idx_scan, 0) AS idx_scan,
round(100.0 * COALESCE(idx_scan, 0)
/ NULLIF(seq_scan + COALESCE(idx_scan, 0), 0), 1) AS idx_scan_pct,
n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY seq_scan DESC
LIMIT 20;12. Unused indexes
Indexes that have never been scanned still cost space and slow down every write. Primary keys and unique indexes are excluded because they enforce constraints.
SELECT s.schemaname,
s.relname AS table_name,
s.indexrelname AS index_name,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
AND NOT i.indisunique
AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;13. Top 10 queries by total execution time
Needs the pg_stat_statements extension. Column names are for PostgreSQL 13 and later.
SELECT queryid,
calls,
round(total_exec_time::numeric, 2) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
round(100.0 * shared_blks_hit
/ NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;4. Vacuum and maintenance health
14. Dead tuples and last vacuum
A quick bloat indicator: many dead rows and an old last_autovacuum mean autovacuum is not keeping up with that table.
SELECT schemaname,
relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_vacuum,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;15. Transaction ID wraparound risk
Shows how far each database is from the 2-billion transaction ID limit. Autovacuum freezes rows long before that, but a rising age means something is blocking it.
SELECT datname,
age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;16. Replication lag (run on the primary)
For streaming replication: how far each standby is behind, in bytes and in time.
SELECT application_name,
client_addr,
state,
sync_state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_size,
replay_lag
FROM pg_stat_replication;
5. Taking action safely
Finding a blocking session is only half the job. PostgreSQL gives you two levels of intervention:
-- Gentle: cancel only the running query, keep the connection SELECT pg_cancel_backend(<blocking_pid>); -- Firm: end the whole session (rolls back its open transaction) SELECT pg_terminate_backend(<blocking_pid>);
Always try pg_cancel_backend first. Note that a session in idle in transaction has no running query to cancel, so it can only be cleared with pg_terminate_backend. Check with the application owner before terminating sessions in production.
To stop idle transactions building up in the first place, set idle_in_transaction_session_timeout (for example '10min') at the database or role level.
For exact bloat figures instead of the dead-tuple estimate in query 14, use the pgstattuple extension. To actually reclaim space, use VACUUM to make it reusable, or pg_repack to give it back to the OS without a long exclusive lock.
Quick reference: what "healthy" looks like
| Metric | Healthy | Investigate when |
|---|---|---|
| Cache hit ratio (OLTP) | 99% or higher | Below 95% |
| Connections | Below 80% of max_connections | Above 90%, or many idle in transaction |
| Transaction age | Seconds to minutes | Hours |
| Dead tuples | Below 10–20% of the table | Above 20% with old last_autovacuum |
| XID age | Below 200 million (default freeze age) | Above 1 billion |
| Unused indexes | None on large tables | Large indexes with idx_scan = 0 over weeks |
These are starting points, not hard rules. A reporting database can live happily with a lower cache hit ratio than an OLTP system.
Conclusion
You do not need a full monitoring stack to understand the state of a PostgreSQL database. A handful of well-chosen queries against pg_stat_activity, pg_stat_user_tables and pg_stat_statements answer most day-to-day questions. Save them, run them regularly, and you will notice problems long before your users do.
Which query do you run first when something looks wrong? Share it in the comments.
Tamilselvan Perumal · Lead Product Engineer & Senior DBA
