PostgreSQL has a lot of parameters to be configured
Published on September 05, 2022 by Stefanie Janine Stölting
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.
This can easily be solved with SQL:
That results in three column listing of all parameters with their current setting and a decent description, what the parameter is about.
|allow_in_place_tablespaces||off||Allows tablespaces directly inside pg_tblspc, for testing.|
|allow_system_table_mods||off||EAllows 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 ;
You can of course just look at a single parameter setting with a SQL statement:
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, ' / ' ) ) AS category , count(*) AS subgroups FROM pg_settings GROUP BY 1 ORDER BY 1 ;
|Client Connection Defaults||46|
|Connections and Authentication||33|
|Reporting and Logging||42|
|Version and Platform Compatibility||8|
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.
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.
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 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.
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:
From PostgreSQL within a docker container it is:
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.
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.