PostgreSQL

PostgreSQL — ERROR: canceling statement due to statement timeout

Q: Should I raise statement_timeout?

Almost never as a fix. The timeout is the only thing stopping one pathological statement from holding a connection and an lock indefinitely. Raising it makes the outage longer and quieter. Fix the plan, the bound, or the blocker.

Q: Why is the query fast in psql but times out in the application?

Several possibilities, all common: the application passes a parameter with far more rows, a cached generic plan differs from the plan you get with literals, the production system has lock and I/O contention your session does not, or the driver wraps the query in a transaction that is also waiting on something.

Q: How do I tell a lock wait from a slow query?

`pg_stat_activity.wait_event_type`. If it is `Lock`, the statement is doing nothing and the problem belongs to whoever holds the lock — `pg_blocking_pids()` will name them. If it is null or I/O-related, the statement is genuinely working and the plan is the place to look.

Q: Is OFFSET pagination really that bad?

For deep pages, yes. The database must generate and discard every row before the offset, so cost grows linearly with page depth and the last page is the slowest. Keyset pagination is constant-cost and is the right default for any list that can grow.

Written and reviewed by Sahil Srivastav

PostgreSQLQuery performanceTimeouts

ERROR:  canceling statement due to statement timeout
STATEMENT:  SELECT o.id, o.created_at, count(i.id) FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = $1 GROUP BY o.id ORDER BY o.created_at DESC

What this error actually means

PostgreSQL cancelled your statement because it exceeded `statement_timeout`. The timeout is a guard you configured, so this message is not itself the problem — it is the guard doing its job and telling you a statement took longer than you declared acceptable.

The diagnostic question is which of three very different things happened. The statement may have been doing real work too slowly, because the plan is bad or the result set is unbounded. It may have been doing no work at all, blocked waiting for a lock held by someone else. Or the plan may have been fine and the data volume simply grew past what the query shape can handle.

Distinguishing "slow" from "blocked" is the first fork and the one most often skipped. A statement waiting on a lock consumes almost no resources and will not look slow in any CPU or I/O metric; `EXPLAIN ANALYZE` on an idle system will return in milliseconds and tell you nothing. `pg_stat_activity.wait_event_type` is what separates them.

Causes, most common first

1Missing or unusable index for the access path. A sequential scan on a large table, or an index that cannot satisfy both the filter and the ordering. Frequently caused by a predicate wrapped in a function, a type mismatch that prevents index use, or a leading wildcard in a `LIKE`.
2Unbounded result set. The query has no `LIMIT`, or a `LIMIT` applied after an expensive sort of the entire history. Cost grows with the customer’s total data rather than with the page requested — the "works in dev, dies for the biggest tenant" shape.
3Blocked on a lock, not slow. The statement spent its whole budget waiting. Common behind a long-running transaction, an `ALTER TABLE` queued for an `ACCESS EXCLUSIVE` lock, or a batch job holding row locks.
4A plan that changed for the worse. Stale statistics after a bulk load, or a parameterised query where a generic plan was cached that suits one parameter distribution and not another. The query and the data look unchanged and the plan is different.
5N+1 amplification at the application layer. Each individual statement is fast, but one request issues thousands. Here the timeout fires on an arbitrary member of the set and the real fix is in the application, not the query.

When you see it

One endpoint times out reliably for specific customers or date ranges and is fast for everyone else
Failures start after a data-volume milestone rather than after a deploy
The same query is fast in `psql` on a quiet system and times out under production concurrency
Timeouts cluster around a known write-heavy period or a migration window (the lock-wait case)
`pg_stat_statements` shows the statement with high total time and a large rows-per-call value

How to diagnose it

Step 1

First, was it slow or was it waiting?

Run this while the problem is happening. A non-null `wait_event_type` of `Lock` means you have a blocking problem and no amount of query tuning will help.

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for, left(query, 100)
FROM pg_stat_activity
WHERE state = 'active' ORDER BY query_start;

Step 2

Get the real plan with real buffers

Use the actual production parameter values. `BUFFERS` shows whether the cost is I/O or CPU, and the mismatch between estimated and actual row counts is what exposes stale statistics.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ;

Step 3

Rank statements by total time, not by worst case

`pg_stat_statements` tells you where the database actually spends its time, and `rows / calls` immediately reveals unbounded result sets.

SELECT calls, mean_exec_time, total_exec_time, rows / calls AS rows_per_call,
       left(query, 90)
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

Step 4

Test against the largest real input

Find the customer, tenant, or date range with the most rows and run the query for them. If time scales with that number, the query shape is the bug — not the timeout value.

The fix

For a missing access path, build an index that serves the filter and the ordering together — a composite index on the filter columns followed by the sort column lets PostgreSQL return the page directly instead of sorting everything first. Verify with `EXPLAIN` that the plan actually uses it; an index the planner ignores is dead weight.

For unbounded results, push the limit down to the database and paginate on a keyset rather than `OFFSET`. `OFFSET 50000` makes the database produce and discard 50,000 rows, so deep pages get slower forever; `WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT $3` is constant-cost at any depth.

For lock waits, the query is innocent — find and fix the blocker. That usually means a long-running transaction, and the durable fix is `idle_in_transaction_session_timeout` plus a `lock_timeout` on DDL so a migration queues briefly and fails instead of blocking every reader behind it.

For plan regressions, run `ANALYZE` on the affected tables and check whether estimated rows are wildly off actual. Raise the statistics target on skewed columns. For generic-plan problems, the driver-level fix is usually to stop reusing a server-side prepared statement across very different parameter distributions.

For N+1 amplification, fix it in the application: fetch the set in one query with a join or `= ANY($1)` rather than looping. Raising `statement_timeout` is the wrong move in every one of these cases — it converts a fast failure into a slow one and lets a pathological query hold a connection longer.

How to stop it coming back

Keep `statement_timeout` set deliberately per workload: short for request paths, longer for reporting, longest for batch. A database with no timeout has no defence against one bad query
Enable `pg_stat_statements` everywhere and review the top of it after every release
Set `lock_timeout` before DDL so migrations fail fast instead of queueing readers behind them
Load-test with production-shaped skew, especially the largest single tenant, rather than uniform seed data
Assert bounded work in tests: a page request should touch O(page) rows regardless of total history

Practise this failure in a real repository

Gronex ships this as a runnable repository: an order-history endpoint with a missing index, an N+1 loop, and an unbounded fetch. The tests assert statements executed and rows visited per request, so a query that merely runs fast on seed data still fails.

FAQ

Should I raise statement_timeout?