A load test that supports a decision measures the build, not just the query

A load test in this setting exists to settle one argument: whether to keep vectors in Postgres with the pgvector extension or move them to a store built for nothing else. The number such tests almost always produce is a p95 query latency — the time under which ninety-five of every hundred searches complete, so the ninety-fifth-percentile request rather than the average one. That number is a real measurement of a real thing. It is also the cheap half of the system, because a search walks a small part of the index and stops, while building the index touches every stored vector and holds the whole structure in memory while it does. The term that decides a migration is the second one, and a benchmark that omits it cannot separate the candidates however carefully it measures the first.

The reason this shape of benchmark is so common is that it is the only part of the system that is easy to instrument. A query has a start and an end and returns a number. A build has a start, an end, and a blast radius across an instance that shares memory with the rest of the company. Measuring the first takes twenty minutes; measuring the second takes a staging environment loaded with production-sized data and somebody willing to watch it for a day.

What a build does that a query does not#

Start with what a search actually does, because the bound on its work is the thing that makes it cheap. HNSW — the proximity graph both pgvector and the dedicated stores build, in which each stored vector is a node holding links to a few dozen of its near neighbours — answers a query by entering the graph at some node and walking toward the query vector, keeping a working list of the best candidates found so far. The size of that list is the parameter ef_search. When a full round of neighbours fails to improve the list, the walk stops. So the number of distance computations a query performs is set by ef_search and by how many neighbours each node holds, and both of those are fixed before the query arrives. Growing the corpus adds hops to cross the graph and does not change the width of the search.

A build has no equivalent bound, and the reason is that it has to put every vector into the graph rather than find one. Inserting a single vector means running a search to find where in the graph it belongs, then writing the new node into its chosen neighbours' link lists, then revisiting those neighbours because their lists are now over-full and have to be pruned back. One insertion therefore costs at least one query's worth of traversal, plus writes on top, and the build performs one insertion per stored vector. Three million chunks is three million of those. That is the first reason neither cost can be inferred from the other: one of them scales with the corpus and one of them does not.

Memory is the second reason, and it is the one that reaches the rest of the company. A query reads the graph, touching a small and scattered set of pages; if a page is not resident the query waits for it once and moves on. A build mutates the graph, and every insertion rewrites link lists that later insertions will read, so no region of the structure is finished until all of it is. The index under construction is live working set in a way the finished index never is, which is why Peak instance memory during build is a row in the table below at 118 GB of 128 GB and no query row carries a memory figure at all.

The third step follows from the second and is where the cost lands on somebody who did not ask for it. On the pgvector side the machine doing the build is the machine serving the application's ordinary short reads and writes, and the two contend for the same physical RAM and the same buffer cache. A build occupying almost the whole instance evicts the working set those ordinary queries were being served from, turning cached reads into disk reads. The measured consequence is the OLTP p99 during build row: 31 ms rising to 380 ms, a degradation by more than an order of magnitude, suffered by traffic that has nothing to do with retrieval.

One row in that table is easy to read as an engine property and is not. The dedicated store's OLTP figure reads unaffected because, as the sentence introducing the table says, it runs on its own two-node cluster: there is no shared buffer cache for its build to evict. That is a deployment decision rather than a fact about the software, and pgvector on its own instance would be equally immune. The row directly above it is a genuine engine difference, since 118 GB of 128 GB against 11 GB of 64 GB says how much memory each build algorithm needs — though even there the shared instance is counting the application's footprint as well as the build's. What is strictly comparable is the headroom left behind: under a tenth of the instance free on one side, over four fifths on the other.

The five omissions, and what each one hides#

A single-client, unfiltered, no-writes, warm-cache latency number omits five things, and each omission systematically favours whichever store you were already inclined toward.

