Vector Search in Postgres: When pgvector Is Enough

If you already run Postgres, pgvector is enough for most vector search workloads: store embeddings in the same rows they describe, index them with HNSW, and query them with plain SQL inside the same transactions as the rest of your data. It stops being enough when the vector index no longer fits in memory, when vector queries crowd out your transactional traffic, or when you need a feature it does not have. All three are measurable, so measure them before you add a second database.

Key takeaways

• Keep embeddings next to the rows they describe, so writes, deletes, tenant filters and backups cover them too.
• Start with HNSW and default parameters. Raise hnsw.ef_search per query before touching m or ef_construction.
• Approximate indexes apply WHERE filters after the scan, so filtered queries can silently return fewer rows. Iterative scans (0.8.0 and later) or a B-tree on the filter column fix it.
• Hybrid search with reciprocal rank fusion fits in one SQL statement.
• Size for memory: the HNSW index holds every vector plus graph links. halfvec and binary quantization shrink it.
• Run 0.8.2 or later for the CVE-2026-3172 fix, and preferably 0.8.3 for an HNSW vacuum corruption fix.

Why vectors next to your rows is the point

The argument for pgvector is not speed. It is that the embedding lives in the same transaction, the same backup and the same permission model as the data it describes. A separate vector store turns every one of those into a sync problem you now own.

Teams that start with a separate store usually find out months later, when search returns a deleted document or an unpublished draft. Those are consistency bugs, and they disappear when the vector is a column. If you are building retrieval for an LLM feature, our RAG architecture guide covers the chunking and retrieval design that sits on top of this.

Schema and the one-transaction write

Here is a schema that holds up in production. Documents are split into chunks, each chunk carries its own embedding, and everything the search query filters on is on the chunk row itself.

create extension if not exists vector;
 
create table documents (
  id          bigint generated always as identity primary key,
  tenant_id   bigint not null,
  title       text not null,
  body        text not null,
  updated_at  timestamptz not null default now()
);
 
create table document_chunks (
  id              bigint generated always as identity primary key,
  document_id     bigint not null references documents (id) on delete cascade,
  tenant_id       bigint not null,
  chunk_index     int not null,
  content         text not null,
  content_hash    text not null,
  embedding       vector(1536),
  embedding_model text,
  search_tsv      tsvector generated always as (to_tsvector('english', content)) stored,
  unique (document_id, chunk_index)
);
 
create index on document_chunks (tenant_id);
create index document_chunks_search_tsv on document_chunks using gin (search_tsv);
create index document_chunks_needs_embedding on document_chunks (id) where embedding is null;

Why each column is there:

• tenant_id on the chunk lets the vector query filter without a join. Filters are where approximate search goes wrong, so keep them simple.
• content_hash lets you reuse vectors for unchanged chunks instead of paying to embed the same text again.
• embedding_model records which model produced each vector, so a model change never mixes incompatible vectors in one ranking.
• A nullable embedding with a partial index means a chunk is still written, and findable by keyword, when the embedding API is down. A worker fills it in later.
• search_tsv is a generated full-text column for hybrid search.

The dimension must match your model; vector(1536) fits OpenAI's text-embedding-3-small. pgvector indexes vector columns up to 2,000 dimensions and halfvec up to 4,000, so a 3,072-dimension model such as text-embedding-3-large fails with column cannot have more than 2000 dimensions for hnsw index. Use a half-precision expression index:

create index on document_chunks
  using hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
 
-- queries must use the same expression to hit the index
select id from document_chunks
order by embedding::halfvec(3072) <=> $1::halfvec(3072)
limit 10;

The write path

Call the embedding API first, then open the transaction. A transaction held open across an HTTP call to a model provider turns a slow response into held locks.

-- The application computed the vectors before BEGIN.
begin;
 
update documents
set body = $2, updated_at = now()
where id = $1;
 
delete from document_chunks where document_id = $1;
 
insert into document_chunks
  (document_id, tenant_id, chunk_index, content, content_hash, embedding, embedding_model)
values
  ($1, $3, 0, $4, $5, $6, 'text-embedding-3-small'),
  ($1, $3, 1, $7, $8, $9, 'text-embedding-3-small');
 
