Change data capture: keep a search index in sync without dual writes

A customer searches for a listing that was deleted on Tuesday and finds it, at a price from the Monday before. The engineer who takes the ticket finds the code that updates the search index, sees it is called right after SaveChangesAsync, adds a retry, and closes the ticket. Three weeks later the same ticket comes back with a different listing.

The retry was never going to fix it, because the bug is not a failed call. It is the shape of the code. The request writes to the database and then to the search index, two systems with no transaction across them, and every ordering of success and failure between those lines produces a different kind of wrong. That is a dual write, the same bug class as publishing an event after a commit, and the fix is the same: make one write, then derive everything else from it.

Derived data

One write, then derive everything else

single source of truth

Dual write

API Search index

no transaction covers both writes; every ordering of success and failure leaves them disagreeing

Derive

API → one commit to the database, nothing else in the request

Database

01

source of truth

the only write the request makes

→

Change stream

02

WAL decoding or outbox

commit order, keyed per row

→

Projector

03

idempotent upsert

skip unless the version is newer

→

Search index

04

derived and rebuildable

rebuild fresh, then swap the alias

The dual write, in slow motion

Here is the handler that looks finished:

public async Task UpdatePriceAsync(
    Guid listingId,
    decimal price,
    CancellationToken cancellationToken)
{
    var listing = await _db.Listings.SingleAsync(l => l.Id == listingId, cancellationToken);
    listing.SetPrice(price);
    await _db.SaveChangesAsync(cancellationToken);

    await _searchIndex.UpsertAsync(ListingDocument.From(listing), cancellationToken);
}

Walk the failure orderings. The commit succeeds and the index call times out: the index is stale, and a retry inside the request only helps if the process is still alive to run it. Reverse the two lines and a failed commit leaves the index describing a price the database never held. Two requests edit the same listing 30 ms apart and commit in order, but the second index call lands first, so the index keeps the older price until the next edit. A delete removes the row and dies before removing the document, and the listing haunts search until somebody notices.

None of these are rare under load, and none are fixed by a try/catch. The transactional outbox article covers the same gap for broker messages; a search index is a consumer that happens to be a query engine.

The database is the source of truth. The search index is a cache of it that answers different questions. You do not write to a cache and its source in the same breath and expect them to agree.

Three ways to derive the index

All three share one principle: the request commits to the database and nothing else, and a separate process turns committed changes into index updates. They differ in where the change stream comes from.

An outbox and a projector. The handler inserts the changed document, or just the listing id and a version, into an outbox table in the same transaction as the business row. A projector polls the outbox, applies each change to the index, and marks it done. You control the payload and the ordering key, and you already need the table if you publish events. The cost: every write path must remember to add the row, and a bulk UPDATE from a migration script or a DBA session bypasses it silently.

Log-based change data capture. The database already keeps an ordered log of every committed change. Logical decoding exposes it: declare which tables to publish, open a replication slot, and a consumer receives every insert, update, and delete in commit order with transaction boundaries intact. Nothing bypasses it, because the write-ahead log sees every write, including the DBA's.

-- Source database, with wal_level = logical
CREATE PUBLICATION listings_pub FOR TABLE listings, listing_prices;

SELECT pg_create_logical_replication_slot('search_projector', 'pgoutput');

-- The slot pins WAL until the consumer confirms it. Alert when this grows.
SELECT slot_name,
       active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS retained_wal_bytes
FROM pg_replication_slots;

The PostgreSQL logical decoding documentation describes the stream and the slot semantics. Debezium is the well-known connector family that reads this kind of stream and puts it on a message broker; the mechanism matters more than the product. The cost is operational: a slot retains WAL until its consumer confirms it, so a projector that stops for a weekend fills the primary's disk with logs held on its behalf. That third query is the one to graph.

Polling by updated_at. Every minute, select rows whose updated_at is later than the last watermark and push them. Cheapest to build and full of holes. Two rows with the same timestamp straddle the watermark and one is skipped. A transaction that started at 12:00:00 and committed at 12:00:09 carries a timestamp from before a watermark that has already moved past it. Two application servers with clocks 2 seconds apart disagree with commit order. And a deleted row leaves nothing to poll; you need soft deletes just to see it go. Polling is fine for a nightly report. It is not a synchronization mechanism.

	Outbox and projector	Log-based CDC	Polling by updated_at
