Keeping a search index in sync

The problem: two copies of every link

Snip outgrows Postgres full-text search: the agencies want typo tolerance, facets by tag and campaign, and ranking that blends relevance with clicks. Snip adds an OpenSearch cluster. Links still live in Postgres, the source of truth; the search cluster holds a copy shaped for searching. Every create, edit and delete in Postgres has to reach it, in the right order, without losing any, and searches should see an edit within a few seconds.

Don't dual-write

The tempting version updates both in the API handler:

await db.query("UPDATE links SET title = $2 WHERE code = $1", [code, title]);
await search.index({ index: "links", id: code, body: { title } }); // what if this fails, or runs out of order?

That's the dual write of 07.06 The outbox pattern and change data capture: if the second call fails, the index is wrong until someone notices; if two edits race, the older one can land last. Scripts and admin tools that change Postgres directly never reach the index at all.

Feed it from the database's changes

Make Postgres the only thing that's written, and stream its changes to an indexer:

Three details keep it correct:

• Re-read, don't trust the event. The indexer fetches the link's current row rather than applying the change in the event. A late or repeated event then just re-indexes the latest state: safe to repeat (07.03 Delivery guarantees and idempotent consumers).
• Version every document. Index each document with the row's version (an updated_at or a counter), and tell the engine to ignore a write whose version is older than the one it holds. Two indexers racing can't regress a link.
• Deletes are events too. A deleted link must be removed from the index, or searches keep finding links that 404.

How fresh is "fresh"?

Add up the stages: commit to outbox relay (a fraction of a second, or the poll interval), the topic, the indexer's batching (say, 500 ms), and the engine's refresh. Elasticsearch refreshes every second by default, though only for indices that have been searched in the last 30 seconds. Snip's measured end-to-end is two to three seconds, inside the "a few seconds" it promised owners.

For an owner's own edits, two seconds can still feel broken: they rename a link, search for the new name, and get nothing. Like read-your-writes elsewhere (08.02 Consistency models), the dashboard covers the gap: after an edit it shows the updated link from Postgres at the top of the results until the index catches up.

Rebuilding from scratch

Sometimes the whole index must be rebuilt: a new analyzer, a new field, a mapping change, or a bug that corrupted documents. Doing it in place means a long period where search is half-built. Instead:

1. Create a new index (links_v8) with the new settings, alongside the live one (links_v7).
2. Backfill it from Postgres in bulk, while the live change stream is written to both indexes.
3. Compare counts and sample queries between the two.
4. Switch an alias (links → links_v8) in one atomic step. Searches use the alias, so nothing else changes.
5. Keep links_v7 for a day as a way back, then delete it.

It's the same shape as expand and contract (04.09 Schema changes and online migrations): build the new thing beside the old, move over, clean up.

Notice when it drifts

Bugs, missed deletes and failed bulk requests make copies drift (04.12 Denormalisation). A nightly reconciliation job compares the index with Postgres: counts per owner, a sample of documents by version, and links deleted in Postgres but still in the index. It repairs what it finds and alerts if the drift is large, because large drift means a bug in the pipeline.

Recap

Interview questions