Monitoring PostgreSQL performance metrics makes it possible to separate slow-query problems from lock contention, I/O stalls, and connection exhaustion before a minor slowdown turns into an outage.
PostgreSQL maintains cumulative runtime statistics through system views such as pg_stat_activity, pg_stat_database, and pg_stat_user_tables, and exposes current lock state through pg_locks. Reading these views is non-destructive and surfaces session wait events, cache-hit behavior, temporary-file usage, autovacuum activity, and blocking relationships.
Visibility depends on server version and privileges, and many counters reset on restart or explicit resets, so comparing snapshots taken under similar load is often more useful than a single read. Commands below assume local access via the postgres OS account and a target database named appdb, and require a role able to read other sessions (superuser or pg_read_all_stats on modern versions).
Related: How to enable pg_stat_statements in PostgreSQL \\
Related: How to optimize PostgreSQL performance
Steps to monitor PostgreSQL performance metrics:
- List long-running non-idle sessions with their wait information.
$ sudo -u postgres psql -d appdb -c "SELECT pid, usename, datname, application_name, client_addr, state, now() - query_start AS query_age, wait_event_type, wait_event, left(query,120) AS query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_age DESC NULLS LAST LIMIT 20;" pid | usename | datname | application_name | client_addr | state | query_age | wait_event_type | wait_event | query -----+----------+---------+------------------+-------------+--------+-----------+-----------------+------------+-------------------------------------------------------------------------------------------------------------------------- 412 | postgres | appdb | psql | | active | 00:00:00 | | | SELECT pid, usename, datname, application_name, client_addr, state, now() - query_start AS query_age, wait_event_type, w (1 row)
wait_event_type and wait_event indicate what the backend is waiting on when not running on CPU.
- Summarize non-idle sessions by wait event to reveal the current bottleneck.
$ sudo -u postgres psql -d appdb -c "SELECT COALESCE(wait_event_type, 'CPU') AS wait_type, COALESCE(wait_event, 'On CPU') AS wait_event, count(*) AS sessions FROM pg_stat_activity WHERE state <> 'idle' GROUP BY 1,2 ORDER BY sessions DESC, wait_type, wait_event LIMIT 20;" wait_type | wait_event | sessions -----------+------------+---------- CPU | On CPU | 1 (1 row)
A sudden rise in Lock waits is often faster to diagnose than chasing query timeouts symptom-by-symptom.
- Check database cache hit ratio to gauge read pressure.
$ sudo -u postgres psql -d appdb -c "SELECT datname, blks_hit, blks_read, round(blks_hit*100.0/nullif(blks_hit+blks_read,0),2) AS cache_hit_pct FROM pg_stat_database WHERE datname NOT IN ('template0','template1') ORDER BY cache_hit_pct ASC LIMIT 20;" datname | blks_hit | blks_read | cache_hit_pct ----------+----------+-----------+--------------- postgres | 4065 | 175 | 95.87 appdb | 1023043 | 1763 | 99.83 (2 rows)Cache-hit ratios are most meaningful as a trend over time, not as a single point-in-time number.
- Review temporary file usage to spot sorts spilling to disk.
$ sudo -u postgres psql -d appdb -c "SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_bytes FROM pg_stat_database WHERE datname NOT IN ('template0','template1') ORDER BY temp_bytes DESC NULLS LAST LIMIT 20;" datname | temp_files | temp_bytes ----------+------------+------------ appdb | 1 | 1367 kB postgres | 0 | 0 bytes (2 rows)Rising temp_bytes typically indicates sorts or hashes spilling to disk because available memory is insufficient for the workload.
- Review cumulative I/O timing counters to detect storage stalls.
$ sudo -u postgres psql -d appdb -c "SELECT datname, round(blk_read_time::numeric,2) AS read_ms, round(blk_write_time::numeric,2) AS write_ms FROM pg_stat_database WHERE datname NOT IN ('template0','template1') ORDER BY (blk_read_time + blk_write_time) DESC NULLS LAST LIMIT 20;" datname | read_ms | write_ms ----------+---------+---------- postgres | 0.00 | 0.00 appdb | 0.00 | 0.00 (2 rows)blk_read_time and blk_write_time stay at 0 unless track_io_timing is enabled.
- Show blocked sessions with their blocking PIDs.
$ sudo -u postgres psql -d appdb -c "SELECT a.pid AS blocked_pid, pg_blocking_pids(a.pid) AS blocker_pids, now() - a.query_start AS blocked_age, a.usename, a.datname, left(a.query,120) AS blocked_query FROM pg_stat_activity a WHERE coalesce(array_length(pg_blocking_pids(a.pid),1),0) > 0 ORDER BY blocked_age DESC LIMIT 20;" blocked_pid | blocker_pids | blocked_age | usename | datname | blocked_query -------------+--------------+-------------+---------+---------+--------------- (0 rows)
Inspect the blocker by querying pg_stat_activity for the returned blocker_pids.
- Inspect dead tuple growth to confirm autovacuum keeps up.
$ sudo -u postgres psql -d appdb -c "SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup*100.0/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;" relname | n_live_tup | n_dead_tup | dead_pct | last_autovacuum | last_autoanalyze -----------+------------+------------+----------+-------------------------------+------------------------------- events | 5000 | 0 | 0.00 | 2025-12-24 13:45:09.130331+00 | 2025-12-24 13:45:09.149926+00 customers | 1000 | 0 | 0.00 | | 2025-12-24 13:45:09.090383+00 orders | 100000 | 0 | 0.00 | 2025-12-24 13:45:09.129112+00 | (3 rows)
Related: How to tune autovacuum in PostgreSQL
- Compare current connection usage to max_connections.
$ sudo -u postgres psql -d appdb -c "SELECT current_setting('max_connections')::int AS max_connections, count(*) AS current_connections, round(count(*)*100.0/current_setting('max_connections')::int,2) AS pct_used FROM pg_stat_activity;" max_connections | current_connections | pct_used -----------------+---------------------+---------- 120 | 6 | 5.00 (1 row)
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
