What is the best way to sync Postgres and ElasticSearch?

Question

I have the choice to Sync ES with latest changes on my Postgres DB

1- Postgres Listen / Notify :

I should create a trigger -> use pg_notify -> and create listener in a separated service.

2- Async queries to ES :

I can update ElasticSearch asynchronously after a change on DB. ie:

model.save().then(() => {model.saveES() }).catch()

Which one will scale best ?

PS: We tried zombodb in production but it doesn’t goes well, it slows down the production.

Answer 1

I recommend you consider https://github.com/debezium/debezium. It has Postgresql support and implements the change capture model proposed in other posts instead of the dual write model.

debezium benefits:

• low latency change streaming
• stores changes in a replicated log for durability
• emits only write events (creates, updates, deletes) which can be consumed and piped into other systems.

UPD. Here is a simple github repository, which shows how it works

Answer 2

as you are asking for the ways, I assume you want to know the possibilities to apply the better architecture, I would like you to propose an advice given by confluent:

here https://www.confluent.io/blog/bottled-water-real-time-integration-of-postgresql-and-kafka/