Rebuilds becoming maintenance events is the signal, not the vector count

The question usually asked about pgvector — the Postgres extension that adds a vector column type and vector index types to ordinary tables — is how many vectors it holds before it stops working, and in that form the question has no answer. The index at issue is HNSW, the hierarchical navigable small world graph the previous topic covered, in which a query walks from node to neighbouring node towards the query point rather than scanning everything. In pgvector, HNSW keeps the full vector inside the index next to each node's neighbour list. That one implementation fact is where most of what follows comes from.

What breaks is operational, and it announces itself as a calendar problem. A routine change to a retrieval feature — a better embedding model, a retuned parameter — becomes a multi-hour batch job that holds the primary database's memory while it runs, and holding that memory means taking it away from the OLTP workload on the same instance: online transaction processing, the short reads and writes the application serves to users while all this is going on. No documented limit has to be exceeded for a team to arrive there, and no line in any vector count gets crossed. The threshold is real, and it is expressed in hours and gigabytes.

Why the row count is the wrong unit#

The quantity that governs everything is bytes, and bytes are the product of three numbers the vector count is only one factor of. A 1536-dimensional float32 embedding occupies 6,144 bytes plus tuple overhead. An HNSW index in pgvector stores the full vector inside the index alongside the neighbour lists, so the index is not a compact pointer structure sitting next to the data — it is approximately a second copy of the vectors plus the graph.

The three factors are worth naming separately, because only one of them is ever quoted. There is how many vectors there are; there is how many dimensions each one has; and there is how many bytes one dimension takes, which is four for float32 — hence 1536 dimensions at four bytes each giving 6,144 bytes for one embedding. Storage is the product of all three. A threshold stated as a vector count fixes one factor and says nothing about the other two.

That makes the arithmetic straightforward and the conclusions sharp.

Read that table by the differences between its rows rather than by the rows themselves. Going from the first row to the second, the corpus multiplies by six and the build time multiplies by roughly nine — close enough to proportional that the excess can be put down to graph construction getting slower as the graph being searched grows. Going from the second row to the third, the corpus multiplies by four and the build time multiplies by more than thirteen. Something happens between the second and third rows that did not happen between the first and second.

The last column breaks in the same place. Six times the corpus, from the first row to the second, costs under twice the query latency. Four times the corpus, from the second to the third, costs nearly thirty times. Two columns measured with different instruments — a build clocked in minutes, a query clocked in milliseconds — change character between the same pair of rows, and a coincidence that precise usually means one kind of cause showing up twice.

Those figures are for one 128 GB instance with shared_buffers — the pool of memory Postgres reserves for caching table and index pages — at 32 GB, and an OLTP working set of roughly 40 GB. The interesting row is the last one, and the interesting column is the fourth. pgvector builds the HNSW graph in a memory segment bounded by maintenance_work_mem; when the graph fits, the build is a fast in-memory construction that parallel workers share. When it does not fit, pgvector falls back to a two-pass path that inserts tuples through the on-disk index one at a time, and the build does not get twice as slow — it gets eight to twenty times slower, which is where the 11 h 40 min comes from. The same instance at 3M chunks can hold the graph if you are willing to set maintenance_work_mem to 24 GB for the duration; at 12M it would need 96 GB on a machine that has 128 GB total and is also serving traffic, so the fast path is simply unavailable.

The size of that jump follows from what one inserted tuple has to do. Inserting into an HNSW graph means walking it: start at an entry point, look at a node's neighbours, move to whichever is closest to the new vector, repeat. While the graph sits in the memory segment, each of those steps is a memory reference. Once the graph is on disk, each step is a read of a page that the step before it gave no way to predict, so prefetching cannot help and the read is a random one. The per-tuple cost therefore moves from the cost of a memory reference to the cost of a random read, and the ratio between those two is where an order of magnitude comes from. It is a step rather than a slope. Nothing degrades gradually as the graph approaches the limit, and everything changes the moment it passes it.

The fourth column and the fifth close on each other exactly. The rows with a fast build are precisely the rows whose build memory that instance could be persuaded to grant, and the row with the slow build is precisely the row whose build memory it could not; no row on either side breaks the pattern and nothing is left over. That makes the fourth column the cause of the fifth rather than a companion to it, and it makes the table more than three measurements: given a dimensionality, a precision and a corpus size, you can work out the build memory, hold it against what the instance has spare, and read off which side of the step the workload will land on. Next year's corpus can be added as a row without building anything.

The p95 column moves for a related but separate reason. At 21 GB the index fits comfortably in page cache alongside the transactional working set, and an HNSW traversal touches a few hundred pages that are all resident. At 84 GB it does not fit, the traversal starts taking page faults, and a query whose cost was previously dominated by distance computations becomes dominated by random reads. This is the same mechanism that makes any index slow when it stops fitting in memory; vectors just get there faster because each entry is six kilobytes rather than sixteen bytes.

