How do you keep a search index up to date?

Incrementally, until roughly 9% of your documents change in a day — that is where the crossover sat on a 100,000-document SQLite FTS5 index, where a full rebuild took 1.36 s and applying the day's changes one document at a time took 1.47 s. At 1,000,000 documents the crossover falls to about 5%. Below that line incremental updates win by one to two orders of magnitude; above it, stop being clever and rebuild.

Everything below was measured on an Apple M3, 16 GB, macOS 26.4.1, using Python 3.14.6's sqlite3 module against SQLite 3.53.4 with FTS5 compiled in (ENABLE_FTS5 appears in PRAGMA compile_options). Node v23.5.0 produced none of it: its built-in node:sqlite is SQLite 3.47.2 with no FTS5 — no such module: fts5 — worth knowing before planning a benchmark around it.

The short answer

• A full rebuild of a 100k-document FTS5 index takes 1.36 s; a single-document update takes 0.10 ms. Rebuilding is 13,600x the work for one changed document.
• The crossover is a change rate, not a corpus size. Incremental wins below ~9% daily change at 100k documents, ~5% at 1M. Most corpora change well under 1% per day, where incremental wins by 4–5x at worst.
• Deleting documents does not bloat an FTS5 index the way folklore says. Deleting 20% of a corpus left the index anywhere from 15% smaller to 10% larger depending on whether automerge fired — run optimize and stop guessing. Delete-and-reinsert churn is what bloats it: +77% size and +100% query latency after five rounds.
• optimize is the cheap fix, not rebuild. On the churned index, optimize cost 0.67 s and restored size and latency; rebuild cost 1.84 s for the same result.
• A reader sees a clean snapshot only if the update is one transaction. WAL with one transaction: 0 torn reads out of 1,212. WAL with per-document commits: 963 torn out of 1,766. Rollback-journal mode: the reader blocked 5.22 s.

How was this measured?

The corpus is synthetic and deterministic. A 20,000-word vocabulary of pronounceable nonsense tokens (bavo, kelira) is sampled with Zipf-ish weights (w_i = 1/(i+1)^0.9); each document is a 6-word title and a 60-word body seeded from its own integer id, so document i is reproducible without storing anything. That yields 414 bytes of text per document — short records, like a product catalogue, not PDFs.

The schema is the external-content pattern from full-text search that works on a plane: a docs table, CREATE VIRTUAL TABLE docs_fts USING fts5(title, body, content='docs', content_rowid='id'), and the three sync triggers. WAL journal, synchronous=NORMAL. Index size comes from dbstat, summing pgsize over the docs_fts% shadow tables, so it is the index alone. The absolute milliseconds are this machine's; the ratios transfer, and the decision turns on ratios.

When does a nightly rebuild stop fitting?

Later than you would guess, and that is the trap.

The index is consistently 0.76–0.85x the size of the text it indexes, and gets relatively smaller as the corpus grows because the vocabulary saturates. Each 10x of documents costs about 11.5x the time (11.7x then 11.4x across the two measured steps) — superlinear, not quadratic. Extrapolating from those three points: 10M documents ≈ 9 minutes, 100M ≈ 1.8 hours, and an eight-hour window runs out around 400 million short documents on one core. That is why nightly rebuilds survive for years.

So the clock is not what kills the nightly rebuild. Freshness is. A rebuild starting at 02:00 leaves a document edited at 02:05 invisible for 24 hours; average staleness is twelve hours no matter how fast the job runs. Teams abandon nightly rebuilds the first time someone asks why a corrected price still shows the old value at lunchtime.

At what change rate does incremental beat rebuild?

Each row applies that fraction of the corpus as UPDATE statements in a single transaction, with the FTS5 triggers doing the index maintenance, against re-running INSERT INTO docs_fts(docs_fts) VALUES('rebuild') on the same corpus.

At 100k the two costs meet at roughly 9% of the corpus; at 1M, at roughly 5%. The 1M figures are cumulative — each row includes the rows above it — while the 100k figures each start from a clean copy.

At 0.1% daily change incremental is 54–68x cheaper; at 1% it is still 4–5x cheaper. And the crossover falls as the corpus grows, because a rebuild is a sequential bulk write while incremental maintenance pays a merge tax that scales with index size. If you are betting incremental keeps winning as you grow, you are betting the wrong way.

