Vector Search in Postgres
Using pgvector for embeddings without adding another database, covering index choice, filtering, quantisation and the point at which you should move off it.
Most teams building their first retrieval system reach for a dedicated vector database. Often they did not need one. If your application already runs on Postgres, the pgvector extension gives you similarity search in the database you are already backing up, already monitoring and already know how to operate.
It is not the right answer at every scale. But the scale at which it stops being the right answer is a lot higher than people assume, and getting there with one system instead of two is worth a great deal.
Setting up
pgvector is an extension, so it either ships with your Postgres or you install it. Every managed provider worth using has it: RDS, Aurora, Cloud SQL, Azure Database, Supabase, Neon.
CREATE EXTENSION vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id bigint NOT NULL REFERENCES documents(id),
content text NOT NULL,
embedding vector(1024),
created_at timestamptz NOT NULL DEFAULT now()
);The dimension is fixed at the column level and has to match your embedding model exactly. Change models and you change columns, which is worth planning for rather than discovering.
Note what you get for free here. The chunk sits next to a real foreign key. You can join it to the document, to the owning tenant, to permissions, to anything else in your schema. In a separate vector store all of that becomes metadata you copy in and keep in sync by hand, and keeping it in sync is a job nobody wants.
Distance operators
pgvector exposes distance as operators, which means the planner understands them and indexes can serve them.
<=>cosine distance<->L2 (Euclidean) distance<#>negative inner product<+>L1 (Manhattan) distance
Which one you use is determined by your embedding model, not by preference. Most modern text embedding models are normalised to unit length and expect cosine. When vectors are normalised, cosine and inner product rank identically, so the choice between them is about arithmetic cost rather than results.
SELECT id, content, embedding <=> $1 AS distance
FROM chunks
ORDER BY embedding <=> $1
LIMIT 10;The inner product operator returns the negative, because Postgres index ordering is ascending and higher inner product means more similar. If you want the actual similarity, negate it back: (embedding <#> $1) * -1.
The thing to understand about indexes
With no index at all, that query is exact. Postgres compares the query vector against every row and returns the true ten nearest. It is perfectly correct and it is often fast enough. At 50,000 rows a sequential scan with distance computation runs in tens of milliseconds.
The moment you add a vector index, the query becomes approximate. You are trading recall for speed. This is the opposite of every other index you have used in Postgres, where an index changes the plan but never the answer, and it surprises people.
So the first question is not which index, it is whether you need one at all. Measure the unindexed query on realistic data volumes first.
HNSW
HNSW builds a layered graph where each vector links to its neighbours, and searches by walking down from a sparse top layer to the dense bottom one. It gives the best recall for a given latency and it handles incremental inserts well, which is why it is the default recommendation.
CREATE INDEX ON chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);m is how many links each node keeps, and it sets the memory footprint. ef_construction is how hard the builder works to find good neighbours. Raising either improves recall and costs build time and memory. The defaults are sensible. Try 24 and 128 if recall is not where you want it.
At query time, ef_search controls how wide the search goes:
SET hnsw.ef_search = 100;This is a session setting and it is your main runtime dial. Higher means better recall and slower queries. It costs nothing to change, so tune it against a real evaluation set rather than guessing.
The downside of HNSW is build time and memory. Building over several million vectors takes a while, and the index wants to fit in memory to be fast.
IVFFlat
IVFFlat clusters the vectors, then searches only the nearest clusters. It builds far faster than HNSW and uses much less memory, at the cost of lower recall at the same speed.
CREATE INDEX ON chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);The critical constraint: IVFFlat must be built on a table that already has data. It learns cluster centroids from what it sees. Build it on an empty table and you get an index built around nothing, which will quietly return poor results forever. HNSW has no such requirement.
For lists, use rows/1000 up to a million rows and sqrt(rows) beyond that. At query time set ivfflat.probes to roughly sqrt(lists) and adjust from there.
The other IVFFlat weakness is drift. As data changes, the centroids learned at build time stop reflecting the distribution, and recall degrades. You need to rebuild periodically. HNSW does not have this problem.
Use HNSW unless build time or memory forces your hand.
Filtering, which is the whole reason to be here
This is where pgvector genuinely beats most of the alternatives, and it is worth being specific about why.
Nearly every real query is filtered. Only this tenant's documents. Only what this user may read. Only the last year. In a dedicated vector store, filtering is a bolted on feature with awkward semantics, and you either filter before the index (slow) or after it (you asked for ten results and got two).
In Postgres, a filter is a WHERE clause and the planner deals with it:
SELECT c.id, c.content, c.embedding <=> $1 AS distance
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.tenant_id = $2
AND d.status = 'published'
AND c.created_at > now() - interval '1 year'
ORDER BY c.embedding <=> $1
LIMIT 10;That is a join against your actual permissions model, in one query, with no synchronisation between systems. It is a real advantage and it is why teams keep coming back to pgvector after trying something else.
There is still a catch. When the filter is very selective, the index walk can exhaust itself before finding enough matching rows, and you get fewer results than you asked for. pgvector 0.8 fixed this with iterative scans:
SET hnsw.iterative_scan = strict_order;
SET hnsw.max_scan_tuples = 20000;With this on, the index keeps scanning until it has enough rows that survive the filter. strict_order guarantees correct ordering, relaxed_order is faster and mostly ordered. Turn this on. It removes the single most common complaint about filtered vector search in Postgres.
Partial indexes are the other tool. If most searches are scoped to a small number of large tenants, an index per tenant is both smaller and more accurate than one index for everything.
Making it fit in memory
A million 1024 dimension vectors at four bytes per component is four gigabytes before the index. That gets expensive. pgvector gives you several ways down.
halfvec stores 16 bit floats, halving storage for a recall cost that is usually too small to measure:
CREATE INDEX ON chunks
USING hnsw ((embedding::halfvec(1024)) halfvec_cosine_ops);Note this is an expression index. The column stays full precision and only the index is compressed, so you can still rescore exactly.
Binary quantisation goes much further, one bit per dimension, a 32x reduction. Recall on its own is poor, so it is used as a first pass and then rescored against the real vectors:
CREATE INDEX ON chunks
USING hnsw ((binary_quantize(embedding)::bit(1024)) bit_hamming_ops);
SELECT id, content
FROM (
SELECT id, content, embedding
FROM chunks
ORDER BY binary_quantize(embedding)::bit(1024) <~> binary_quantize($1)
LIMIT 200
) candidates
ORDER BY embedding <=> $1
LIMIT 10;Fetch a wide set cheaply, then rank that set precisely. The same pattern appears in every serious vector system, and it works well here.
Worth knowing: vector columns can hold up to 16,000 dimensions but only 2,000 are indexable. halfvec raises the indexable limit to 4,000. If your model outputs more than that, use Matryoshka truncation if the model supports it, or dimensionality reduction, or halfvec.
Hybrid search
Postgres has had full text search for twenty years, so you can run keyword and vector retrieval together and fuse the results. Reciprocal rank fusion in a single statement:
WITH semantic AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
FROM chunks ORDER BY embedding <=> $1 LIMIT 50
),
keyword AS (
SELECT id, ROW_NUMBER() OVER (
ORDER BY ts_rank_cd(to_tsvector('english', content),
plainto_tsquery('english', $2)) DESC) AS rank
FROM chunks
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
LIMIT 50
)
SELECT COALESCE(s.id, k.id) AS id,
COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS score
FROM semantic s
FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY score DESC
LIMIT 10;Rank fusion sidesteps the score normalisation problem entirely. It only uses positions, so the incompatible scales of BM25 and cosine distance never come up. Add a GIN index on the tsvector and store it as a generated column so it is not recomputed per query.
This matters more than people expect. Semantic search is weak on exact identifiers, product codes, names and acronyms, which is precisely what users type. Keyword search covers that gap.
Operating it
Index builds need memory. Raise maintenance_work_mem before creating an HNSW index. If the graph does not fit, the build spills to disk and takes many times longer. Set max_parallel_maintenance_workers to use more cores.
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;Build the index after the bulk load, not before. Loading into an existing HNSW index is far slower than loading first and indexing once.
Watch bloat. Updates in Postgres are delete plus insert, and updating an embedding rewrites the index entry. High churn tables need vacuum attention.
Changing embedding model means a full rebuild. Every vector has to be regenerated and the index recreated. Plan for this with a second column and a background backfill, then swap, rather than taking the table out of service.
Measure recall, not just latency. Run your queries with the index disabled to get exact ground truth, then compare against indexed results. If you are not measuring recall you do not know what your tuning is doing, you only know it got faster.
When to move off it
pgvector is comfortable into the tens of millions of vectors on properly sized hardware. Beyond that, or if any of the following apply, look at a dedicated store:
Vector search volume that would need read replicas your transactional load does not justify.
Very high write throughput on embeddings, where index maintenance competes with your OLTP work.
A need for features Postgres does not have, such as built in reranking, multi vector documents or managed sharding across nodes.
Even then, the migration is not dramatic. The chunks, the metadata and the pipeline all stay. You move the index. That is a much better position than having run two systems from the start on the assumption you would eventually need to.
Start here
If you are building retrieval and you already have Postgres, start with pgvector. Add an HNSW index when measurement says you need one, turn on iterative scans, add keyword search alongside it, and keep an evaluation set so you can tell whether changes help. That will carry most products a long way.
Eight Mile builds retrieval systems, AI assistants, backend APIs and the data platforms underneath them, and we spend a fair amount of time helping teams work out whether the extra database was ever necessary. If you are designing one or trying to make an existing one behave, get in touch.