Since I’m a consultant, I frequently (well, kind of always, actually) run into customers with bad managed PostgreSQL configuration file.

It seems that I keep giving the same advice always and always, so I decided to write a blog post about them.

Too long configuration files

PostgreSQL conf files come with a lot of comments to explain to you what this setting is used for, what value you can put there, and what is the default value. It’s pretty great to learn and that’s really good if you’re a beginner.

But, a lot of people use that commented conf files in production. This is wrong.

A conf file needs to be short if you want it to be processed by a human brain. You should be able to see all your settings on a shell screen. Why would you want a human brain to process your conf file? Because when there’s a problem, humans will need to process that file just to understand how the engine was set and so how it will work.

Comments

How can you manage to have a Postgresql conf file in less than a shell screen? Just get rid of comments, included the commented settings. You don’t need them. Some might object they need the commented value because it reminds them of the default value for that setting. The default settings can be queried in Postgres. You just need to query over the pg_settings system view.

Here is an example of a query:

\set param  shared_buffer

select name,
       setting,
       unit,
       reset_val
from pg_settings
where name = :'param';

(You might notice I use a variable for the setting name. That way, I can reuse the same query several times and just change the variable name.)

Sometimes, you might want to keep the comments that are used to separate different sections of settings. If you know both the configuration file and the documentation well, maybe you have noted that the sections in the file match the chapters of the documentation.

Duplicates and useless settings

So, let’s assume you cleaned up your file and it’s still too long to be watched in a glance. What should we do? There are two things you could still do :

• delete duplicates
• delete settings for which the value is the same as the default

First, let’s talk about duplicates. It happens that sometimes, you’re too lazy to look for your setting and you just add the new value at the end of the file. Please stop doing that! It’s pretty confusing and your co-workers might curse you over 5 generations because they lost half a day trying to figure out why their settings change wasn’t taken into account.

Then, why would setting a parameter to its default value be a bad thing? Two simple reasons: It’s useless and you need to keep your configuration file concise to increase readability. The second reason is more tricky. Let’s say you set a setting to its default value. 4 years later, this default value somehow changes in Postgres because the community found out this new default is more accurate, you won’t be able to use this Postgres improvement!

This query will help you find parameters set to the default value in your postgresql.conf file.

select name,
       setting,
       unit,
       reset_val
from pg_settings
where setting = reset_val
  and source = 'configuration file';

Include directions

If after all this cleanup you still don’t have a concise configuration file, it means you need to split it into sections and use the “include” features, either by including a whole directory or by including each file by name. (See Postgres documentation if you need to figure out how to do that)

As I explained earlier, the configuration file is already divided into sections and these sections match the documentation. It seems pretty accurate to create a file for each section (and to name those files after the section names).

Managing configuration files

As I said earlier, comments in configuration files in production aren’t good for readability and maintainability, so how can we keep a trace of the changes we made into our configuration files?

The answer is so simple: use git (or any other versioning tool). Don’t forget to explain in your commit messages why you made these changes. You might find it useful someday… ;-)

Postgresql.auto.conf

For SQL compliance reasons, Postgres 9.4 was added the ability to change a setting by using alter system statement. The setting changed will be added to the postgresql.auto.conf file which you can’t manage and which is read after the postgresql.conf file. If you decide to use the alter system statement anyway, make sure you:

• document your way of doing things so that your co-workers don’t waste their time
• document the settings changes and their reasons

Conclusion

Managing configuration files is not complicated, but it’s somehow often not managed because no one ever thought about it. Just take the time to understand why you’re doing things in certain ways. If you ever lose half a day because a process is not good, try to improve it.