Postgres v. 16 was released by the PostgreSQL Global Development Group on September 14, 2023, and has roughly 200 updates.
This blog highlights key improvements to Postgres introduced in this latest version.
It’s just FASTER.
Logical Replication updates*
For context, Physical Replication uses byte-by-byte and block addresses whereas Logical Replication uses data objects and their changes based on their primary keys (or other replication identity).
In Postgres Physical replication, each change in the master is streamed through the WAL (Write-Ahead Log) and applied to the standby/destination server. However, there are certain limitations to using Physical Replication:
With Physical Replication, one cannot…
- Replicate between two different major versions or platforms (i.e. Linux and Windows)
- Perform selective (part of the database) replication
- Perform writes in the standby server
Postgres introduced support for Physical Replication in Postgres v. 9.
Logical Replication support was first introduced in Postgres v. 10.
This latest release introduces some *new capabilities:
- Logical Replication from Physical Standbys -
It is best not to have a high load on the primary so this improvement mitigates that risk.
Replicating logically also sidesteps storage issues. An increasingly common use case is to use this to logically replicate to something like Redshift or Snowflake.
- Performance Improvements for long transactions – This helps with Subscriber-side bottlenecks.
Improvements to Boost Performance -
- Parallel execution of FULLand RIGHT JOINs
- SELECT DISTINCTqueries get better performance from incremental sorts.
- Addition of the string_agg and array_agg aggregate functions
- Concurrent bulk loading of data using COPY
- Caching RANGE and LIST partition lookups
Monitoring enhancements -
- The introduction of the pg_stat_io system view offers detailed I/O activity insights. This will assist Administrators with troubleshooting and optimizing their systems.
- Record statistics on the last sequential and index scans on tables in pg_stat_* _tables and pg_stat_*_indexes
- Record statistics on the occurrence of updated rows moving to new pages in the pg_stat_*_tables column, n_tup_newpage_upd
- Speculative lock information to the pg_locks system view
Privilege Administration –
Until now, a Superuser account was needed for many administrative tasks. This practice was not ideal for Postgres environments with multiple administrators. PostgreSQL 16 now allows users to grant privileges to roles only if they possess the ADMIN OPTION for those roles. This new method ensures a more secure approach to privilege management.
This is, of course, a small subset of the many additions and enhancements found in Postgres v.16
The community encourages you to have a look for yourself and give it a spin!
Sources:
