pgvector or a Dedicated Vector Database?

Every “pgvector vs Pinecone” comparison online eventually turns into a benchmark chart of raw queries-per-second at some fixed vector count, as if that number alone should decide the architecture of your retrieval system. It shouldn’t. The teams who regret their vector database choice six months in almost never regret it because of raw throughput — they regret it because they picked based on a benchmark instead of asking whether their filtered queries, their operational headcount, or their consistency requirements actually matched what they chose.

The honest version of this decision has three real inputs, and none of them is “which one is faster in isolation”: how large is the corpus actually going to get, how complex are the filters that need to run alongside the similarity search, and how much dedicated operational budget exists to run and tune a new piece of infrastructure. Get those three answers first, and the pgvector-versus-dedicated-database question mostly answers itself — not because one is universally better, but because they’re optimized for genuinely different points on those three axes.

You’ll learn:

• What pgvector actually is, and what “vector search inside Postgres” really means mechanically
• The real tradeoffs between pgvector’s HNSW and IVFFlat index types
• Why filter complexity, not raw vector count, is often the deciding factor
• What operationally changes when you add a dedicated vector database to your stack
• A concrete decision framework based on corpus size, filter needs, and ops budget
• How to actually measure recall and latency on your own data instead of trusting a vendor chart

Table of Contents

1. What pgvector Actually Is
2. pgvector’s Index Types: HNSW vs. IVFFlat
3. Why Filter Complexity Matters More Than Vector Count
4. What a Dedicated Vector Database Actually Buys You
5. The Decision Framework
6. Measuring Recall and Latency on Your Own Data
7. Running pgvector in Production
8. Common Pitfalls

What pgvector Actually Is

pgvector is a Postgres extension that adds a vector column type and a set of distance operators — <-> (Euclidean/L2), <=> (cosine distance), <#> (negative inner product) — plus index types built specifically for approximate nearest-neighbor search over those columns. It isn’t a bolted-on side process; the vectors live in an ordinary table, alongside whatever relational columns already describe that row, and a similarity query is an ordinary SQL query with an ORDER BY on a distance operator.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    published_at TIMESTAMPTZ NOT NULL,
    content TEXT NOT NULL,
    embedding VECTOR(1536)
);

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

This is the whole pitch in one query: postgres vector search that composes naturally with WHERE, JOIN, transactions, and every other relational tool already in the stack, instead of living in a separate system that has to be kept in sync with the source of truth. Because the embedding lives in the same row as everything else — the same transaction that inserts a new document can compute and store its embedding, with no separate write path to keep consistent — there’s no eventual-consistency window between “the document exists” and “the document is searchable,” which is a real, if underappreciated, correctness property compared to syncing a source-of-truth database into a separate search index. That composability is also exactly where its limits show up, covered below.

pgvector’s Index Types: HNSW vs. IVFFlat

Without an index, ORDER BY embedding <=> ... is an exact nearest-neighbor search — it computes the distance to every row and sorts, which is correct but scales linearly with table size. Past a few tens of thousands of rows this gets slow enough to need an approximate index, and pgvector ships two.

IVFFlat partitions the vector space into a fixed number of clusters (lists) via k-means, and a query only searches the probes closest clusters to the query vector instead of the whole table. It’s cheaper to build and smaller on disk than HNSW, but it needs to be built after a representative amount of data is already loaded (an IVFFlat index built on an empty or unrepresentative table produces poorly-shaped clusters), and recall degrades measurably as new data is added without rebuilding.

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

SET ivfflat.probes = 10;  -- higher = better recall, slower query

HNSW (Hierarchical Navigable Small World) builds a multi-layer graph structure connecting nearby vectors, and it’s become the default recommendation for new pgvector deployments because it doesn’t need the “build after loading representative data” caveat IVFFlat has, and it typically delivers meaningfully better recall at comparable query latency. The cost is a slower, more memory-hungry build process and a larger on-disk footprint than IVFFlat at the same vector count.

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

SET hnsw.ef_search = 40;  -- higher = better recall, slower query

Both index types are approximate — they trade a small amount of recall for large speed gains, and both expose a tunable knob (probes for IVFFlat, ef_search for HNSW) that lets you move along that recall-versus-latency curve at query time without rebuilding the index. Default to HNSW for new work; reach for IVFFlat specifically when build time or memory during index construction is the binding constraint.

Why Filter Complexity Matters More Than Vector Count

Most comparisons fixate on raw vector count as the deciding factor, but the harder problem in a real application is almost always filtered similarity search — “find the 10 most similar documents for this tenant, published in the last 30 days, excluding archived ones” — not unfiltered search over the whole corpus.

