You can achieve consistency using a transactional outbox and "homegrown" solutions the following way.
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.
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.