Operating and diagnosing PostgreSQL

A slow history request may be waiting for an application connection, waiting inside PostgreSQL or doing more work than the server can comfortably serve. PostgreSQL’s activity view can show the second and part of the third. It cannot show a statement the application has not sent, and a long statement in that view need not be the work holding everyone else up.

Follow Mina’s order history after order 103 has committed. Her read should return 103, pending, followed by 101, paid. Order 103 still has no payment and its two agreed line values total 29.00. We will connect that request to a backend, follow a table-lock wait to its holder, then compare the evidence for excessive execution, connection pressure and storage trouble.

Diagnosing a database incident establishes how to test a cause against recovery. Here the evidence and controls are PostgreSQL 18’s. The drawings are labelled models. A separate disposable native server checks the lock, cancellation and cleanup behaviour described below; it does not measure the shop’s capacity.

From a request to its backend

The application asks its pool for a connection, borrows one, sends SQL, consumes the result and returns the client. Record those boundaries with the request identity. Once borrowed, a direct server connection has a PostgreSQL backend process; pg_backend_pid() reports its process ID.

SELECT order_id, placed_at, status
FROM orders
WHERE customer_id = 1
ORDER BY placed_at DESC, order_id DESC
LIMIT 10;

A request still waiting for checkout has no current SQL to find on its behalf. Once it borrows a connection, backend observations can explain server waiting or execution. Result consumption and returning the client need application evidence again.

Give connection classes recognisable application_name values, such as shop-history and shop-reports. They help locate candidates; they are neither unique request identifiers nor proof of who currently owns a borrowed client. A pool reuses a backend for successive requests. Keep the server, database, user, PID and connection start alongside the request’s borrow and statement intervals.

Capture the backend identity when establishing a connection and associate it with each borrow. Do not add an unbounded identity query before every diagnostic sample: that query can itself wait behind a busy connection. With an intermediary, use its mapping or an identity read inside the transaction you are tracing.

Read activity state and waits together

Use pg_stat_activity to inspect current server conversations. This sample includes connection, transaction and statement times because they answer different questions. It excludes the monitoring session itself and background processes.

SELECT clock_timestamp() AS sampled_at,
       pid, backend_start, usename, application_name,
       state, xact_start, query_start, state_change,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database()
  AND backend_type = 'client backend'
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST, pid;

backend_start dates the connection; xact_start dates its current transaction. query_start dates the current statement only while the state is active; otherwise it dates the last statement. state_change dates entry into the displayed state. An old query_start beside idle does not mean that query is still running.

State and wait are independent observations. active includes blocked execution; a null wait is not a processor-use meter. Sample at a bounded rate over the affected statement’s interval. A fleeting I/O wait can be real without explaining most of a second-long request.

Read samples in fresh, short monitoring transactions. Activity and cumulative statistics can be cached within a transaction; deliberately clearing that cache with pg_stat_clear_snapshot() is another option. Seeing other users’ details needs suitable authority, commonly pg_read_all_stats through an approved monitoring role. Missing detail is not evidence of no work.

Keep evidence after the statement finishes

The live activity row changes as the connection moves on. Preserve timestamped samples and correlate them with request traces and retained server logs. Include session identity and application name in the log format, and arrange duration and error logging before an incident. log_min_duration_statement can retain slow completed statements; log_min_error_statement covers statements that error. log_lock_waits reports waits exceeding deadlock_timeout, which is a reporting threshold here, not a limit that ends the wait.

Choose scope, thresholds and retention deliberately. SQL and parameters can contain private values, and logging every statement can create substantial work and storage growth. A short wait below the logging threshold can still appear in a live sample; an absent log line cannot rule it out.

Follow the blocked statement to its blocker

In this independent branch, session H runs BEGIN, then LOCK TABLE orders IN ACCESS EXCLUSIVE MODE, and leaves its transaction open. H changes no rows. Call Mina’s waiting history backend W. Its ordinary SELECT needs ACCESS SHARE on orders; those modes conflict. An ordinary row update would not explain this ordinary read waiting for that row’s lock.

Start with the blocked backend’s PID, obtained from the request mapping. pg_blocking_pids supplies the immediate blocking session identities. Inspect their state as well as the waiter’s.

