Tuesday, April 3, 2018
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.
Please submit the .CSV file as a patch to Postgres; IMHO it should be available as a read-only table in the database. Tools could join to it when presenting current configuration data.
ReplyDeleteI like also how you have category and sub_category, while the current pg_settings view only has category.
And even just adding this as it is would hopefully come with the requirement that adding new configuration settings comes with the obligation to add information here too.
You're welcome to try to get some of that tuning advice into the PostgreSQL official docs. That requires obtaining consensus on what the advice should be, though, so good luck.
DeleteFor that matter, I would *love* to see anyone overhaul the categories for settings; today, 1/4 of the settings get stuck in Miscellaneous (called "Client Connection Defaults").
In 9.3, I tried to make it project policy that we don't offer user-facing GUCs with no tuning recommendations. I was outvoted. That's why I specifically mention vacuum_multixact_freeze_min_age above.
Are you familiar with postconf from Postfix?
ReplyDeletepostconf -n outputs the current non-default settintgs from the configuration file. It is sorted. You can put it in your config file.
postconf OPTION gives you the current value for OPTION.
That is much more useful than reading the config file.