Syncing a database to Elasticsearch: a step-by-step guide

The search synchronization challenge

Your e-commerce platform shows products in Elasticsearch, but the database says they're out of stock. Your support team gets angry emails, and you're left debugging why search and source of truth diverged. Syncing a database to Elasticsearch: a step-by-step guide isn't optional—it's the difference between a reliable search experience and one that makes customers distrust you. In this article, we'll walk through the practical approaches, trade-offs, and implementation details that let you keep your search index fresh without burning down your infrastructure.

Why database and search fall out of sync

Elasticsearch is eventually consistent by design. Your database commits a transaction, but the search index doesn't know about it until you tell it. That lag—whether it's milliseconds or minutes—is the synchronization problem. The longer the lag, the staler the search results. Pick the wrong sync strategy, and you'll either spend weeks firefighting consistency bugs or over-engineer a solution that becomes fragile.

Option 1: Dual writes—simplicity at the cost of risk

The simplest approach: when your application writes to the database, also write to Elasticsearch immediately. Same transaction handler, two destinations.

// Pseudo-code: update product in database and Elasticsearch
async function updateProduct(id, changes) {
  const dbResult = await db.products.update(id, changes);
  
  await elasticsearch.index({
    index: 'products',
    id: id,
    body: {
      name: changes.name,
      price: changes.price,
      stock: changes.stock,
      updatedAt: new Date().toISOString()
    }
  });
  
  return dbResult;
}

Trade-off: Simple, low latency—but fragile. If the database write succeeds and the Elasticsearch write fails, you're inconsistent. If your application crashes between the two writes, you're inconsistent. If a network partition hits during the Elasticsearch call, the timeout logic matters.

Use dual writes only if you have a fallback reindex job and can tolerate brief stale windows. It works for non-critical features or very low-scale systems.

Option 2: Outbox pattern with a worker—reliable and pragmatic

Write to the database in a single transaction, and include a record in an outbox table. A separate worker polls the outbox, processes events, and pushes updates to Elasticsearch. This is the workhorse of many e-commerce systems because it separates concerns and is straightforward to debug.

// Step 1: Update product and write to outbox in a single transaction
async function updateProduct(id, changes) {
  await db.transaction(async (trx) => {
    await trx('products').where('id', id).update(changes);
    
    await trx('outbox').insert({
      id: uuid(),
      entity: 'product',
      entityId: id,
      action: 'updated',
      payload: JSON.stringify(changes),
      createdAt: new Date(),
      processed: false
    });
  });
}

// Step 2: Worker polls outbox every 5 seconds
async function syncWorker() {
  const batch = await db('outbox')
    .where('processed', false)
    .orderBy('createdAt', 'asc')
    .limit(100);
  
  for (const event of batch) {
    const product = await db('products').where('id', event.entityId).first();
    
    await elasticsearch.index({
      index: 'products',
      id: product.id,
      body: transformProduct(product)
    });
    
    await db('outbox').where('id', event.id).update({ processed: true });
  }
}

Trade-off: Reliable and easy to reason about, but you're managing a worker process and an extra table. If the worker crashes, Elasticsearch falls behind until it restarts. If you scale to multiple instances, you need idempotency and lock handling to avoid duplicate updates.

This is the sweet spot for most teams. You get transactional safety without Kafka.

Option 3: CDC with Debezium—near real-time with complexity

Change Data Capture tools like Debezium watch your database's transaction log (PostgreSQL WAL, MySQL binlog) and stream changes to Kafka. A consumer reads from Kafka and updates Elasticsearch. This is the most sophisticated approach and gives you the freshest search index, but it requires running Kafka and managing a new failure domain.

// Debezium PostgreSQL connector configuration (simplified)
{
  "name": "postgres-products-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres.local",
    "database.port": 5432,
    "database.user": "debezium",
    "database.password": "secret",
    "database.dbname": "shopdb",
    "table.include.list": "public.products",
    "topic.prefix": "shopdb"
  }
}

// Kafka consumer updates Elasticsearch
function consumeChanges(kafkaMessage) {
  const { op, after } = JSON.parse(kafkaMessage.value);
  
  if (op === 'c' || op === 'u') {
    // Create or update
    elasticsearch.index({
      index: 'products',
      id: after.id,
      body: after
    });
  } else if (op === 'd') {
    // Delete
    elasticsearch.delete({
      index: 'products',
      id: after.id
    });
  }
}

Trade-off: Near real-time consistency (sub-second lag possible) and decoupled from application code, but you're running Debezium + Kafka + consumer logic. Debugging spans multiple systems. If the consumer falls behind, backpressure can back up to your database.

