On September 14, 2023, the PostgreSQL Global Development Group introduced PostgreSQL 16, marking the latest release of the renowned open-source database. This version brings significant enhancements in performance, particularly in query parallelism, bulk data loading, and logical replication, including expanded SQL/JSON syntax, additional monitoring statistics for workloads, and heightened flexibility in setting access control rules for policy management across large deployments. click here for more details on the new release of PostgreSQL16 and the feature matrix of PostgreSQL16 click here.

What’s New in PostgreSQl16

1. Logical Replication Enhancements

2. Performance Improvements

3. I/O Statistics Monitoring pg_stat_io

4. Authentication and Privileges Changes

5. Parallelization of Joins

6. SQL/JSON Features

7. Data Type and Functionality Additions

8. Libpq Enhancements

Logical Replication

In this series covering the new features of PostgreSQL 16, we will be addressing refinements made to Logical Replication.

• Logical replication publishers can be created from standby instances. user can also set a Logical replica from the read-only Physical standby this required wal_level = logical on both primary and standby. PostgreSQL 16 introduced a new parameter, origin for the CREATE SUBSCRIPTION command. This parameter controls whether the subscription will only receive changes that do not have an associated origin, or whether it will receive all changes, regardless of origin.
  In other words, the origin parameter specifies how selective the subscription is about the changes it receives. A value origin = NONE means that the subscription will receive all changes, regardless of origin. A value of origin = ANY means that the subscription will only receive changes that do not have an associated origin. Let’s take an example, NODE1 is primary and NODE2 is logical standby, if there are two other standbys for NODE2 – NODE3, NODE4.
  NODE3 has the origin value ANY, so it will apply all changes in WAL logs it received from NODE2 which can have changes of NODE1, and NODE2.
  NODE4 has an origin value of NONE, so it applies only to changes that are generated on the NODE2.
  Standard SQL
  
  CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (origin = none);

  1 2	CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (origin = none);

  Example:-
  
  -bash-4.2$ psql -p 5432 -d test_pg_11 psql (16.0, server 11.21) Type "help" for help. test_pg_11=# CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE PUBLICATION -bash-4.2$ psql -p 5440 -d test_pg_16 psql (16.0) Type "help" for help. test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (origin = none); NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  -bash-4.2$ psql -p 5432 -d test_pg_11 psql (16.0, server 11.21) Type "help" for help.   test_pg_11=# CREATE PUBLICATION pub1 FOR ALL TABLES; CREATE PUBLICATION   -bash-4.2$ psql -p 5440 -d test_pg_16 psql (16.0) Type "help" for help.   test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (origin = none); NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION

• Initial table synchronization in binary format
  This option specifies whether the subscription requests data in binary format from the publisher (default is false). The binary format can be faster but less portable. It’s important to note that only data types with binary send and receive functions will be transferred in binary.
  
  PgSQL

  CREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (binary = true);

  1	CREATE SUBSCRIPTION sub1 CONNECTION 'conninfo' PUBLICATION pub1 WITH (binary = true);

  Example:-
  
  PgSQL

  test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (binary = true); NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION

  1 2 3

  test_pg_16=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 user=postgres dbname=test_pg_11' PUBLICATION pub1 WITH (binary = true); NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION

• Now a logical replication subscriber can use indexes other than the PRIMARY KEY to perform lookups during UPDATE or DELETE operations, Employing REPLICA IDENTITY FULL on the publisher may result in a comprehensive table scan for each tuple alteration on the subscriber, especially in cases where REPLICA IDENTITY or a primary key index is absent.
• Subscribers can now apply large transactions using parallel workers

This setting streaming controls how transactions are sent to subscribers. The value “on” stores changes temporary files, applying them after the transaction is committed. “Parallel” applies changes directly, using parallel workers if available, otherwise, they’re stored temporarily and applied after the transaction is committed. Note, that errors in parallel may not report the finish LSN in the server log.

Example:-

Having delved into the Logical Replication enhancements in PostgreSQL 16 thus far, our journey through its exciting new features continues. In the upcoming blog post, we will delve into Performance Improvements and I/O Statistics Monitoring. Keep an eye out for further insights into the evolving landscape of PostgreSQL 16