PostgreSQL Configuration


PostgreSQL has a lot of parameters to be configured

Published on September 05, 2022 by Stefanie Janine Stölting

PostgreSQL postgres Learning Configuration

4 min READ

The amount of configuration parameters have grown a lot since when I started using PostgreSQL. With PostgeSQL 14, the current version at the time writing, there are currently 346 parameters, that can be configured.

Most of the time it is unnecessary to touch most of them. On the other hand, the default configuration is anything but usable for nearly anything, except en embedded environment.

Looking At Configuration Parameters

This can easily be solved with SQL:

SHOW ALL;

That results in three column listing of all parameters with their current setting and a decent description, what the parameter is about.

namesettingdescription
allow_in_place_tablespacesoffAllows tablespaces directly inside pg_tblspc, for testing.
allow_system_table_modsoffEAllows modifications of the structure of system tables.

Or you can just query the system table, which will give you some more columns:

SELECT *
FROM pg_settings
;

Single Configration Parameter Setting

You can of course just look at a single parameter setting with a SQL statement:

SHOW shared_buffers;
shared_buffers
128MB

Configuration Categories

There is one column, that isn’t part of show all result: category. The categories can help you, where to have a look at.

This groups the the configuration in currently 40 categories. These can be split up into 15 main categories:

/******************************
Calculation of subgroups
by converting the categories
into an array
******************************/
SELECT (
    string_to_array (
      category, ' / '
		)
  )[1] AS category
  , count(*) AS subgroups
FROM pg_settings
GROUP BY 1  
ORDER BY 1
;
categorysubgroups
Autovacuum13
Client Connection Defaults46
Connections and Authentication33
Developer Options20
Error Handling4
File Locations6
Lock Management5
Preset Options18
Query Tuning47
Replication22
Reporting and Logging42
Resource Usage34
Statistics12
Version and Platform Compatibility8
Write-Ahead Log35

More information About Parameters

There is a website, where you can see some more information about the parameters: postgresqlCO.NF. Not only are the giving you good overview, what a parameter is about, the also have direct links to the PostgreSQL documentation for that parameter.

In addition, you can search the source code, to find out, where that parameter is used in the PostgreSQL source code.

Tools For Adjusted Configurations

The tools to generate a the configuration are not a complete replacement for DBAs, and they aren’t meant to be, but they might give you a starting point.

Cybertec PostgreSQL Configurator

The Cybertec PostgreSQL Configurator is a decent tool, where the settings are based on the probable usage of PostgreSQL.
At the time of writing, the newest supported version is 13.

PGTune

PGTune is another tool, to get a decent configuration. It is not as detailed, as the tool by Cybertec.
At the time of writing, the newest supported version is 14.

Configuration Hints

Some of configuration parameters are even connection based, though, they aren’t part of the postgresql.conf file. For example if your clients sets connection name, you can see that in “application_name”. It might be useful to set it in the database connection, because it’ll get into log files.
That might help for example to find the application, that did run a slow query.

If you don’t know, where you can find the configuration, you just need to ask your database:

SHOW config_file;

From PostgreSQL within a docker container it is:

config_file
/var/lib/postgresql/data/postgresql.conf

Attention If Running Patroni And/Or Kubernetes

Patroni

When you are running PostgreSQL with Patroni, you should never change the configuration inside postgresql.conf. Patroni has it’s own configuration handling and it takes care, that all nodes will have the same configuration.
Either change it with Ansible, if you are configuring your databases with that tool, or use patronictl.

Kubernetes

On Kubernetes you have to change the configuration within the YAML file of the operator you are using. Recently we have written about some PostgreSQL operators at PostgreSQL On Kubernetes Experiences.



Author: Stefanie Janine Stölting