TL;DR - The Quick Fix
If you need to run a long-running query once (like a data migration or a heavy report), run this command in your SQL editor before executing your main query:
-- Disable timeout for the current session
SET statement_timeout = 0;
-- Or set it to a specific duration (e.g., 5 minutes)
SET statement_timeout = '5min';
To check your current timeout setting, run:
SHOW statement_timeout;
Why this error occurs
The ERROR: canceling statement due to statement timeout occurs because the PostgreSQL server has a limit on how long a single SQL statement can run. When a query exceeds this limit, the server kills the process to prevent it from consuming system resources indefinitely.
This limit is controlled by the statement_timeout parameter. While the default is often 0 (no timeout), many managed database services (like AWS RDS) or DBAs set a default limit (e.g., 30s or 60s) to keep the database responsive.
How to fix the error
Approach 1: Change timeout for the current session
This is the safest method because it only affects your current connection. It is ideal for one-off tasks.
-- Set timeout to 10 minutes for this session only
SET statement_timeout = '10min';
-- Run your heavy query here
SELECT * FROM large_table JOIN another_large_table ...;
Approach 2: Change timeout for a specific user or database
If you have a specific background worker user that always runs long tasks, you can increase the limit specifically for that user without affecting the rest of the application.
-- Apply to a specific user
ALTER ROLE reports_user SET statement_timeout = '1 hour';
-- Apply to a specific database
ALTER DATABASE analytics_db SET statement_timeout = '30min';
Approach 3: Change the global configuration
To change the limit for every connection to the server, modify the postgresql.conf file. Note that this requires superuser permissions and may require a configuration reload.
- Locate your
postgresql.conffile. - Find the line
statement_timeout. - Change the value (value is in milliseconds if no unit is specified).
# Set global timeout to 2 minutes
statement_timeout = 120000
After saving, reload the configuration:
SELECT pg_reload_conf();
Approach 4: Optimize the query (The long-term fix)
Increasing the timeout is often just a band-aid. If a query that used to be fast is now timing out, you should investigate why it is slow using EXPLAIN ANALYZE.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at > '2023-01-01'
ORDER BY total_amount DESC;
Look for Seq Scan (Sequential Scan) on large tables. Adding a missing index is usually the best way to stop the timeout error from happening in the first place.
Verification
To confirm that your changes have taken effect, run the following command in the same environment where you were seeing the error:
SHOW statement_timeout;
If the output shows 0, the timeout is disabled. If it shows a value like 5min, that is your new limit. Try running your query again; it should now proceed past the previous time limit.