Search indexing pipeline

The search index is a derived copy. The database stays the source of truth, and the index has to follow it without losing or reordering updates. The tempting first design is a dual write: the app writes to the database, then writes to the index. It fails when the second write fails or the process crashes between the two.

The big idea

Think of a filing clerk who copies every change from the original cabinet's log, not from the moment a form was filled in. The copy follows the log, so it can be rebuilt, retried and replayed.

The picture breaks in one place. The copy lags behind the original by seconds. That is fine for search, but not for anything that needs to read its own write right away.

Walking through it

The robust pattern has four parts.

1. Capture. A CDC tool reads the database's write-ahead log or binlog and publishes each row change to a log such as Kafka.
2. Enrich. An indexer consumes the changes and joins related data, such as a brand name or a category path.
3. Write. The indexer bulk-writes documents to the search cluster, keyed by primary key, with the source's version number.
4. Reject stale updates. The cluster refuses any write whose version is lower than the stored one.

Step 4 is what makes replays safe. Here is one product, with versions arriving out of order:

version 7 arrives   → indexed (stored version is now 7)
version 6 retried   → rejected, 6 is older than 7
version 8 arrives   → indexed (stored version is now 8)

Refresh timing matters too. Lucene makes new documents searchable only when it refreshes, which Elasticsearch does every second by default. So a new document appears within about a second. During a large bulk load, you can set the refresh interval to -1 and replicas to 0, then restore them afterwards.

Reindexing behind an alias

Changing an analyzer, a field type or the shard count needs a new index. Applications should query an alias, not the index name, so the switch is one step.

1. Create products_v8 with the new mapping.
2. Backfill it from the source, while the CDC consumer writes to both indexes.
3. Compare document counts and run a set of golden queries against both.
4. Swap the alias from products_v7 to products_v8 in a single atomic call.
5. Keep v7 for a few days, in case you need to roll back, then delete it.

Common mistakes

• Dual writes. A crash between the two writes leaves the index quietly wrong.
• A non-idempotent indexer. Retries then create duplicates or overwrite newer data with older data.
• Expecting read-your-write from search. Documents appear after a refresh, roughly a second later.
• Reindexing into the live index. Build a new index and swap the alias instead.
• Deleting the old index too early. Keep it until the new one has proven itself.

Check yourself

1. A replay sends version 6 after version 7 is indexed. What should happen?

Answer: The index rejects version 6, because the stored version 7 is newer, so the replay does no harm.

2. Why reindex behind an alias instead of in place?

Answer: The alias swap is atomic, so queries move to the new index with no downtime, and rolling back is one more swap.

Key takeaways

• Keep the source database as the truth. The search index is a derived copy.
• Use CDC into an ordered log instead of dual writes.
• Make indexing idempotent with versions, so retries and replays are safe.
• Reindex into a new index, swap the alias, and keep the old index for rollback.

Further learning

Background reading: event streaming and the outbox pattern in distributed transactions.

Next, see how keyword and vector results are combined in hybrid search.