Per-document costs, measured with one commit per document:

This contradicted what we expected. Single-document latency is flat as the index grows a hundred-fold — 0.095 ms to 0.114 ms — while the batched per-document cost quadruples. Batching is supposed to be the cheap path. It is not, because the cost is not per-statement overhead: it is FTS5's automerge, which fires once enough new segments accumulate. Three hundred isolated updates never trigger it; five thousand in a transaction do. You are not paying for the write, you are paying off the merge you owe.

Why does the index grow when you delete?

The standard warning about inverted indexes is that a delete cannot remove the term entries — it can only mark the document dead, so the postings stay and the index grows. Two runs of this disagreed, and the disagreement is worth more than either result on its own.

In the first run, deleting 20% of the documents from a clean 100k index took it from 32.1 MB to 27.4 MB — it shrank. In an independent re-run on the same machine it grew, in both possible table layouts:

The mechanism explains both. FTS5's automerge rewrites segments as part of ordinary write traffic, and a rewritten segment drops tombstoned entries as it goes. Whether your index shrinks or grows after a bulk delete depends entirely on whether automerge happened to fire during it — which depends on segment counts you do not control and cannot easily predict.

So do not plan around either number. Plan around optimize, which is deterministic: in the re-run it took the plain index from 88.5 MB to 76.7 MB in 0.39 s, below where it started. Treat a bulk delete as leaving an index of unknown size until you compact it.

Churn is where the bloat actually lives. Repeatedly deleting 20% of the corpus and inserting 20% fresh documents, holding the count at 100,000 throughout:

The non-monotonic middle is real: rounds 1 and 2 barely bloat because automerge keeps up, then it falls behind and the index runs away. That sawtooth is why "we checked the index size once and it was fine" is not a check.

Which of this is engine-specific? The self-healing on plain deletes is FTS5's automerge; do not assume it elsewhere — Lucene leaves deleted documents in segments until a merge policy reclaims them, which can be a long time. What is general to every inverted index is the shape: an update is a delete plus an insert, old postings survive until something rewrites the segment holding them, and that rewrite is deferred, not avoided.

Does search get slower as the index gets dirty?

Yes, and it shows long before the size is alarming. Query latency doubled across the churn above — 0.284 ms to 0.567 ms median over 200 queries, p95 1.389 ms to 1.689 ms. Numbers this small do not matter on one query; they matter when the same factor of two lands on a p99 under load.

Three ways to clean it up, on the same churned index:

optimize merges the FTS5 segments into one and is the operation you want: a third of the cost of rebuild for the same latency. VACUUM does something else — it reclaims free pages, taking the file from 121.7 MB to 75.6 MB — and does nothing for segment fragmentation. You need both; they are not substitutes.

What does a reader see mid-update?

Two OS processes: a writer changing 40,000 of 100,000 documents, and a reader polling the counts of a marker term in the old and new text every 2 ms. If the two counts ever fail to sum to 40,000, that is a torn read.

WAL plus one transaction is a clean snapshot: the reader saw exactly two states, before and after, worst read 14.77 ms. Rollback-journal mode buys correctness by blocking the reader for the entire 5.2-second write — max read latency is the write duration, the failure mode people mistake for "the search box hung".

The third row is the one to internalise. The engine's isolation cannot save you from your own commit boundaries: committing per document made 55% of reads observe a half-updated corpus. If your indexer loops over changed rows and commits each one, users are searching a torn index for the length of the run, and no journal mode fixes that. One transaction per batch is not an optimisation, it is the correctness requirement.

Is it safer to rebuild into a shadow table and swap?

On the 100k corpus, building a second FTS5 index alongside the live one took 1.29 s and took the file from 77.6 MB to 109.8 MB (1.41x, not 2x, because the base table is half the file and is not duplicated). The swap — drop the old table, ALTER TABLE docs_fts_new RENAME TO docs_fts — completed in 6.9 ms inside one transaction, and a following VACUUM cost 0.25 s and returned the file to 75.7 MB.

That 6.9 ms window is the appeal: readers see the old index, then the new one, never an incomplete one. The price is 1.41x peak disk — scale that by your own text-to-index ratio — and a dependence on FTS5 renaming its shadow tables along with the virtual table, which works but is not something to discover in production.

Why do real search engines append segments instead?

Because writing into an existing index is the expensive part, and you can avoid it. Crudely: leave the 100k base index alone and write each day's 1,000 changed documents into a new small FTS5 table, querying the union.

Writing 1,000 documents into a fresh segment costs 0.028 s; writing the same 1,000 into the live index costs 0.28 s — ten times more. In exchange, query latency grows almost exactly linearly with segment count: 0.095 ms at one index, 0.336 ms across eleven. Merging the ten tails back cost 0.77 s and returned query latency to 0.077 ms.

That is the Lucene bargain in one table: writes get an order of magnitude cheaper, reads get linearly worse, and a background merge converts the debt back into speed. The crude version hides a real cost — BM25 scores are computed per index, so ranking across segments needs global term statistics you no longer hold in one place, the same class of problem as why search misses. Do not build this until you have measured that optimize is not enough.

Check it yourself

Save as freshness.py and run it: Python 3 with FTS5 in its sqlite3 module, about 40 MB of disk, cleans up after itself. It builds a 50,000-document index, finds the crossover, then churns it and shows what optimize recovers.

import sqlite3, time, os, random, shutil, statistics as st

N, DB = 50000, "fresh.db"
r0 = random.Random(1234)
CONS, VOW = "bcdfgklmnprstvz", "aeiou"
VOCAB = list({"".join(r0.choice(CONS) + r0.choice(VOW) for _ in range(r0.choice((2,2,3,3,4))))
              for _ in range(30000)})[:20000]
import bisect
W = [1.0/(i+1)**0.9 for i in range(len(VOCAB))]
T, s, CUM = sum(W), 0.0, []
for w in W:
    s += w/T; CUM.append(s)
def word(r): return VOCAB[min(bisect.bisect(CUM, r.random()), len(VOCAB)-1)]
def doc(i, salt=0):
    r = random.Random((i*2654435761 + salt*40503) & 0xFFFFFFFF)
    return " ".join(word(r) for _ in range(6)), " ".join(word(r) for _ in range(60))

def rm(p):
    for x in ("", "-wal", "-shm"):
        if os.path.exists(p+x): os.remove(p+x)

def build(p):
    rm(p); c = sqlite3.connect(p)
    c.execute("PRAGMA journal_mode=WAL"); c.execute("PRAGMA synchronous=NORMAL")
    c.executescript("""CREATE TABLE docs(id INTEGER PRIMARY KEY,title TEXT,body TEXT);
      CREATE VIRTUAL TABLE docs_fts USING fts5(title,body,content='docs',content_rowid='id');""")
    c.execute("BEGIN")
    c.executemany("INSERT INTO docs VALUES(?,?,?)", ((i,)+doc(i) for i in range(1, N+1)))
    c.commit()
    c.execute("INSERT INTO docs_fts(docs_fts) VALUES('rebuild')"); c.commit()
    c.executescript("""
      CREATE TRIGGER ai AFTER INSERT ON docs BEGIN
        INSERT INTO docs_fts(rowid,title,body) VALUES(new.id,new.title,new.body); END;
      CREATE TRIGGER ad AFTER DELETE ON docs BEGIN
        INSERT INTO docs_fts(docs_fts,rowid,title,body) VALUES('delete',old.id,old.title,old.body); END;
      CREATE TRIGGER au AFTER UPDATE ON docs BEGIN
        INSERT INTO docs_fts(docs_fts,rowid,title,body) VALUES('delete',old.id,old.title,old.body);
        INSERT INTO docs_fts(rowid,title,body) VALUES(new.id,new.title,new.body); END;""")
    c.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    return c

def idx_mb(c):
    return c.execute("SELECT SUM(pgsize)/1048576.0 FROM dbstat "
                     "WHERE name LIKE 'docs_fts%'").fetchone()[0]

qr = random.Random(99); QS = [VOCAB[qr.randrange(50, 4000)] for _ in range(150)]
def qmed(c):
    t = []
    for q in QS:
        s = time.perf_counter()
        c.execute("SELECT rowid FROM docs_fts WHERE docs_fts MATCH ? "
                  "ORDER BY rank LIMIT 10", (q,)).fetchall()
        t.append(time.perf_counter()-s)
    return st.median(t)*1000