This matters because approximate-nearest-neighbor indexes and WHERE filters don’t automatically combine well. If the filter is applied after the ANN index returns its top candidates, a highly selective filter can leave you with far fewer than the LIMIT you asked for, because the ANN search found its nearest neighbors before knowing most of them would get filtered out. pgvector handles this reasonably well because it’s a real relational database — the query planner can choose to filter first with a normal index on tenant_id and published_at and only then run the distance search over the reduced set, exactly the kind of planner decision the EXPLAIN ANALYZE guide teaches you to read and verify:

EXPLAIN ANALYZE
SELECT id, content
FROM documents
WHERE tenant_id = 42
  AND published_at > now() - interval '30 days'
  AND status != 'archived'
ORDER BY embedding <=> '[0.012, ...]'::vector
LIMIT 10;

If the plan shows the tenant_id/published_at filter running before the ANN index scan and comfortably narrowing the candidate set, filtering is cheap here. If the corpus is dominated by one tenant, or the filter is on a field with poor selectivity, the planner may fall back to scanning more of the HNSW graph than expected to satisfy both the filter and the LIMIT together — and that’s a real cost worth measuring, not assuming away. Many purpose-built vector databases historically handled filtered search by either pre-filtering the entire candidate set (expensive) or post-filtering after ANN search (inaccurate), and closing that gap has been an active area of development across the whole category — it’s worth checking a specific vendor’s current filtered-search behavior rather than trusting general reputation, in either direction.

What a Dedicated Vector Database Actually Buys You

A dedicated vector database — Pinecone, Weaviate, Qdrant, Milvus — earns its place through a genuinely different set of engineering priorities than a general-purpose relational database extended with a vector type:

• Horizontal scaling designed in from the start. Sharding a vector index across many nodes to serve a corpus in the hundreds of millions to billions of vectors is a solved, productized problem in these systems; scaling pgvector past a single Postgres instance’s practical limits means sharding manually or leaning on Postgres-specific scaling tools (Citus, read replicas) that weren’t purpose-built for vector workloads specifically.
• Purpose-built operational tooling. Live index rebuilds without downtime, hybrid dense-plus-sparse search, and multi-tenant namespace isolation are first-class, well-documented features rather than something assembled from general-purpose primitives.
• Managed infrastructure as the default. Most dedicated options ship as a managed service — no capacity planning for HNSW’s memory footprint, no manual index tuning, at the cost of a new vendor relationship, a new bill, and a new system to monitor and secure.
• Ecosystem integrations. Managed vector databases often ship first-class connectors for the popular embedding and RAG frameworks, which can shave real integration time off a new project versus wiring pgvector into the same frameworks by hand — though this gap has narrowed as pgvector’s own ecosystem support matured.

None of this makes a dedicated vector database strictly “better” — it makes it the right tool specifically when corpus scale or feature needs cross a threshold that a single Postgres instance, however well-tuned, wasn’t designed to clear.

Use this table as a quick sanity check against the framework below, not as the decision itself — actual filter complexity and measured recall on your own data override any general table like this one.

The Decision Framework

Weigh these three axes together rather than any one in isolation:

Corpus size. Under a few million vectors, pgvector on reasonably-provisioned hardware handles both build time and query latency comfortably for most applications. Tens of millions starts to require real HNSW tuning attention (m, ef_construction, available RAM for the graph). Hundreds of millions to billions is where the horizontal scaling story of a dedicated system starts to matter more than any single-node tuning knob can compensate for.

Filter complexity. Simple, low-cardinality filters (a handful of tenant IDs, a boolean flag) are cheap in either architecture. Complex, high-cardinality, frequently-changing filter combinations are where being a genuine relational database is pgvector’s strongest structural advantage — the query planner already knows how to optimize arbitrary filter combinations, because that’s the entire discipline a general-purpose database has been refined for over decades.

Operational budget. If the team already runs and monitors Postgres, adding pgvector is close to free operationally — it’s a CREATE EXTENSION statement and an index, not a new system. Standing up a dedicated vector database is a real new piece of infrastructure: a new deployment, new credentials and network paths to secure, a new dashboard to monitor, and a new failure mode to have an on-call runbook for. That cost is worth paying when corpus scale or feature needs genuinely require it, and is pure overhead when they don’t.

