Exposing Postgres Performance Secrets

We spend a lot of time at Crunchy Data helping people dig into the performance of their Postgres. If you're setting up a new Postgres database or already running on in production there are a number of very basic steps you can take that will save your tail in the future when it comes to investigating performance. Here is your guide that'll take less than 5 minutes to get in place. Future you will thank you for doing this today.

1. Use pg_stat_statements to record queries

Pg_stat_statements records and parameterizes queries, how long they run, how often and much more for you. With pg_stat_statements you can ask questions such as:

• Which ones have taken up the most amount of time cumulatively to execute
• Which ones are run the most frequently
• And how long on average they take to execute

To enable simply:

CREATE EXTENSION pg_stat_statements;

2. Log slow queries

Postgres can auto-log queries into your standard logs that run over a specified amount of time. Postgres is capable of executing queries in milliseconds quite easily, so the threshold you pick here is important. You don't want to log all queries, but don't want only the single worst offender. For most web applications I tend to recommend 100 milliseconds or 1 second. Set this with:

ALTER DATABASE us SET log_min_duration_statement = '1s';

3. Log the explain plans for slow queries

Setup auto_explain to log the explain plan for slow queries. In addition to logging slow queries this will get you an EXPLAIN plan. You can feed that into tools like despez EXPLAIN or HypoPG. You can enable auto explain for your Postgres database by adding it to the shared preload libraries:

ALTER SYSTEM SET session_preload_libraries = 'auto_explain';
SELECT pg_reload_conf();

Once you've enabled auto_explain you want to set your various config for it on what you want it to log. Here's a reasonable config to start with which will log all queries over 2 seconds:

ALTER SYSTEM SET auto_explain.log_min_duration = 2000;
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_triggers = on;
ALTER SYSTEM SET auto_explain.log_nested_statements = on;
SELECT pg_reload_conf();

4. Kill long running queries

Postgres can easily auto-kill long running queries, this can prevent other performance issues from backing up and causing customer impact. Any query that needs to run longer than your statement timeout you can explicitly change in the session or on a per database user basis.

ALTER DATABASE mydatabase SET statement_timeout = '30s';

Now you're ready when time comes to debug Postgres performance

There you have it, your checklist for preparing your Postgres for better performance troubleshooting.

To recap:

• pg_stat_statments ✅
• log queries over 1s ✅
• log explain plan of queries over 2s ✅
• auto-kill queries over 30s ✅

And your Postgres will be 💯.