In September I started looking at replacing OpenSearch in my search engine with something closer to home, beginning with plain Postgres full-text search: tsvector columns, a GIN index, ts_rank_cd for scoring. The big question was latency at a realistic scale, so I built a benchmark on the Raspberry Pi I'm prototyping on. It gave me two wrong answers before it gave me a useful one, and both wrong answers came from how I'd built it.
The setup
The real corpus wasn't available, because the server fleet was paused, so I generated a synthetic one: a million rows of fake documents, indexed the same way the real search table would be. Then I ran two-word queries against it and timed them, cold and warm.
The first result was 27 seconds for a single two-word query. If that were real, Postgres full-text search was dead on arrival, and I could have stopped there.
Mistake one: a vocabulary that was too small
The synthetic documents were built from a word list, and the word list was too repetitive. With a small vocabulary spread over a million rows, every word appears in a large share of documents. My "two-word query" matched 10.8% of the table: more than 100,000 rows to fetch and rank.
Real text doesn't behave like that. Word frequencies in natural language follow a long tail: a few words are everywhere, and most are rare. A realistic two-term query matches a small fraction of a corpus. My synthetic data had flattened that curve, and the benchmark was measuring a query nobody would ever run.
I regenerated the data so that a two-term query matched about 2.2% of the table, roughly 22,000 rows, which is a far more plausible shape.
Mistake two: a temporary table
The second mistake was quieter. I'd put the benchmark data in a TEMP TABLE, because it was throwaway data and that felt tidy.
Temporary tables in Postgres don't use shared_buffers, the main buffer cache, which is 512 MB on the Pi. They use a separate per-session pool, temp_buffers, which defaults to 8 MB. That one detail invalidated every cold-versus-warm comparison I'd run. "Warm" didn't mean what I thought, because the table could never stay in the cache I was assuming it used. The numbers measured the wrong cache.
The fix was boring: put the data in a real table.
The numbers, done properly
With a real table and a realistic match rate, around 22,000 matching rows:
- Cold: about 105 seconds.
- Fully warm, with zero disk reads: about 2.9 seconds.
The cold number is still partly an artifact. I'd bulk-inserted the rows and then updated them without vacuuming in between, which leaves a lot of dead tuples and bloat. A table built up through steady incremental writes wouldn't look like that. I don't trust 105 seconds as a measure of anything except my benchmark construction.
The warm number is the one that matters, because it can't be blamed on disk. With every page in memory, ranking 22,000 matches still cost about 2.9 seconds of pure CPU on the Pi. Postgres has to fetch each matching row from the table and run ts_rank_cd on it, one row at a time. Lucene-based engines like OpenSearch score directly from their own index segments, with no per-match trip back to a row store. That's a structural difference, not a tuning problem.
What the corrected benchmark changed
My plan had said to only reach for pg_search, the Postgres extension that brings Tantivy's BM25 into the database, if ranking quality turned out to be worse than OpenSearch's. The corrected benchmark made latency a reason to look at it too, independent of quality, because Tantivy doesn't pay that per-row cost.
The Pi's ARM CPU is slow, and 2.9 seconds needs re-testing on real server hardware before I treat it as final. But the cost scales with the number of matching rows, so a real corpus of millions of documents would make common terms worse, not better.
What I'd check first next time
Both mistakes had the same root: the benchmark's environment differed from production in a way that changed the result, and nothing flagged it. A synthetic vocabulary needs a realistic frequency distribution, or every query is a worst case. A temporary table lives in a different cache, so any caching conclusion drawn from one is suspect.
Neither of those is exotic. What caught them was that the first number came back implausible, not any check I'd planned. If the first result had been merely disappointing instead of absurd, I might have believed it.
Leave a comment
Comments are reviewed before they appear.