Extensions Available On RDS Postgres: pg_stat_statements

PostgreSQL extensions provide a way of adding extra functionality to your PostgreSQL database. I think of it as a plugin that you can enable without any changes to the core code of the database engine.

There are several extensions out there, but limited number available on managed PostgreSQL database services in the Cloud. I will go through the options available on AWS RDS and their use cases, with some examples.

pg_stat_statements:

This extension allows you to tracks execution statistics for all SQL statements executed by the database. Just like other extension, this extension has to be added to the shared_preload_libraries in the parameter group of your RDS instance for it to be enabled. After rebooting your Postgres RDS instance, you can create the extension in your databases and further configure pg_stats_statements to define:

pg_stat_statements.max: This defines the number of statements track by the module. If the number of distinct statements exceeds this limit, information about the least frequently executed statements is removed. The default is 5000 which is sufficient for most systems, but this can be increased if you notice you are not able to find statistics about least executed queries on you system.

pg_stat_statements.track: This setting determines which statements are tracked by the module. Use top to track only top-level statements (those executed directly by clients), all to include nested statements (such as those called within functions), or none to disable statement statistics tracking entirely.

pg_stat_statements.track_utility: This determines whether the module tracks utility commands. Utility commands include all commands except SELECT, INSERT, UPDATE, DELETE, and MERGE.

pg_stat_statements.track_planning: The pg_stat_statements.track_planning setting determines whether the module tracks planning operations and their durations. Enabling this option can introduce a significant performance overhead, particularly when many concurrent connections execute statements with the same query structure, as they compete to update a limited number of pg_stat_statements entries.

pg_stat_statements.save: This specifies whether statement statistics are preserved across server restarts. If disabled, statistics are neither saved during shutdown nor reloaded when the server starts.

HOW TO ENABLE pg_stat_statements on RDS:

1. Check shared_preload_libraries in parameter group and ensure that pg_stat_statements is added.

   Add if not added. Since default parameter groups are uneditable, you'll need to create a custom parameter group.

2. Reboot your RDS instance since this parameter requires a reboot.

3. Login into the database and create your extension with the command

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   

4. This will create a pg_stat_statements table; check if table exist

    SELECT * FROM pg_stat_statements;
   

5. Adjust the parameters above as needed for tracking. Note that pg_stat_statements.max requires a database reboot, while the others are dynamic and can be changed immediately.