Admin and Inspection Queries

Overview

• Purpose: Copy-paste SQL for “what is the database doing right now?” — sessions, slow queries, locks, sizes, index usage, vacuum health
• Main Sources: pg_stat_activity, pg_locks, pg_stat_statements, pg_stat_user_tables, pg_stat_user_indexes, size functions
• Permissions: Superuser or the pg_monitor role sees everything; others see only their own sessions’ query text
• Related: Indexes and Query Performance, Transactions and Locking, Database Connection Pooling, Replication Postgres Example

Sessions: pg_stat_activity

Key Columns

Column	Meaning
pid	Backend process ID — used to cancel/terminate
state	active, idle, idle in transaction, idle in transaction (aborted)
query	Current (or last, if idle) statement text
query_start / xact_start	When the statement / transaction began
wait_event_type / wait_event	What it is waiting on (Lock, IO, Client, LWLock)
backend_type	client backend, autovacuum worker, walsender, …

-- What's running right now, longest first
SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - query_start                AS running_for,
       wait_event_type || ':' || wait_event AS waiting_on,
       left(query, 80)                    AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

-- Connection count by state (are we near max_connections?)
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count(*) DESC;
 
SELECT current_setting('max_connections')::int AS max_conn,
       count(*)                                AS used
FROM pg_stat_activity;

-- Long-running queries (> 5 minutes) and old open transactions
SELECT pid, state, now() - query_start AS query_age, now() - xact_start AS xact_age, left(query, 80)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND (now() - query_start > interval '5 minutes'
       OR (state LIKE 'idle in transaction%' AND now() - xact_start > interval '5 minutes'));

• Idle in Transaction: The worst state — holds locks and blocks vacuum from cleaning dead rows. Set idle_in_transaction_session_timeout

Cancelling and Killing Queries

Function	Effect	Use
pg_cancel_backend(pid)	Cancels the current query; session stays	First try — gentle (like Ctrl-C)
pg_terminate_backend(pid)	Kills the whole session/connection	Stuck idle-in-transaction, cancel didn’t work
pg_terminate_backend(pid, timeout_ms)	Waits for it to die (PG 14+)	Scripts

-- Cancel one query (replace 12345 with a pid from pg_stat_activity)
-- SELECT pg_cancel_backend(12345);
-- SELECT pg_terminate_backend(12345);
 
-- Kill all sessions idle in a transaction for more than 10 minutes
SELECT pid, pg_terminate_backend(pid) AS terminated
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes'
  AND pid <> pg_backend_pid();
 
-- Prevent runaway queries in the first place
SET statement_timeout = '30s';                        -- per session; or ALTER ROLE app SET …
SET idle_in_transaction_session_timeout = '60s';
SET lock_timeout = '5s';
RESET ALL;

Locks and Blocking

Who Is Blocking Whom

-- The one query to remember: blocked sessions and the pid(s) blocking them
SELECT blocked.pid                     AS blocked_pid,
       left(blocked.query, 60)         AS blocked_query,
       now() - blocked.query_start     AS waiting_for,
       blocking.pid                    AS blocking_pid,
       blocking.state                  AS blocking_state,
       left(blocking.query, 60)        AS blocking_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
ORDER BY waiting_for DESC;

-- Raw lock table: which relations are locked, in which mode, granted or waiting
SELECT l.pid,
       l.locktype,
       l.relation::regclass AS relation,
       l.mode,
       l.granted,
       a.state,
       left(a.query, 60)    AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation IS NOT NULL
  AND a.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

• granted = false: That session is waiting
• Lock Tree: A waiting ALTER TABLE (AccessExclusiveLock) makes everything behind it wait too — see Transactions and Locking
• Deadlocks: Logged automatically; count with SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

pg_stat_statements: Slowest and Most Expensive Queries

Setup

# postgresql.conf (requires restart)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Top Queries

-- Top 10 by TOTAL time — what is the database spending its life on
SELECT round(total_exec_time::numeric, 1)                      AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)                       AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       rows,
       left(query, 80)                                         AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Slowest on average (called at least 10 times), plus cache hit ratio per query
SELECT calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(max_exec_time::numeric, 2)    AS max_ms,
       round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
       left(query, 80)                     AS query
