Top useful SQL queries for PostgreSQL




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.

Before you start: connect with a superuser or a role that has the pg_monitor role. Without it, other users' query text in pg_stat_activity is hidden. Queries are written for PostgreSQL 13 and later. The full set is also available as a single .sql file (link at the end).

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

MetricHealthyInvestigate when
Cache hit ratio (OLTP)99% or higherBelow 95%
ConnectionsBelow 80% of max_connectionsAbove 90%, or many idle in transaction
Transaction ageSeconds to minutesHours
Dead tuplesBelow 10–20% of the tableAbove 20% with old last_autovacuum
XID ageBelow 200 million (default freeze age)Above 1 billion
Unused indexesNone on large tablesLarge 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