If you're weighing a move from pgvector to a dedicated vector database, don't decide on vector count or vibes. Define the switch as a service-level objective, run the candidate system in shadow mode against production-shaped queries, and only migrate when it wins the measured SLO by enough margin to pay for the operational cost of a second system. A commenter on an earlier post framed this well, and it's worth turning into a repeatable procedure.
Why is vector count a bad migration trigger?
"We crossed ten million vectors, time for a real vector DB" is the most common reason teams give, and it's one of the weakest. Vector count correlates with pressure but doesn't cause it. What actually hurts is some combination of recall degrading under your chosen index parameters, tail latency climbing when filters get selective, and your embedding writes competing with OLTP traffic for the same CPU and buffer cache.
Two systems with the same vector count can behave completely differently depending on dimensionality, filter patterns, update rate, and how much of the working set fits in memory. A 3-million-vector table with heavy per-tenant filtering and constant deletes can be a worse fit for pgvector than a 20-million-vector table that's mostly static and queried without filters.
So the trigger isn't a number in a dashboard. It's a specific SLO you're failing, or projected to fail, with headroom you can measure.
The takeaway: migrate against a failing objective, not a growing counter.
What should the migration threshold actually be?
Write it down as an SLO before you benchmark anything, so you're testing a hypothesis instead of shopping for a system that looks fast on a landing page. A usable threshold has more than one dimension:
| Dimension | What to measure | Why it matters |
|---|---|---|
| Recall | recall@k vs an exact-search sample | Fast but wrong retrieval is worse than slow-and-right |
| Tail latency | p95 / p99, split by tenant and filter selectivity | Averages hide the queries that actually hurt |
| Freshness | ingestion-to-searchable lag | Stale results break "I just uploaded this" flows |
| Cost | per-million-queries plus idle cost | Serverless idle and node minimums differ wildly |
| OLTP impact | CPU, cache hit rate, WAL and replica lag | pgvector shares resources with your primary workload |
| Failure behavior | recall/latency during reindex, node loss, backfill | Steady-state numbers lie about bad days |
The single most abused metric here is latency in isolation. A system that returns in 8ms at recall@10 of 0.72 is not beating one that returns in 25ms at recall@10 of 0.96 — it's failing a different, more important objective quietly. Always pin latency to a recall target, never report one without the other.
The takeaway: an SLO with only a latency number is a benchmark designed to be gamed.
How do you measure recall without ground truth?
You don't need labeled data. Your own exact search is the ground truth. Run an unindexed nearest-neighbor query — a sequential scan with an ORDER BY on the distance operator — to get the true top-k, then compare it against what your indexed (or candidate) system returns for the same query vector.
In pgvector, force the exact path by making the planner ignore the ANN index:
-- Exact top-k: no index, full scan, the ground truth
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SELECT id
FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> $1 -- <=> is cosine distance
LIMIT 10;
Then recall@k is just set overlap between that result and your candidate system's top-k for the same vector, averaged over a sample of real query vectors:
def recall_at_k(exact_ids, candidate_ids, k):
truth = set(exact_ids[:k])
got = set(candidate_ids[:k])
return len(truth & got) / k
# Average over a sample of production query vectors
scores = [recall_at_k(e, c, 10) for e, c in zip(exact_runs, candidate_runs)]
mean_recall = sum(scores) / len(scores)
Sample query vectors from real traffic, not from a synthetic distribution. Recall on random vectors tells you almost nothing about recall on the clustered, filtered, repeat-heavy queries your users actually send.
The takeaway: exact search is free ground truth — if you're not measuring recall, you're guessing.
What does shadow mode look like in practice?
Shadow mode means every production query also runs against the candidate system, on the same input, with the candidate's results logged and compared but never served. Users see today's system; you see tomorrow's numbers.
async def search(query_vec, tenant_id, k=10):
primary = await pg_search(query_vec, tenant_id, k) # served to the user
# Fire-and-forget the shadow; never block or fail the request on it
asyncio.create_task(shadow_compare(query_vec, tenant_id, k, primary))
return primary
async def shadow_compare(query_vec, tenant_id, k, primary):
try:
t0 = time.perf_counter()
candidate = await candidate_search(query_vec, tenant_id, k)
latency_ms = (time.perf_counter() - t0) * 1000
await metrics.record(
tenant_id=tenant_id,
candidate_latency_ms=latency_ms,
overlap=len(set(primary) & set(candidate)) / k,
)
except Exception as e:
await metrics.record(tenant_id=tenant_id, shadow_error=str(e))
Two rules keep shadow mode honest. First, isolate resources: run the candidate against its own instance, not one co-located with the database it's trying to replace, or you'll measure interference instead of performance. Second, mirror filters exactly — if production applies tenant_id and an ACL predicate, the shadow query must apply the same ones, because filter selectivity is where dedicated engines and pgvector diverge most.
The takeaway: shadow mode turns a migration argument into a week of logged evidence.
Why must the benchmark include deletes and permission changes?
This is the part most benchmarks skip, and it's the one that can turn a "search upgrade" into a security regression. Leaving Postgres introduces a second source of truth, and a second source of truth has a consistency lag. Every eval that only inserts fresh documents is testing the easy path.
Your benchmark has to include the writes that revoke visibility: hard deletes, permission changes, tenant moves, and re-indexing of documents whose ACL changed. The question isn't "how fast does a new document become searchable" — it's "how long can a deleted or newly-restricted document still be returned." Inside Postgres, a DELETE and the search see the same transaction, so that window is effectively zero. Push search into an external system fed by a replication or ETL pipeline and that window becomes real, measurable, and user-visible.
"Fast but stale for revoked access" is not a search tradeoff you get to accept quietly — it's returning documents to people who are no longer allowed to see them. If your product has multi-tenancy, sharing, or any notion of access revocation, add a specific test: revoke access to a document, then hammer the candidate system with queries that should no longer surface it, and record how many still do and for how long. That number is a security SLO, and it belongs in the migration gate next to recall.
The takeaway: benchmark deletes and revocations, or you'll ship stale-permission results and call it a latency win.
What's the actual migration gate?
Put it in one sentence you can hold a launch to: the dedicated system may replace pgvector only when it wins the full measured SLO — recall, tail latency under real filters, freshness, revocation staleness, and cost — by a margin large enough to pay for dual-write reconciliation, backup and restore, access-control parity, and someone owning it at 3 a.m.
Framed that way, a lot of "we should probably use a vector DB" conversations end differently. Transactional locality — filters, joins, deletes, and permissions living in the same place as your search — is doing quiet work that a latency chart never shows. The candidate has to beat that whole bundle, not just the milliseconds.
| Signal | Stay on pgvector | Consider migrating |
|---|---|---|
| Recall at target latency | Meets SLO in Postgres | Can't hit it without unacceptable p99 |
| OLTP contention | Search load invisible to primary | Embedding queries starve transactions |
| Revocation staleness | Zero (same transaction) | Tolerable and independently monitored |
| Scale / hand-off | One team runs one system | You need managed ops or horizontal scale |
The takeaway: the gate isn't "is it faster" — it's "does it win by enough to pay for a second system's whole lifecycle."
Bottom line
Teams that already run Postgres should treat migrating off pgvector as a decision that has to earn itself, not a rite of passage. Define the switch as a multi-dimensional SLO, use your own exact search as free recall ground truth, and run the candidate in shadow mode against real, filtered, delete-heavy traffic for long enough to see a bad day. If you have access revocation, make stale-permission results a first-class metric — it's a security property, not a search nicety. Migrate when the candidate wins the whole SLO by a margin that covers the operational bill; until then, transactional locality is the cheapest scaling you have.
Top comments (0)