pgvector in Production: A PostgreSQL Vector Search Guide

pgvector is a PostgreSQL extension that stores embedding vectors and runs similarity search directly inside your existing database. If you already run Postgres and need semantic search or a retrieval layer for RAG, pgvector lets you skip a separate vector database and keep vectors next to your relational data, in the same transaction, behind the same backups. This guide covers the parts that actually matter once you leave the tutorial behind: choosing between HNSW and IVFFlat, picking the right distance operator, writing queries the index will use, and the operational traps that show up at scale.

The pitch is simple. One system to run, one connection pool, one set of ACID guarantees. You can filter vectors by tenant, join them to orders, and enforce foreign keys, all with SQL you already know. The tradeoff is that pgvector is not a purpose-built vector engine, so you have to understand its indexes to keep queries fast. That understanding is what this article gives you.

Installing pgvector and creating your first table

pgvector ships as a standard extension. On a self-managed Postgres you install the extension package for your major version, then enable it per database. Most managed providers (Neon, Supabase, RDS, Cloud SQL) already bundle it, so you only run the enable step.

CREATE EXTENSION IF NOT EXISTS vector;

The extension adds a vector column type. You declare the dimension count, which must match your embedding model. A common general-purpose embedding model emits a few hundred to a couple thousand dimensions, so check your model's output length and hard-code it.

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  tenant_id   bigint NOT NULL,
  content     text NOT NULL,
  embedding   vector(1536)
);

Inserting a vector is just passing a bracketed array as text. Your application usually generates the array from an embeddings API and sends it as a parameter.

INSERT INTO documents (tenant_id, content, embedding)
VALUES (1, 'Postgres handles vectors too', '[0.012, -0.033, ...]');

At this point you have working vector storage but no index. A query will run a sequential scan and compute distance for every row. That is fine for a few thousand rows and correct for all of them. It stops being fine somewhere in the tens of thousands, which is where indexing comes in.

Distance operators: match them to your embedding model

pgvector exposes distance as operators, and the operator you use has to match both your embedding model and the index you build. Pick the wrong one and you get results that look plausible but are subtly ranked wrong.

• <-> is L2 (Euclidean) distance.
• <=> is cosine distance.
• <#> is negative inner product.
• <+> is L1 (Manhattan) distance.

Most text embedding models are trained for cosine similarity, so <=> is the common default. If your model produces normalized vectors (unit length), cosine and inner product rank identically, and inner product is slightly cheaper to compute. The rule that saves you: read your model's documentation for the recommended similarity metric, then use the matching operator everywhere, in queries and in the index.

A nearest-neighbor query orders by the operator and limits the result:

SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.01, -0.02, ...]'
LIMIT 10;

The critical detail: the index only helps when the ORDER BY uses the exact same operator the index was built with. An HNSW index built for cosine will not accelerate an L2 query. Keep them aligned.

HNSW vs IVFFlat: the pgvector index decision

pgvector offers two approximate nearest neighbor (ANN) index types. Both trade a little recall for a lot of speed. Choosing between them is the most consequential pgvector decision you will make.

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph. It gives excellent recall and fast queries, handles incremental inserts gracefully, and does not need to see your data before building. Its costs are higher build time and more memory. For most production workloads in 2026, HNSW is the default recommendation.

IVFFlat (Inverted File with Flat compression) partitions vectors into lists (clusters) and searches only the nearest lists at query time. It builds faster and uses less memory than HNSW, but its recall depends on having representative data at build time, and it degrades as you insert rows the clusters never accounted for. Choose it when build speed and memory are tight and your data is relatively static.

Build an HNSW index by naming the operator class that matches your distance operator. vector_cosine_ops pairs with <=>, vector_l2_ops with <->, vector_ip_ops with <#>.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

HNSW takes two build parameters. m is the number of connections per node (higher means better recall and more memory). ef_construction is the size of the candidate list during build (higher means better recall and slower builds). Sensible starting points are moderate values; raise them only if recall testing says you need to.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

IVFFlat needs a lists parameter, the number of clusters. A common heuristic is roughly the square root of your row count for large tables, tuned by testing. Crucially, build the IVFFlat index after the table has a representative sample of data, because the clusters are computed from what is present at build time.

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

