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 💯.
Related Articles
- PostGIS Day 2024 Summary
8 min read
- Crunchy Data Warehouse: Postgres with Iceberg for High Performance Analytics
8 min read
- Loading the World! OpenStreetMap Import In Under 4 Hours
6 min read
- Easy Totals and Subtotals in Postgres with Rollup and Cube
5 min read
- A change to ResultRelInfo - A Near Miss with Postgres 17.1
8 min read