PostgreSQL Configuration

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.

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;

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
;

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:

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.