commit;

Either the edit and its new chunks are visible together, or neither is. For asynchronous embedding, insert chunks with a null embedding and let a worker fill them in. The content_hash check stops a slow worker from writing a vector for text that has since changed:

-- worker: find pending chunks (uses the partial index)
select id, content, content_hash
from document_chunks
where embedding is null
order by id
limit 100;
 
-- worker: write back only if the chunk is unchanged
update document_chunks
set embedding = $2, embedding_model = $3
where id = $1 and content_hash = $4;

Rows with a null embedding are not indexed, so they appear in keyword search but not in vector results until the worker catches up.

HNSW or IVFFlat, and the parameters that matter

Without an index, pgvector does exact search: perfect recall, cost proportional to table size. Add an approximate index when that gets too slow.

Default to HNSW. Choose IVFFlat only when build time or index memory is a constraint you cannot buy your way out of.

set maintenance_work_mem = '2GB';
set max_parallel_maintenance_workers = 7;
 
create index concurrently document_chunks_embedding_hnsw
  on document_chunks using hnsw (embedding vector_cosine_ops)
  with (m = 16, ef_construction = 64);

Four rules that prevent most surprises:

1. Match the operator class to the query operator. vector_cosine_ops serves <=>, vector_l2_ops serves <-> and vector_ip_ops serves <#>. A cosine index does nothing for an L2 query. For embeddings normalized to length 1, as OpenAI's are, pgvector's docs recommend inner product for performance.
2. Build concurrently in production. create index concurrently avoids blocking writes and cannot run inside a transaction block.
3. Give the build memory, within reason. HNSW builds much faster while the graph fits in maintenance_work_mem; when it stops fitting, you get the notice hnsw graph no longer fits into maintenance_work_mem and the build slows sharply. Never set it so high that the server runs out of memory, especially multiplied by parallel workers on a box serving traffic.
4. Tune queries before builds. Raise hnsw.ef_search with SET LOCAL for better recall. Rebuilding with a larger m or ef_construction is slow and rarely the first fix.

For IVFFlat, pgvector suggests lists at rows divided by 1,000 up to a million rows, the square root of rows beyond that, and ivfflat.probes at the square root of lists.

The query has to look a certain way

The index is used only for ORDER BY <distance operator> ... LIMIT n in ascending order:

select id, document_id, content, embedding <=> $1 as distance
from document_chunks
order by embedding <=> $1
limit 10;

Ordering by an expression, such as similarity computed as one minus the distance in descending order, makes Postgres scan the whole table. Compute similarity in the select list if you need it, and order by the raw distance.

Filtered vector search without losing results

With an approximate index, the WHERE clause is applied after the index returns its candidates. pgvector's documentation gives the arithmetic: with the default hnsw.ef_search of 40 and a filter matching 10% of rows, about 4 rows survive on average. A filter matching 1% of rows, one tenant in a hundred, often returns nothing. No error, just missing results.

Pick the tool by how selective the filter is.

The B-tree on tenant_id above is the first line of defense: for a selective filter the planner often uses it and sorts the matching rows by exact distance, which is fast and fully accurate. When the planner picks the vector index instead, turn on iterative scans, which keep scanning until enough rows pass the filter:

begin;
set local hnsw.iterative_scan = relaxed_order;
set local hnsw.ef_search = 100;
 
select id, document_id, content
from document_chunks
where tenant_id = $2
order by embedding <=> $1
limit 10;
 
commit;

strict_order keeps exact distance order. relaxed_order allows slight reordering for better recall; restore exact order with a materialized CTE (the + 0 is required on Postgres 17 and later):

with candidates as materialized (
  select id, document_id, content, embedding <=> $1 as distance
  from document_chunks
  where tenant_id = $2
  order by distance
  limit 10
)
select * from candidates order by distance + 0;

Iterative scans stop at a budget: hnsw.max_scan_tuples (default 20,000) and hnsw.scan_mem_multiplier (default 1, a multiple of work_mem) for HNSW, ivfflat.max_probes for IVFFlat. If raising hnsw.max_scan_tuples does not improve recall, pgvector's docs suggest raising the memory multiplier next.

