Prepare for Postgres 12: Configuration Changes
PostgreSQL 12 will be released very soon, the current ETA for release is October 3, 2019! I have been looking forward to Pg12 pretty much since we upgraded to Pg11. The full list of amazing improvements in Postgres 12 is too long to get into here, but here are three great reasons:
- Improvements to enable PostGIS to take advantage of parallel query
- Covering GIST indexes
- Generated columns
See the release notes for the full list!
This post is part of the series PostgreSQL: From Idea to Database.
Updates change configurations
With a major upgrade around the corner it is time to review the changes between configuration files, namely
postgresql.conf, and ensure
our deployment process is
I expected the upgrade to Pg12 to have a decent number of changes
as a result of
recovery.conf options alone.
In the past, I have grabbed a clean copy of
postgresql.conf with its defaults and used
git diff to get the results I needed. While that works, it does leave quite a bit to be desired from a user-friendly perspective.
Introducing postgresql.conf compare
With the desire for an easy way to see changes between versions, a lazy Sunday at home resulted in a new site, postgresql.conf compare. The initial version of the site has two main functions:
- Display differences between version X and Y
- Display a single parameter showing history of default values
I loaded default
postgresql.conf files for PostgreSQL versions 9.2 through 12beta4. When Pg12 is released the site will be updated with the final version, though it seems unlikely any of these will change in the next couple of weeks.
PostgreSQL 12 changes
postgresql.conf between Pg 11 and Pg 12, there are 24 new, 3 updated and 1 removed parameter.
The remainder of this post summarizes those changes.
Configuration over Time
As Postgres has grown and evolved, the configuration has naturally done the same. The following table shows the number of options
postgresql.conf growing by 40% from Pg version 9.2 (released September 2012) through 12 (pending release October 2019).
default_with_oids is gone, it had been
disabled by default since after PostgreSQL 8.0. It seems the parameters was added back in to not break old pg_dump files, but it was not listed in the config files I got from building from source.
I haven't confirmed this, but if your databases have/had this option you should check and test before upgrading.
There were three (3) items in
postgresql.conf that had default values
change from Pg11 to Pg12.
autovacuum_vacuum_cost_delay was reduced from 20ms -> 2ms.
Feel free to read the discussion on the change.
The default for
extra_float_digits changed from 0 -> 1. This relates to how FLOAT data is handled with this change intended to improve performance.
Third, the default for
jit changed from off -> on. Enabling
JIT by default is a good
indicator that the Postgres community believes the feature has gotten
good enough to be beneficial more often than not, and be unlikely to cause nasty surprises.
Now onto the 24 new parameters to the
postgresql.conf in version 12.
There are three (3) new options for connection related settings.
Two are related to SSL/TLS encrypted connections,
ssl_max_protocol_version. These allow defining
minimum and maximum versions of TLS to use. While the options are named
ssl_*, the docs state explicitly that protocols preceding TLSv1 (e.g. SSL) are not supported.
"Protocol versions before TLS 1.0, namely SSL version 2 and 3, are always disabled."
The Postgres docs also remind you that TLSv1 is old (circa 1999) and has not been recommended to use since June 2018. I will be changing our configurations to require at least TLS 1.1 though I think I can get away with TLS 1.2 as our minimum.
tcp_user_timeout allows timing out connections but isn't available on all systems, such as Windows and older versions of Linux.
Replication and Recovery
Most, if not all, of the following were moved over from
They do not introduce new or changed functionality.
I have grouped these based on where in the documentation they are described.
The first batch listed are documented in the WAL configuration section.
These next two are found under the warm standby configuration:
Postgres 12 adds the ability to
track a percentage of the total transactions
on a server using
One of the downsides to logging is the associated overhead and logging
all queries can become expensive on high traffic servers. I can see
this option being very appealing to allow monitoring a subset of the queries
without the full negative performance impact.
plan_cache_mode stuck out to me when I read about it.
"This setting overrides the default behavior and forces either a custom or a generic plan. This can be used to work around performance problems in specific cases."
"Note, however, that the plan cache behavior is subject to change, so this setting, like all settings that force the planner's hand, should be reevaluated regularly."
Seeing this added was a bit surprising to me. One of the frequent questions from admins of non-Postgres databases is why Postgres doesn't have a query hint feature. Truns out, it is the best missing feature!
The best missing feature in @PostgreSQL : query hints— Laszlo Hogyishivjak (@kozka) September 13, 2019
please never ever implement it
Most people won't need to fiddle with these new controls.
should be properly set
by default (
mmap on Linux) for nearly all systems.
The next two,
wal_recycle, allow changing how WAL files are managed.
The former allows generating new WAL files with zeros to allocate the space right away, the latter allows reusing WAL files to avoid having to create new files all the time.
default_table_access_method relate to the relatively new pluggable table access methods.
This post outlined differences in the
postgresql.conf options between Postgres 11 and 12. One of the important changes I need to ensure
makes it into our
Ansible deployments is updating
TLSv1.1 or TLSv1.2 instead of leaving the default of TLSv1.0.
The great thing about automatic deployment of configurations is it is easy
to deploy new configurations, test them out, and roll them back if they
don't work for any reason.
I am excited for the official PostgreSQL 12 release so I can get into the final stages of testing and upgrading our servers. I don't expect everyone to rush to upgrade servers, but as I outlined previously you are getting free performance and features. Use them! 🤓
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published September 24, 2019
Last Updated September 24, 2019