Keeping Elasticsearch in Sync with the Primary Database
Fast recall
Quick revision
Core answer
Whether you understand the consistency risks of dual-write and can describe more robust sync patterns that handle failures gracefully.
Dual-writing directly from the application without any fallback mechanism, so a failed Elasticsearch write silently leaves the search index stale with no retry or alerting.
Explain how Debezium CDC reads the PostgreSQL WAL (write-ahead log) to capture every committed change and streams it to Kafka, which then drives the Elasticsearch indexer -- ensuring no event is ever lost even if Elasticsearch is temporarily unavailable.
Interview-ready guide
Interview answer
Keeping Elasticsearch in sync with a primary relational database is a distributed systems problem: you have two stores that must stay consistent, and writes happen to one at a time. The three main approaches each have different failure modes.
Dual-write — application writes to both
- The application writes to PostgreSQL (or MySQL), then immediately writes to Elasticsearch — which means both stores are updated in the same request
- The failure mode: if the database write succeeds but the Elasticsearch write fails (network error, Elasticsearch node down), the data exists in the database but not in search — and there's no automatic retry; the developer must handle this case explicitly with retries, dead-letter queues, or periodic reconciliation
- Not recommended for high-reliability requirements; acceptable when the search index is "best-effort" and a reindex can repair inconsistency
CDC with Debezium — event-driven from the database log
- Debezium captures every row-level change from the database's WAL (PostgreSQL) or binlog (MySQL) and publishes it to Kafka as a change event; a consumer reads from Kafka and applies the change to Elasticsearch
- The advantage: Debezium reads from the database log, which is append-only and reliable — even if the Elasticsearch consumer is down, Kafka retains the events until the consumer is ready; no data is lost during Elasticsearch downtime
- This is the production-grade approach for teams that need reliable sync without dual-write complexity
Scheduled ETL — simple but delayed
- A scheduled job queries the database for records changed since the last run and re-indexes them in Elasticsearch —
WHERE updated_at > ?with a stored watermark - Simple to implement and reason about; the downside is that freshness is limited by the schedule interval — a 5-minute schedule means search results can be up to 5 minutes stale; not suitable for search on data that changes frequently
Use CDC for reliable production sync; dual-write only for low-stakes applications; scheduled ETL only for reference data that changes infrequently.
Keeping Elasticsearch in Sync with the Primary Database
Direct Answer
There are three main approaches to syncing Elasticsearch with a primary database: dual-write from the application (simple but risks inconsistency), event-driven sync via a message queue (decoupled and reliable), and Change Data Capture (CDC) using tools like Debezium that read the database's transaction log (most reliable, no application changes required). CDC is the production-recommended approach for large-scale or consistency-critical systems.
Code Example
Keeping Elasticsearch in sync with your primary OLTP store is a "dual-write" problem — naive code writes to both and drifts on failure. The robust pattern is Change Data Capture (CDC): the database's write-ahead log is the source of truth; a connector tails it and pushes events to ES.
# Debezium Postgres connector config (POST to Kafka Connect)
{
"name": "postgres-orders-connector",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres",
"database.dbname": "app",
"database.user": "debezium",
"database.server.name": "pg",
"plugin.name": "pgoutput",
"publication.name": "dbz_pub",
"slot.name": "dbz_slot",
"table.include.list": "public.orders",
"tombstones.on.delete": true
}
}
// Spring Boot consumer: Kafka → Elasticsearch
@Component
@RequiredArgsConstructor
public class OrderCdcConsumer {
private final ElasticsearchClient es;
@KafkaListener(topics = "pg.public.orders", groupId = "orders-es-sync")
public void onChange(ConsumerRecord<String, JsonNode> rec) throws IOException {
JsonNode payload = rec.value().get("payload");
String op = payload.get("op").asText(); // c=create, u=update, d=delete, r=snapshot
JsonNode after = payload.get("after");
if ("d".equals(op) || after == null || after.isNull()) {
es.delete(d -> d.index("orders").id(rec.key()));
return;
}
OrderDoc doc = mapper.treeToValue(after, OrderDoc.class);
es.index(i -> i.index("orders").id(doc.getId().toString()).document(doc));
}
}
-- For write paths that cannot use CDC, use the Outbox pattern
BEGIN;
INSERT INTO orders (user_id, total, status) VALUES ($1, $2, 'NEW');
INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload)
VALUES ('order', currval('orders_id_seq'), 'OrderCreated', $3::jsonb);
COMMIT;
-- A separate poller reads `outbox` → publishes to Kafka → ES consumer indexes
- Dual-write is the anti-pattern:
db.save()thenes.index()leaves ES stale (or ghost-documented) whenever one side fails after the other committed. No amount of retries fixes the atomicity hole. - CDC (Debezium + Kafka) is the gold standard — the WAL is the single source of truth, and the connector gives exactly-once semantics per message via Kafka offsets and ES versioning. Lag is typically under a second.
- Outbox fits when you own both the write and the publisher: insert an event row in the same transaction as the business write; a separate process tails the outbox and publishes. No CDC connector, no external dependency.
- Reconciliation job nightly: full reindex from the primary DB to catch drift from retries, bugs, or truncated Kafka topics. Track delta with
updated_at >= NOW() - INTERVAL '24 hours'. - Use the ES version field or CDC offset as the external version (
version_type: external) to make ordering idempotent — an older event cannot overwrite a newer state.
Common Mistakes & Pitfalls
Dual-writing directly from the application without any fallback mechanism, so a failed Elasticsearch write silently leaves the search index stale with no retry or alerting.
Check your understanding
Answer these without looking back at the article.
- What is Change Data Capture (CDC) and how does Debezium implement it with PostgreSQL?
- How do you handle ordering of events in Kafka when multiple partitions are used for a table's changes?
- How do you perform a zero-downtime Elasticsearch reindex when the mapping changes?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.