Sees every write	Only through code that adds the row	Yes, including bulk and manual	No: deletes and same-timestamp rows slip
Ordering	Per key, by outbox sequence	Commit order from the log	Approximate
Latency	Poll interval, typically under a second	Milliseconds to a second	The poll period
Operational cost	Table cleanup and a worker	Slot monitoring, WAL retention	Almost none, which is the trap

The projector must be idempotent and ordered per key

Whatever produces the stream will eventually deliver a change twice: the projector crashes after writing the document and before acknowledging, and the next run sees it again. Unless every consumer is a single thread, it will also deliver two changes for one key out of order. The projector's job is to make both harmless, and a version number does it: a counter column on the row, or the log position the change came from, anything that increases with every commit to that key.

public sealed record ListingChange(
    Guid ListingId,
    long Version,
    bool Deleted,
    ListingDocument? Document);

public sealed class ListingProjector(ISearchIndex index)
{
    public async Task ApplyAsync(ListingChange change, CancellationToken cancellationToken)
    {
        var stored = await index.GetVersionAsync(change.ListingId, cancellationToken);

        // A replay or an older change carries a version we already hold. Skip it.
        if (stored is not null && change.Version <= stored)
        {
            return;
        }

        if (change.Deleted)
        {
            await index.DeleteAsync(change.ListingId, change.Version, cancellationToken);
            return;
        }

        await index.UpsertAsync(change.Document!, change.Version, cancellationToken);
    }
}

Two details make that production-correct rather than whiteboard-correct. The read-then-write is a race if two projector instances handle the same key at once, so partition the stream by key so one worker owns a listing at a time, or use the index's conditional write on an external version, which most search engines offer, so the check and the write are one operation. And the delete must leave a tombstone that records the version, so a late update at version 41 cannot resurrect a listing deleted at version 42. Idempotency is the consumer's discipline, not the producer's promise; idempotency in API design is the same rule at the HTTP layer.

Rebuild into a fresh index, then swap

Mappings change, a bug corrupts a field, or the projector was down long enough that the slot was dropped. You will need to rebuild, and never in place: a live index being rewritten serves half old and half new documents for the duration, and a rebuild that fails halfway leaves it that way.

Build a new index from the source of truth, then flip an alias so search traffic moves in one atomic step. The arithmetic decides whether that is a lunch break or a weekend: 20 million listings at a sustained 2,000 documents per second takes 10,000 seconds, a little under three hours, during which the old index keeps serving every query. Record the log position before the rebuild starts; when the bulk load finishes, start a projector for the new index from that position. Changes made during those three hours replay on top, and the version check makes the overlap harmless. Then swap, and keep the old index for a day in case the new one is wrong.

Eventual consistency, and how the product hides it

The index will always trail the database by something. Budget it. A projector that handles 1,500 changes per second against a steady 500 has 1,000 per second to spare. A flash sale pushes 5,000 changes per second for 30 seconds: the backlog grows by 3,500 per second for 30 seconds, 105,000 changes, and drains at the spare 1,000 per second, so search runs up to 105 seconds behind and recovers under two minutes after the burst. If that is acceptable, the budget is met. If not, the projector scales out, and per-key ordering is what makes that safe.

The one user who cannot tolerate the lag is the author. They saved the listing and expect to see it. Serve their own listing from the database, not from search: the edit page, the "your listings" page, and the confirmation after save all read the source of truth, so the author never sees the window. Everyone else searches the index, and a result two seconds old is indistinguishable from a current one. Martin Kleppmann's essay on turning the database inside-out is the long form of the argument that everything downstream of the log is a derived view.

Where the pattern gets built

The "Derive a Search Index" fundamentals challenge in Katabench's System Design Studio is this article on a canvas. You wire the API to a Database, run a change stream through a Worker or Message Broker to a Search component, and deterministic rules check the required path and flag a forbidden bypass such as the API writing straight to Search. The capacity simulation reports throughput, p99 latency, and the component that saturates first under a versioned traffic scenario, which is where an under-provisioned projector shows up. It is a reproducible model built for learning, not a provisioned load test.

The projector side, the part that crashes mid-batch and has to recover, is what the Production Outbox Lab is for: you engineer and operate a polling outbox with PostgreSQL and RabbitMQ through executable incidents with real crashes and measured recovery, the at-least-once world an index projector lives in.