Scribd
Upload
21 views3 pages

Postgres Performance Optimization Tips

This document provides a quick guide for optimizing Postgres database performance through four key steps: enabling pg_stat_statements to record query performance, logging slow queries, logging explain plans for slow queries, and setting up auto-killing for long-running queries. It emphasizes the importance of these practices for effective performance troubleshooting. Following these steps can significantly enhance the management of Postgres databases in both new and existing setups.

Uploaded by

Ahmet Duruöz
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
21 views3 pages

Postgres Performance Optimization Tips

This document provides a quick guide for optimizing Postgres database performance through four key steps: enabling pg_stat_statements to record query performance, logging slow queries, logging explain plans for slow queries, and setting up auto-killing for long-running queries. It emphasizes the importance of these practices for effective performance troubleshooting. Following these steps can significantly enhance the management of Postgres databases in both new and existing setups.

Uploaded by

Ahmet Duruöz
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Exposing Postgres Performance Secrets

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 .

You might also like