[Geo] Logical replication case
We use a regular PG streaming replication with replication slots to setup a read-only replica on a secondary node. The secondary uses that data directly and to replicate external resources, such as repositories, uploads, etc. To build a queue system we need to be able to save some state of any particular item and this is why we have tracking database where we store that state. As an example, I would like to consider here the
geo_event_logs table which is filled up with events on a primary side. It stores 9 types of events. The event data is stored in a separate table, like
geo_repository_deleted_events and the ID of the event details record is referenced in the common
geo_event_log. The ID of event in the common
geo_event_log is used to create a queue system on a secondary. The secondary saves the last processed ID in a tracking database. In this case we can easily request the items that have not been processed on a secondary and update the tracking database after processing them.
However, this approach has one drawback. In highly concurrent environments, there is no guarantee that items will be added sequentially, according to IDs order. So it’s possible that some events will be skipped on a the secondary. The first idea that comes to mind is to save all the processed event ID and use anti-join to filter them. Unfortunately, this solution won’t work at our scale. So we need to find some alternative solution.
Applying Logical Replication to solve our problem
The idea is to have Logical Replication create a RW-capable
geo_event_log table on a secondary, so we could simply remove already-processed items from this table. Theoretically, we could use either Plogical extension or PG 10 logical replication feature. Unfortunately, it’s not as easy as it looks at first glance.
The subscriber should be in the tracking database, this is a clear part but - where to create a publisher?! Ideally, we would want a publisher to listen to the local standby copy on a secondary but that’s not possible as that database is read-only. So the only option (known so far for us) is to create a publisher on a primary database and subscribe secondaries to it.
This solution has a few problems too:
- We would have to have two replications working in parallel which adds overall complexity. It’s harder to set up and maintain
- This makes upgrades more complex. Not only because of more complex setup but also, because logical replication does not work well when the schema is changed on one of the sides
- Synchronization. If two replications have different latencies we can have errors because of not consisting data
The last one could be mitigated (or even fully eliminated) by having logical replication also copy 9 more tables from the main database so all the needed for Geo data is consistent. But this approach brings few more problems by itself, as we now have duplicates and extra resource usage.
Using a single logical replication
- Could we use the only logical replication to replicate everything?
- How much overhead is that?
- How reliable is that?
- Schema changes during upgrading one of the nodes. Should be investigated properly.
In this case we could have a single database on a secondary which would be RW-capable. That would simplify things a lot.