PostgreSQL — ERROR: canceling statement due to statement timeout

Investigate PostgreSQL canceling statement due to statement timeout, find which layer set the deadline, measure the query real cost, and fix the plan or the budget.

beginnerdebuggingpostgresqltimeoutsquery-tuning

Symptoms

ERROR: canceling statement due to statement timeout aborts specific queries at a consistent duration
Failures cluster on reports or analytics endpoints while OLTP queries stay healthy
The same query succeeds manually in psql and fails through the application driver

Possible causes

The driver, ORM, or pool sets statement_timeout lower than the report actually needs
A query regressed (plan flip, data growth) and now exceeds a timeout that used to fit
Per-role or per-database ALTER ROLE ... SET statement_timeout applies only to some sessions
Network or lock waits consume the budget before execution even starts

Diagnosis steps

Confirm the effective timeout in the failing session

psql -c "SHOW statement_timeout;" && grep -Rn "statement_timeout\|options.*statement" app/ driver-config/

Interpretation

The setting can come from the server, the role, the database, or the connection options string — the effective value in the failing session decides which one to change.

Measure the real cost of the query

psql -c "SET statement_timeout = 0; EXPLAIN (ANALYZE, BUFFERS) <the query>;"

Interpretation

Unbounded execution reveals the true duration and plan; lock waits appear as wait events, plan problems as scan shapes.

Check for lock waits consuming the budget

psql -c "SELECT wait_event_type, wait_event FROM pg_stat_activity WHERE query ilike '%<fragment>%';"

Interpretation

A query waiting on Lock burns its entire statement_timeout without executing a single buffer read — the timeout is a victim, not the cause.

Root cause

statement_timeout bounds the wall time of a single statement in that session. When the query needs longer than the session budget — because it genuinely costs more, because its plan regressed, or because lock waits consume the allocation first — PostgreSQL cancels it at the deadline with this exact error.

Fix

Fix the cost when the query is the problem (index the plan, rewrite, materialize a summary table), scope budgets per workload class (role or session level: tight for OLTP, generous or zero for reports), and address lock waits separately — raising the timeout on a lock victim only extends the queue.

Risk note

Global statement_timeout = 0 removes protection from every session. Scope budgets per role or per connection, not cluster-wide.

Verify the fix

Reports run to completion under their scoped budget, OLTP keeps its tight guardrails, and the error appears only for queries that genuinely exceed their class budget.

Symptom walkthrough

The error includes the rule that fired:

ERROR: canceling statement due to statement timeout

The consistent duration is the fingerprint: failures at exactly 30 seconds mean a 30s budget somewhere. Find whose budget it is, because the setting can arrive from four places:

SHOW statement_timeout;                -- effective now
ALTER ROLE reporting SET statement_timeout = '300s';

Then measure the truth with the guard off:

SET statement_timeout = 0;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- real duration and plan

Two outcomes split the path: the query genuinely needs 4 minutes (budget problem, scoped raise), or it needs 4 minutes because the plan flipped to a seq scan (cost problem, fix the plan). A third variant hides in pg_stat_activity: a wait_event of Lock means the budget burned before execution started.

Fix in practice

Scope budgets by workload class, not cluster-wide:

ALTER ROLE oltp_app     SET statement_timeout = '5s';
ALTER ROLE reporting    SET statement_timeout = '300s';

# or per connection, for mixed pools:
conn.execute("SET statement_timeout = '300s'")   # this session only

Fix the cost when the measurement says so:

CREATE INDEX orders_report_idx ON orders (tenant_id, created_at);
-- or pre-aggregate:
CREATE MATERIALIZED VIEW daily_totals AS SELECT ... ;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_totals;

And treat lock-wait victims separately: find the holder (pg_blocking_pids), fix the holder pattern — raising the timeout on a queued query just lengthens the queue.