If you build an IVFFlat index on an empty or tiny table and then load millions of rows, your clusters are meaningless and recall collapses. This is the single most common IVFFlat mistake. HNSW does not have this failure mode, which is another reason it is the safer default.

Tuning query-time recall

Both index types have a query-time knob that trades speed for recall. You set it per session or per transaction, and it is the lever you reach for when search quality is too low.

For HNSW, hnsw.ef_search controls how many candidates the graph search keeps. Higher gives better recall at the cost of latency. It defaults to a small number; raise it when you need more accurate results.

SET hnsw.ef_search = 100;

SELECT id, content
FROM documents
ORDER BY embedding <=> '[...]'
LIMIT 10;

For IVFFlat, ivfflat.probes controls how many lists are scanned. Setting it to 1 scans only the nearest cluster (fast, lower recall); raising it scans more clusters (slower, higher recall). At the extreme, probes equal to lists becomes an exact search.

SET ivfflat.probes = 10;

Set these with SET LOCAL inside a transaction if you want the change scoped to one query batch rather than the whole connection. Because pooled connections are reused, a plain SET leaks to the next request that borrows the connection, which leads to confusing latency swings. Prefer SET LOCAL inside an explicit transaction for per-query tuning.

Measuring recall so you tune with data, not vibes

ANN indexes return approximate results by design, so "is my recall good enough" is an empirical question. Answer it before shipping. The method: take a sample of query vectors, compute the exact top-k with a sequential scan (disable the index), then compute the approximate top-k with the index, and measure the overlap.

You can force an exact scan by turning off index scans for a session:

SET enable_indexscan = off;
SET enable_bitmapscan = off;

Run your query, capture the exact neighbor ids, re-enable index scans, run again, and compare. Recall at k is the fraction of exact neighbors that appear in the approximate result. If it is below your target, raise ef_search or probes first, then rebuild with higher m or ef_construction if the query-time knob is not enough. Doing this with real query vectors from your application beats copying parameter values from a blog post, including this one.

Filtering vectors: the trap that kills performance

Real applications rarely search all vectors. They search within a tenant, a category, a date range. Combining a WHERE filter with vector ordering is where pgvector performance quietly falls apart, so this section matters more than the index-building one.

The naive query looks fine:

SELECT id, content
FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> '[...]'
LIMIT 10;

The problem is that the ANN index orders by distance across all tenants. Postgres walks the vector index in distance order, discards rows failing tenant_id = 42, and keeps going until it collects 10 survivors. If tenant 42 is rare, the index walk can traverse a huge fraction of the graph before finding 10 matches, and latency spikes. In the worst case it exhausts ef_search candidates and returns fewer than 10 rows, silently.

There are three practical answers.

First, raise ef_search so the walk considers enough candidates to find your filtered matches. This helps for mildly selective filters but wastes work for very selective ones.

Second, use partial indexes when you have a small, fixed set of filter values. A partial HNSW index per high-traffic tenant builds a separate graph containing only that tenant's rows, so the walk never sees anyone else.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE tenant_id = 42;

Third, for highly selective filters, an exact scan can beat the ANN index. If tenant_id = 42 matches only a few hundred rows, let Postgres filter to those rows and compute exact distances on them. A B-tree index on tenant_id plus a sequential distance computation over the survivors is both faster and perfectly accurate. Check the query plan with EXPLAIN ANALYZE to see which path the planner actually chose; do not assume.

The general principle: filter selectivity decides the strategy. Broad filters favor the ANN index with a higher ef_search; narrow filters favor exact search over a B-tree-filtered subset; a fixed set of hot values favors partial indexes.

Building indexes without locking out writes

On a large table, CREATE INDEX takes a heavy lock and blocks writes for the whole build, which can be many minutes for HNSW. In production, build concurrently instead. It takes longer and uses more resources but does not block inserts and updates.

CREATE INDEX CONCURRENTLY ON documents
USING hnsw (embedding vector_cosine_ops);

Two operational notes. A concurrent build can fail and leave an invalid index behind; check for it and drop it before retrying. And HNSW builds are memory-hungry, so raising maintenance_work_mem for the build session speeds it up substantially. Parallel workers can also help; set them for the session before building.

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

Storage, dimensions, and the cost of wide vectors

Every dimension is four bytes. A 1536-dimension vector is about 6 KB before overhead, and the HNSW graph adds its own memory on top. Multiply by tens of millions of rows and you are managing real storage and RAM. Three levers keep this under control.

