5mins of Postgres E43: Logical replication in Postgres 15 & why the REPLICA IDENTITY matters
Today, we're going to talk about logical replication improvements in Postgres 15. In particular, we’ll talk about improvements to data filtering, why the REPLICA IDENTITY is important, and how logical replication deals with errors.
Let's dive in!
In this blog post, Amit Kapila describes some of the logical replication features that were improved in Postgres 15.
He starts out by talking about prepared transactions. If you're not familiar with prepared transactions or two phase commit, it's usually used when you have two servers where you're committing the data you're trying to synchronize and make sure that either the transaction fails on both servers or succeeds on both servers. You're not getting constraint violations on one of them, but not the other.
The way you would use this with logical replication is that you first enable the
two_phase = true setting. This can be used to make sure that logical replication replicates data at
PREPARE time instead of at
Now, you still have to do work to actually use this to build, what Amit calls in his article, “conflict-free logical replication architecture”. Postgres doesn't necessarily take care of everything here for you, but it gives you an important building block to then be able to build an effective architecture.
Next, the article walks through a few ways that help you better define which data gets replicated in the replication stream.
Amit starts out by using a new setting that lets you define that all tables in a particular schema should be replicated. This setting saves you from having to list tables manually. You could have done something like this in previous Postgres versions, but in these versions, when the schema got new tables added, you would have to go back and add them later manually.
This new option does it automatically – when a new table gets created, it automatically gets added to the replication stream! Note: On the subscriber, you do have to make sure that the table exists, so this does not replicate DDL. But, this makes sure that the replication stream contains the data. You do still have to make sure that the table definitions are updated ahead of time though.
On the subscriber, you do have to make sure that the table exists, so this does not replicate DDL. But, this makes sure that the replication stream contains the data.
And there are two improvements on how to filter the data in the tables that are being replicated.
Row filters Column lists
First of all, there are row filters. Row filters are simple expressions that are applied to the data in the replication stream and they can do things like: “this column needs to be larger than this value”, or “this column needs to match this LIKE text expression”. Basically, these expressions let you select a subset of your data and just direct that data to your replica.
There are important restrictions here: The most important one that I would note is that the
DELETEs that go into your replication stream will only contain what's referred to as the
REPLICA IDENTITY. Usually, that would be the primary key. The
REPLICA IDENTITY is something you can set on a per table basis. If you are setting a row filter, that row filter needs to include the
REPLICA IDENTITY columns only because of the way this is built inside Postgres.
The REPLICA IDENTITY is something you can set on a per table basis. If you are setting a row filter, that row filter needs to include the REPLICA IDENTITY columns only because of the way this is built inside Postgres.
In Amit’s particular example, if I have
UPDATEs on this table, I would have to make sure that both "c1" and "c2" are included in the
The other thing that was added were column lists. Previously, you always had to replicate the whole table, not a subset of columns – sometimes that is not desirable.
For example, imagine you have a "users" table. You’d like to replicate the metadata, but you don't really want to replicate your hashed password field. With Postgres 15, you now can restrict which columns get replicated!
Previously, you always had to replicate the whole table, not a subset of columns – sometimes that is not desirable.
Similar to the row filters, you also still have to make sure that your column lists include the
REPLICA IDENTITY because otherwise Postgres wouldn't be able to identify which column an
DELETE is referring to.
Now, there are a couple of other improvements here. The one I find quite noteworthy is how logical replication deals with errors. If you've used logical replication in production, you probably ran into a problem where you added a column and you forgot to do that on your subscriber side.
Postgres 15 now has a better way of dealing with errors like that and others. First of all, it lets you define how to turn off replication of a subscription when there are errors. You can say
disable_on_error = true. What this means is that the subscription will actually stop trying to repeat the exact same error again and again when it hits an error the first time, and you can go in and fix it.
There is also a better way to skip transactions. Let's say for some reason you have data that conflicts with what's on the subscriber side and you're okay with skipping over the bad data. There is a new way to skip a particular LSN explicitly. There are some additional safety checks that allow you to make that more easily without running into a couple of problems.
Last but not least, there is an improvement in terms of statistics tracking and monitoring. There's a new pg_stat_subscription_stats view. If you look at this more closely, there are really two main things here for each subscription that you have.
"Sync" would apply at the initial synchronization time and "apply" would apply when the replication stream runs and when you run into errors. The scenario that I've seen often in production is: you mess something up, you forget something, and then suddenly the replication breaks and errors start happening on the subscriber.
This is where I would watch out for the
apply_error_count becoming non-zero or being incrementing from the previous count because that would mean that there is something that needs my attention and I need to go in and fix it.
Thanks so much for joining us for episode 43 of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes!