Cutting a BM25 Index Build From Four Hours to Ninety Minutes

Our first BM25 index over a large public text corpus specified seven text columns and took more than four hours to build. The same index, respecified over two columns, builds in 60 to 90 minutes on the same hardware. Nothing about the machine changed, nothing about the tokenizer changed, and the search quality did not drop, because five of the seven columns were roughly 95% redundant renderings of the same underlying text.

That is the whole story, and it is worth writing down because the lesson generalizes past our corpus, past our stack, and past full-text search. The build time of a text index is not a function of your row count. It is a function of the bytes of text you hand the tokenizer. Once you internalize that, the tuning decisions stop being guesswork.

This post covers the arithmetic we now use before launching any index build, the bulk-load configuration that took the load phase from around five hours to roughly 30 to 45 minutes, and the tools we evaluated and rejected. We work in legal, and the corpus in question is public-domain legal text pulled from a public bulk dataset, but nothing here is legal-specific. If you are running full-text search over tens of millions of wide text rows in Postgres, this is your problem too.

The setup, in numbers

We run the corpus on a single dedicated box: four cores and eight threads on a 2017-era desktop-class CPU, 62 GiB of usable RAM, 452 GB of usable disk on NVMe in RAID 1. It is not a large machine. That constraint is what forced the tuning, and it is why the numbers here are useful to anyone who is not renting a fleet.

The database is Postgres 18.4 with ParadeDB's pg_search 0.24.1, which puts a Tantivy BM25 index inside Postgres. We chose BM25-first and skipped a vector index entirely, for reasons worth stating briefly: on long legal text, BM25 beats zero-shot dense retrieval in the published evaluations we trust, most recently at recall-at-5 of 63.63% against 61.28%. And pg_search is between 20 and 1000 times faster than Postgres's native full-text search at the ten-million-document scale, which is exactly the scale we are at.

We mirror only the handful of tables our product actually reads. One of them is the only one this post is really about: the document-body table, on the order of ten million rows. Ten million rows does not sound like much. But that table is TOAST-heavy and carries roughly 150 GB of text. Row count is a lie here; bytes are the truth.

The rule: roughly one microsecond of build time per text-column-byte

Here is the heuristic we now apply, and the reason this post exists.

Budget about 1 microsecond of index build time for every byte of text in every column you index.

Tantivy detoasts and tokenizes each indexed column, per row, independently. It does not notice that two of your HTML columns are the same document wearing different markup. Seven columns over the same 150 GB of underlying text is not 150 GB of tokenizing work. It is closer to seven times that, because each column gets pulled out of TOAST and run through the tokenizer on its own.

So do the arithmetic before you type CREATE INDEX:

150 GB of text  x  7 columns
  = 1.05 x 10^12 text-column-bytes
  x 1 microsecond per byte
  = ~1.05 x 10^6 seconds
  = ~12 days, theoretical single-threaded

Twelve days is obviously not what we observed. Parallel maintenance workers and the OS page cache pull the real number down to the 4 to 8 hour band, and we landed at "4+ hours" on a four-worker build. The theoretical figure is not a prediction. It is a smell test. It tells you, in ten seconds of mental math and before you commit a single wall-clock hour, that a seven-column spec on this table is in the wrong order of magnitude.

Run the same arithmetic on the lean spec:

150 GB of text  x  2 columns
  = 3.0 x 10^11 text-column-bytes
  -> the same parallelism and cache effects
  -> ~60 to 90 minutes observed

The ratio of the estimates is 3.5 to 1. The ratio of the observed build times is roughly 3 to 1. The heuristic is crude and it does not need to be better than crude, because the decision it drives is binary: is this spec sane, yes or no.

The corrected spec is unglamorous:

-- The lean spec. Two text columns, not seven.
CREATE INDEX doc_text_bm25_idx ON doc_text
USING bm25 (id, plain_text, html_main)
WITH (key_field = 'id', target_segment_count = 4);

-- The seven column spec, for contrast. Same content, five extra tokenizer passes.
-- USING bm25 (id, plain_text, html_main, and several more redundant
--             markup renderings of the same underlying text)

html_main is the cleanest rendering; plain_text covers the rows where the HTML is empty. The other columns were redundant markup renderings of the same underlying text, produced by different upstream processors. Indexing them bought us a fractional recall improvement on a rounding-error slice of rows and cost us three hours per build, every build, forever.

Which brings me to the second rule, the one that turns the first rule from a nice-to-have into a requirement.

Why the estimate has to happen before you press enter

pg_search's build loop does not check Postgres's interrupt status. This is a known, documented upstream behavior, tracked publicly as ParadeDB issue #2211. The consequence is that a BM25 CREATE INDEX is, in practice, uncancellable once it starts.

We confirmed the whole ladder:

• pg_cancel_backend(pid) returns t. The backend keeps burning CPU.
• pg_terminate_backend(pid) returns t. The backend keeps burning CPU.
• kill -INT at the host PID level is ignored.
• kill -9 or a container restart works, and crashes Postgres.

That last one is not a free escape hatch for us, because every table in this database is UNLOGGED, which means a crash truncates them all to empty and costs us the entire load. That decision is deliberate and I wrote about it separately in We Made Our Database Deliberately Lose All Its Data. The interaction is what matters here: an uncancellable build plus UNLOGGED storage means a bad index spec has exactly two exits, and both are expensive. You wait it out and rebuild lean afterward, or you throw away the load. There is no third door.

So the estimate is not an optimization ritual. It is the only control you have, and it is only available before you press enter.

The sanity check is three questions, and it takes under a minute:

1. How big is the table on disk? Over 50 GB means minimize columns aggressively.
2. Is every text column structurally unique, or are some of them the same content re-rendered?
3. What does 1 microsecond per text-column-byte predict? If the answer is measured in days, the spec is wrong.

