memo · ADR 0002 · with 0005, hybrid retrieval fused by reciprocal rank
2.pgvector in Postgres, not a vector database.!
- The problem
- Ask has to find the right note among thousands, by meaning and by exact words, filtered by time and by user, and cite it.
- The options
- A hosted vector database beside Postgres (Pinecone, Qdrant); pgvector inside the Postgres that already holds everything; full-text search alone.
- The move
- pgvector. Vector similarity, full-text rank and metadata filters run in one query against one database, and reciprocal rank fusion merges the two orderings so neither recall nor exact matches are lost.
- What we accepted
- One database to tune, with two kinds of index on it, and an approximate index that will need attention past a few million chunks. Not a problem this app will meet for years.
- When we would revisit
- When the chunk table passes a few million rows, or when retrieval latency stops being dominated by the model call.
The interview question this answers is “why not a vector database?”, and the honest answer is that the hard part of retrieval here was never the nearest-neighbour search. It was keeping the search inside the same transaction, the same tenant filter and the same backup as the rest of the data. A second datastore would have meant a second isolation boundary to get right.