New Annotated Config Files for PostgreSQL 10

Teal Deer: The Annotated.conf has been updated for PostgreSQL 10, and it's a Github repo now.

13 years ago, for PostgreSQL 8.0, I released the first Annotated.conf because we had (at that time) over 140 configuration settings and most people had no idea how to set them.  Today Postgres has nearly twice as many (269), and the problem is twice as bad. More about that later.

The repository is intended to be a quick reference for how to figure out how to set a lot of these parameters.  It includes my tutorial slides in both Libreoffice and PDF format, and a giant table of settings and recommendations in either CSV format or as a PostgreSQL 10 database dump.  Accompanying each setting are my notes on why you'd change the setting and, if so, to what.

Probably more usefully, I've included two sample .conf files.  postgresql.10.simple.conf contains the 20 most commonly changed settings with detailed advice on how to set them.  extra.10.conf has the next 20 most-likely-to-be-changed settings, with somewhat more limited advice.  The idea is for you follow the instructions, and then drop one or both of these files into your include_dir configuration directory and have a more sensible configuration without messing with the preposterously long default configuration file that ships with most PostgreSQL packages.  Those 20 to 40 settings should cover 90% of the needs of 90% of users.

If you feel that my advice is inaccurate, incomplete, or out-of-date, then Github has both Issues and Pull Requests available for you to suggest replacements.

Now, I can't help but feel that the configuration situation with PostgreSQL is getting worse.  While the database has improved with some better sensible defaults, and some good auto-configuration code (for example, for transaction log size), overall both the number of settings and the number of settings most users have to care about keeps going up.

The worst part of this is settings added with absolutely no information on how to determine a reasonable level for the setting.  For example, these four data flushing settings were added in version 10:

#backend_flush_after
#bgwriter_flush_after
#checkpoint_flush_after
#wal_writer_flush_after

... but there is no concrete advice on how to set these. Yes, there are docs, but even as someone who has a solid grounding in database, memory, and filesystem performance, I'd hesitate to recommend any specific level for these for a specific problem.  I'd like to be optimistic and assume that an explanation is coming in Postgres 11 or so, but after four years there's still nothing on how to set vacuum_multixact_freeze_min_age.

In the meantime, hopefully annotated.conf gives you a good start on tinkering with your own PostgreSQL settings.