For a few hot values, a partial index keeps each graph small:

create index on document_chunks using hnsw (embedding vector_cosine_ops)
  where (tenant_id = 42);

For many large tenants, partition the table by tenant_id so each partition has its own index. Our SaaS architecture guide covers when per-tenant partitioning pays for itself beyond search.

Hybrid search in one query

Embeddings are good at meaning and bad at exact tokens: error codes, SKUs, surnames. Full-text search is the reverse. Running both and fusing the rankings fixes most misses, and in Postgres it is one statement.

Reciprocal rank fusion (RRF) scores each result by summing 1 divided by (k plus its rank) across the lists it appears in, with k conventionally 60, from the paper that introduced RRF. It needs no score normalization, which matters because cosine distances and ts_rank_cd scores are not comparable.

with semantic as (
  select id, rank() over (order by embedding <=> $1) as rnk
  from document_chunks
  where tenant_id = $3
  order by embedding <=> $1
  limit 40
),
keyword as (
  select id, rank() over (order by ts_rank_cd(search_tsv, query) desc) as rnk
  from document_chunks, websearch_to_tsquery('english', $2) as query
  where tenant_id = $3
    and search_tsv @@ query
  order by ts_rank_cd(search_tsv, query) desc
  limit 40
)
select coalesce(s.id, k.id) as id,
       coalesce(1.0 / (60 + s.rnk), 0) + coalesce(1.0 / (60 + k.rnk), 0) as rrf_score
from semantic s
full outer join keyword k on k.id = s.id
order by rrf_score desc
limit 10;

$1 is the query embedding, $2 the raw user query and $3 the tenant. websearch_to_tsquery accepts what users actually type without raising syntax errors, and iterative scan settings apply inside the semantic branch. Fetch more candidates per branch than you return, join the fused IDs back for content, and rerank the top results with a cross-encoder if you need better ordering still.

Sizing and operating it

Storage and memory

pgvector documents the storage cost per value: vector takes 4 bytes per dimension plus 8, halfvec 2 bytes per dimension plus 8, and bit one bit per dimension plus 8 bytes. A 1,536-dimension vector is 6,152 bytes, so a million chunks carry about 6.2 GB of raw vector data before any index.

The HNSW index stores the vectors again plus neighbor links, and query speed depends on it staying in memory. Measure it:

select pg_size_pretty(pg_relation_size('document_chunks_embedding_hnsw')) as index_size;

Compare it with the RAM you can give Postgres; pgvector's tuning notes start shared_buffers at about a quarter of server memory. When the index outgrows memory, shrink it before buying a bigger box:

• halfvec storage or a half-precision expression index halves the size. Re-run your recall check after switching.
• Binary quantization with re-ranking indexes one bit per dimension and re-ranks a larger candidate set with full vectors:

create index on document_chunks
  using hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);
 
select id from (
  select id, embedding from document_chunks
  order by binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
  limit 100
) candidates
order by embedding <=> $1
limit 10;

Vacuum, upgrades and recall

Updates and deletes leave dead tuples in the HNSW graph, and vacuuming an HNSW index can take a long time. pgvector's docs suggest reindexing first, which makes the vacuum fast:

reindex index concurrently document_chunks_embedding_hnsw;
vacuum document_chunks;

Keep the extension current. CVE-2026-3172, fixed in 0.8.2, is a buffer overflow in parallel HNSW index builds that affected 0.6.0 through 0.8.1 and could let a database user who can build an index leak data or crash the server. Version 0.8.3 fixes a possible index corruption during HNSW vacuuming. Check and upgrade per database:

select extversion from pg_extension where extname = 'vector';
alter extension vector update;

On a managed service, alter extension only reaches the newest version the provider has installed, so check before assuming you are patched.

Approximate search can lose recall quietly as data grows. Compare index results with exact search for a sample of real queries on a schedule:

begin;
set local enable_indexscan = off;  -- forces exact search
select id from document_chunks order by embedding <=> $1 limit 10;
commit;

If the overlap drops, raise hnsw.ef_search first.

When to move to a dedicated vector database

Move when one of these is true and you have the numbers to show it:

