Make sure postgresql is configured with `synchronous_commit = remote_apply`
* Create a postgresql logical replication slot which creates a postgresql snapshot in time.
* Start a repeatable read transaction with the snapshot id
* Store all relevant data from the snapshot in sqlite / kv store
* Start listening for WAL changes ( json or protobufs )
* Receive WAL change, mark to postgresql the "write" position of the slot
* Process the data and query all relevant data for materialization from sqlite/kv
* Send data to elasticsearch
* Mark to postgresql the "flush" and "apply" position of the slot
This way you achieve consistency using "homegrown" or Kafka connect possibly too.
So when I transact data into tables I immediately fetch the latest outbox id.
And then when query from Elasticsearch I first fetch what the last outbox id of the processed data is.
This way I know if the transaction was already processed into Elasticsearch or not. Repeat. Until outbox id of Elasticsearch is equal or higher than the outbox id of the mutation.
This way I don't have to use logical replication, no k/v store and I can just use a script that fetches and processes the latest outbox changes on a loop.
They are using Debezium + Kafka for receiving the WAL changes. And using send the processed WAL offsets back using a Kafka topic to Debezium + Postges. This way they can achieve consistency
Materialize, by contrast, has been explicitly designed to preserve the consistency present in your upstream system. Our PostgreSQL source, for example, ensures that transactions committed to PostgreSQL appear atomically in Materialize, even when those transactions span multiple tables. See our "consistency guarantees" docs for some more information [0]. We have some additional features coming down the pipe, too, like allowing you to guarantee that your queries against Materialize reflect the latest data in your upstream sources [1].
[0]: https://materialize.com/docs/unstable/overview/isolation-lev...
[1]: https://github.com/MaterializeInc/materialize/issues/11531