PostgreSQL "canceling statement due to statement timeout" — Slow Query or Lock Contention?
Deconstructing SQLSTATE 57014: statement timeout vs lock timeout vs user request vs recovery conflict. Tracing settings origin in pg_settings and tiered role thresholds.
Operational Verification & Reliability Metadata
The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.
▸Symptom
ERROR: canceling statement due to statement timeoutUnder PostgreSQL SQLSTATE code 57014 (query_canceled), four entirely different operational events can occur:
ERROR: canceling statement due to statement timeout
ERROR: canceling statement due to lock timeout
ERROR: canceling statement due to user request
ERROR: canceling statement due to conflict with recovery57014 and assuming *"the query is slow, let's add an index"* leads engineers down the wrong path in three out of four cases:
- `statement timeout`: The query actively consumed CPU/IO and exceeded the allotted runtime threshold.
- `lock timeout`: The query execution could be sub-millisecond, but it was blocked waiting for an exclusive lock held by another transaction! Adding indexes will not help (H-19, H-27).
- `user request`: The cancellation was issued by the client application (JDBC setQueryTimeout, .NET CommandTimeout, Go context deadline). The client explicitly transmitted a cancel request; server-side database configuration is not at fault.
- `conflict with recovery`: The query was executing on a Standby Read Replica and was cancelled due to competing WAL replay operations from the primary (H-11).▸Tracing Configuration Sources via `pg_settings`
In PostgreSQL, statement_timeout can originate from five distinct configuration levels:
SELECT name, setting, unit, source, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('statement_timeout', 'lock_timeout', 'idle_in_transaction_session_timeout');The source column determines the winning override:
- •
configuration file: Global configuration frompostgresql.conf. - •
database: Applied viaALTER DATABASE ... SET. - •
user: Applied viaALTER ROLE ... SET. - •
client: Injected dynamically by the application driver or connection pooler. - •
default: Unset; defaults to0(unlimited).
▸Step-by-Step Diagnostic Workflow
1. Measure Long-Running Queries via pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT queryid, calls,
round(mean_exec_time::numeric, 1) AS avg_ms,
round(total_exec_time::numeric) AS total_ms,
left(query, 100) AS query_text
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;- •
total_exec_timeidentifies the top cumulative resource hogs. - •
mean_exec_timehighlights individual queries that breach timeout thresholds.
2. Check for Lock Contention
SELECT pid, now() - query_start AS duration, wait_event_type, wait_event,
cardinality(pg_blocking_pids(pid)) AS blocker_count,
pg_blocking_pids(pid) AS blocker_pids,
left(query, 80) AS query_text
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
ORDER BY duration DESC;If wait_event_type = 'Lock', the query is stalled waiting for a lock, not consuming CPU.
3. Inspect Query Execution Plans with Buffers
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;The BUFFERS clause is critical: it distinguishes queries reading from shared RAM cache (shared hit) from those thrashing disk subsystems (read).
▸Production Best Practices: Tiered Timeout Policies
1. Never Disable Timeouts Globally
Setting global statement_timeout = 0 disables critical safety mechanisms. A runaway query will simply consume connection pool slots and bring down the entire cluster under peak load.
2. Define Tiered Timeouts per Role
Web APIs and reporting batches must operate under separate timeout rules:
-- Interactive web tier (5 seconds)
ALTER ROLE app_web SET statement_timeout = '5s';
-- Batch reporting service (15 minutes)
ALTER ROLE app_reporting SET statement_timeout = '15min';3. Set lock_timeout Lower than statement_timeout
ALTER SYSTEM SET lock_timeout = '3s';
ALTER SYSTEM SET statement_timeout = '10s';
SELECT pg_reload_conf();When lock contention occurs, the query fails early with lock timeout rather than holding up connection pool threads for the full 10 seconds.
▸Related Error Guides & Hub Links
- •PostgreSQL Conflict with Recovery (H-11) — Standby replication query cancellation
- •PostgreSQL Deadlock Detected (H-27) — Deadlock graph diagnosis
- •SQL Server Lock Request Timeout 1222 (H-19) — SQL Server lock wait timeout
- •MySQL Lock Wait Timeout Exceeded (H-09) — MySQL InnoDB lock waits
- •PostgreSQL Hub — Performance engineering and query tuning
Other Database Error Triage Playbooks
Other battle-tested diagnostic guides for high-severity database errors.
PostgreSQL Connection Exhaustion & too many clients Triage
Root causes of FATAL: sorry, too many clients already and remaining connection slots errors, read-only pg_stat_activity diagnosis with privilege boundaries, and two-tier pooling mitigation.
PostgreSQL password authentication failed: Root Cause and Fix
Four root causes of psql password authentication failed: incorrect password, md5/SCRAM mismatch, pg_hba.conf rule ordering, and port/cluster confusion. Step-by-step triage.
PostgreSQL connection refused: 8-Step Definitive Triage
Difference between connection refused, timeout, and no pg_hba entry; triage for listen_addresses, port conflicts, firewalls, and Docker container mappings.