pgvector or a Dedicated Vector Database: How to Decide, With Real Thresholds

TL;DR: Start with pgvector when your vectors share transactional data, filters are relational, corpus size is moderate, and one operational boundary is valuable. Consider a dedicated vector database when you need billions of vectors, high concurrent recall-sensitive queries, independent scaling, heavy multitenancy, or specialized filtering and replication. Decide with a benchmark that includes your filters and update rate, not a top-k query over an empty table.

Why this matters in 2026

The “Postgres or vector database” debate is often framed as a technology preference. The better framing is an access-pattern decision. A retrieval system has an embedding index, metadata filters, source-of-truth records, authorization rules, ingestion updates, and an application that needs a predictable latency budget. The best store is the one that makes those interactions correct and operable at your expected scale.

pgvector keeps vector search beside relational records, transactions, full-text search, and row-level security. That is a strong default for product search, document retrieval, recommendations, and early RAG systems. A dedicated vector database can provide purpose-built index management, independent scaling, namespace isolation, filtering behavior, and high-concurrency query capacity. Those benefits become more important as vector traffic and corpus size stop resembling the rest of your application database.

The pgvector documentation is unusually useful because it documents index choices, filtering behavior, iterative scans, half precision, binary quantization, and hybrid search. Read it against your real workload. A configuration that is excellent for 10 million vectors may be wasteful or insufficient at 1 billion.

Key terms and mental model

Model the request as a pipeline:

query -> authorization and tenant filter -> candidate generation
      -> vector and lexical scores -> rerank -> source records and citations
      -> response

The vector store is only candidate generation. It should not become the owner of permissions, document lifecycle, billing, or audit facts without a deliberate design. Keep a stable document ID and version so a retrieval result can be checked against the source of truth before it reaches a model or user.

Start with the workload matrix

Write down the shape of one hour of production traffic. Include vector count, embedding dimensions, new and deleted vectors per second, query rate, p50 and p95 latency target, recall target, filter combinations, tenant count, concurrency, update freshness, and backup requirements. A single aggregate vector count hides the workload variables that determine the index.

These are planning thresholds, not laws. Hardware, dimensions, index parameters, query distribution, and recall target can move the boundary. The correct next step is a benchmark, not a migration.

Choose HNSW or IVFFlat in pgvector

HNSW builds a graph of neighboring vectors. It often provides a useful speed and recall tradeoff without a separate training step. The m parameter affects graph connectivity and memory, ef_construction affects build quality and time, and hnsw.ef_search affects query-time candidate exploration. Higher values can improve recall at the cost of CPU and latency.

IVFFlat groups vectors into lists. lists controls the index partitioning and ivfflat.probes controls how many lists a query visits. It can be faster and lighter to build, but the index needs representative data and tuning. A new corpus or highly changing distribution may require a rebuild or a careful list strategy.

CREATE TABLE document_chunks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id uuid NOT NULL,
  document_id uuid NOT NULL,
  content text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}',
  embedding vector(1536) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX chunks_tenant_idx ON document_chunks (tenant_id);
CREATE INDEX chunks_embedding_hnsw_idx
  ON document_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 80;
SELECT id, document_id, content,
       1 - (embedding <=> $1::vector) AS similarity
FROM document_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 20;

The example starts with a tenant filter and an HNSW index. That does not guarantee good filtered recall, which is a separate problem. Measure it before setting a production limit.

Treat filtered recall as a first-class metric

Approximate vector indexes usually find neighbors first and apply a filter during or after candidate scanning. If only a small fraction of the corpus matches tenant_id, region, product, or ACL predicates, the index can stop before it has found enough valid rows. A query may return 20 rows quickly while missing relevant rows that a full filtered scan would have found.

The pgvector project documents iterative scans for filtered searches in newer releases. This lets the index continue scanning when the initial candidates do not satisfy the filter, subject to strict or relaxed behavior and an application-level cap. Benchmark high-selectivity and low-selectivity filters separately. A filtered recall of 0.95 on an unfiltered dataset is not evidence that every tenant query has recall 0.95.

Use a labeled evaluation set with relevant IDs. For each query, compare approximate results to exact filtered results, then record recall at 5, 10, and 20, p95 latency, rows scanned, CPU, and memory. Include new tenants with small partitions and large tenants with hot indexes. If your authorization predicate is complex, test the exact SQL shape, not a simplified filter.

Add hybrid search when terms matter

Semantic similarity is weak at exact identifiers, error codes, SKUs, version numbers, and rare names. PostgreSQL full-text search can provide lexical candidates, and reciprocal rank fusion or a cross-encoder can combine the lists. A dedicated vector database does not remove this requirement; you may still need a second search system or a lexical feature.

ALTER TABLE document_chunks
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX chunks_fts_idx
  ON document_chunks USING gin (search_vector);