-- Bind $1 to the current waiter PID from the request evidence.
SELECT w.pid AS waiter_pid, w.backend_start AS waiter_start,
       w.state AS waiter_state, w.wait_event_type, w.wait_event,
       p.pid AS blocker_pid, b.backend_start AS blocker_start,
       b.application_name AS blocker_application,
       b.state AS blocker_state, b.xact_start, b.query
FROM pg_stat_activity AS w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS p(pid)
LEFT JOIN pg_stat_activity AS b ON b.pid = p.pid
WHERE w.pid = $1;

For our branch, W is active with a lock wait and H is idle in transaction. H’s displayed LOCK is its last completed command. The transaction remains the owner of the lock.

-- Bind $1 and $2 to H and W. Connect to the orders database.
SELECT pid, relation::regclass AS relation, mode, granted
FROM pg_locks
WHERE database = (SELECT oid FROM pg_database
                  WHERE datname = current_database())
  AND relation = 'orders'::regclass
  AND pid IN ($1, $2)
ORDER BY pid, mode;

The relevant rows are H’s granted AccessExclusiveLock and W’s ungranted AccessShareLock. A lock list on its own is not a dependency graph: it also includes harmless compatible locks. Use the blocking relationship to find whom to inspect, then the requested object and mode to explain why.

Compare owner rollback with the two cancellations. W looks busy and H looks idle, yet H’s transaction is the resource owner. Cancelling W stops one attempt. Cancelling H’s finished statement cannot be assumed to end its transaction. Only the paths that actually end H’s transaction remove this dependency.

Read the whole dependency

A blocker can itself be waiting. Follow its blocking PIDs until you reach the participant that can make progress, retaining the relationships and sample times. PostgreSQL can also report a conflicting request ahead in the lock queue as a blocker; a backend need not already hold the requested lock to delay those behind it.

A returned PID of zero represents a prepared transaction, whose originating session has detached. Inspect pg_prepared_xacts with its transaction manager’s owner; there is no backend zero to cancel. In pg_locks, such held locks have a null PID. Do not infer an outcome and resolve prepared business work merely to clear a queue.

A chain ending at H need not contain a cycle, so waiting longer than deadlock_timeout does not make it a deadlock. Nor does the oldest connection or longest statement identify the right target. Locks, contention and deadlocks develops protected intervals and protocols; PostgreSQL schema changes under load handles the lifecycle of real schema operations that acquire excluding locks.

Find the work behind the incident

In another branch there is no demonstrated table blocker. Optional paid-order reports overlap with history requests. Ask whether reports ran more often, became more expensive, or both. pg_stat_statements retains counters for groups of structurally equivalent statements after those statements finish.

It needs advance setup: preload the module, enable query identifiers, restart as required for preloading, and install its extension views in the database used to inspect them. Do not restart an unhealthy service merely to recover evidence the module never collected.

SELECT clock_timestamp() AS sampled_at,
       dbid, userid, queryid, toplevel, query, stats_since,
       calls, total_exec_time, rows,
       shared_blks_hit, shared_blks_read, temp_blks_written
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database());

SELECT stats_reset, dealloc FROM pg_stat_statements_info;

Use query, the group’s representative SQL, to identify the paid-order report and associate its counters with the application’s report operation. Literal values may appear as placeholders; the text identifies the statement’s structure, not every execution’s parameters. Match saved entries by dbid, userid, queryid and toplevel. Check the entry’s stats_since and the collection’s reset and eviction history before subtracting. Missing or recreated entries do not supply a complete interval. Avoid resetting shared counters just to make an incident report shorter.

For a valid interval, divide the change in total_exec_time by the change in calls to obtain that interval’s mean execution time. Do not subtract the two lifetime means. More calls can raise the total without changing the cost per call; slower calls can raise it without changing the call count. The corresponding repairs address different demand.

The group can combine different customer IDs and time ranges. Its execution counters cover successful executions; failed or cancelled attempts still need activity and error evidence. It has no request trace or latency distribution, and its representative SQL is not a record of every parameter. Keep parameter classes and request measurements beside the counters. Query identifiers are local diagnostic identities with limited stability, rather than permanent cross-version names.

Connect that statement group to processor, memory and storage evidence during the same window. Compare buffer and temporary-work increases per call as well as totals. Then select a bounded plan investigation on representative inputs. Reading and repairing PostgreSQL plans reads the plan and tests repairs. EXPLAIN ANALYZE executes the statement again; a busy server may need containment before it has room for that test.

