A Technical Dive into PostgreSQL's replication mechanisms

I learned Postgres replication by studying <i>PostgreSQL 16 Administration Cookbook</i> by <i>Simon Riggs et al</i><p>- Chap 11 Backup and Recovery<p>- Chap 12 Replication and Upgrades<p>Highly recommended!<p><a href="https:&#x2F;&#x2F;learning.oreilly.com&#x2F;library&#x2F;view&#x2F;-&#x2F;9781835460580&#x2F;" rel="nofollow">https:&#x2F;&#x2F;learning.oreilly.com&#x2F;library&#x2F;view&#x2F;-&#x2F;9781835460580&#x2F;</a> or <a href="https:&#x2F;&#x2F;www.packtpub.com&#x2F;product&#x2F;postgresql-16-administration-cookbook&#x2F;9781835460580" rel="nofollow">https:&#x2F;&#x2F;www.packtpub.com&#x2F;product&#x2F;postgresql-16-administratio...</a>

&gt; It even assists in PostgreSQL’s implementation of Multiversion Concurrency Control (MVCC) - the WAL keeps a version history of data changes,<p>That&#x27;s not really correct - postgres&#x27; MVCC implementation doesn&#x27;t read from the WAL. Sure, row changes are WAL logged, but that&#x27;s not really related to MVCC.

Good stuff, I&#x27;m a little obsessed with Postgres replication and the WAL (write ahead log).<p>If you&#x27;re an Elixir user, you might find my library for subscribing to Postgres WAL events useful: <a href="https:&#x2F;&#x2F;github.com&#x2F;cpursley&#x2F;walex">https:&#x2F;&#x2F;github.com&#x2F;cpursley&#x2F;walex</a><p>It&#x27;s a lot easier to operate than the typical debezium setup (which is what I think Airbyte uses behind the scenes).<p>I need to write a guide on how to use WalEx with Neon.

This is a guide to logical replication in Postgres where I break down some of the internal components of the database to explain CDC. If you&#x27;ve ever wondered how WAL buffers work, or what happens when a transaction is executed, check it out!

Sortof unrelated, I&#x27;ve been looking at moving some data from SQL Server to Postgres, and one of the big reasons is replication. SQL Server replication has been super brittle for me - it&#x27;s always silently choking, getting desynchronized, or exhibiting weird locking behavior with no indication of the issue until you notice something downstream is broken. It&#x27;s been tough to test Postgres replication though, since a lot these issues only occur at huge volumes of data. Anyone have any experience with the two they can pass on?

There was recently a very interesting overview of the different distributed PostgreSQL architectures:<p><a href="https:&#x2F;&#x2F;www.crunchydata.com&#x2F;blog&#x2F;an-overview-of-distributed-postgresql-architectures" rel="nofollow">https:&#x2F;&#x2F;www.crunchydata.com&#x2F;blog&#x2F;an-overview-of-distributed-...</a>

Is it possible to run some user-defined functions (e.g., to perform some transformations) on the subscriber side? It&#x27;ll be super useful when the external data source is not identical to the source.

It is a cool idea to use Neon and Airbyte together, as database and pushing to analytics is a classic expensive-only use case.<p>I don&#x27;t know how expensive this would get, of course.

Unrelated to the post:<p>Is there realtime features with postgres?<p>It seem like a kludge to have to add redis, mqtt, or kafka to our application to get things as they change.

Logical replication is an immensely powerful for integrating backend data.