WITH semantic AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1::vector) AS rank
  FROM document_chunks
  WHERE tenant_id = $2
  ORDER BY embedding <=> $1::vector
  LIMIT 50
), lexical AS (
  SELECT id, row_number() OVER (ORDER BY ts_rank_cd(search_vector, $3)) AS rank
  FROM document_chunks
  WHERE tenant_id = $2 AND search_vector @@ $3
  ORDER BY ts_rank_cd(search_vector, $3) DESC
  LIMIT 50
)
SELECT COALESCE(semantic.id, lexical.id) AS id,
       COALESCE(1.0 / (60 + semantic.rank), 0) +
       COALESCE(1.0 / (60 + lexical.rank), 0) AS rrf_score
FROM semantic
FULL OUTER JOIN lexical USING (id)
ORDER BY rrf_score DESC
LIMIT 20;

RRF is a useful baseline because it does not require score calibration across different ranking functions. For high-value search, evaluate it against a trained or hosted reranker. Keep the original scores and ranks in traces so a poor answer can be traced back to candidate generation, fusion, or reranking.

Choose dimensions, precision, and storage

Embedding dimensions directly affect memory, index size, cache behavior, and query CPU. Do not select a lower dimension because the index is large unless you have evaluated the embedding model's quality at that dimension. halfvec can reduce storage and memory, but it needs a recall comparison. Binary quantization can be useful for a two-stage search where compressed vectors generate candidates and higher precision vectors rerank them.

Estimate before provisioning:

raw vector bytes = vector_count * dimensions * bytes_per_value
working set      = raw vector bytes + index overhead + metadata + WAL headroom
required memory  = hot working set + Postgres shared buffers + connections

The index is not the only cost. Ingestion writes WAL, vacuum maintains dead tuples, replicas copy changes, and backups include the data. If you retain every document version, vector growth may be driven more by lifecycle policy than by current corpus size. Store the minimum searchable representation and delete or archive old versions deliberately.

Know when a dedicated database wins

A dedicated vector database is worth evaluating when vector queries need independent scaling from transactions, the index is too large for the Postgres memory and storage tier, query concurrency is highly spiky, or the service provides filtering and replication semantics you would otherwise build yourself. It is also useful when teams need separate operational ownership and a managed vector workload with no impact on the primary database.

The cost is another distributed system. You now have dual writes or an outbox, data freshness lag, separate auth and tenant policy, backup and deletion workflows, network latency, provider limits, and a migration contract. If the source row is deleted but the vector remains, a retrieval result can cite data the user no longer owns.

Use a dedicated service only after measuring the boundaries:

Public comparisons can help form hypotheses, but they are not your decision. For example, a 2026 study compared several systems on million-scale benchmark datasets, while the independent pgvector filtered-recall benchmark shows how filters can change outcomes. Neither represents your documents, hardware, or authorization predicates.

Plan migration and dual operation

Move through an interface, not a provider-specific query spread across the application. Define search(query, tenant, filters, topK) and return stable IDs, scores, ranks, index version, and source version. Implement pgvector first, then a dedicated adapter. This also makes offline replay and shadow traffic possible.

For Postgres to dedicated migration, export source IDs, text or source references, metadata, embeddings, and model version. Load in batches, build the index, replay recent updates from an outbox, then shadow production queries. Compare candidate overlap, recall set membership, p95 latency, costs, and deletion freshness. For the reverse migration, make sure the relational schema and vector dimension support the index, then backfill and dual-read before removing the service.

Do not dual-write from the request handler without an idempotency key. Use an outbox in the source database, retry safely, and expose lag. Deleting a document should create a durable deletion event that both stores process. Tenant moves and ACL changes deserve the same treatment.

Tradeoffs and when not to do this

Use pgvector when the data is already relational, query volume is moderate, filters are SQL-shaped, and your team benefits from one transaction and backup boundary. A dedicated service is not automatically more accurate, cheaper, or simpler. It can be the right split at scale, but it also introduces network and lifecycle failure modes.

Do not select a vector database because a demo returns neighbors faster. First establish recall and latency with filters, updates, deletes, reranking, cold starts, and tenant isolation. Conversely, do not force vectors into a busy OLTP primary when the index has become an independent product workload.

Benchmark your own workload

Build a benchmark runner that replays real query shapes with anonymized text and the same metadata distributions. Include exact ground truth for a sample that is small enough to calculate, then run approximate variants under concurrency. The benchmark should report recall at each top-k, p50 and p95 latency, candidate count, database CPU, storage I/O, index memory, and fresh-update visibility.

Test four data states: warm and cold cache, stable corpus and active updates. Test filter selectivity at 100 percent, 10 percent, 1 percent, and the smallest tenant you operate. Add deletes and ACL changes while queries are running. A search system that looks good on a static corpus may have poor freshness or tombstone behavior under continuous ingestion.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, document_id
FROM document_chunks
WHERE tenant_id = '00000000-0000-0000-0000-000000000001'
ORDER BY embedding <=> '[0.01,0.02,0.03]'::vector
LIMIT 20;