Change the dimensionality and every number moves. The same 12M chunks at 384 dimensions is a 21 GB index that behaves like the middle row. Switching the column type to halfvec(1536), which keeps each dimension in 16 bits rather than 32, halves the storage to roughly 42 GB and brings the build back inside the machine at some cost in recall. Binary quantization — reducing each dimension to a single bit, so that the index searches a crude approximation and an exact rerank over the top few hundred candidates restores the ordering — compresses the searchable structure to about 2.3 GB and changes the problem entirely. Any threshold stated as "pgvector stops working at N vectors" is quoting one point on a surface with at least four axes.

The second sentence of that paragraph is the whole stumble, and it is worth saying on its own. Twelve million chunks at 1536 dimensions sits in the bottom row of the table; the same twelve million at 384 dimensions behaves like the middle one. The vector count is identical across those two workloads, and everything that decides whether the rebuild is an hour or a weekend differs. A quantity that stays where it is while the dangerous property moves cannot be used to order two workloads by risk, and cannot be used to reassure anybody about either. "We are still under the limit" has the same truth value before and after the dimensionality doubles, before and after the precision halves, and before and after the rebuild cadence goes from yearly to quarterly — which is precisely the set of changes that decides the outcome.

The three numbers that are the actual threshold#

Name the limit in these terms, and it survives contact with a growth projection.

Build duration against the maintenance window. How long does CREATE INDEX take on production-sized data, on production-sized hardware, with the memory settings you would actually be willing to grant it while the instance serves traffic? If that number exceeds the window your company can schedule, the index has become something you cannot recreate on purpose, only on a Saturday with permission. REINDEX INDEX CONCURRENTLY softens this — it builds a replacement without an exclusive lock — at the cost of holding both indexes simultaneously, so 84 GB becomes 168 GB of storage and cache pressure, and the concurrent build is slower than the blocking one.

The doubling there is a consequence rather than an implementation quirk. A replacement index has to be complete before the old one can be dropped, since queries have to be served from something throughout, so for the length of the build both exist. The concurrent build is the slower of the two for a related reason: it has to account for rows that changed while it was running, which a build holding an exclusive lock never has to do. Holding two copies is the price of not holding the lock. That is a choice Postgres made, and a different system could make a different one.

Peak resident memory during construction, and what it displaces. The number to record is not maintenance_work_mem as configured but the instance's actual peak — resident set size, page cache composition, and what happened to the buffer hit ratio on unrelated tables while the build ran. A build that takes nine hours in a corner where nothing else lives is an annoyance. A build that takes nine hours while degrading the checkout path is an outage with a scheduled start time.

The step from the first of those to the second is worth walking, because it is what turns a slow build into a customer-visible failure. shared_buffers is a fixed pool. Pages the build touches have to be brought into it. Bringing a page in evicts one that is already there. What gets evicted is whatever has been untouched longest, which over nine hours of continuous build traffic means the transactional tables that were being served from memory a moment earlier. Every later read of one of those becomes a disk read. A checkout path that was doing a handful of memory reads is now doing a handful of disk reads, and its latency moves by the ratio between the two. Nothing in that chain is specific to vectors; vectors only supply the volume of pages.

Rebuild frequency. A build you perform once at launch and never again can cost a weekend. A build you perform every six weeks cannot. The frequency is set by things nobody counts when they choose a store:

The fourth row is the one that surprises people. HNSW in pgvector handles deletes by marking entries, and the graph's neighbour lists still route through them until a vacuum — the background pass in which Postgres reclaims the space held by rows that are no longer visible to anyone — reclaims the space; heavy churn leaves a structure that is progressively less well connected than a freshly built one, so recall drifts down over months in a way that is invisible unless somebody is measuring it. Chapter 8.10 covered why the graph's connectivity determines recall; the operational consequence is that a corpus with a high update rate does not get to build its index once.

The scope column is worth reading on its own, because it turns out to carry no information. Four of the five causes force a full rebuild, and the fifth builds one index by the same method, so nothing in that column separates one row from another. All the variation lives in the cadence column. That is what makes the annual cost computable without a taxonomy of causes: multiply each cadence by one build duration, add them up, and the answer does not depend on which cause fired.

Multiply those three numbers together and you have the real quantity: how much of your database's capacity, and how many of your maintenance windows per year, this feature consumes. A workload at 3M chunks that re-embeds twice a year costs two 52-minute builds. A workload at 12M chunks that re-embeds quarterly and retunes after each quality regression costs perhaps six eleven-hour builds, which is not a database feature any more.

Those two answers are worth converting into the unit a maintenance calendar is kept in. Two builds of fifty-two minutes is under two hours in a year, which fits inside a single window with room to spare and can be scheduled by one person without asking anybody. Six builds of eleven hours is sixty-six hours, which is more than a full working week of windows, and not one of the six fits inside a Saturday morning. The first of those workloads has a task. The second has a standing claim on the operations calendar, and a standing claim is something that has to be argued for annually.

Watching a build honestly#

The instrumentation for this is in the box and most teams never look at it.

-- What phase is the build in, and how far through?
SELECT phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total
FROM pg_stat_progress_create_index;

-- Did the graph fit? pgvector logs this at NOTICE when it did not.
SET client_min_messages = notice;
SET maintenance_work_mem = '24GB';
SET max_parallel_maintenance_workers = 7;

