How do I have resolve issues caused by an LWLock:pg_stat_statements wait event in Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible?

Resolution

The pg_stat_statements module tracks the statistics about SQL statements. For PostgreSQL DB instances that are compatible with PostgreSQL 11 or later, the pg_stat_statements library loads by default. For more information, see pg_stat_statements on the PostgreSQL website.

When the number of tracked unique statements exceeds the pg_stat_statements.max value, PostgreSQL deallocates statistics for the least often run queries from the hash table in the shared memory. Deallocation creates space for new entries.

During deallocation, PostgreSQL uses LWLock for the hash table to prevent concurrent access. This might block concurrent backend processes, and you might see the waiting event LWLock:pg_stat_statements in Performance Insights.

Note: If the hash table frequently allocates entries, then the overall performance of the workload might degrade.

Increase the pg_stat_statements.max module

To reduce LWLock:pg_stat_statements wait events, increase the pg_stat_statements.max value in the parameter group. For more information, see pg_stat_statements.max on the PostgreSQL website.

Note: When you increase the value of pg_stat_statements.max, the hash table consumes additional shared memory to store more SQL statement information.

For Amazon RDS for PostgreSQL, you can modify pg_stat_statements.max in the DB parameter group.

For Aurora PostgreSQL-Compatible, you can modify the value in either the DB cluster parameter group or DB parameter group.

After you modify the pg_stat_statements.max in the parameter group, reboot the DB instance to apply the change. During the reboot, you might experience a brief outage. For more information, see Rebooting a DB instance: basic steps and Rebooting a DB instance within an Aurora cluster.

You can't modify parameters in the default DB parameter group or default DB cluster parameter group. To modify parameters in default groups, create a custom DB parameter group or custom DB cluster parameter group. Then, associate it with your DB instance or DB cluster.

Note: You can store long query texts in a separate disk file. If the file grows too large because of query length or high pg_stat_statements.max values, then all query texts might be discarded. Then, pg_stat_statements.query fields might become empty. For more information, see pg_stat_statements -- track statistics of SQL planning and execution on the PostgreSQL website.

Reduce the pg_stat_statements entries

It's a best practice to use PostgreSQL version 17 to reduce pg_stat_statements entries. For example, when you use PostgreSQL versions 17 and later, you can replace constant SAVEPOINT names with placeholders. For example, you can save SAVEPOINT sp1 and SAVEPOINT sp2 as two different pg_stat_statements entries in earlier versions of PostgreSQL. However, in PostgreSQL versions 17 and later, these two statements are stored as one entry, for example as SAVEPOINT $1. For more information, see pg_stat_statements on the PostgreSQL website.

If you can't upgrade to PostgreSQL versions 17 or later, then verify whether your application, Object-Relational Mappers (ORMs), and database drivers automatically issue SQL such as SAVEPOINT commands. These automatic SAVEPOINT commands might increase the number of pg_stat_statements entries.

Check how many deallocations occurred

PostgreSQL versions 14 and later

To check the total number of times deallocation happened, review the dealloc column of the pg_stat_statements_info view. You can use that information to adjust your pg_stat_statements.max appropriately. For more information, see The pg_stat_statements_info view on the PostgreSQL website.

To check the number of deallocations over a certain time, select the pg_stat_statements_info view periodically, and then calculate the difference from pg_stat_statements_info.dealloc.

When you select the pg_stat_statements_info view, you might receive the "ERROR: relation 'pg_stat_statements_info' does not exist" error message. This error occurs when pg_stat_statements only loads into the memory but doesn't install on your database. To resolve this error, install the pg_stat_statements extension into the database where your application connects. Connect to the database, and then run the following SQL statement to install pg_stat_statements into the database:

CREATE EXTENSION pg_stat_statements;

For more information, see CREATE EXTENSION on the PostgreSQL website.