Turning on query logging for PostgreSQL in Amazon RDS

This is a very specific topic, but I needed to do it to track down performance of a slow web service call.

First, go to your Amazon RDS instances tab and select the database that needs logging turned on, then go to “See details” under instance actions.

Find the Parameter Group attached to the database. Click on it to edit it, or go to “Parameter groups” in the sidebar and find the corresponding parameter group to edit.

There are two parameters you want to look for: log_statement and log_min_duration. log_statement accepts 3 values: none (default), ddl, mod, and all. For nearly all purposes, you want to change this to all. log_min_duration simply sets a filter (in milliseconds) for when to log a SQL query – this lets you specifically target slow queries. The default value is -1 (disabled). In my case, most queries were in the 3-5ms range, so I set it to 10.

Once you save the parameters, it’ll take about a minute or so for them to be applied to your database(s) and logging to begin. Once they are done applying, go back to “See details” for your database instance, and scroll down to the “Logs” section. Logs (postgres.log files) are displayed from oldest to newest, so you actually have to click through to the last page (for me, page 15) to see the newest log files.

It turns out we had very few queries that were over 10ms, and the slowest one was 68ms, so the bottleneck was elsewhere.

Leave a Reply