CREATE INDEX CONCURRENTLY document_chunks_embedding_hnsw
ON document_chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 411324 tuples
-- DETAIL:  Building will take significantly more time.
-- HINT:  Increase maintenance_work_mem to speed up builds.

That NOTICE is the single most useful line of output in this entire subject, and it appears in a log nobody tails during a Saturday build. It tells you exactly where the fast path ended and lets you compute what maintenance_work_mem would have been required: if the graph outgrew 8 GB at 411,324 tuples out of 3,000,000, the full graph needs roughly 58 GB, and you now know the answer without running the experiment.

That computation is one division and one multiplication, and it is worth doing slowly because it is the only place a build gives you its own limit for free. The graph reached the ceiling after 411,324 of 3,000,000 tuples. Dividing the total by that figure says the finished corpus holds a little over seven times as many tuples as the graph had absorbed when it ran out of room. Graph memory grows roughly in proportion to the number of elements held in it, so a graph holding seven-and-a-bit times as many elements needs about seven-and-a-bit times the memory, and seven-and-a-bit times 8 GB is the 58 GB figure. Two conditions travel with that number and should travel with it whenever it is quoted. It assumes proportional growth, which holds while m and the vector width stay put and stops holding if either moves. And it describes the in-memory construction arena rather than the finished on-disk index, which are different structures carrying different overheads — so it states what the fast path would have needed on that particular run, and is not a prediction of how large the index will be when it lands.

The same reasoning explains why a sample-based projection of build time is the estimate most likely to be wrong by an order of magnitude. The natural move is to scale a small sample's build time by the ratio between the sample and the full corpus. That extrapolation would be roughly right if build time were linear in row count, and it is not, for two reasons. HNSW insertion cost grows with the size of the graph being searched during construction, which adds a logarithmic factor and would on its own make a linear projection modestly optimistic. Far more importantly, a sample small enough to be convenient is a sample whose graph fits in memory, while the full corpus's graph may not, in which case the two runs executed different algorithms and the projection was carried across the step. A linear projection across a discontinuity is not a small error. Any sample-based estimate has to state which side of the memory boundary it was measured on, and the only estimate that survives is one taken from a corpus large enough to cross it.

Alongside it, sample the instance rather than the session — resident memory, buffer cache hit ratio on your busiest OLTP table, and p95 on a transactional endpoint that has nothing to do with retrieval. The build's own duration is the least interesting of the four series, because it is the only one you were already expecting to be large.

Slow queries, and the moves that come before a migration#

None of this is a claim that slow queries mean you have outgrown pgvector. A p95 of 340 ms at recall 0.94 usually means the index does not fit in cache, and the responses to that are more memory, a smaller vector type, quantization, or partitioning — all of which keep you in Postgres. Query-side tuning belongs to the parameters covered in the previous topic, and reaching for a different product because ef_search was set badly is an expensive way to fix a configuration.

It is also not an argument that the answer to a long build is always a different database. Three moves sit between "the rebuild is a maintenance event" and "we are adopting a second datastore, with everything chapter 1 said that costs." Partitioning the table by tenant or by time gives you many small indexes that build in minutes and can be rebuilt independently, which converts one eleven-hour event into forty forty-minute ones you can spread across a week. Moving the vector tables to a second Postgres instance fed by logical replication, which streams committed row changes from the primary into a copy you can index however you like, removes the contention with OLTP entirely while keeping SQL, the same backup tooling and the same on-call knowledge. Reducing the stored precision with halfvec or binary quantization attacks the byte count directly, which is the variable everything else depends on. Each of those is cheaper than a migration, and a decision record that has not considered them has not made the case.

Each of those three attacks a different part of the arithmetic above. Partitioning shrinks the number of vectors any one build has to handle, which is the first of the three factors bytes are made of. Quantization shrinks how many bytes a dimension takes, which is the third of them. A second instance leaves every byte figure exactly where it was and changes only whose memory the build displaces, which is a move against the second threshold number rather than against the byte count at all. Knowing which of the three threshold numbers binds is what tells you which move to reach for; without that, the choice between them is made on taste.

And it is not a settled question. The honest state of things is that Postgres has no online, incremental index maintenance for HNSW: there is no background compaction that restores a degraded graph, and no way to rebuild without either a lock or a second full copy. Extensions built on top of Postgres — pgvectorscale's StreamingDiskANN among them — exist specifically to attack the memory-resident assumption by keeping most of the index on SSD, and they change these numbers substantially. Whether they change them enough for your corpus is not something you can read off a blog post.

The threshold, then, is three quantities and not one: how long a build takes on production-sized data with memory you would actually grant it, how much of the instance that build displaces while it runs, and how many times a year you have to do it. Multiply them and you have the share of the operations calendar this feature owns. Each is measurable before anything is migrated, and each moves when the dimensionality, the precision or the churn rate moves — which is what a vector count fails to do and why it cannot stand in for them.

Which leaves you with three quantities that decide the question and no measurements for any of them. A single query benchmark, which is what most teams produce when asked to justify a store, will not give you one.