Concurrency is the first. A single client measures service time; production measures service time plus queueing. A lone client leaves the machine idle between requests, so every request finds the whole machine waiting for it, and that is a condition production never reproduces. At 30 concurrent clients on a 16-vCPU instance, the same query that took 12 ms takes 34 ms, and the shape of that curve depends on how many distance computations each traversal performs, which is set by ef_search. That dependency is the part worth holding onto: a store configured for higher recall does more work per query, so it reaches the utilisation where queueing dominates at a lower request rate than its idle latency suggests. A benchmark at concurrency 1 cannot distinguish a store that scales to your peak from one that falls over at half of it.

Write load is the second, and it is the one most often skipped because it requires two processes rather than one. HNSW insertion is not cheap: adding a vector means traversing the graph to find neighbours and updating their lists, so a sustained ingest rate competes with searches for both CPU and buffer cache. Because an insertion contains a traversal, the two rates in the decision record below are directly comparable: at 40 inserts per second against a peak of 38 queries per second, the ingest worker issues more graph traversals than the users do, and writes on top of them. Forty inserts per second against the three-million-chunk corpus took the query p95 from 34 ms to 61 ms in the test that eventually replaced this benchmark. If your product embeds documents as users upload them, that concurrent-write number is your real latency and the idle number is a fiction.

Filters are the third, and they usually move the result more than anything else. Chapter 1 established why a filter is an ordinary predicate in Postgres and a metadata copy elsewhere; the measurement consequence is that the two stores are not being asked the same question. Sixty percent of the queries in the workload behind this chapter were scoped to a single workspace holding under 0.1% of the corpus, where Postgres can use a B-tree and an exact scan and return in 4 ms with recall 1.0, while the dedicated store's post-filter path had to over-fetch and still came back with recall 0.71 on that slice. The mechanism behind those two numbers is the order the two systems do their work in. Postgres narrows first: the B-tree index on the workspace column produces a few thousand rows, which are few enough to score exhaustively, so the approximate index is never consulted and the answer is exact by construction. The dedicated store searches the whole collection first and applies the filter to whatever came back, so when the filter admits under a thousandth of the corpus, nearly every candidate is discarded and the survivors are whichever qualifying vectors happened to fall inside the over-fetched set. An unfiltered benchmark reports none of this.

Recall is the fourth, and it is frequently not measured at all because measuring it requires a ground truth that costs real compute. A store returning wrong answers quickly will win any benchmark that does not check the answers. The sharper version of that point is that latency and recall are set by the same knob: raising ef_search widens the candidate list, which costs time and buys accuracy. A latency comparison between two stores at unstated recall is therefore comparing one point on one store's curve against one point on another's, and which points those are was chosen by whoever wrote the defaults.

Cache state is the fifth. A benchmark that runs the same 1,000 query vectors repeatedly against a 21 GB index on a 128 GB machine is measuring a fully resident index with a warm CPU cache, and it is also walking the same graph paths on every repetition, so the pages it needs are the pages it just touched. Production issues queries whose vectors have never been seen before, against an index competing with the OLTP working set. Draw the query vectors from a held-out sample of real production queries; synthetic vectors sampled from a uniform distribution have a different distance profile from real embeddings, which cluster, and the difference shows up as recall estimates that are too optimistic.

Why a latency figure cannot order the two candidates#

The five omissions have one property in common, and naming it is more useful than remembering the list. A p95 query latency does not move when the things that decide the migration move. The dedicated store's 4 ms is the same figure whether its build takes nineteen minutes or nineteen hours, whether it leaves four fifths of its instance free or none of it, and whether its recall on the queries that make up most of the traffic is 1.00 or 0.71. A quantity that holds still while the dangerous property varies cannot be used to order two options by that property, and being measured precisely does not repair that.

The same defect appears one level up, in an aggregate recall figure. A mean over a probe set that mixes filter regimes will barely move when one regime collapses, provided that regime is a minority of the probes. The number that comes out of that mixture in the test below is 0.91, and the recall floor written into the service level objective is 0.90. The aggregate clears the gate. The slice carrying sixty percent of production traffic sits at 0.71 and does not.