FROM pg_stat_statements
WHERE calls >= 10
ORDER BY mean_exec_time DESC
LIMIT 10;
 
-- Reset counters after a deploy / tuning change
SELECT pg_stat_statements_reset();

• Normalised Text: Literals become $1, $2 so the same query shape aggregates together
• Total vs Mean: Optimise total time first — a 2 ms query called 10M times beats a 5 s report run twice

Sizes

-- Database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;
 
-- Biggest tables: heap, indexes, TOAST, total
SELECT c.relname                                         AS table,
       pg_size_pretty(pg_relation_size(c.oid))           AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))            AS indexes,
       pg_size_pretty(pg_total_relation_size(c.oid))     AS total,
       c.reltuples::bigint                               AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;
 
-- Biggest indexes
SELECT indexrelid::regclass AS index, relid::regclass AS table,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;

Function	Measures
pg_relation_size(t)	Main heap only
pg_table_size(t)	Heap + TOAST + FSM/VM
pg_indexes_size(t)	All indexes of the table
pg_total_relation_size(t)	Everything
pg_database_size(db)	Whole database

Fast Row Count Estimate

-- count(*) scans the whole table under MVCC; the planner's estimate is instant
SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE oid = 'orders'::regclass;

Index Usage

-- Unused indexes (idx_scan = 0 since stats reset) — candidates to drop, except PK/UNIQUE
SELECT s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;
 
-- Tables read mostly by sequential scans (maybe missing an index)
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       round(seq_tup_read::numeric / NULLIF(seq_scan, 0)) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;
 
-- Invalid indexes left by failed CREATE INDEX CONCURRENTLY
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Vacuum, Bloat and Health

-- Dead rows and last (auto)vacuum/analyze per table
SELECT 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_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- Transaction ID wraparound risk (alert well before 2 billion)
SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;
 
-- Buffer cache hit ratio (OLTP should be > 99%)
SELECT round(100.0 * sum(blks_hit) / NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;
 
-- Is autovacuum running right now?
SELECT pid, now() - xact_start AS running_for, query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

Settings and Server Info

SELECT version();
SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;
 
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('max_connections', 'shared_buffers', 'work_mem', 'maintenance_work_mem',
               'effective_cache_size', 'random_page_cost', 'statement_timeout',
               'idle_in_transaction_session_timeout', 'autovacuum')
ORDER BY name;
 
-- Settings changed from defaults
SELECT name, setting, source FROM pg_settings WHERE source NOT IN ('default', 'override') ORDER BY name;

Replication Quick Checks

-- On the primary: connected standbys and lag
SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       replay_lag
FROM pg_stat_replication;
 
-- On a standby
SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replay_delay;

• More: Database Replication, Synchronous vs Asynchronous Replication

psql Meta-Commands Cheat Sheet

Command	Shows
\l	Databases
\dt / \dt+	Tables (with sizes)
\d table	Columns, indexes, constraints, triggers
\di+	Indexes with sizes
\dv / \dm / \df	Views / matviews / functions
\du	Roles
\x auto	Expanded output for wide rows
\timing on	Show query time
\watch 2	Re-run the last query every 2 seconds
\e	Edit the query in $EDITOR

Interview Questions

• “A query is stuck — what do you do?”: Find it in pg_stat_activity, check pg_blocking_pids, pg_cancel_backend then pg_terminate_backend
• “How do you find the slowest queries?”: pg_stat_statements ordered by total_exec_time and mean_exec_time
• “Table is huge but has few rows?”: Bloat — check n_dead_tup, autovacuum history, long transactions holding back cleanup
• “cancel vs terminate?”: Cancel stops the statement; terminate kills the connection
• “How big is this table with its indexes?”: pg_total_relation_size

Summary

• Activity: pg_stat_activity for running/idle sessions; watch idle in transaction
• Control: pg_cancel_backend → pg_terminate_backend; prevent with timeouts
• Locks: pg_blocking_pids() + pg_locks to find the blocker
• Workload: pg_stat_statements — optimise by total time
• Housekeeping: Sizes, unused indexes, dead tuples, XID age, cache hit ratio