Karen Jex: Tuning PostgreSQL to work even better (PGConf.EU 2023)

Tune your PostgreSQL instance for optimal performance by learning how to adjust key parameters, including cache size, max connections, and log settings, and discover how to use tools like `show parameter` and `pgSettingsView`.

Key takeaways

Some Postgres parameters require a restart to change, while others can be changed online.
The default PostgreSQL.conf file assumes a sequential read takes one whatever one is, so it’s not a memory allocation.
The effective cache size is an estimate of how much memory is available for disk caching.
The default value of 4 megabytes for cache size might be too high for fast disks.
Each DB connection requires a certain amount of resource, so it can be worthwhile setting some parameters.
The default value for max connections is 100.
You can use the show parameter command to see the current value of a parameter in use.
Changes to these parameters will affect existing sessions.
Effective cache size can be calculated as 50-70% of total RAM.
Some parameters, such as log_min_duration_statement, can be set to make debugging easier.
Log line prefix can be set to add information to log lines.
Benchmarking is important to find the right settings for your system.
Autovacuum can be set to a higher value for specific sessions that need it.
There are super user parameters that can be changed in the postgresql.conf or a super user can set.
Some parameters, such as fsync, are generally left alone as they affect database reliability.
The default value for work mem is 128 megabytes.
There are many Postgres parameters to consider (361 in version 16).
You can set parameters just for a specific user or role.
You can use the pgSettingsView to get information about the current settings.
It’s recommended to start with a few parameters and then test to see what works best for your system.

Karen Jex: Tuning PostgreSQL to work even better (PGConf.EU 2023)

More talks