SELECT query, calls, mean_exec_time, rows,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query ILIKE '%document_chunks%'
ORDER BY total_exec_time DESC
LIMIT 20;

Publish the benchmark input, hardware, index parameters, and quality labels with the result. This makes provider conversations more useful and stops a favorable demo from becoming an undocumented architecture decision.

Choose acceptance thresholds before reading the result. For example, you might require recall at 10 above a stated floor for every important tenant class, p95 latency below the interactive budget at expected concurrency, and deletion visibility within a fixed interval. The exact values belong to the product, but the rule must be explicit. Otherwise the team will choose the store that produces the most comfortable chart rather than the one that protects the user experience.

Repeat the benchmark after a month of index growth. Vector systems change as the corpus grows, tenant distribution shifts, and update churn creates dead or stale entries. Record index build duration, replica catch-up, backup size, and recovery time. A provider decision is operationally sound only when the system remains inside its quality and latency budget after routine maintenance.

Keep the raw benchmark results, not only the recommendation. Future embedding models and tenant growth should be evaluated against the same acceptance contract.

Common failure modes

The most common failure is benchmarking unfiltered HNSW on a synthetic corpus. Real retrieval often includes tenant and ACL filters, where approximate candidates may not be sufficient. Another failure is tuning ef_search until recall improves without measuring CPU, queueing, and tail latency under concurrency.

Teams also forget embedding versioning. A new model changes dimensions or neighborhoods, and a partial reindex produces incomparable scores. Store model and index versions with each chunk. Finally, a dedicated service can drift from source data through missed deletes or failed upserts. The search path needs a reconciliation job and a durable event trail.

Production readiness checklist

• The workload matrix includes vector count, dimensions, QPS, updates, filters, tenants, recall, and latency.
• Exact filtered results provide a labeled baseline for approximate recall.
• HNSW or IVFFlat parameters are benchmarked under production-shaped concurrency.
• Filter selectivity and iterative scan behavior are measured by tenant and ACL pattern.
• Hybrid lexical and semantic search is evaluated for identifiers and exact terms.
• Embedding model, dimension, precision, index, and document versions are stored.
• WAL, vacuum, backup, delete, and reindex costs have capacity headroom.
• Search is behind a provider-independent interface with stable source IDs.
• Outbox, retry, deletion, reconciliation, and lag metrics exist for split deployments.
• A migration can shadow, dual-read, and roll back without losing authorization correctness.

Frequently Asked Questions

Is pgvector good enough for a production RAG system?

Often, yes. It is a strong choice when the corpus and query rate fit the Postgres tier, metadata and permissions are relational, and your team wants one transactional source of truth. Production quality depends more on chunking, filters, embedding choice, reranking, citations, and evaluation than on the brand of index. Measure filtered recall and tail latency before deciding that you need a separate service.

When should I choose HNSW over IVFFlat?

HNSW is a useful first test when you want strong approximate recall and can afford more build time and memory. IVFFlat can be attractive when build cost and memory are more constrained or your workload has a stable distribution and a carefully tuned lists and probes configuration. Benchmark both with your data. The query, update, and restart behavior matter as much as the initial search latency.

Does a dedicated vector database solve filtered recall?

Not automatically. Every approximate index has a candidate-generation and filtering interaction. A service may offer better native filtering or iterative behavior, but you still need to measure recall for your predicates, namespaces, and tenant sizes. Ask for the exact filtering semantics and test deletes, updates, and high-selectivity conditions. A faster query that misses authorized relevant documents is not a successful retrieval system.

Should I store full document text in the vector database?

Store enough for efficient retrieval and tracing, but keep the source of truth where lifecycle, authorization, and deletion are managed. Some systems store chunk text beside vectors for lower read latency. If you do, version it and reconcile it with the source. Never assume a vector result is still authorized just because it exists in the index; recheck the document and tenant state before generation.

How do I estimate the migration effort?

Count more than vectors. Include model and dimension changes, metadata mapping, deletes, ACL changes, outbox events, backfill throughput, index build time, shadow traffic, quality evaluation, dashboards, and rollback. A provider adapter can be small while lifecycle correctness is large. Plan at least one dual-read period and a reconciliation report before switching the default.

Conclusion and next steps

pgvector is the right starting point for many systems because it keeps retrieval close to relational truth. A dedicated vector database wins when vector work needs independent scale, specialized operational behavior, or a different performance envelope. The boundary should be discovered through filtered recall, tail latency, update freshness, and cost measurements.

Read the RAG learning guide, then use the RAG evaluation guide to build a labeled benchmark before moving providers. The next artifact should be a workload matrix with exact filter examples and acceptance thresholds.