Use CDC if you're already running Kafka or if freshness is non-negotiable (trading systems, ride-hailing active matches).

Making updates idempotent

Whichever approach you choose, make your updates idempotent: the same change applied twice should equal the same change applied once. Use your database primary key as the Elasticsearch document ID. This way, if your worker retries or if a change arrives twice, the second write overwrites the first with identical data.

// Use the product ID as the Elasticsearch doc ID
await elasticsearch.index({
  index: 'products',
  id: product.id,  // Database primary key
  body: {
    name: product.name,
    price: product.price,
    stock: product.stock,
    version: product.version  // Optional: track mutation order
  }
});

// Idempotent: calling this twice with the same product produces the same result

If you're worried about ordering—for example, a fast sequence of updates to the same product—add a version or timestamp field and use Elasticsearch's conditional update logic or a versioning strategy to reject stale writes.

The reindex job: recovery and full rebuilds

Even with the best sync strategy, you need a full reindex job. It's insurance. Run it weekly or monthly to catch any drift, and run it on demand if you suspect corruption or schema changes.

// Full reindex from database to Elasticsearch
async function reindexProducts() {
  // 1. Create a new index with current mappings
  const newIndex = `products_${Date.now()}`;
  
  await elasticsearch.indices.create({
    index: newIndex,
    body: {
      mappings: {
        properties: {
          id: { type: 'keyword' },
          name: { type: 'text', analyzer: 'standard' },
          price: { type: 'double' },
          stock: { type: 'integer' },
          updatedAt: { type: 'date' }
        }
      }
    }
  });
  
  // 2. Stream all products from database
  const stream = db('products').stream();
  const bulk = [];
  
  for await (const product of stream) {
    bulk.push({ index: { _index: newIndex, _id: product.id } });
    bulk.push(transformProduct(product));
    
    if (bulk.length >= 2000) {
      await elasticsearch.bulk({ body: bulk });
      bulk.length = 0;
    }
  }
  
  if (bulk.length > 0) {
    await elasticsearch.bulk({ body: bulk });
  }
  
  // 3. Swap alias
  await elasticsearch.indices.updateAliases({
    body: {
      actions: [
        { remove: { index: 'products_old', alias: 'products' } },
        { add: { index: newIndex, alias: 'products' } }
      ]
    }
  });
}

Use an alias (products pointing to the real index) so you can swap indices without changing application code. Reindex takes time for large datasets, so do it off-peak and in parallel with your live index.

Monitoring lag: alert when search falls behind

You can't fix what you don't measure. Add monitoring to track how far behind Elasticsearch is.

For outbox pattern: Count unprocessed rows in the outbox table. Alert if the count grows or if the oldest unprocessed event is older than your SLA.

// Prometheus metric: outbox lag in seconds
async function collectOutboxLag() {
  const oldest = await db('outbox')
    .where('processed', false)
    .orderBy('createdAt', 'asc')
    .first();
  
  if (!oldest) {
    return 0; // No lag
  }
  
  const lagMs = Date.now() - oldest.createdAt.getTime();
  return Math.round(lagMs / 1000);
}

For CDC with Kafka: Monitor Kafka consumer lag. Most monitoring tools (Confluent, Kafdrop) expose this out of the box.

For dual writes: Periodically sample database records and check if their Elasticsearch counterpart exists and is current. This is less precise but catches silent failures.

Common mistakes

• Forgetting to handle deletes. When a product is deleted from the database, it must be deleted from Elasticsearch too. Dual writes and outbox patterns often miss this.
• Not using database IDs as Elasticsearch document IDs. Using auto-generated Elasticsearch IDs breaks idempotency and makes lookups harder.
• Skipping the reindex job because "we have real-time sync." Real-time sync fails silently. Reindex catches bugs you didn't know existed.
• Running sync workers without deduplication. If your worker can run on multiple machines, the same outbox event will be processed twice, potentially creating duplicate documents or outdated data winning.
• Not alerting on lag. Sync falling behind is silent. By the time support notices, you're hours behind and scrambling.

Key takeaways

• Choose a sync strategy by freshness needs: dual writes for low-risk features, outbox + worker for most cases, CDC + Kafka for trading or real-time matching systems.
• Always use database primary keys as Elasticsearch document IDs to ensure idempotency and make retries safe.
• Deploy a full reindex job as insurance. Run it weekly and on demand after schema changes.
• Monitor lag religiously. Alert when the outbox grows or Kafka consumer falls behind, and investigate immediately.
• Test failure scenarios: what happens if the worker crashes, if the database is overloaded, if Elasticsearch is slow? Your choice of sync strategy should have a clear failure mode.