• The index does not fit in memory on the largest instance you are willing to pay for, even after halfvec or binary quantization.
• Vector queries hurt transactional traffic on the primary, and moving them to a read replica did not fix it.
• You need something pgvector does not do for your case, such as a specific distributed architecture or search feature, and you have checked that it does not.

Try the cheaper steps first: shrink the vectors, move search reads to replicas, partition by tenant, then shard with a tool such as Citus, which pgvector's docs list. Vendor benchmarks are not a signal, and neither is the feeling that AI features deserve an AI database. Our database selection guide covers when a second datastore earns its place.

What goes wrong in production

Fewer results after adding the index. Results are capped by hnsw.ef_search (40 by default), then reduced further by filters and dead tuples. Raise ef_search, enable iterative scans, or vacuum.

The index is not used. The query orders by an expression, has no LIMIT, sorts descending, or uses an operator that does not match the index's operator class. Run EXPLAIN (ANALYZE, BUFFERS) and look for the index scan.

An IVFFlat index built on an empty table. pgvector prints ivfflat index created with little data, and recall stays poor until you rebuild it after loading data.

Two embedding models in one column. Vectors from different models are not comparable, and a mixed column ranks nonsense with total confidence. Re-embed into a new column, index it concurrently, switch reads, drop the old one. A model with a different dimension at least fails loudly: expected 1536 dimensions, not 3072.

Rows that never appear. Null vectors are not indexed, and neither are zero vectors under cosine distance, which is undefined (NaN) for them. A pending queue that never drains, or a pipeline that writes zeros on error, makes rows silently unsearchable. Alert on the count of null embeddings.

Session settings behind a connection pooler. With a transaction-mode pooler such as PgBouncer, a plain SET hnsw.ef_search lands on whichever server connection you got, then leaks to other clients or vanishes. Use SET LOCAL inside a transaction.

Frequently Asked Questions

Can Postgres do vector search?

Yes. The pgvector extension adds vector types, distance operators and two approximate index types, HNSW and IVFFlat, to standard Postgres 13 and later. Most managed providers offer it, including Supabase, Neon, Amazon RDS and Google Cloud SQL. Without an index it does exact search; with one it trades a little recall for a lot of speed.

Which databases support vector search and transactional data together?

Postgres with pgvector is the most common choice: vectors live in ordinary tables and are written in the same ACID transactions as the rest of your data. MongoDB Atlas offers vector search next to its document store, and Oracle Database and SQL Server 2025 have native vector types. Dedicated vector databases such as Pinecone, Qdrant or Milvus do not hold your transactional data, so you keep two systems in sync.

Should I use HNSW or IVFFlat in pgvector?

Use HNSW unless build time or memory is the hard constraint. HNSW gives a better speed and recall trade-off and needs no training data, but it builds slower and uses more memory. IVFFlat builds faster and smaller, but it must be created after the table holds representative data, and its lists are fixed at build time.

How do I filter vector search results in pgvector?

Put the filter in a normal WHERE clause and give the filter column a B-tree index, so selective filters can use exact search over the matching rows. For filters that match a larger share of rows, enable iterative index scans, available since pgvector 0.8.0, so the index keeps scanning until enough rows pass the filter. For a few fixed filter values, partial indexes or partitioning work better.

How many vectors can pgvector handle?

There is no fixed vector limit: pgvector's own documentation points to the default Postgres limit of 32 TB per non-partitioned table. The practical ceiling is memory, because HNSW performs best when the index fits in RAM. Use halfvec or binary quantization to shrink the index, and measure its size with pg_relation_size before buying a bigger instance.

When should I move from pgvector to a dedicated vector database?

Move when you can measure the pain: the index no longer fits in memory on the largest instance you will pay for, vector queries crowd out transactional work even with read replicas, or you need a feature pgvector lacks. Until then, one database with one backup and one permission model is cheaper to run and easier to keep correct.

Need help with search or RAG in production?

We build retrieval and search features on top of the databases teams already run, and we stay on the pager for them after launch. Our bias toward one well-run Postgres over two loosely synced systems comes from being the people who get paged when the two drift apart.

• AI and machine learning services
• Software development
• Talk to an engineer