Geo: Investigate using logical replication to implement event log
Right now Geo keeps a cursor on the data in the geo_event_log
table to determine what events it needs to process.
This is a read-only table. From gitlab-com/migration#295 (comment 88041617), there is no guarantee that IDs will appear sequentially when you query the table.
To create a queue in PostgreSQL, we'd need to replicate this data into a table from which we can delete. There are a few possible ways we can do this:
- Implement our own copy mechanism. I'm not sure how we can do this without storing lots of state in Redis or DB about which IDs we haven't seen yet.
- Use cross-database triggers. When one event is inserted to the table, the secondaries use a trigger to insert this into their queues.
- Use
pglogical
to replicate all Geo event tables and then having the secondary delete from the tables when it has finished processing them. - Use PostgreSQL 10's logical replication: https://blog.2ndquadrant.com/logical-replication-postgresql-10/
Edited by Stan Hu