This 9-part Blog series is getting interesting day by day. Yesterday, we concluded our part of the blog about some of the interesting Performance enhancements around System-level, Data handling & modifications, and Workload specific features introduced in PostgreSQL 14.
Today, on this beautiful Sunday, let’s talk about the New Configuration parameters introduced as part of PG14.
Well, a bit of a flashback, I started using PostgreSQL for the first time in 2009, when PostgreSQL 9 was just introduced and we were literally going gaga over it. The journey from PG 9.0 to PG 14 has just been amazing for me, and the amount of feature set built into the PostgreSQL ecosystem is really commendable.
The PostgreSQL Feature Matrix is a great acclamation of the journey thus far and many more years to come.
Here are the new configuration parameters introduced in PostgreSQL 14 major version.
We are not going to go look into all of these parameters, but, a few of my favorites for sure. Let’s delve right into it.
vacuum_failsafe_age and vacuum_multixact_failsafe_age
The vacuum of a table will be forced to accelerate, ignoring the delay parameter, and skip unnecessary garbage collection (for example, the vacuum of the index will be skipped), thereby completely preventing xid wraparound.
Before PG 14, although there are methods, vacuum still receives the control of the delay parameter, and it will take a break during vacuum.In addition, the index vacuum of the table will also increase the vacuum time. The vacuum acceleration of PG 14 is aimed at emergency situations, that is, to prevent xid wraparound.
enable_async_append and enable_memoize
Both of the new configuration parameters provide a crude method of influencing the query plans chosen by the query optimizer. If the default plan chosen by the optimizer for a particular query is not optimal, a temporary solution is to use one of these configuration parameters to force the optimizer to choose a different plan.
enable_async_append – Enables or disables the query planner’s use of async-aware append plan types.
enable_memoize – Enables or disables the query planner’s use of memoize plans for caching results from parameterized scans inside nested-loop joins.
Enables in-core computation of a query identifier. Query identifiers can be displayed in:
- pg_stat_activity view,
- using EXPLAIN
- emitted in the log when
log_line_prefix(%Q) parameter is configured
To be honest, I haven’t seen many postgres instances without the extension pg_stat_statements installed, and if you had it on, the calculation of query_id, which uniquely identifies a query andshows aggregate statistics about queries that have finished executing.
The default is auto which lets modules such as
pg_stat_statements automatically enable it. Previously the query id information was only available with pg_stat_statements, but now this is available with pg_stat_activity (query_id column) as well as in log files when the log_line_prefix – %Q parameter is configured. I’d recommend enabling it on both logging front and in the configuration file.
The choice of compression for PostgreSQL’s TOAST system, which is used to store larger data like blocks of text or geometries, can now be configured. PostgreSQL 14 adds LZ4 compression for TOAST columns, LZ4 is all about using less CPU, compression and decompression speed. However, note that it still retains support for pglz compression, which is the default value.
The parameter track_wal_io_timing enables monitoring of WAL write times. You can also get more details of the I/O impact of WAL writes by enabling the optional track_wal_io_timing setting, which then gives you the exact I/O times for WAL writes, and WAL file syncs to disk. Note this setting can have noticeable overhead, you can use the pg_test_timing tool to measure the overhead of timing on your system. I/O timing information is displayed in pg_stat_wal. The default value is off, so enabling it on-demand seems to be a good option.
Terminate any session that has been idle (that is, waiting for a client query), but not within an open transaction, for longer than the specified amount of time. Disabled by default.
Unlike the case with an open transaction, an idle session without a transaction imposes no large costs on the server, so there is less need to enable this timeout than idle_in_transaction_session_timeout. Be wary of enforcing this timeout on connections made through connection-pooling or ORMs, may not react well to unexpected connection closure.
Sets the time interval between optional checks that the client is still connected, while running queries. The check is performed by polling the socket, and allows long running queries to be aborted sooner if the kernel reports that the connection is closed.
It is a useful parameter, especially when the Application doesn’t have an ability to monitor the query processing of the long-running query. The default value is 0, which disables this feature. For starters, a good value is to set it for a few seconds.