print("sqlite", sqlite3.sqlite_version, " N =", N)
c = build(DB)
t0 = time.perf_counter(); c.execute("INSERT INTO docs_fts(docs_fts) VALUES('rebuild')"); c.commit()
REB = time.perf_counter()-t0
print("full rebuild of the index: %.2fs   index %.1f MB   query median %.3f ms"
      % (REB, idx_mb(c), qmed(c)))
c.close(); shutil.copyfile(DB, "base.db")

print("\n change/day    docs   incremental   rebuild   winner")
for f in (0.001, 0.01, 0.05, 0.10, 0.50):
    rm(DB); shutil.copyfile("base.db", DB)
    c = sqlite3.connect(DB); c.execute("PRAGMA synchronous=NORMAL")
    k = max(1, int(N*f)); ids = random.Random(int(f*1e4)).sample(range(1, N+1), k)
    rows = [doc(i, 21)+(i,) for i in ids]
    t0 = time.perf_counter()
    c.execute("BEGIN"); c.executemany("UPDATE docs SET title=?,body=? WHERE id=?", rows); c.commit()
    dt = time.perf_counter()-t0
    print(" %-11s %7d %10.2fs %9.2fs   %s" % ("%.1f%%" % (f*100), k, dt, REB,
          "incremental (%.1fx)" % (REB/dt) if dt < REB else "rebuild (%.1fx)" % (dt/REB)))
    c.close()

rm(DB); shutil.copyfile("base.db", DB)
c = sqlite3.connect(DB); c.execute("PRAGMA synchronous=NORMAL")
print("\n churn round   index MB   query median ms")
print(" %-12s %8.1f %14.3f" % ("clean", idx_mb(c), qmed(c)))
alive, nxt, rr = list(range(1, N+1)), N+1, random.Random(11)
for rnd in range(1, 6):
    dead = rr.sample(alive, N//5); ds = set(dead)
    alive = [i for i in alive if i not in ds]
    c.execute("BEGIN")
    c.executemany("DELETE FROM docs WHERE id=?", [(i,) for i in dead])
    new = [(i,)+doc(i, 33) for i in range(nxt, nxt+N//5)]
    alive += [x[0] for x in new]; nxt += N//5
    c.executemany("INSERT INTO docs VALUES(?,?,?)", new); c.commit()
    c.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    print(" %-12s %8.1f %14.3f" % ("%d%% churned" % (20*rnd), idx_mb(c), qmed(c)))
t0 = time.perf_counter(); c.execute("INSERT INTO docs_fts(docs_fts) VALUES('optimize')"); c.commit()
to = time.perf_counter()-t0
c.execute("PRAGMA wal_checkpoint(TRUNCATE)")
print(" %-12s %8.1f %14.3f   (optimize took %.2fs)" % ("optimized", idx_mb(c), qmed(c), to))
c.close()
for p in (DB, "base.db"): rm(p)

python3 freshness.py

On this machine:

sqlite 3.53.4  N = 50000
full rebuild of the index: 0.55s   index 16.6 MB   query median 0.147 ms

 change/day    docs   incremental   rebuild   winner
 0.1%             50       0.01s      0.55s   incremental (54.0x)
 1.0%            500       0.12s      0.55s   incremental (4.4x)
 5.0%           2500       0.49s      0.55s   incremental (1.1x)
 10.0%          5000       0.71s      0.55s   rebuild (1.3x)
 50.0%         25000       2.51s      0.55s   rebuild (4.6x)

 churn round   index MB   query median ms
 clean            16.6          0.162
 20% churned      16.4          0.144
 40% churned      21.0          0.161
 60% churned      25.5          0.177
 80% churned      18.8          0.164
 100% churned     23.0          0.167
 optimized        16.5          0.128   (optimize took 0.21s)

Change N to your own corpus size and the crossover moves — that is the point. The churn column sawtooths harder at 50,000 documents than at 100,000 because automerge fires at different moments relative to the rounds.

Where this goes next

Measure your daily change rate before arguing about any of this. If it is under 1% — and for most catalogues, document sets and reference corpora it is — incremental updates in one transaction per batch plus a weekly optimize is the whole answer, and the nightly rebuild is wasted machine time buying twelve hours of average staleness.

None of it matters if the index was answering wrongly to begin with. That failure sits upstream of the update strategy: the string you indexed and the string they typed are different strings, and no update schedule repairs it.