Then validate on a 100,000-row sample, which costs about five minutes and proves the tokenizer choice before you commit hours. ParadeDB's own documentation says this plainly: index creation is time-consuming, so experiment with tokenizers before running CREATE INDEX. That advice is worth more than it looks.

The other half: getting the load itself under an hour

The index is the second phase. The first phase is getting all eight tables into Postgres at all, and unoptimized that took about five hours. Tuned, the load phase runs in roughly 30 to 45 minutes for the big table. Here is what actually moved the number.

COPY FREEZE inside the same transaction as the TRUNCATE. This is the single highest-leverage line in the loader.

BEGIN;
SET LOCAL synchronous_commit = 'off';
SET LOCAL work_mem = '256MB';
SET LOCAL maintenance_work_mem = '8GB';
TRUNCATE <table>;
COPY <table> FROM PROGRAM 'curl ... | lbzip2 -dc' WITH (FORMAT csv, FREEZE);
COMMIT;

FREEZE is only legal when the table was created or truncated in the same transaction, which is why the TRUNCATE has to be inside the BEGIN. The payoff is that rows land already frozen, so the post-load VACUUM drops from minutes to seconds. Cybertec's benchmark of this puts the VACUUM speedup at 204 times, and the end-to-end load at roughly 4 times.

Drop every index before loading. COPY into a table carrying indexes runs about 30% slower. Rebuilding the primary keys afterward costs about 30 minutes. Net saving on a 2.5-hour load: roughly an hour.

LZ4 for TOAST compression, applied before the COPY.

ALTER TABLE doc_text ALTER COLUMN plain_text SET COMPRESSION lz4;
ALTER TABLE doc_text ALTER COLUMN html_main SET COMPRESSION lz4;

Roughly 80% faster INSERT against the default pglz. The catch that will bite you: LZ4 applies only to newly inserted rows. Existing data keeps its old compression, so this only pays on a fresh load. Which, for a corpus we truncate and reload every quarter, is every time.

Parallelism capped at exactly 4. Four-way CSV split, four concurrent COPYs into the same table. Beyond four, TOAST OID contention starts eating the gains. The same discipline applies to the decompressor: single-threaded decompression per process times eight processes matches our eight logical cores exactly. Giving every process all cores means 64 threads fighting over 8 cores, which is slower than doing it sequentially. The parallel loader nets about 2.5 times over the sequential one.

Split the CSV with a CSV-aware tool. The source CSVs contain embedded newlines inside quoted text fields. split -n l/4 and awk both corrupt rows on that input, silently, and you find out during the smoke test at hour three.

Cache-friendly memory, not maximal memory. shared_buffers at 16 GB, which is 25% of RAM, and not a byte more. Going higher starves the OS page cache and hurts sequential scans, which is what a bulk load is made of. effective_cache_size at 48 GB, maintenance_work_mem at 8 GB for the index build that follows, max_wal_size at 32 GB so checkpoints do not fire mid-load, random_page_cost at 1.1 and effective_io_concurrency at 200 because the disk is NVMe.

Mount with noatime,nodiratime. Five to ten percent extra throughput on write-heavy paths, zero risk. It is free and there is no reason not to.

Stacked up, the current budget looks like this:

The tools we rejected, and why

Three rounds of research went into this, and most of it produced negatives. Negatives are the useful part, so here they are.

The pattern is that the exotic tool is almost never the answer. Tuned COPY plus correct parallelism plus dropping indexes beat every specialized loader we evaluated, and it beat them while adding zero dependencies to a box we have to operate ourselves at 2am.

What this costs us

A tuning post with no cost section is an advertisement, so here is the bill.

We own the operations. There is no managed-service support contract behind this box. When the index build hangs, when the loader dies because an SSH session dropped, when the upstream dataset adds a NOT NULL column mid-quarter that is missing from its own CSV export, that is our afternoon. We have hit all three.

The refresh is a batch job, not a stream. We reload whenever the upstream data refreshes. Between refreshes, our mirror is exactly as fresh as the last dump and no fresher. For the retrieval work this corpus feeds, that refresh cadence is genuinely fine. If we needed same-day freshness, this entire architecture would be the wrong one and we would say so.

A refresh is roughly three hours of downtime. Truncate, reload, rebuild indexes. Our API shim returns 404s during that window. We accept it today; at a larger customer count we would need a second box and a cutover, which is real money we have chosen not to spend yet.

Get the index spec right or wait it out. The uncancellable build means there is no cheap iteration loop on the full table. That is precisely why the 100K-row sample test and the microsecond heuristic exist. They are not process for its own sake. They are the substitute for a Ctrl-C key that does not work.

Old hardware, no ECC. The box is an auction-tier machine with a 2017 CPU and non-ECC memory. It is a fraction of the cost of the current-generation equivalent, and the tradeoff is exactly what it looks like.

The transferable part

Strip out the specifics and two things survive.

The first is the heuristic. About 1 microsecond of build time per text-column-byte. Multiply your table's text volume by the number of text columns you are about to index, apply the constant, and look at the answer before you commit. It will not predict your build time. It will tell you when your spec is off by an order of magnitude, which is the only question that matters at that moment, and it takes ten seconds.

The second is the observation underneath it. Row count is not the cost driver of a text index. Redundant renderings of the same content are indistinguishable to a tokenizer, and every duplicate column you list is a full extra pass over your entire corpus. We paid three hours to learn that once, and now we pay ninety minutes a quarter, forever.

That is a good trade, and the arithmetic that makes it is free.

Priyansh Khodiyar

Co-Founder & CTO

Priyansh leads engineering and AI at Vaquill, from the matter workbench to drafting, document comparison, document matrix, and citation-verified research.