How to fix Query timeout in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 235 views

A query exceeded the allowed execution time. Configure statement_timeout, optimize slow queries with indexes, monitor lock contention, and adjust timeout settings based on query complexity.

Quick fix

Fixes Query timeout

SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;
  1. Check the configured statement_timeout
  2. Enable slow query logging
  3. Analyze the slow query plan
  4. Add or improve indexes
postgresql — error
SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;
Query timeout

What this error means

The "Query timeout" error occurs when a SQL query exceeds the maximum allowed execution time defined by PostgreSQL's timeout parameters. The primary parameter is statement_timeout, which aborts any statement that takes longer than the specified duration (default is no limit). This timeout protects the database from runaway queries that consume excessive CPU, memory, and lock resources. When a timeout triggers, PostgreSQL cancels the statement and rolls back any work performed by that query, but keeps the database connection alive for retry or subsequent commands.

Typical symptoms

  • Database queries fail with timeout messages during normal operations
  • Certain reports or batch jobs consistently fail at the same point
  • Queries work in development but timeout in production under higher load
  • Application receives timeout errors before operations complete
  • Lock waits from other transactions cause unrelated queries to timeout
  • Long-running analytical queries fail unexpectedly

Common causes

  1. statement_timeout parameter configured too aggressively for query needs
  2. Queries lack proper indexes causing expensive full table scans
  3. Database experiencing resource contention or high load
  4. Transactions holding locks block dependent queries until timeout
  5. Network latency delays query execution beyond timeout threshold
  6. Deadlock situations cause queries to wait indefinitely
  7. Lock contention from concurrent write operations (accounts for ~42% of timeout cases)
  8. Misconfigured connection pooler increases queue waits

How to fix "Query timeout"

1Check the configured statement_timeout

Connect to the database and verify the current timeout settings:

sql
SHOW statement_timeout;
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

These values are in milliseconds. A value of 0 disables the timeout. Check both database defaults and any session-level overrides that may apply to your connection.

2Enable slow query logging

Configure PostgreSQL to log long-running queries so you can identify which statements are timing out:

sql
ALTER DATABASE mydatabase SET log_min_duration_statement = 5000;  -- Log queries > 5 seconds
ALTER DATABASE mydatabase SET log_statement = 'all';

Alternatively, query pg_stat_statements if enabled:

sql
SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

This reveals which queries consume the most time.

3Analyze the slow query plan

Use EXPLAIN ANALYZE on the problematic query to understand why it's slow:

sql
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM large_table WHERE status = 'active' ORDER BY created_at DESC LIMIT 100;

Look for sequential scans on large tables where index scans should occur. Nested loop joins with many rows indicate missing indexes. Use output to identify the bottleneck.

4Add or improve indexes

Create indexes on frequently filtered or joined columns:

sql
CREATE INDEX idx_status_created ON large_table(status, created_at DESC);

For complex queries, use covering indexes that include columns needed for index-only scans:

sql
CREATE INDEX idx_covering ON table_name(filter_col) INCLUDE (data_col1, data_col2);

After adding indexes, re-run EXPLAIN ANALYZE to confirm it uses the index.

5Optimize the query logic

Refactor queries to reduce workload:

- Use JOINs instead of multiple subqueries
- Push filters down early (WHERE clauses before joins)
- Avoid SELECT * and specify only needed columns
- Use DISTINCT or GROUP BY sparingly (sorts data)
- Consider materialized views for complex aggregations

Example:

sql
-- Bad: Full table scan then filter
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US') LIMIT 100;

-- Good: Join with filter first
SELECT o.* FROM orders o INNER JOIN customers c ON o.customer_id = c.id WHERE c.country = 'US' LIMIT 100;
6Increase statement_timeout if appropriate

If the query legitimately requires more time after optimization, increase the timeout:

sql
ALTER DATABASE mydatabase SET statement_timeout = '120s';

Or set per-session before running long operations:

sql
SET statement_timeout = 300000;  -- 5 minutes in milliseconds
SELECT ... FROM expensive_operation;

For application code, configure timeout in your driver:

javascript
// Node.js example
await client.query({ text: 'SELECT ...', statement_timeout: 120000 });
7Address lock contention and deadlocks

If timeouts occur while waiting for locks, use lock_timeout to fail fast:

sql
SET lock_timeout = '5s';
SET statement_timeout = '60s';

Monitor blocking sessions:

sql
SELECT pid, usename, query FROM pg_stat_activity WHERE state = 'active';
SELECT * FROM pg_locks WHERE NOT granted;

Identify and kill long-running transactions that block others:

sql
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < now() - INTERVAL '10 minutes';
8Validate changes and monitor

Re-run the previously timing-out query and measure execution time:

sql
\timing on
SELECT ...;  -- Check the "Time:" output

Monitor application logs for timeout errors after deployment. Set up alerting for slow queries:

sql
CREATE TABLE slow_query_log (query text, duration_ms int, logged_at timestamp);
ALTER DATABASE mydatabase SET log_min_duration_statement = 10000;  -- Log > 10 seconds

For critical operations, test on a replica under production-like load before deploying to production.

Advanced notes

PostgreSQL 17+ introduces transaction_timeout to limit entire transaction duration, not just individual statements. Use idle_in_transaction_session_timeout to prevent sessions from holding locks while idle between application requests. Lock contention accounts for ~42% of timeout issues in production; consider connection pooling (PgBouncer, pgpool-II, pgbouncer) to manage resource usage. For applications, implement client-side retry logic with exponential backoff. Use pg_stat_activity and pg_stat_statements to gain insight into slow queries in real-time. For write-heavy workloads, consider partitioning large tables or sharding to reduce lock contention. If using prepared statements, ensure they're not causing parameter sniffing issues. Row-level security policies can add overhead; profile them separately from query time.

Frequently asked questions

What does "Query timeout" mean?
The "Query timeout" error occurs when a SQL query exceeds the maximum allowed execution time defined by PostgreSQL's timeout parameters. The primary parameter is statement_timeout, which aborts any statement that takes longer than the specified duration (default is no limit). This timeout protects the database from runaway queries that consume excessive CPU, memory, and lock resources.
How do I fix "Query timeout"?
A query exceeded the allowed execution time. Configure statement_timeout, optimize slow queries with indexes, monitor lock contention, and adjust timeout settings based on query complexity.
What causes "Query timeout"?
statement_timeout parameter configured too aggressively for query needs Queries lack proper indexes causing expensive full table scans Database experiencing resource contention or high load
Is the "Query timeout" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading