# How can we rebuild a search index from a live database without missing writes or indexing impossible intermediate states? > Use one coordinated database snapshot plus its matching change stream, enforce per-document write order, checkpoint only durable writes, and cut over only after validation. Canonical URL: https://www.devobs.io/articles/qa-ge50-search-index-live-rebuild-cutover/ By: Theo Morgan Published: 2025-03-21T18:15:22.306Z Updated: 2026-09-06T08:31:04.426Z Section: Architecture Rebuild from one coordinated database snapshot and change stream. Create a versioned index, backfill the snapshot, apply the matching later changes in source order, and cut over only after checking ingestion and search visibility. The guarantees depend on that boundary, the document projection, and the write-order enforcement; an alias swap alone cannot prevent missing writes or inconsistent documents. ## Which snapshot belongs with the change stream? Use the snapshot associated with the logical decoding setup rather than taking an unrelated snapshot and separately sampling a current position. [PostgreSQL logical decoding](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html) describes a logical replication slot as a stream of changes that can be replayed in the order they were made on the origin server, and it also notes an exported-snapshots mechanism in the same feature area. For implementation details, verify the exact export and import procedure, transaction scope, and validity constraints in PostgreSQL's exported snapshot documentation before you run the rebuild. The key point for the design is that the backfill snapshot and the later change stream must come from the same coordinated boundary, not from two independently chosen reads. Keep the slot and required WAL available for the rebuild. [PostgreSQL logical decoding](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html) warns that replication slots retain required resources and can consume storage if they are left in place, and that after a crash a slot can return to an earlier LSN and resend recent changes. Plan for duplicate handling, monitor retained WAL and source-database capacity, and abort or reseed deliberately if the stream boundary is lost. ## What document consistency can we promise? An atomic source-row image can become one document. A document assembled from several tables needs a transaction-consistent projection; fetching each table's latest value independently can produce a combination that never coexisted. Define how the transform obtains that view before promising consistent joined documents. Source ordering must also reach the write boundary. Serialize events for each document, or enforce a monotonic source-version rule that rejects obsolete events, including deletes. A worker pool that merely reads the log in order can still finish writes out of order. ## What does the rebuild sequence look like? For a product catalog whose documents derive from complete source-row images: 1. Create `products_v2` with its intended mappings and analyzers before loading data. If you use Elasticsearch reindexing as part of the migration, [the reindex API documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/docs-reindex.html) says the destination should be configured as wanted ahead of time and that reindex does not copy source settings or its associated template. 2. Create the logical slot and record the coordinated boundary you will use for both the backfill and the subsequent stream. 3. Run the backfill from that coordinated snapshot boundary, and finish loading its rows before applying later events. 4. Consume the matching change stream, preserving per-document source order and handling deletion explicitly. 5. Advance the durable checkpoint only after every destination write through that position has succeeded. Retry ambiguous outcomes idempotently; never skip an unapplied event because its checkpoint was saved first. 6. Catch up to an explicit source checkpoint, inspect rejects and deletion handling, and verify destination documents are visible to search. 7. Validate representative documents and queries, then change the read alias with a single [Elasticsearch aliases API](https://www.elastic.co/guide/en/elasticsearch/reference/current/aliases.html) request. That API supports multiple actions in one atomic operation; inspect `errors` and `action_results`, and use `must_exist: true` when you want the whole action list to fail if any action fails. ## Does Elasticsearch optimistic concurrency preserve source order? Not by itself. [Elasticsearch optimistic concurrency control](https://www.elastic.co/guide/en/elasticsearch/reference/current/optimistic-concurrency-control.html) uses `if_seq_no` and `if_primary_term` so a caller can avoid overwriting a document changed since it was last read. Those values describe Elasticsearch's own change history. That is useful for protecting Elasticsearch from stale Elasticsearch writes, but it is not the same thing as preserving PostgreSQL source-event order. A stale source event that reads the newest Elasticsearch version can still submit obsolete content successfully. Preserve source order independently; after a conflict, do not blindly fetch a new sequence number and retry stale content. ## What should block cutover or rollback? Block cutover for unhandled rejects, unexplained divergence, missing deletes, lost source history, or lag beyond the application's declared budget. Counts and sampled queries are useful checks, but they cannot prove every document correct. Keep the previous generation current during the rollback window, or catch it up and validate it before reverting. Merely retaining an old index preserves an old view; swapping its alias back does not repair staleness. **Must lag be exactly zero?** Choose a measurable lag budget and checkpoint. A live source can keep advancing while validation runs. **Can the old index supply the backfill?** Only if its coverage and consistency satisfy the rebuild's source contract. A database repair should use database history. Next step: document the coordinated snapshot boundary, projection semantics, durable checkpoint rule, duplicate-handling plan, and rollback catch-up procedure before starting. Reviewed: 2026-09-06 ## Source references - - - -