Houston teams are past the pgvector demo stage. The prototype indexed a few hundred thousand embeddings, queries came back in single-digit milliseconds, and everyone moved on. Then the corpus grew — refinery maintenance manuals, upstream drilling reports, municipal permit records, or an e-discovery set — and the same index started losing recall, or the build ran for hours, or the box ran out of RAM.

Scale changes the rules. The knobs that matter at ten million vectors are not the ones that matter at a hundred thousand. Here is the production playbook, with the arithmetic behind each decision.

Do the storage math first

A vector(1536) — the shape most hosted embedding models return — costs 4 × dimensions + 8 bytes, or 6,152 bytes per row. Ten million chunks is roughly 61 GB of vector payload before you add an HNSW graph on top. An HNSW index stores graph links in addition to the payload, so budget for the index being comparable in size to the column it indexes (pgvector README).

If that number exceeds your instance RAM, you are not tuning queries — you are tuning around random I/O. Two moves fix it: store less, and build the index deliberately.

Step 1 — Store less: halfvec and binary quantization

halfvec uses half-precision storage at 2 × dimensions + 8 bytes, so those ten million rows drop from about 61 GB to about 31 GB. Binary quantization goes much further — dimensions / 8 + 8 bytes, roughly 2 GB for 1536-dimension embeddings — but it keeps only the sign of each dimension, so you re-rank the candidates.

-- Half precision: same recall profile, half the working set
CREATE TABLE chunks (
    id        bigserial PRIMARY KEY,
    tenant_id int  NOT NULL,
    doc_id    bigint NOT NULL,
    embedding halfvec(1536)
);

CREATE INDEX chunks_embedding_idx ON chunks
    USING hnsw (embedding halfvec_cosine_ops);

Know the indexing limits before you pick a model: vector indexes up to 2,000 dimensions, halfvec up to 4,000, and bit up to 64,000. A 3,072-dimension embedding model therefore cannot be indexed as vector at all — half-precision indexing is the minimum viable path.

Step 2 — Treat the index build as an ops event

HNSW builds get dramatically faster when the graph fits in maintenance_work_mem, and Postgres notices when it no longer does: you get a NOTICE: hnsw graph no longer fits into maintenance_work_mem after N tuples along with a hint to raise it. Builds also parallelize — max_parallel_maintenance_workers defaults to 2.

SET maintenance_work_mem = '16GB';         -- room for the graph, not the table
SET max_parallel_maintenance_workers = 7;  -- 2 by default
SET max_parallel_workers = 8;              -- must cover workers + leader

CREATE INDEX CONCURRENTLY chunks_embedding_idx ON chunks
    USING hnsw (embedding halfvec_cosine_ops)
    WITH (m = 16, ef_construction = 64);   -- pgvector defaults

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

Raise ef_construction only when you have measured a recall gap — it costs build time and insert throughput. Leave m at 16 unless you have evidence the graph is too sparse (progress reporting docs).

Step 3 — Tune recall per query, not per cluster

HNSW searches a dynamic candidate list of 40 entries by default (hnsw.ef_search), and IVFFlat probes a single list by default (ivfflat.probes = 1). Those defaults are conservative. Set them per query with SET LOCAL so a recall-sensitive endpoint does not slow down the chatty one.

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id, 1 - (embedding <=> $1::halfvec) AS score
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::halfvec
LIMIT 10;
COMMIT;

Step 4 — Understand the filtered-search trap

With approximate indexes, filters are applied after the index scan. If a WHERE clause matches 10% of rows and ef_search is 40, expect roughly four matching rows back — not ten. This is the most common source of “vector search lost my document” bug reports, and it is not a bug.

Since pgvector 0.8.0, iterative index scans fix it by scanning more of the graph until enough rows satisfy the filter, bounded by hnsw.max_scan_tuples (20,000 by default) and hnsw.scan_mem_multiplier. Relaxed ordering buys recall; use a materialized CTE when you need exact distance order.

SET hnsw.iterative_scan = relaxed_order;   -- more recall, slight reordering

WITH candidates AS MATERIALIZED (
    SELECT id, embedding <=> $1::halfvec AS distance
    FROM chunks
    WHERE tenant_id = $2
    ORDER BY distance LIMIT 10
)
SELECT * FROM candidates ORDER BY distance + 0;  -- "+ 0" needed on PG 17+

For multi-tenant systems, note that sharing one approximate index means one tenant’s vectors influence recall for everyone. List partitioning by tenant — or separate tables for the largest tenants — keeps recall and latency predictable (partitioning docs).

Step 5 — Binary quantization with re-ranking

When the index simply will not fit in RAM, quantize the index and re-rank against full-precision vectors. Overlap the candidate list three to four times the final limit, then sort exactly.

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

SELECT * FROM (
    SELECT id, embedding
    FROM chunks
    ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
    LIMIT 40                     -- 4x the final limit
) ORDER BY embedding <=> $1 LIMIT 10;

Where this fits in Houston

• Energy and upstream: two decades of drilling and completion reports in hundreds of thousands of PDFs, with engineers asking what was done in analogous wells. Half-precision vectors keep the working set on one instance.
• Legal and e-discovery: document sets run to millions of pages, and semantic search over them has to be defensible. AWS published a case study on CORTO running billion-scale legal semantic search on Aurora PostgreSQL with pgvector — the storage and quantization arithmetic above is exactly the problem that architecture solves (AWS Database Blog).
• Industrial maintenance: refinery and petrochemical contractors sitting on decades of OEM manuals, where a confidently wrong answer is expensive — keep ef_search high and always re-rank.

What to measure in production

Recall@k against a labeled sample rather than intuition, p95 latency at that recall, index size versus shared_buffers, cache hit ratio, and build time for your largest table. If p95 latency is still the binding constraint after quantization, the DiskANN-based pgvectorscale is the next step, and if you are still choosing index types, start with the pgvector setup and index guide.

Bottom line: at ten million vectors and beyond, the winning move is almost never a bigger instance. It is storing less (halfvec, binary quantization), building the index with memory in mind, and measuring recall instead of assuming it.