Reduce dimensions if your model supports it. Some embedding models let you request shorter output vectors that keep most of the quality. Fewer dimensions means smaller storage, less memory, and faster distance math. Test recall at the shorter length before committing.

Use quantized vector types when available. pgvector supports half-precision storage (halfvec), which halves the bytes per dimension with minor recall impact for many models. Binary quantization goes further for workloads that tolerate it. These are worth testing when memory is your bottleneck.

Keep the working set in memory. ANN search is fast only when the index pages are in RAM. If your index does not fit in shared_buffers plus OS cache, queries hit disk and latency becomes unpredictable. Size the instance so the hot index fits, or shard across tables.

An end-to-end RAG retrieval example

Putting it together, here is the shape of a retrieval query for a RAG pipeline that filters by tenant, tunes recall per request, and returns the distance so the application can threshold weak matches.

BEGIN;
SET LOCAL hnsw.ef_search = 80;

SELECT id,
       content,
       embedding <=> '[...]' AS distance
FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> '[...]'
LIMIT 8;

COMMIT;

Pass the query embedding as a bound parameter from your application rather than string-concatenating it, both for safety and so the planner can cache the plan. Return the distance so you can drop results above a similarity threshold instead of always feeding eight chunks to the model, including junk when the corpus has no good match. And log the distances in early production; they tell you whether your retrieval is finding real neighbors or scraping the bottom of the barrel.

Operational checklist before you ship

• Confirm the distance operator matches your embedding model's recommended metric, and that the index operator class matches the operator.
• Default to HNSW unless build time or memory forces IVFFlat, and never build IVFFlat on an empty table.
• Measure recall with real query vectors against an exact scan, then tune ef_search or probes to hit your target.
• Test filtered queries with EXPLAIN ANALYZE, and use partial indexes or exact scans for selective filters.
• Build production indexes with CREATE INDEX CONCURRENTLY and a raised maintenance_work_mem.
• Size the instance so the index fits in memory, and consider halfvec or shorter embeddings if it does not.
• Set tuning GUCs with SET LOCAL inside a transaction so pooled connections do not leak state.

FAQ

When should I use pgvector instead of a dedicated vector database?

Use pgvector when you already run Postgres, your vector count is in the millions rather than the billions, and you value keeping vectors transactional and joined to relational data. You get one system to operate, ACID guarantees, and SQL filtering for free. Reach for a dedicated engine when you need billions of vectors, extreme query throughput, or built-in distributed sharding that Postgres would force you to hand-roll.

Is HNSW or IVFFlat better for pgvector?

HNSW is the safer default in most 2026 production setups: better recall, fast queries, and it handles incremental inserts without needing representative data at build time. IVFFlat builds faster and uses less memory but degrades if your data changes after the index is built. Start with HNSW and only switch to IVFFlat if build time or memory pressure forces it.

Why does my filtered vector query return fewer rows than the LIMIT?

Because the ANN index walks candidates in distance order and discards rows that fail your WHERE clause. If the filter is selective and ef_search is too low, the walk runs out of candidates before collecting enough matches. Raise ef_search, add a partial index for that filter value, or switch to an exact scan over a B-tree-filtered subset for highly selective filters.

How do I know if my recall is good enough?

Measure it. Disable index scans to get the exact top-k for a sample of real query vectors, run the same queries with the index, and compute the overlap fraction. If it is below your target, raise the query-time knob (ef_search or probes) first, then rebuild with higher build parameters if needed. Do not trust default parameters without checking against your own data.

Do I need to rebuild the index when I insert new vectors?

With HNSW, no. It supports incremental inserts and new vectors are added to the graph automatically, though very heavy write volumes can gradually affect graph quality over a long period. IVFFlat also accepts new rows without a rebuild, but because its clusters are fixed at build time, recall can drift as the data distribution shifts, so periodic rebuilds are more valuable there.

What causes slow pgvector queries in production?

The usual suspects are: no ANN index (sequential scan over every row), an index that does not fit in memory so queries hit disk, a distance operator that does not match the index so the index is ignored, ef_search set too high for the latency you want, or a selective WHERE filter forcing a long index walk. Run EXPLAIN ANALYZE on the slow query and check which of these applies before changing anything.