A concrete rule of thumb: start with pgvector if the corpus is under roughly ten million vectors, filters lean on data that’s already relational (tenant, date, status, permissions), and the team doesn’t already run a separate specialized data infrastructure team. Move to a dedicated vector database when corpus growth is heading well past that range, when hybrid or hierarchical search features become a hard requirement, or when vector search load is large and spiky enough that isolating it from the primary transactional database’s resource budget becomes valuable in its own right.

Measuring Recall and Latency on Your Own Data

Every vendor benchmark uses a dataset and query distribution that may not resemble the actual application’s embeddings, filters, or corpus shape. The only benchmark worth trusting is one run against real (or realistic synthetic) data:

import time
import numpy as np

def recall_at_k(approx_results, exact_results, k=10):
    approx_ids = set(r[0] for r in approx_results[:k])
    exact_ids = set(r[0] for r in exact_results[:k])
    return len(approx_ids & exact_ids) / k

# Compare HNSW results against an exact (no-index) scan on the same query
exact = conn.execute(
    "SELECT id FROM documents ORDER BY embedding <=> %s LIMIT 10", [qvec]
).fetchall()
approx = conn.execute(
    "SET hnsw.ef_search = 40; SELECT id FROM documents ORDER BY embedding <=> %s LIMIT 10", [qvec]
).fetchall()

print(recall_at_k(approx, exact))

Run this across a representative sample of real queries, not one hand-picked example, and sweep ef_search or probes against measured latency to find where the recall curve flattens out for your actual data — that’s the number that should drive the ef_search/probes setting in production, not a default copied from documentation.

Running pgvector in Production

Getting pgvector working in a notebook is a five-minute task; running pgvector production workloads reliably means treating the index the same way you’d treat any other performance-critical Postgres object — planned, monitored, and rebuilt deliberately rather than left alone after the first CREATE INDEX. Building an HNSW index on a table with millions of rows takes real time and memory, and a plain CREATE INDEX blocks writes to the table for the duration — the same lock behavior every Postgres index build has, covered in more depth in the guide to zero-downtime migrations. Use CREATE INDEX CONCURRENTLY for a vector index on a live table exactly as you would for any other index type, and budget realistic time for it — HNSW builds are CPU- and memory-intensive enough that they take meaningfully longer than an equivalent B-tree build on the same row count.

maintenance_work_mem also matters more here than for a typical B-tree build — HNSW construction is memory-hungry, and an undersized maintenance_work_mem can make the build dramatically slower than necessary. Raise it for the build session specifically rather than globally, if the server’s normal working memory budget is tighter than what a large HNSW build wants.

Common Pitfalls

Mistake: building an IVFFlat index before loading representative data. The k-means clustering step needs real data to form good clusters; an index built on an empty or tiny table produces poor clusters that hurt recall permanently until rebuilt. Solution: load a representative sample first, or default to HNSW, which doesn’t have this ordering requirement.

Mistake: assuming a filtered query with a highly selective filter is automatically fast. A very selective filter combined with an ANN index can, in some plans, mean scanning more of the index than expected to satisfy both the filter and the row limit. Solution: run EXPLAIN ANALYZE on your actual filtered queries, not just the unfiltered similarity search.

Mistake: choosing a dedicated vector database based on a benchmark chart alone. Vendor benchmarks rarely match your filter complexity, embedding dimensionality, or query distribution. Solution: measure recall and latency against your own data before committing to either architecture.

Mistake: treating the pgvector-vs-dedicated decision as permanent and irreversible. Many teams start with pgvector, and the migration path to a dedicated system later — once corpus size or feature needs genuinely justify it — is a well-trodden one, not a rare exception. Solution: default to the lower-operational-cost option first unless you already have clear evidence the corpus will cross the threshold where it stops being enough.

Mistake: skipping maintenance_work_mem tuning for the index build. A slow, memory-starved HNSW build can take dramatically longer than it needs to, and in extreme cases spill in ways that hurt build quality. Solution: raise maintenance_work_mem for the build session before running a large CREATE INDEX CONCURRENTLY ... USING hnsw.

Wrapping Up

pgvector and a dedicated vector database aren’t competing for the same job — they’re optimized for different points on corpus size, filter complexity, and operational budget, and the right choice falls out of being honest about where your actual application sits on those three axes rather than which system won a benchmark chart. Start with pgvector when the corpus is moderate and the filters lean relational, since it’s close to operationally free on top of infrastructure you likely already run; move to a dedicated system when scale or feature needs genuinely outgrow it, and validate that move with recall and latency numbers measured on your own data rather than someone else’s.

Have you actually measured recall on your own queries, or is your index configuration still running on documentation defaults?