Here at pgEdge we are excited to see the release of PostgreSQL 16 – and even more excited at how it advances logical replication within Postgres.  We are also very happy to announce that as of today pgEdge Platform already supports PostgreSQL 16, and pgEdge Cloud supports it as a deployment choice also.

With the release of PostgreSQL 16, the foundation for leveraging logical replication in active-active setups has significantly improved.

Multi-master replication, the ability to write to two or more PostgreSQL instances, each with a full live set of data, is an appealing solution for improving availability. However, it often introduces complexity as a tradeoff. Until now, using logical replication for bi-directional replication in core PostgreSQL caused an infinite loop as each node endlessly forwarded all transactions to other nodes (including the transactions that did not originate on it).

So, what enables Multi-master in core PostgreSQL? It's the transaction loopback and origin filtering of WAL (Write-Ahead Logging) messages.

Transaction loopback occurs when a transaction is replicated from the source to the target and then replicated back to the source. PostgreSQL 16 introduces a feature to address this issue. When creating a subscription, the subscriber can request that the publisher ignore transactions applied via the replication apply process, thanks to the origin messages in the WAL stream.

The WAL stream contains origin messages that identify the source of the transaction, whether it originated locally or from an apply process.

Logical replication in PostgreSQL is a powerful solution for replicating data changes across multiple database instances. PostgreSQL 16 enhances this feature, making bi-directional replication achievable using native replication. However, careful planning and testing are essential to maintain data integrity and consistency.

Addressing previous concerns, pgEdge offers a flexible and scalable high-availability solution for PostgreSQL databases. It achieves multi-master bi-directional replication across databases and locations through the Spock extension. Spock uniquely provides a true multi-master architecture, allowing users to read and write transactions on any master node simultaneously. It captures conflict resolution events, centralizing them for easier management.

For more details on pgEdge visit our blog section at: https://pgedges.com/blog 

In PostgreSQL 16, several other exciting features include:

  • Logical replication from standby servers: Improving Scalability and Availability with Logical Replication from Standby Servers

Credit goes to my colleague, Ibrar, for seamlessly integrating this patch into pgEdge binaries across supported versions. With this enhancement, we can harness the power of standby servers in pgEdge nodes running PostgreSQL 15, achieving Ultra High-availability.

  • Parallel application of logical replication changes: Boosting performance by applying changes to multiple standby servers concurrently.

While we're on this topic, check out the blog posts from my colleague Ahsan at: https://pgedges.com/blog/postgresql-16-logical-replication-improvements-in-action 

https://pgedges.com/blog/postgresql-replication-and-upcoming-logical-replication-improvements-in-postgresql-16 

  • Support for regular expression matching in pg_hba.conf and pg_ident.conf: Adding flexibility and efficiency in matching user and database names using regular expressions. Now, in both of these fields (DATABASE and USER), we can add regexps, by prefixing them with “/”

  • New pg_stat_io view: Offering comprehensive I/O statistics for troubleshooting performance issues and database optimization. With this view in place, we'll be able to:

    • Monitoring system-wide buffer cache hit ratio calculation precisely

    • Accumulating system-wide I/O times, providing a comprehensive view beyond the existing I/O counts in pg_stat_io.

    • Enhancing cumulative WAL statistics, surpassing the capabilities of pg_stat_wal.

    • Expanding I/O tracking to include tables and indexes, offering a more comprehensive overview.

To get the latest insights on distributed PostgreSQL subscribe to the blog at https://pgedges.com/blog.