Rebuilding Our Search Index Without Taking the Site Down

We run HaoNews on a single Postgres 15 instance with a read replica, and search is the part that keeps me up. The index is about 4.2M documents (stories plus comment threads), average doc size 1.8KB, and reindexing the old way took a little over three hours with the site noticeably slow the whole time. The trigger for this work was a schema change: we needed to add a topic_family field and change the analyzer for CJK titles. A naive REINDEX would have locked writes and eaten the pager budget for an afternoon.

What I did instead was build the new index alongside the old one. Postgres lets you create an index with CONCURRENTLY, but it only handles one index at a time and doesn’t help when the analyzer changes. So the plan was: new table, backfill in batches, then cut over with a view.

CREATE TABLE search_docs_new (
  id bigint PRIMARY KEY,
  story_id bigint NOT NULL,
  topic_family text NOT NULL,
  body_tsv tsvector NOT NULL
);

-- backfill in id ranges so each batch stays under a second
INSERT INTO search_docs_new (id, story_id, topic_family, body_tsv)
SELECT id, story_id, topic_family,
       to_tsvector('simple', coalesce(title,'') || ' ' || coalesce(body,''))
FROM search_docs
WHERE id > $1 AND id <= $2;

I drove the backfill from a small Python loop, 5k rows per batch, sleeping 200ms between batches, and watching replica lag. Then I kept it fresh with a trigger on the source table that wrote to both old and new tables. After a day of shadow reads comparing result sets, I swapped a view name and dropped the old table. Total write pause: zero. Total time: about 26 hours, mostly waiting.

The mechanism worth naming is that dual-write is the cheap part and the comparison is the expensive part. I logged the top 50 queries from the previous week, replayed them against both indexes, and diffed the ranked IDs. About 3% differed, and every one of those was a CJK title where the old analyzer had been tokenizing per character. That was the point of the change, so I accepted it, but I would not have known to accept it without the diff.

Where this breaks: if your source table has heavy update churn, the dual-write trigger becomes a second write path you now have to keep correct, and I did have one bug where a soft delete updated search_docs but not search_docs_new because the trigger only fired on INSERT. I caught it in the diff, not in staging. Also, this whole approach assumes you can afford roughly 2x index storage for a day; on our box that was fine, on a smaller instance it might not be. And CONCURRENTLY on a busy table still takes an AccessExclusive lock briefly at the end, so it is not free either.

The question I keep coming back to: how do you decide when the diff is clean enough to cut over? I used 50 replayed queries and a manual eyeball, which felt thin. If you have a threshold or a harness you trust for search parity, I would like to hear what it looks like.