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 updated accordingly. I expected the upgrade to Pg12 to have a decent number of changes as a result of consolidating the 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

Comparing 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 in postgresql.conf growing by 40% from Pg version 9.2 (released September 2012) through 12 (pending release October 2019).

Removed

The parameter 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.

Updated

There were three (3) items in postgresql.conf that had default values change from Pg11 to Pg12.

Parameter 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.

New

Now onto the 24 new parameters to the postgresql.conf in version 12.

Connections

There are three (3) new options for connection related settings. Two are related to SSL/TLS encrypted connections, ssl_min_protocol_version and 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 recovery.conf. 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.

• archive_cleanup_command
• primary_conninfo
• recovery_end_command
• recovery_target
• recovery_target_name
• recovery_target_time
• recovery_target_xid
• recovery_target_lsn
• recovery_target_inclusive
• recovery_target_timeline
• recovery_target_action
• restore_command

These next two are found under the warm standby configuration:

• primary_slot_name
• promote_trigger_file

And finally:

• recovery_min_apply_delay

Logging

Postgres 12 adds the ability to track a percentage of the total transactions on a server using log_transaction_sample_rate. 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.

Query Planner

plan_cache_mode stuck out to me when I read about it. The benefit:

"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."

The downside:

"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
please never ever implement it

— Laszlo Hogyishivjak (@kozka) September 13, 2019

Others GUCs

Most people won't need to fiddle with these new controls.

It seems shared_memory_type should be properly set by default (mmap on Linux) for nearly all systems.

The next two, wal_init_zero and 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.

Summary

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 ssl_min_protocol_version to 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!