pgvector vs a vector database: when Postgres is enough

PostgreSQL with the pgvector extension is enough for most company AI systems: your documents, permissions and vectors sit in one database, so a search, an update and a row level security check all happen in one transaction. A dedicated vector database such as Qdrant, Weaviate or Milvus earns its place when filtered search gets thin or slow, when you need hybrid search built in, or when the vectors outgrow one machine.

These notes are for the developer or technical lead who has to choose before the first RAG prototype turns into something people rely on.

What does pgvector give you that a separate store does not?

A vector database stores embeddings and returns the nearest ones to a query. pgvector adds that to Postgres as column types (vector, halfvec, bit, sparsevec) and index types. The README puts the point plainly: you keep "ACID compliance, point-in-time recovery, JOINs, and all of the other great features of Postgres."

In practice that means:

• One write path. A document, its chunks and their embeddings go in, or come out, in one transaction. Delete a contract and its vectors go with it, which matters when someone asks you to erase their data.
• One permission model. Postgres row level security filters rows per user. Once you enable it on a table with no policy, the default is deny: no rows are visible. Watch one detail: the table owner bypasses policies unless you set FORCE ROW LEVEL SECURITY, and superusers always bypass them, so the app should not connect as the owner.
• One backup and one thing to monitor. pgvector writes to the write-ahead log, so replication and point-in-time recovery work as they do for the rest of your data.
• Joins. "Find passages like this, only from invoices for customer 42 in the last quarter" is one SQL query.

HNSW or IVFFlat: which index should you use?

pgvector offers two approximate indexes. The README describes them like this:

HNSW is the usual choice. Both index up to 2,000 dimensions for vector and 4,000 for halfvec, so a model with 4,096 dimensions must be shortened or stored as halfvec. For a smaller working set, the README suggests halfvec columns and binary quantisation for the index, with re-ranking afterwards.

The filtering trap

This is the part to test before you commit. pgvector's approximate indexes apply your WHERE clause after the index scan. The README's example: if a condition matches 10% of rows, HNSW with the default ef_search of 40 returns only about 4 rows. In a permissions-heavy system, a user who can see a small slice of the documents gets thin answers.

The fixes in Postgres:

1. Iterative index scans (pgvector 0.8.0 and later) keep scanning until enough rows pass the filter.
2. Partial indexes when a filter has few distinct values, such as one index per department.
3. Partitioning when it has many, such as one partition per tenant.
4. A plain B-tree index and exact search when the filter matches a small share of rows.

Qdrant takes a different route. It filters during the graph walk, adding extra HNSW edges between points that share a value of an indexed payload field. Its planner estimates how many points match: few matches are scored directly, many matches go through the filtered graph. ACORN, available from v1.16 and off by default, helps when several strict filters combine. One catch from the docs: those extra edges only exist if you create the payload indexes before the graph is built.

How does hybrid search compare?

Meaning search misses exact strings such as a part number or a GSTIN, so most production systems add keyword search and merge the two lists.

• pgvector: combine it with Postgres full-text search and merge with reciprocal rank fusion (RRF) in SQL, or re-rank with a cross-encoder. It works, but you write the query.
• Qdrant: dense and sparse queries run as prefetches in one request, fused with RRF or DBSF.
• Weaviate: fuses vector search with BM25F; an alpha of 1 is pure vector and 0 pure keyword. Relative Score Fusion is the default from v1.24.
• Milvus: computes BM25 itself, turning raw text into sparse vectors, and can combine that with dense search.

Our guide to semantic search over company documents covers chunking, RRF and re-ranking in more detail.

When does a dedicated vector database earn its place?

Move when you can name the problem, not before. Signs that it is time:

• Filtered results stay thin or slow after iterative scans, partial indexes and partitioning, and permissions or tenants slice the data finely.
• The vectors outgrow one Postgres machine and sharding Postgres is a bigger job than running a search service. Milvus describes "billions of vectors" and runs as Lite, Standalone or Distributed; it also has an on-disk index (DISKANN) and GPU indexes such as GPU_CAGRA.
• Search load hurts your transactional database, and you want it on separate hardware anyway.
• You need hybrid or multi-vector queries without writing the fusion yourself.

What you take on: a second system to deploy, back up and secure, a sync job from Postgres, and no transaction across the two. Permissions now live in two places, and deletes must reach both. Licences are permissive: Qdrant and Milvus are Apache 2.0, and most of Weaviate's repository is BSD 3-Clause, with one folder under a commercial licence, so read it before you ship.

A sensible default

Start on pgvector in the database you already run. Write 50 to 200 real questions with the people who will use the system, record which passages should come back, and measure recall with the filters real users have. If the numbers hold, you have one fewer system to run. If they do not, you know exactly what the dedicated store has to fix.

That is how we approach RAG chatbots and knowledge assistants: pgvector or Qdrant, with permissions applied before any model sees the text.

If you are weighing pgvector against Qdrant or Milvus for a real system, tell us about your data, your users and their permissions, and we will help you test it before you choose. Book a consultation.