Swapping the database without losing the plot

Category: AI Engineering/Database/Benchmarking

Moving an AI search engine from Qdrant to Postgres with pgvector: why it got clearly worse at first, and the five fixes that brought it back.

Company A runs Postgres everywhere and wanted the assistant's search index in Postgres too, using the pgvector extension, instead of the dedicated vector database (Qdrant) I'd built on. A sensible request: one database to back up, monitor and understand instead of two.

"It's just a storage swap," I thought. The same text, the same vectors, a different box. I measured it anyway, mostly out of habit. The habit paid for itself within the hour.

A fair fight

The setup made any difference attributable to the database alone:

• Same data. All 33,589 text sections were copied with their existing vectors (the numeric fingerprints of their meaning), so nothing was re-embedded. The copy took two minutes.

• Same questions. 246 test questions with a known source passage.

• An exact instrument. A "funnel" tool that follows each question's correct source through every step of the search and records where it drops out. Search is deterministic: same question, same result, every run. So every difference is real, with no noise to argue about.

Both systems run hybrid search: one search by meaning (vectors) and one by exact words (keywords), with the two rankings merged. Qdrant has both built in. In Postgres, the vector half comes from pgvector, and the keyword half from Postgres's built-in full-text search with Swedish word stemming.

Round 1: a clear loss

Twenty questions worse, and almost thirty times slower. The keyword row is the shocker: 26 against 102. Same words, same documents, a quarter of the hits.

"It's just a storage swap" was officially retired.

Fix 1: teach Postgres that rare words matter

Postgres ranks full-text matches by how often the search words appear in a document, adjusted for length. What it doesn't do is ask how rare each word is across all documents. Classic search engines call that rarity weighting IDF (inverse document frequency), and it's the heart of BM25, the standard keyword-ranking formula.

Without it, take a question like "Which number did the Zephyr kayak get?" (names changed). The word "number" appears in thousands of texts; "Zephyr" in a handful. To Postgres they weighed the same, so any long text that said "number" a lot outranked the one text that actually mentioned the kayak.

The fix was to implement BM25 in plain SQL. After indexing, a small table records how many documents each word appears in, per customer. Rebuilding it for 33,589 sections and 105,708 distinct words takes under two seconds. The ranking formula then runs as an ordinary query.

There is a ready-made extension for this (ParadeDB). I left it out: its AGPL licence is a poor fit for a product meant to be resold, and the SQL version is a few dozen lines nobody has to license.

Fix 2: stop the vector index from cutting corners

The next surprise was in the vector half, which should have been identical. Same vectors, after all.

pgvector, like most vector databases, uses an approximate index (HNSW). It finds near neighbours quickly by not checking everything. With its default settings, compared against an exhaustive search:

The default index quietly missed about one in seven of the best matches. On top of that, the scan mode I'd first chosen for filtered queries could return results slightly out of order.

The stronger index got very close: 97 % recall in 3 ms. And yet, in the funnel, it still lost 4 questions compared with Qdrant: 202 versus 207. So I went one step further and let Postgres check every vector. On 33,600 sections that costs about 85 ms. On a typical FAQ knowledge base, a few thousand sections, it costs almost nothing, and it gives perfect recall. For a really large customer, one setting switches back to the index.

A side discovery: above a certain search width, Postgres's query planner on its own decided that exact search was cheaper than using the index. That's why an early test run looked mysteriously accurate and mysteriously slow. The database was quietly being smarter than my settings.

Fix 3: don't score every document that says "2023"

BM25 fixed the ranking but not the speed. A question containing "number" or "2023" matched about 7,000 of the 33,600 sections, and every one of them was scored, word by word.

The fix: pick candidates only through the question's rare words, those appearing in at most 5 % of the documents. Every word still counts in the score; common words just no longer decide who gets scored. Same results, and keyword search went from 159 to 69 ms. In BM25, common words add almost nothing to the score anyway. We were spending most of the time computing near-zeros.

Fixes 4 and 5: the unglamorous ones

Fix 4: shared memory. Building the stronger index crashed with "could not resize shared memory segment… No space left on device", on a machine with plenty of free disk. Docker gives a Postgres container only 64 MB of shared memory by default, and parallel index builds need more. One line in the container setup (shm_size: 1gb) fixed it, and it goes into the production configuration too, where it would otherwise have bitten someone at night.

Fix 5: invisible characters. The migration stopped halfway with "PostgreSQL text fields cannot contain NUL (0x00) bytes". Some text extracted from PDFs contained invisible null characters. Qdrant had stored them without complaint; Postgres refused them on sight. They're now stripped on import, with a test.

Round 2: parity

• Quality is equal. The difference is one question: Postgres fixed one and lost two. Its keyword half is still a little weaker than Qdrant's, but the meaning half (exact search) is a little stronger, and merged they come out even.

• Speed is worse but irrelevant. 0.16 seconds of search sits next to about 4 seconds of the language model writing the answer. Nobody waits for the database. On a smaller knowledge base, it's faster still.

While we were in there: one table, many customers

Company A serves many organisations from one system, so the new store is multi-tenant from the start: every row carries a tenant ID, and every query is bound to one tenant inside the store itself. It's not left to the code that calls it. A test checks that two tenants searching for the same word each see only their own data. A bug elsewhere in the application can return a wrong answer, but not someone else's answer.

Takeaways

1. "Just a storage swap" is never just a storage swap. Same data and same vectors still gave 20 fewer correct sources and a 30× slowdown out of the box.

2. Postgres full-text ranking isn't BM25. Without rarity weighting, common words drown out the ones that matter. Add IDF, even if you have to write it yourself.

3. Check your vector index's recall against exact search. Defaults favour speed; for a small knowledge base, exact search may cost almost nothing and give you everything.

4. Measure with an exact instrument. Deterministic search means every flipped question is real, so you can fix and verify in minutes instead of arguing about noise.

5. Expect the boring failures. Shared memory and invisible bytes don't show up in architecture diagrams, but they show up at 3 a.m.

pgvector works just as well for this job, but not with default settings. Without measuring, the answers would have got noticeably worse, and nobody would have known why.

Next and last: from prototype to proposal. Docker, GPUs, a polite "no thanks", and what I'd do differently.