Ground truth is a query with the index turned off#

Recall@k is the fraction of the true k nearest neighbours that the approximate search returned, so measuring it means computing the true answer once. Postgres will do this for you if you stop it using the index.

-- Exact top-10 for one probe vector: no index, full scan, correct by construction.
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;

SELECT c.id
FROM document_chunks c
JOIN documents d ON d.id = c.document_id
WHERE d.workspace_id = $1 AND d.archived_at IS NULL
ORDER BY c.embedding <=> $2
LIMIT 10;

The two SET LOCAL lines are what make the result a ground truth rather than another approximate answer. With index scans disabled the planner has one option left, which is to read every qualifying row and score it, and a full scan cannot miss a neighbour it has already read. Correctness here is bought with the very thing the index exists to avoid.

Run that query for a sample of 500 probe vectors — query vectors set aside purely for measurement — drawn from real traffic, store the results, and you own a ground truth you can reuse for every configuration and every candidate store. It is expensive — roughly 900 ms per probe against three million rows, so about eight minutes for the sample — and it is a one-off. The harness around it is small.

import time, statistics, psycopg

def recall_at_k(approx: list[str], exact: list[str], k: int = 10) -> float:
    return len(set(approx[:k]) & set(exact[:k])) / k

def run_probe_set(conn, probes, truth, ef_search: int):
    latencies, recalls = [], []
    with conn.cursor() as cur:
        cur.execute("SET hnsw.ef_search = %s", (ef_search,))
        for probe in probes:
            t0 = time.perf_counter()
            cur.execute(SEARCH_SQL, (probe.workspace_id, probe.vector))
            ids = [row[0] for row in cur.fetchall()]
            latencies.append((time.perf_counter() - t0) * 1000)
            recalls.append(recall_at_k(ids, truth[probe.id]))
    latencies.sort()
    return {
        "ef_search": ef_search,
        "p50_ms": round(latencies[len(latencies) // 2], 1),
        "p95_ms": round(latencies[int(len(latencies) * 0.95)], 1),
        "recall@10": round(statistics.mean(recalls), 3),
    }

ef_search is a parameter of that function rather than a constant because a single call returns one point on a curve rather than a verdict, and sweeping it is what lets two stores be compared at equal recall instead of at equal defaults.

Run the probe set under three conditions rather than one — idle, at production concurrency, and at production concurrency with the ingest worker running — and you have nine numbers instead of one, which is roughly the point at which a benchmark starts being able to change somebody's mind.

The probe set deserves as much care as the harness. Five hundred queries sampled uniformly from last week's traffic will be dominated by whatever the most common access pattern is, which usually means the easy one, and an average taken over them hides the slice that will break. Stratify instead: draw roughly equal numbers from each filter regime you care about — unfiltered, workspace-scoped, date-bounded, and whatever combination the product produces most — and report recall and latency per stratum as well as overall. The narrow-filter row in the table below only exists because somebody insisted on that split; a uniform sample would have buried a 0.71 inside a weighted mean of 0.91 and nobody would have looked again.

Where the test data comes from#

None of these numbers mean anything on a 200,000-row sample, for the reason chapter 2 gave: the memory boundary that changes the build's algorithm sits between the sample and the corpus, and latency has its own boundary where the index stops fitting in cache. The test needs production-sized data, which in practice means one of three things, in descending order of trustworthiness.

A restore of a production snapshot into an isolated environment is the honest option, with the text columns scrubbed or tokenised if the corpus is sensitive. The embeddings themselves usually do not need scrubbing for a load test, and they are the part that governs every measurement — you can null out content and still get correct build, memory and latency figures, because the index only ever touches the vector column.

Failing that, re-embedding a public corpus of comparable size and dimensionality gets the byte count right, and byte count is what drives build time and cache pressure. What it gets wrong is the distance distribution: real corpora cluster by topic and by tenant, and clustered data produces different graph structure and different recall than a corpus that does not cluster the same way. Treat recall from a substitute corpus as indicative and latency as reasonably solid.

Generating random vectors is the option to avoid for anything except a smoke test of the harness itself. Uniformly random high-dimensional vectors are all roughly equidistant from each other, which is the pathological case for a proximity graph: recall measured on them is not related in any predictable direction to recall on real embeddings, and the resulting number has misled more than one migration.

What the full test said#

Here is the same comparison as the three-bullet pull request, extended to the measurements that were missing. The pgvector column is a 128 GB instance shared with the OLTP workload; the dedicated store is on its own two-node cluster.

The first row is worth reading before any of the others, because the naive benchmark and the full test agree on it exactly. It is the one condition the two harnesses share, and two independently built harnesses returned 12 ms for it, which licenses a conclusion the team nearly missed: the original benchmark was not sloppy and re-running it more carefully would have changed nothing. Its defect was scope rather than accuracy, and that is the harder defect to argue somebody out of, because every attempt to check it confirms it.

The latency rows are more informative read as differences than as levels. Adding production concurrency costs pgvector a little over twenty milliseconds and the dedicated store a little under twenty, close enough to call the same penalty. Adding the ingest load costs pgvector nearly thirty milliseconds more and the dedicated store about three, roughly a tenfold difference in the same column pair. Queueing hurts both stores alike; write contention is where they actually diverge, and the reason is the shared instance rather than the index.

Two rows in the pgvector column carry the same figure, and they are not a duplication. Index build and Rebuild duration both read 52 min, and the same holds for the dedicated store at 19 min. A rebuild costs exactly what a first build costs, which says there is no incremental repair path in either system — you do not patch an index, you construct another one. That identity turns rebuild frequency into an operating cost you can multiply out: the four rebuilds in the record below are four full fifty-two-minute events, each carrying the OLTP degradation from the row above it.

The dedicated store is genuinely faster on every latency row, and on a workload that ran unfiltered at high concurrency the case would be made. This workload does not run unfiltered: the recall row for the narrow filter is a correctness gap, not a performance one, and 0.71 means three of the ten results a user should have seen were missing. Closing it would mean over-fetching by a large factor or moving the filter into the store's own payload index, which is work — and it is exactly the work chapter 4 is about.

The pgvector figure on that row is exactly 1.00, and an exact 1.00 is a signal rather than a score. Perfect recall against a ground truth computed by exhaustive scan means the measured query path was itself an exhaustive scan over the filtered rows. It is evidence that on this slice pgvector was not using its graph at all, rather than evidence that its graph is more accurate, and a reader who takes 1.00 as an index quality result will predict the wrong thing about the unfiltered row, where the two stores sit at 0.94 and 0.96.

Meanwhile the two rows that would justify a migration are real. A build that pushes the instance to 118 GB of 128 GB and multiplies OLTP p99 by more than an order of magnitude is the operational threshold from chapter 2, where a rebuild stops being a background job and becomes an event the rest of the company notices, measured rather than asserted. The question the record has to answer is whether that recurs four times a year or once every three, and whether partitioning would defuse it before a second datastore is required.

The record that ties it together#

A decision made this way is worth writing down in a form that a different engineer can reopen in eighteen months without repeating the work. The six axes from chapter 1 — corpus size and growth, query rate and shape, filter selectivity, SLOs, staffing and failure behaviour — are the skeleton; the measurements are the flesh; the revisit trigger is what stops the document becoming folklore.

decision: keep vectors in Postgres (pgvector), partition document_chunks by workspace
date: 2026-03-14
status: accepted

workload:
  corpus: 3.1M chunks, 1536-dim float32, growing ~400k/month
  query_rate: 38 qps peak, 6 qps median
  write_rate: 40 inserts/s sustained during business hours
  filter_selectivity: 60% of queries scoped to <0.1% of corpus; 12% unfiltered
  slo: retrieval p95 <= 80 ms; recall@10 >= 0.90 on the production probe set

measured:
  p95_filtered_30_clients_ms: 34
  p95_during_ingest_ms: 61
  recall_at_10_unfiltered: 0.94
  recall_at_10_narrow_filter: 1.00
  index_build_minutes: 52
  peak_instance_memory_gb: 118      # of 128
  oltp_p99_during_build_ms: 380     # baseline 31
  rebuilds_last_12_months: 4

alternatives_considered:
  dedicated_store:
    faster_on: unfiltered p95 (4 ms vs 12 ms), build (19 min vs 52 min)
    costs: recall 0.71 on the dominant filtered slice without extra work;
           second backup, replication and on-call story
  partition_by_workspace:
    effect: largest partition index ~2.4 GB, builds in ~5 min, rebuilt independently

staffing: 2 of 7 backend engineers can debug Postgres index behaviour; 0 have
          operated a dedicated vector store in production

failure_behaviour: retrieval failure degrades to keyword search over tsvector;
                   the assistant answers with a "limited results" banner

revisit_when:
  - corpus exceeds 8M chunks, or
  - a single partition's index build exceeds 20 minutes, or
  - rebuild frequency exceeds 6 per year, or
  - filtered recall@10 on the probe set drops below 0.90

The workload block accounts for each of those six axes once, and then carries one field the skeleton did not ask for: write_rate. Its presence is this chapter's argument in miniature. The axis list was written to describe how a system is queried, and measuring the system turned up a term that describes how it is fed, which is the term the p95 was silently holding constant.

The revisit_when block is what makes this a decision rather than an opinion. It converts "we will look at this again when it becomes a problem" into four thresholds that a scheduled job can evaluate, and it means the next argument about vector stores starts from a number somebody agreed to in advance. Each of the four names a measurement that already exists in the block above it, which is what keeps them evaluable: a trigger phrased in terms of something nobody instruments is a wish.

The limits of a two-week spike#

There is a limit to how far a load test can carry the decision, and it is worth stating because otherwise the record claims more than it knows. Every figure in the dedicated-store column was produced by a two-week spike — a time-boxed trial whose only deliverable is knowledge — in which nobody had operated that system before. The p95 is trustworthy; the operational numbers — how it behaves in its third month, what its failure modes look like under memory pressure, how long a version upgrade takes — are not measurable in a spike at all, and they are the numbers that determine whether adopting it was a good idea.

The test also says nothing about whether recall of 0.94 is good enough, because recall is not the product metric. A retrieval system exists to make some downstream answer correct, and it is entirely possible for recall@10 to fall from 0.94 to 0.88 with no measurable change in answer quality, or for a two-point recall drop on one query class to break a feature. Tying the two together is a separate evaluation and the load test is not a substitute for it.

Every figure in the table also has a shelf life, because it was taken against a corpus growing at roughly four hundred thousand chunks a month. The build row degrades first and fastest, since build cost tracks corpus size directly, while the latency rows move slowly until the index stops fitting in cache and then move all at once. That is why the revisit_when thresholds are phrased as corpus size and build duration rather than as a date: a calendar reminder fires whether or not anything has changed.

And it does not touch the work each option quietly assigns to you afterwards. The 0.71 recall row above is one instance of it: a number that looks like a benchmark result and is really a backlog item. There are several of those on both sides, they are asymmetric, and the direction of the asymmetry is the opposite of what most teams assume.

A load test earns its cost by measuring the terms that can reverse the decision, and for a workload of this size the query path is not one of them. The build is, because it scales with the corpus, occupies the machine, and recurs on a schedule nobody chose. Recall on the queries the product actually issues is, because it is the only figure in the set that can be wrong rather than merely slow. A p95 comparison bounds the cheap half, and a bound on the cheap half is not a decision.

Write the result down as a workload, a set of measurements, the alternatives with what each would have cost, and the thresholds that would reopen the question. The thresholds are the part that survives: they let the next engineer see that the answer was conditional, see which condition it rested on, and check whether that condition still holds without rebuilding the harness.