PostgreSQL pgvector Hybrid Search for Web Apps A developer advocates running hybrid vector and keyword search directly inside PostgreSQL using the open-source pgvector extension rather than a dedicated vector database, arguing it suffices for most web apps up to a few million chunks. The approach stores embeddings as a plain column on the same row, written in the same transaction as the content, eliminating sync bugs such as orphaned embeddings from deleted documents or stale vectors after renames. The writeup details a chunk-level schema, a reciprocal rank fusion query merging tsvector keyword results with vector results, a filtered-scan recall trap on multi-tenant tables, and a measurement plan for validating hybrid search against existing keyword search. Two years ago I would have reached for a dedicated vector database first. Now I start with the Postgres instance the application already runs on. pgvector hybrid search in PostgreSQL, using the open-source pgvector extension https://github.com/pgvector/pgvector , is enough for most web apps up to a few million chunks, and it removes an entire class of sync bugs that appear the moment a row and its embedding live in different systems. The failure I see most often is not relevance. It is a deleted document whose embedding survived the cascade, or a product that was renamed while its vector still describes the old text. None of that is possible when the embedding is a plain column on the same row, written in the same transaction as the content. What follows is the schema I use for chunk-level retrieval, the reciprocal rank fusion query that merges keyword and vector results, the filtered-scan trap that quietly destroys recall on multi-tenant tables, and the measurement plan that tells you whether hybrid search actually beats the keyword search you already had. For the retrieval side of this system, read the guide to building a RAG system on a custom knowledge base https://veduis.com/blog/building-rag-system-business-custom-ai-knowledge-base/ . How you split documents before embedding decides most of your retrieval quality, so pair this page with the breakdown of RAG chunking strategies https://veduis.com/blog/rag-chunking-strategies/ . If you want the theory behind the distance operators, vector search fundamentals https://veduis.com/blog/vector-search-fundamentals/ covers embeddings, similarity metrics, and approximate nearest neighbor indexes. Each retrieval method fails in a predictable way, and the failures barely overlap. Vector search matches meaning. It finds "car oil change" for a query about "vehicle maintenance" because the embeddings sit near each other. It is unreliable on strings that carry no semantic weight: part numbers like RP-4471-B , error codes like ERR CONNECTION TIMED OUT , invoice references, and three-letter acronyms that mean six different things depending on the tenant. Lexical search through PostgreSQL's full text search https://www.postgresql.org/docs/current/textsearch-controls.html types, tsvector and tsquery , does the opposite. It nails the exact token and ignores a paraphrase. A user who searches "how do I stop my postgres replica from lagging" gets nothing useful from a document titled "Replication Lag Triage" unless the words happen to line up. Hybrid retrieval runs both branches against the same table and merges the ranked lists. In practice that means a query for RP-4471-B replacement procedure still returns the correct part because the lexical branch locks onto the identifier, while "my order never arrived" finds the refund policy page because the vector branch recognizes the complaint as a refund question. The business case for doing this inside PostgreSQL rather than in a second system comes down to three properties: WHERE clauses, not a separate metadata store you must keep in sync with the index. The mechanism is two ranked lists and a fusion function. Everything else is schema and tuning. websearch to tsquery , match against a generated tsvector column, and rank with ts rank cd . Take the top 50. <= operator, and take the top 50. Two details matter more than the rest. First, embed chunks, not whole documents, because a 40-page handbook averaged into one vector matches everything weakly and nothing precisely. Second, take more candidates from each branch than you return, since fusion can only reorder what it was given. Three tables: a document row for the thing users see, a chunk row for what gets embedded, and the query layer that joins them back together. The chunk table carries the tenant and the generated text vector so both indexes stay on one relation. CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE documents id uuid PRIMARY KEY DEFAULT gen random uuid , tenant id uuid NOT NULL, title text NOT NULL, source url text, updated at timestamptz NOT NULL DEFAULT now ; CREATE TABLE chunks id bigserial PRIMARY KEY, document id uuid NOT NULL REFERENCES documents id ON DELETE CASCADE, tenant id uuid NOT NULL, chunk index int NOT NULL, content text NOT NULL, content tsv tsvector GENERATED ALWAYS AS to tsvector 'english', content STORED, embedding vector 1536 NOT NULL, model text NOT NULL DEFAULT 'text-embedding-3-small', UNIQUE document id, chunk index ; CREATE INDEX chunks tenant hnsw ON chunks USING hnsw embedding vector cosine ops WITH m = 16, ef construction = 64 ; CREATE INDEX chunks tsv ON chunks USING GIN content tsv ; CREATE INDEX chunks document ON chunks document id ; CREATE INDEX chunks tenant ON chunks tenant id ; The generated column means the text vector can never drift from the content: it recomputes on every write, and it cannot be set by hand. The model column is not decoration. When you change embedding models you will need to know which rows are stale, and a column that records the producing model turns an unbounded reindex into a targeted backfill. You cannot add a ts rank cd score to a cosine distance. They live on different scales, and normalization by min-max over a result set shifts from query to query. Reciprocal rank fusion sidesteps the whole problem by throwing away the raw scores and keeping only the ordering: score d = 1 / k + rank lexical d + 1 / k + rank semantic d with k typically set to 60, as in the original reciprocal rank fusion paper https://plg.uwaterloo.ca/~gvcormac/cormacksigir09-rrf.pdf . Each branch contributes a value between 1/61 and 1/110 for its top ranks, so a document that lands near the top of both lists outranks a document that is first in one and absent from the other. The constant compresses the head of each list, which is what makes fusion stable across query types. https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fthul3kqaqe1jyrh76ofb.jpg This is the query, with $1 as the raw user text, $2 as the tenant, and $3 as the query embedding: -- Per-transaction settings. ef search trades latency for recall; -- relaxed order lets filtered scans keep walking the graph. SET LOCAL hnsw.ef search = 100; SET LOCAL hnsw.iterative scan = relaxed order; WITH lexical AS MATERIALIZED SELECT c.id, c.document id, row number OVER ORDER BY ts rank cd c.content tsv, q DESC AS rank FROM chunks c, websearch to tsquery 'english', $1 q WHERE c.tenant id = $2 AND c.content tsv @@ q ORDER BY ts rank cd c.content tsv, q DESC LIMIT 50 , semantic AS MATERIALIZED SELECT c.id, c.document id, row number OVER ORDER BY c.embedding <= $3 AS rank FROM chunks c WHERE c.tenant id = $2 ORDER BY c.embedding <= $3 LIMIT 50 , fused AS SELECT coalesce l.id, s.id AS chunk id, coalesce l.document id, s.document id AS document id, coalesce 1.0 / 60 + l.rank , 0.0 + coalesce 1.0 / 60 + s.rank , 0.0 AS score FROM lexical l FULL OUTER JOIN semantic s USING id , best per document AS SELECT DISTINCT ON document id document id, chunk id, score FROM fused ORDER BY document id, score DESC SELECT d.id, d.title, d.source url, b.score FROM best per document b JOIN documents d ON d.id = b.document id ORDER BY b.score DESC LIMIT 10; Four things about that query are easy to get wrong: DISTINCT ON is load-bearing. MATERIALIZED matters. LIMIT inside it. FULL OUTER JOIN , but every score is now a single term, roughly half of what a two-branch match earns. That is acceptable for ranking inside one result set. It is not comparable across queries, so do not log it as a relevance metric. k is a knob with a narrow useful range. k = 60 the top ranks are compressed. Below about 20, the first result in either branch starts to dominate everything else. Three failure modes show up after launch, and none of them are visible in a demo. Duplicate chunks. Overlapping chunk windows produce near-identical rows that both score well. Dedupe at write time with a content hash per document, or you will return the same paragraph twice. Lexical-only wins. If every query that hybrid search wins was already answered correctly by keyword search, the vector branch is costing you embedding calls and index memory for nothing. That is a measurement question, covered below. Chunk-level fusion that ignores position. A chunk from the middle of a document has no idea it is supporting material. If the document title or heading matters, include it in the chunk text before embedding, not after retrieval. This is the part that surprises people, and it has nothing to do with which index you picked. Approximate indexes are scanned first, then filters are applied to what the scan returned. The pgvector documentation on filtering https://github.com/pgvector/pgvector filtering states the consequence plainly: if a filter matches 10% of rows and hnsw.ef search is at its default of 40, roughly 4 rows survive. A tenant-scoped query on a shared table frequently has exactly that selectivity, so a naive plan returns four candidate chunks and the fusion step has almost nothing to work with. There are three fixes, and the right one depends on how many distinct filter values you have: SET LOCAL hnsw.iterative scan = relaxed order keeps walking the graph until enough rows pass the filter or it hits hnsw.max scan tuples , which defaults to 20,000. relaxed order can return slightly out-of-order distances but recovers more results; strict order preserves ordering at a higher cost. The IVFFlat equivalent is ivfflat.iterative scan with ivfflat.max probes . Raise hnsw.ef search per transaction with SET LOCAL , never globally. A global setting leaks across your connection pool and quietly raises latency for every query, including the ones that do not need it. Both are approximate. They fail differently, and the choice is mostly about memory and rebuild cost. | Property | HNSW | IVFFlat | |---|---|---| | Recall at comparable latency | Higher, usually without tuning | Lower unless probes is raised | | Build time and memory | Higher, uses maintenance work mem | Lower, builds faster | | Inserts after build | Absorbed into the graph | Decay until a rebuild | | Query knobs | hnsw.ef search default 40 | ivfflat.probes | | Sizing guidance | m = 16 , ef construction = 64 to start | lists near rows / 1000 up to 1M rows, sqrt rows above; probes near sqrt lists | | Fits | Frequent writes, latency-sensitive reads | Large, mostly static corpora with tight RAM | For a web app with active writes, HNSW is the default answer. Its weakness is memory, and the arithmetic is unforgiving. A 1536-dimension float32 vector is 1,536 × 4 = 6,144 bytes before overhead, so one million chunks is about 6.1 GB of raw vectors, plus roughly 128 bytes of graph links per node at m = 16 , plus page overhead. That working set needs to be in memory for single-digit millisecond reads. There are two levers before you buy a bigger server. halfvec stores half-precision vectors at 3,072 bytes for the same dimensionality and supports expression indexes, so you can keep full precision on disk and index the narrowed cast. Binary quantization with binary quantize gets a 1536-dimension vector down to 192 bytes, which suits a first-pass candidate filter where you rerank the top 200 rows with full precision. Build the index after the data is loaded, not before. An HNSW index built on an empty table and then fed a million rows produces a worse graph than one built once over the existing rows, and CREATE INDEX CONCURRENTLY means you can do that on a live table without blocking writes. If Postgres logs the notice that the graph no longer fits in maintenance work mem , believe it: raise the setting for the build session or accept a much slower index build. Without a measurement set, hybrid search is a preference. With one, it is a decision with numbers attached. Build a golden set of 50 to 100 real queries from your own logs, each with the IDs of documents that a human would accept as correct. Then score four configurations against it: lexical only, vector only, hybrid with k = 60 , and hybrid with a reranking model in front of it https://veduis.com/blog/reranking-models-rag-search-quality/ . Report three numbers: ef search , on your hardware, with the pool saturated. Then check the branch attribution. Log which branch produced the clicked result for every query. If hybrid wins are concentrated in queries the lexical branch already handled, you have bought complexity without a user-visible gain. If hybrid wins cluster in natural-language queries and identifier queries where lexical failed, the vector branch is earning its memory. Two budgets worth writing down before launch: p95 under 150 ms for a user-facing search box, and recall@10 within two points of the best configuration you tested. If you cannot hit both, lower ef search and see what recall you give up before you add another system to the stack. Embedding models get replaced, and the replacement is a schema migration whether people call it one or not. A new model means new dimensions, a new distance distribution, and a full re-embed of every chunk. Doing it in place takes search offline. The sequence that works: add a second column and index, backfill in batches, dual-write both embeddings for new content, then flip a flag at read time and drop the old path once the new one has proven itself on the golden set. ALTER TABLE chunks ADD COLUMN embedding v2 halfvec 1536 ; CREATE INDEX CONCURRENTLY chunks hnsw v2 ON chunks USING hnsw embedding v2 halfvec cosine ops WITH m = 16, ef construction = 64 ; ALTER TABLE chunks ADD COLUMN model v2 text; Backfill in batches small enough that autovacuum keeps up, tracking progress with the model v2 column so a failed run resumes instead of restarting. Run ANALYZE after the backfill, then rebuild statistics again after the cutover, because the planner's estimates for the new index start from nothing. The general pattern is the same one that applies to any large table change, covered in zero downtime database migrations https://veduis.com/blog/zero-downtime-database-migrations/ . EXPLAIN ANALYZE, BUFFERS on the actual hybrid query with a real tenant ID, and confirm the plan uses both indexes rather than falling back to a sequential scan. Hybrid search inside PostgreSQL is a schema decision, a fusion function, and a measurement habit. The schema keeps content and embeddings in one transaction. The fusion function turns two incompatible scoring scales into one ranking without normalizing anything. The measurement habit is what stops you from shipping a vector branch that costs money and changes nothing. Get the filtered-scan behaviour wrong and none of it matters, because your candidate pool will be four rows. Get it right and you have one database to back up, one permission model to defend, and a search that handles both RP-4471-B and "my order never arrived" with the same code path.