pgvector at 100M Scale, in depth: sizing, halfvec and binary quantization, index builds, filtered search, partitioning and replicas

Do the arithmetic first

Start with storage, because it decides everything else. A vector element is a 4-byte float, a halfvec element is 2 bytes, and a binary-quantized bit vector uses one bit per dimension. For 100 million rows, the raw payload alone is:

Those are lower bounds. Add per-row tuple headers and your other columns to the heap. An HNSW index stores its own copy of each indexed vector plus neighbour lists, so for full-precision vectors expect the index to be roughly the size of the vector payload again, often more. Treat these figures as planning estimates, build a 1-million-row sample, measure with pg_relation_size and pg_total_relation_size, and multiply.

The memory rule that follows: HNSW search is random access over the graph, so query latency is good when the index is in RAM and degrades sharply when traversal hits disk. At 1536 float32 dimensions and 100M rows the index alone is beyond most single machines' memory. That is the first reason to reduce precision or dimensions, before tuning anything else.

Index dimension limits shape the schema

pgvector stores vectors of up to 16,000 dimensions, but indexes are narrower: HNSW and IVFFlat index vector up to 2,000 dimensions, halfvec up to 4,000 and bit up to 64,000; HNSW alone also indexes sparsevec up to 1,000 non-zero elements. A 3,072-dimension embedding therefore cannot be indexed as vector at all. You must index it as halfvec, binary-quantize it, or reduce dimensions. Some embedding models are trained so that a truncated prefix of the vector remains useful (Matryoshka-style training); if yours is, truncating and re-normalising is a legitimate reduction, but measure recall before committing.

Reference architecture

The pieces: a loader that uses COPY rather than row inserts, a partitioned table so each index stays a manageable size and can be rebuilt independently, read replicas that serve vector queries so the primary handles writes, and a query layer that sets search parameters per query and re-ranks. A recall harness runs alongside, because nothing else in the system will tell you when quality slips.

Why keep vectors in Postgres at this size at all? Because the alternative has a cost too: a separate vector engine means a second copy of the data, a synchronisation pipeline, and joins between search results and business tables done in application code. Postgres gives you transactions, row-level security, the same backups and the ability to filter on any column with real SQL. The price is that you operate the index yourself, which is what the rest of this article is about. If the workload needs billions of vectors, very high write rates with immediate searchability, or many independent indexes per tenant, compare with the dedicated engines described in vector databases before committing.

Schema: store halfvec or index an expression

There are two ways to use half precision. Store the column as halfvec: heap and index both halve, at a small loss of precision that rarely matters for retrieval. Or keep a full-precision column and build an expression index on its cast, which halves only the index; the heap still holds 4-byte floats, which is useful if you need exact values for re-ranking.

-- Option A: half-precision storage (heap and index both halved)
CREATE TABLE items (
    id         bigint      NOT NULL,
    tenant_id  int         NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    embedding  halfvec(1536) NOT NULL,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY HASH (tenant_id);

CREATE TABLE items_p0 PARTITION OF items FOR VALUES WITH (MODULUS 16, REMAINDER 0);
-- ... items_p1 .. items_p15

-- Option B: full-precision column, half-precision index expression
CREATE INDEX ON items_full USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);
-- Queries must use the same expression to use this index:
SELECT id FROM items_full
ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
LIMIT 10;

The expression-index trap catches many teams: a query that orders by embedding <=> $1 without the cast does not match the index and falls back to an exact scan of 100M rows. Check every query shape with EXPLAIN.

Loading and building

Load first, index second. Inserting 100M rows into a table that already has an HNSW index makes every insert a graph insertion, which is far slower than one bulk build. Use COPY items (...) FROM STDIN WITH (FORMAT BINARY) from your loader; the pgvector client libraries support the binary format.

Then build per partition. HNSW builds are much faster when the graph fits in maintenance_work_mem; when it stops fitting, pgvector emits a notice such as hnsw graph no longer fits into maintenance_work_mem after 100000 tuples and the build slows. Raise max_parallel_maintenance_workers for parallel builds. In Docker, parallel builds use shared memory, so set --shm-size at least as large as maintenance_work_mem.

SET maintenance_work_mem = '32GB';           -- size to the partition, not the table
SET max_parallel_maintenance_workers = 7;    -- plus the leader process

CREATE INDEX CONCURRENTLY items_p0_emb_hnsw
    ON items_p0 USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 16, ef_construction = 64);     -- the documented defaults

-- watch progress from another session
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

Partitioning is what makes this tractable. Sixteen partitions of about 6M rows each means sixteen builds that each fit in memory, can be scheduled one at a time, and can be redone individually after a bad build. CREATE INDEX CONCURRENTLY avoids blocking writes but runs longer. Raising m or ef_construction improves recall at the cost of build time and index size; change them only after the recall harness shows you need to.

