DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Formulating a Robust Strategy for Storage in Amazon Relational Database Service PostgreSQL Deployments
  • Just Use PostgreSQL, a Quick-Start Guide: Exploring Essential and Extended Capabilities of the Most Beloved Database
  • Migrating Data From Amazon Neptune to PostgreSQL Using AWS Services
  • PostgresML: Streamlining AI Model Deployment With PostgreSQL Integration

Trending

  • The Human Side of Logs: What Unstructured Data Is Trying to Tell You
  • Automating Data Pipelines: Generating PySpark and SQL Jobs With LLMs in Cloudera
  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  • 5 Subtle Indicators Your Development Environment Is Under Siege
  1. DZone
  2. Data Engineering
  3. Databases
  4. Plotting the Evolution of Logical Replication in PostgreSQL 16

Plotting the Evolution of Logical Replication in PostgreSQL 16

PostgreSQL 16 has been released to include features for making the logical replication feature more robust and performant.

By 
Ahsan Hadi user avatar
Ahsan Hadi
·
Jul. 12, 23 · Opinion
Likes (1)
Comment
Save
Tweet
Share
3.6K Views

Join the DZone community and get the full member experience.

Join For Free

PostgreSQL, a robust and open-source relational database system, is renowned for its native replication mechanisms, logical replication, and physical replication (also known as streaming replication). PostgreSQL 16 includes numerous improvements in logical replication designed to enhance performance, bolster data consistency, and advance compatibility.

In the context of PostgreSQL, logical replication employs a publisher/subscriber model to mirror changes between PostgreSQL servers. The primary node, where the database resides, is the publisher, while the stand-by node, which receives transaction copies, is the subscriber. Changes in the database are replicated from the publisher node to one or more subscriber node(s) identified by the subscription.

Logical replication functions by taking a snapshot of the published database data and copying it to the subscriber. When the subscription commences, changes on the publisher go to the subscriber in real-time. Using a transactional model, changes are applied to the subscriber in the same order they occur on the publisher to ensure transactional consistency.

Conversely, physical or streaming replication transmits data from the primary node to the standby node in Write-Ahead Log (WAL) files. Streaming replication can be synchronous or asynchronous, with the former offering better data protection at the expense of more resources, while the latter is more cost-effective.

Parallel Application of Changes

PostgreSQL 16 introduces several enhancements to logical replication to boost performance, data consistency, and compatibility. In the current scenario, changes for large, ongoing transactions are transferred from the publisher to the subscriber in multiple streams, divided into chunks based on the logical_decoding_work_mem parameter value. PostgreSQL 16 enhances this by applying changes to the subscriber node in parallel using numerous background workers, improving performance by 30-40%. You can review the benchmarking as part of the patch history. 

Binary Format for Initial Data Transfer

With PostgreSQL 16, when you create a subscription, you can use binary format for the initial data transfer. Prior versions performed the initial sync in text format, with the option to change to binary only after starting logical replication. The new functionality allows the initial sync to be done in the format intended for replication, potentially reducing the initial sync time.

It is possible to significantly enhance performance by specifying the index to be used on the subscriber when applying UPDATES and DELETES. This prevents time-consuming full table scans and efficiently identifies the correct row to modify on the subscriber.

Logical Decoding on Stand-By

Previously, the only support for logical decoding was for the primary node. PostgreSQL 16 introduces minimal logical decoding on the stand-by node, allowing the creation of a logical replication slot on a stand-by node, subscription to a stand-by node, and logical decoding on the stand-by node. This functionality introduces the pg_log_standby_snapshot() function, which quickly takes a snapshot of a running transaction and writes it into WAL files without needing a checkpoint.

Origin Filtering (Bi-Directional Replication — It Is Just the Beginning)

PostgreSQL 16 also introduces another feature that can be considered a good start for bi-directional replication support in PG. The origin filtering feature allows the user to create a subscriber with ORIGIN set to None. Previously, the only value accepted for the ORIGIN parameter was ANY. With ANY for Origin, it means that the subscription will send changes regardless of its origin — this leads to infinite loops or errors in case the table has a unique key constraint. 

PG-16 Introduces Origin = None, which enables subscribers to request publishers for changes that don’t have an origin. This makes bi-directional replication possible between two servers,

Both servers will be publishers and subscribers to each other.  

Looking Ahead

These improvements to PostgreSQL 16's logical replication system make it more efficient and robust, setting the stage for more advanced features in future releases. PostgreSQL's dedication to continuous enhancement is why it remains one of the most reliable and renowned open-source relational database systems on the market.

Relational database PostgreSQL

Opinions expressed by DZone contributors are their own.

Related

  • Formulating a Robust Strategy for Storage in Amazon Relational Database Service PostgreSQL Deployments
  • Just Use PostgreSQL, a Quick-Start Guide: Exploring Essential and Extended Capabilities of the Most Beloved Database
  • Migrating Data From Amazon Neptune to PostgreSQL Using AWS Services
  • PostgresML: Streamlining AI Model Deployment With PostgreSQL Integration

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!