Postgres logical replication challenges & solutions

Logical replication is a valuable feature in Postgres that was introduced in Postgres version 10 in 2017. It was originally meant to be used for Postgres-to-Postgres data replication in a way that is compatible over different Postgres versions and databases, by replicating row data in a configurable way (as opposed to physical replication, which does byte-by-byte replication of the exact Postgres data on disk).

The flexibility of logical replication has paved the way for a variety of third party tools to emerge that do useful things with Postgres: for example, ETL tools such as PeerDB, change data capture tools such as Debezium, and local-first sync engines such as PowerSync.

When we built PowerSync using Postgres logical replication, we ran into some challenges related to making the PowerSync architecture compatible with logical replication assumptions. In this post, we will explore some of those challenges and how we solved them. In no specific order, here are the 4 challenges we will examine:

1. Handling schema/DDL changes
2. LSNs that overlap between transactions
3. Accessing TOASTed values 
4. Handling different row identifier permutations

Let’s dive in:

1. Handling schema/DDL changes

An important aspect of Postgres logical replication is that Data Definition Language (DDL) statements are not published in the stream of logical replication messages, so if you are a subscriber to a logical replication publication, you do not receive messages indicating changes made to the source database’s schema. 

Unless you are manually applying schema changes on both the publisher and subscriber databases, you would need to inspect the  published logical replication messages to try to deduce that the schema has changed. Logical replication messages do include type identifiers that can be used to identify columns and their types.

One of our primary design objectives for PowerSync was to make the system as low-maintenance as possible, and schema evolution was one the areas where we didn’t want to put an undue maintenance burden on the developer. 

We did a number of things in pursuit of this design objective:

• Schemaless replication: We made the PowerSync protocol and client-side database storage schemaless. PowerSync replicates data from Postgres as schemaless JSON, and on the client-side, it applies SQLite views on top of the schemaless data. Therefore, the developer can work with the SQLite database as if it has a regular structured schema, while the underlying data is actually schemaless. This reduces the surface area of the system affected by schema changes: we do not need to perform schema migrations on the client-side SQLite database.
• Allow basic schema transformations: PowerSync’s Sync Rules can apply some basic transformations to Postgres data during replication. The developer can use this for some backwards-compatibility, allowing clients to keep using an older SQLite “schema” even if the Postgres schema has changed.
• Detect and handle Postgres schema changes automatically where possible. Lastly, we wanted the system to automatically detect and handle schema changes on the Postgres side as far as possible, and in scenarios where the schema change cannot be detected or handled automatically, minimal developer action should be required (and this should be well-documented). Here is a quick rundown of the some of these scenarios: 

More details on the various scenarios can be found here in the PowerSync documentation.

It should be noted that there are some fundamental limitations to what is possible with Postgres logical replication when it comes to the schema. For example, [.inline-code-snippet]GENERATED STORED[.inline-code-snippet] values are not published in the logical replication stream. 

2. LSNs that overlap between transactions

Postgres creates logical replication messages by applying logical decoding to entries in its Write-Ahead Log (the primary purpose of the WAL is database recovery e.g. in case of a crash). WAL messages are identified by means of Log Sequence Numbers (LSNs).

One thing to note is that you cannot rely on LSNs that appear in the logical replication stream to be a strictly monotonically increasing sequence: Transaction commits are always in sequence in the WAL, but operations inside each transaction can have LSN ranges overlapping with other transactions. Specifically, the "begin" LSN of a transaction may be less than the "commit" of the previous transaction. This typically happens when the transactions were running concurrently on the source server.

Because PowerSync uses a server-authoritative architecture that maintains a global ordered list of operations, we needed a  monotonically increasing “operation ID” ([.inline-code-snippet]op_id[.inline-code-snippet]) sequence to keep track of operations in the Postgres database. 

Initially, we used the Postgres LSNs to derive our [.inline-code-snippet]op_id[.inline-code-snippet] sequence. However, we moved away from that architecture to using our own auto-incrementing integer sequence. One reason for this was the aforementioned issue that LSNs in the WAL are not guaranteed to be monotonically increasing, but there were also other architectural reasons:

• We want to architecturally support sharded databases where each Postgres instance has its own LSN sequence that has to be merged into a singular [.inline-code-snippet]op_id[.inline-code-snippet] sequence
• When we reprocess sync data, we need to continue with the same [.inline-code-snippet]op_id[.inline-code-snippet] sequence, while the LSN sequence does not get reset.

PowerSync stores operation history using pluggable storage in the PowerSync Service, which is where we maintain this auto-incrementing integer [.inline-code-snippet]op_id[.inline-code-snippet] sequence.

3. Accessing TOASTed values

You may be familiar with TOAST, which is the “The Oversized Attribute Storage Technique” in Postgres: If a value exceeds the page size (8 kB by default), Postgres compresses the value, and if still remains larger than the page size, it is stored in a separate TOAST table, and a pointer to the TOAST value is placed in the original table. 

TOAST affects logical replication, in that a “TOASTed value” is only published in the logical replication stream if the value has changed. Otherwise, the field has an ‘unchanged’ flag. For a system like PowerSync, it is useful to be able to know the current state of any row that is published in the logical replication stream. But because of TOAST, we cannot guarantee that all column values will be represented in the logical replication messages. [1]

In order to be able to access current field values regardless of TOAST, PowerSync stores an additional copy of the current value of any field that we replicate. This is a form of stateful stream processing — this useful write-up by Debezium has some more details.

4. Handling different row identifier permutations

The last challenge relates to [.inline-code-snippet]REPLICA IDENTITY[.inline-code-snippet]. The Postgres [.inline-code-snippet]REPLICA IDENTITY[.inline-code-snippet] configuration controls which row identifier information is recorded in the logical replication stream. Having some kind of row identifier information in the subscriber database is essential to be able to know which row to mutate in the replica when a change to an existing row is received from the publisher database. But tables in Postgres do not necessarily have to have a primary key, hence the additional [.inline-code-snippet]REPLICA IDENTITY[.inline-code-snippet] options available (for a practical analysis, see this blog post).

We wanted PowerSync to support all the different permutations of [.inline-code-snippet]REPLICA IDENTITY[.inline-code-snippet] for developer convenience. What this means is that if the [.inline-code-snippet]DEFAULT[.inline-code-snippet] (i.e. using the primary key as row identifier) is not being used, we need to generate some kind of ID to track changes to rows in the PowerSync replica of the Postgres data. This table summarizes how we derive row IDs depending on the [.inline-code-snippet]REPLICA IDENTITY[.inline-code-snippet] configuration in use. 
‍

As you can see above, if [.inline-code-snippet]REPLICA IDENTITY NOTHING[.inline-code-snippet] is in use with logical replication enabled, Postgres will block updating or deleting rows in that table — if this was not the case, logical replication subscribers would have no way of knowing which rows are being updated or deleted! However, for the purposes of PowerSync, we still generate a random ID in this scenario, so that we can correctly process [.inline-code-snippet]TRUNCATE[.inline-code-snippet] operations: when a table is truncated, we need to remove all rows in that table from the PowerSync replica of the data.

PostgresConf 2024 talk

I spoke about these logical replication challenges and solutions in a talk I gave at PostgresConf 2024 in San Jose, California. I started the talk by going over how PowerSync works, and then ended with the logical replication items. You can see the video of the talk here: (you can also find the slides here)