Binary quantization with re-ranking

When even half precision is too big, index the binary quantization: each dimension becomes one bit (its sign), and Hamming distance on bits approximates angular distance. The vector copies in the index shrink 32 times relative to float32; the graph's neighbour lists do not, so measure the result with pg_relation_size. Recall from bits alone is poor, so you over-fetch from the index and re-rank by exact distance:

CREATE INDEX ON items_p0 USING hnsw
    ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT id FROM (
    SELECT id, embedding
    FROM items_p0
    ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1::halfvec(1536))
    LIMIT 200                                  -- over-fetch: 10-20x the final k
) candidates
ORDER BY embedding <=> $1::halfvec(1536)       -- exact re-rank on stored vectors
LIMIT 10;

The cost moves rather than disappears. The re-rank reads the full vector for every candidate from the heap, which at 100M rows is random I/O unless the heap is cached. With 200 candidates per query that is 200 heap fetches; at high query rates this, not the index, becomes the bottleneck. Tune the over-fetch factor against measured recall, and remember binary quantization works best with high-dimensional embeddings whose values are centred around zero; test it on your model.

Filtered search without empty results

The classic failure: WHERE tenant_id = 42 ORDER BY embedding <=> $1 LIMIT 10 returns three rows. HNSW visits about ef_search candidates (default 40) and the filter is applied afterwards; if few candidates belong to tenant 42, few survive. Since 0.8.0, iterative index scans fix this by continuing the graph search until enough rows pass the filter:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order;   -- or strict_order
SET LOCAL hnsw.max_scan_tuples = 20000;          -- the default cap
WITH r AS MATERIALIZED (
    SELECT id, embedding <=> $1 AS distance
    FROM items
    WHERE tenant_id = $2
    ORDER BY distance
    LIMIT 10
)
SELECT * FROM r ORDER BY distance;               -- restore exact order
COMMIT;

strict_order guarantees results in exact distance order; relaxed_order allows slight misordering for better recall, and the materialized CTE re-sorts. The scan stops at hnsw.max_scan_tuples, so very selective filters can still come back short. For those, the structural fixes are better: partition by the filter column so the planner prunes to one partition with its own index, use a partial index for a few hot values, or use a plain B-tree index and let the planner choose an exact scan when the filter leaves only thousands of rows. Partitioning strategy covers choosing keys.

Measure recall, not just latency

Approximate search fails quietly: results look plausible while the true nearest neighbours are missing. Keep a fixed sample of query vectors, compute exact neighbours with the index disabled, and compare.

import psycopg
from pgvector.psycopg import register_vector

def topk(conn, q, k, exact):
    with conn.transaction():
        if exact:
            conn.execute("SET LOCAL enable_indexscan = off")
        conn.execute("SET LOCAL hnsw.ef_search = 100")
        rows = conn.execute(
            "SELECT id FROM items_p0 ORDER BY embedding <=> %s::halfvec LIMIT %s", (q, k)
        ).fetchall()
    return {r[0] for r in rows}

def recall_at_k(dsn, queries, k=10):
    with psycopg.connect(dsn) as conn:
        register_vector(conn)
        hits = sum(len(topk(conn, q, k, False) & topk(conn, q, k, True)) for q in queries)
    return hits / (k * len(queries))

Run it on one partition so the exact scan finishes in reasonable time, per release and after every rebuild or parameter change. Then raise ef_search with SET LOCAL until recall meets your target, and record the latency cost. The graph mechanics behind the trade-off are in HNSW maths.

Replicas, updates and vacuum

Indexes are physical structures, so streaming replicas receive them through WAL; serving vector reads from replicas is the simplest horizontal scale. Index builds generate a lot of WAL, so watch replica lag during rebuilds. Beyond one machine, the pgvector documentation points to sharding with tools such as Citus.

Updates and deletes are the slow-burning problem. Deleted rows leave entries in the HNSW graph until vacuum repairs it, and vacuum on a large HNSW index is slow. The README advises speeding it up by reindexing first: REINDEX INDEX CONCURRENTLY then VACUUM. With per-partition indexes, rebuilding one partition at a time keeps this manageable. Re-embedding the corpus with a new model is a full rewrite; do it into a new column or table and switch reads atomically rather than updating in place.

Failure modes

For general index behaviour in Postgres, see Postgres indexes in depth.

What to do next

1. Compute payload size for your dimensions and precision, then measure a 1M-row sample with pg_relation_size.
2. Check your embedding dimension against the index limits and pick vector, halfvec or binary quantization.
3. Partition the table so each partition index builds within maintenance_work_mem.
4. Bulk load with COPY BINARY, then build indexes per partition with parallel workers.
5. Turn on iterative scans for filtered queries and confirm full result counts.
6. Build the recall harness, set ef_search per query from its results, and run it on every release.