How do I troubleshoot "Statement timeout" errors for queries that run on my Aurora PostgreSQL-Compatible database cluster?

Resolution

Important: Performance Insights will reach its end of life on June 30, 2026. You can upgrade to the Advanced mode of Database insights before June 30, 2026. If you don't upgrade, then DB clusters that use Performance Insights will default to the Standard mode of Database Insights. Only the Advanced mode of Database Insights will support execution plans and on-demand analysis. If your clusters default to the Standard mode, then you might not be able to use these features on the console. To turn on the Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon RDS and Turning on the Advanced mode of Database Insights for Amazon Aurora.

If queries fail to execute within the time specified by the statement_timeout parameter, then the statement_timeout parameter cancels the query. You receive the following error message:

"ERROR: canceling statement due to statement timeout."

To troubleshoot the error, take the following actions.

Check the configured statement_timeout parameter

To check the statement_timeout parameter in the DB cluster parameter group or DB parameter group, run the following SELECT query:

SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'statement_timeout';

Expected output:

       name        | setting | unit | context |       source
-------------------+---------+------+---------+--------------------
 statement_timeout | 5000    | ms   | user    | configuration file

Note: In the example output, the statement_timeout parameter has a value of 5000 milliseconds. The "source" field shows "configuration file," which indicates the parameter is set at the cluster parameter group level.

Then, check the statement_timeout parameter at the role level and database level.

To check the role-level configurations for all roles in the database cluster, run the following query:

SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase
WHERE s.setconfig::text LIKE '%statement_timeout%'
ORDER BY r.rolname;

To check database-level configurations for all databases in the cluster, run the following query:

SELECT d.datname, rs.setconfig
FROM pg_db_role_setting rs
JOIN pg_database d ON d.oid = rs.setdatabase
WHERE rs.setrole = 0;

Review the output to identify any statement_timeout configurations set at the role or database level that might override the cluster-level setting.

Note: Statement_timeout parameters that you set with ALTER ROLE SET do not inherit to child roles. If you configure the statement_timeout parameter for a role, then you can only use the parameter when you log in to that role. For more information, see ALTER ROLE on the PostgreSQL website.

Identify the canceled SQL queries

View the PostgreSQL error log file, and then determine if the log_min_error_statement parameter is set to ERROR or a lower severity. After you identify the statement that failed, find the failed SQL and table names. For more information, see Understanding the log_line_prefix parameter.

Identify the cause of long execution duration for queries

If you find the failed SQL query, then use CloudWatch Database Insights to identify blocked transactions.

To use CloudWatch Database Insights to analyze performance, complete the following steps:

1. Open the Amazon Relational Database Service (Amazon RDS) console.
2. In the navigation pane, choose Databases.
3. Select your Aurora PostgreSQL DB cluster.
4. Choose the Monitoring tab.
5. Choose View details for Performance Insights.
6. Review the DB load. You can group the DB load by wait events, SQL queries, hosts, or users to identify blocked transactions.

If the issue reproduces consistently, then configure the log_min_duration_statement parameter for your DB instance and use the auto_explain module. For more information, see How can I log execution plans of queries for Amazon RDS PostgreSQL or Aurora PostgreSQL to tune query performance?

You can use the EXPLAIN and EXPLAIN ANALYZE commands to get the query execution plan. For more information, see How do I identify and troubleshoot performance issues and slow-running queries in my Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible DB instance?

Check for dead rows in source tables

Dead rows or tuples can increase SELECT time. To check for large numbers of dead rows in the source tables, run the following query:

SELECT * FROM pg_stat_user_tables WHERE relname = 'table_name';

Note: Replace table_name with the name of the source table.

Related information

How do I end long-running queries in my Amazon RDS for PostrgreSQL or Aurora PostgreSQL-Compatible DB instance?

How do I identify what blocked a query on my Amazon RDS PostgreSQL or Aurora PostgreSQL database instance?