If repeated optional reports explain the demand, stop their future admission and check whether new report calls cease. Already running reports require a separate decision. A lower resource graph without better history completions would leave part of the cause unexplained.

Diagnose connection pressure

A full application pool and a server refusing new connections are different observations. Group PostgreSQL’s client backends, then compare them with borrowed, available and queued populations in the actual application pools. Server-side idle does not prove that an application returned its client.

SELECT application_name, usename, state, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, usename, state
ORDER BY sessions DESC;

SELECT name, setting
FROM pg_settings
WHERE name IN ('max_connections', 'reserved_connections',
               'superuser_reserved_connections');

The grouped count spans databases on this server because admission is a server budget. If another client backend is missing from an application’s pool count, it may belong to a worker, tool or another application instance. Background processes and parallel workers do not all consume ordinary client connection slots in the same way; do not compare the unfiltered activity-row total directly with max_connections.

As remaining slots fall to superuser_reserved_connections + reserved_connections, ordinary roles stop being admitted. Only superusers and roles with pg_use_reserved_connections can enter; at the superuser-only margin, only superusers can. Check those settings and the operational role before an incident: a planning allowance for support does not grant reserved access. reserved_connections defaults to zero, and the superuser reservation is a final emergency margin. Increasing max_connections needs a restart and increases associated resource allocation.

If many borrowed clients are waiting behind H, extra connections make more waiters. If admitted execution already overwhelms a resource, extra sessions can worsen it. If most server clients are idle but one application’s queue grows, inspect its checkout, hold time and leak behaviour. Connections, overload and capacity owns the workload budget and overload policy after you have found the pressure.

Separate storage delay from storage exhaustion

An I/O wait locates a delayed operation. Free-space measurements answer whether the relevant volume can keep accepting data. Take both. Save pg_stat_io samples at each end of the same window, checking the reset boundaries before subtracting. Its backend type, object and context locate the accumulated work; match the changes to the affected sessions’ wait samples and host device latency.

SELECT clock_timestamp() AS sampled_at,
       backend_type, object, context,
       reads, read_time, writes, write_time, stats_reset
FROM pg_stat_io
ORDER BY backend_type, object, context;

Time fields require track_io_timing for relation work and track_wal_io_timing for WAL. A zero with timing disabled does not establish free I/O; an untracked operation can appear as null.

Read counters describe PostgreSQL’s requests to the storage stack; some are served by the operating system’s cache. They do not establish drive reads or drive saturation on their own. Equally, a full shared-buffer cache is normal use of a cache, not evidence that the underlying volume is full.

For scale, the incident article’s synthetic trajectory falls from 12 to 8 to 4 GiB free in ten minutes. A proposed repair needing another 6 GiB already does not fit at the last point. That is a repair-space test, not a PostgreSQL threshold. Rewrites, continued writes and recovery all need room.

Do not delete database or WAL files by hand. Deleting rows need not shrink their files, and VACUUM FULL needs spare space and an excluding table lock. A full WAL volume can shut the server down. Preserve recovery headroom while the specialist owner establishes which retention or maintenance obligation can safely change.

Bound the next attempt

Before repeating a history request, decide how much time remains. The application’s checkout wait is already spent; PostgreSQL does not include it in statement_timeout. For an illustrative two-second deadline, 600 ms spent on checkout and 500 ms reserved for later response work leave 900 ms for the server attempt. Lock waiting must fit inside that interval, rather than extending it.

BEGIN;
SET LOCAL lock_timeout = '200ms';
SET LOCAL statement_timeout = '900ms';
SELECT order_id, placed_at, status
FROM orders
WHERE customer_id = 1
ORDER BY placed_at DESC, order_id DESC
LIMIT 10;
COMMIT;

lock_timeout limits each lock acquisition wait. statement_timeout covers the statement’s server interval, including those waits. Multiple acquisitions do not turn the first into an end-to-end request cap. Set a useful lock limit below the statement limit, and fit the whole attempt inside the remaining application deadline.

On a timeout or cancellation error inside this BEGIN, the ordinary cleanup path is ROLLBACK, rather than continuing to COMMIT or handing the failed transaction to the next borrower. Confirm the terminal result, finish cleanup and restore a known client state before release. Discard a broken connection. A caller timeout alone establishes none of those steps.

Two session-ending controls address longer lifetimes. idle_in_transaction_session_timeout ends a session that remains idle inside an open transaction; it does not cap an executing statement. transaction_timeout caps transaction lifetime, including implicit transactions, by ending the session; prepared transactions are excluded. Choose these with the application and pool’s disconnect handling. They are not interchangeable query-cancellation controls.

A timeout contains one attempt. Immediately recreating the same blocked attempt can keep the queue full. Keep retry bounds and admission in force while the dependency is unresolved.

Cancel a statement or end a session

Prefer the owner ending the correct transaction when it can do so. H is deliberately lock-only, so rollback sacrifices no business change in this experiment. A real holder may have writes whose outcome matters. Establish its owner and work before deciding what may be abandoned.

pg_cancel_backend(pid) targets the current statement. pg_terminate_backend(pid, timeout) ends the session, aborting unfinished transaction work and disconnecting its client. With a positive timeout in milliseconds, termination can wait for the session to end and report whether that happened within the bound. Without that wait, a true result establishes signal delivery rather than observed completion.

Refresh the target evidence just before signalling. PIDs can be reused; the same live backend can also have moved on to a different request. Here is a guarded cancellation query for a selected active statement, using values from a fresh sample:

SELECT pg_cancel_backend(pid) AS signal_sent
FROM pg_stat_activity
WHERE pid = $1
  AND backend_start = $2::timestamptz
  AND datname = current_database()
  AND application_name = $3
  AND state = 'active'
  AND query_start = $4::timestamptz;

The four parameters are the sampled PID, connection start, application name and statement start. Preserve the timestamps’ full precision when binding them; rounding can prevent an exact match. No matching row means the sample no longer identifies that active statement. These checks narrow the target; they do not freeze a pooled connection between inspection and signalling. Coordinate with its owner to stop reuse when that race matters. Signalling also requires appropriate authority; monitoring access alone does not grant it, and ordinary signal roles cannot terminate a superuser backend.

Then check the statement or session’s terminal outcome, its remaining locks and the client’s transaction state. The native lock experiment used an isolated Linux PostgreSQL 18.6 instance and node-postgres 8.23.1. An idle-holder cancellation returned true while H’s granted table lock and W’s wait remained. Owner rollback released the lock and W returned 103 then 101. Cancelling W inside an explicit transaction produced 57014; a subsequent statement failed with 25P02 until rollback. A separate lock timeout produced 55P03. These are observed mechanism checks, not timing or least-privilege tests.

Account for the recovery work

Ending a large PostgreSQL write transaction makes its aborted versions invisible; reclaiming their heap and index space still requires later cleanup. Do not assume that killing a session gives its allocated storage straight back. Our lock-only H avoids that cost because it created no row versions.

A server restart interrupts a much wider set of work. An unclean stop can require WAL replay and startup work, shared buffers start cold, and reconnecting clients and retries can compete with useful requests. A normal session termination does not itself mean the whole server must replay WAL. PostgreSQL WAL, checkpoints and durability tests restart’s separate costs.

Finally, a disconnected writing client may have lost a commit reply after the transaction committed. Session loss is not proof of rollback. Retain the operation identity and use Retries and uncertain commit outcomes to reconcile before repeating business work.

Verify the service recovered

For the lock branch, predict the chain before acting. H’s transaction ends and its conflicting lock disappears. W finishes, and the application consumes the rows and returns its client. Queued callers then borrow clients, and useful history responses resume. Each link has its own evidence. A missing activity row establishes neither a delivered response nor a returned pool client.

Our disposable run checked a one-client node-postgres pool with one later borrower queued. Ending H allowed the original read to return, then releasing its client allowed the queued borrower to read the same 103, 101 result. A separate bounded termination of H also released the reader. Those small checks establish the mechanism through the client library. They do not exercise an HTTP response path, production traffic or a burst of retries.

In the real service, check both already queued and newly arriving requests, useful completions, errors, acquisition delay and total latency under comparable arrivals. A queue that drains through errors is not recovered service. If the lock has gone but clients remain occupied, follow execution or result consumption; if new blockers appear, update the dependency rather than repeating yesterday’s target PID.

Restore optional work gradually while retaining resource and admission headroom. Keep the incident window, identities, counters, intervention and measured consequences with the lasting repair: shorter transactions, a repaired report, corrected admission or safe storage relief. The PostgreSQL evidence earns its place when it explains where Mina waited and lets you verify what changed for her request.