Below a few million chunks, keep your vectors in Postgres

Under a few million chunks, vector search fits in the Postgres you already run, and one table of recall against latency at three HNSW settings shows the headroom.

Source rows Chunker Embedding call Chunks table HNSW index Filtered query Recall check Context approximate vs exact scan vector + tenant id WHERE tenant ORDER BY distance retrieved
Source rows are chunked and embedded into one table, searched through an HNSW index under a tenant filter, then checked for recall.
On this page
  1. What 1.2 million chunks weigh
  2. One table, partitioned by client
  3. Where each filter runs
  4. Three settings against an exact scan
  5. What would send me shopping

At 10:02 a partner takes an associate off a matter. At 10:03 the associate searches for indemnity caps, and the third result is a clause from that matter.

The vector index did nothing wrong. Those were the ten nearest chunks. But the record of who may see which matter lives in Postgres, and the vector store’s copy of it syncs every fifteen minutes.

That’s the failure I’d design against first, and it has very little to do with nearest-neighbor search. Below a few million chunks, the index is the easy part. What takes the work is applying tenant and permission filters, and joining to rows that changed a minute ago, all inside one query. The relational database you already run does both. So before buying a vector database, I’d add pgvector to that Postgres and fill in one small table of measurements.

What 1.2 million chunks weigh

That associate works at Mossback Legal, a firm I invented for this sizing exercise, and the numbers I give for it are as invented as the firm.

Mossback reviews contracts for 34 corporate clients. It holds 310,000 clauses, split at their numbered sub-paragraphs into 1.2 million chunks. Each chunk is embedded with OpenAI’s text-embedding-ada-002, which returns 1,536 floats.

Raw vector size is chunks times dimensions times four bytes. For Mossback that’s about 7.4 GB. The HNSW index keeps its own copy of every vector, plus the graph’s links. A 1,536-dimension vector is 6 kB and an index page is 8 kB, so I’d plan on a page per chunk, which comes to nearly 10 GB. The index is what has to stay in memory, and a 32 GB instance holds it with room for whatever the database was doing before.

Two limits are worth knowing early. pgvector won’t index a vector wider than 2,000 dimensions, so 1,536 fits. And the current release, 0.5.1, builds the graph in a single process, so I’d give the first build an evening.

The arithmetic is linear. Five million chunks at the same width is 31 GB of vectors and about 41 GB of index, and somewhere around there I’d stop and price the alternatives. A 384-dimension sentence-transformers model cuts those numbers to about a quarter. It’ll cost something in retrieval quality, and how much is a thing to measure on your own documents.

One table, partitioned by client

The first half of the diagram is plumbing. Rows are chunked, and each chunk comes back from the embedding call as a vector for this table.

CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector 0.5.0 or later, for HNSW

-- Already in this database: matters and clauses, plus
-- matter_permissions (user_id, matter_id), keyed on both columns.

CREATE TABLE clause_chunks (
    tenant_id  int          NOT NULL,  -- the client
    matter_id  bigint       NOT NULL REFERENCES matters (matter_id),
    clause_id  bigint       NOT NULL REFERENCES clauses (clause_id) ON DELETE CASCADE,
    chunk_no   int          NOT NULL,
    body       text         NOT NULL,
    body_tsv   tsvector     GENERATED ALWAYS AS (to_tsvector('english', body)) STORED,
    embedding  vector(1536) NOT NULL,  -- text-embedding-ada-002
    PRIMARY KEY (tenant_id, clause_id, chunk_no)
) PARTITION BY LIST (tenant_id);

-- One partition per client, added when the client is onboarded.
CREATE TABLE clause_chunks_t17 PARTITION OF clause_chunks FOR VALUES IN (17);

-- Declared once on the parent; every partition gets its own graph.
-- These are pgvector's defaults, written out so they're on record.
CREATE INDEX clause_chunks_embedding_hnsw ON clause_chunks
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX clause_chunks_matter   ON clause_chunks (matter_id);
CREATE INDEX clause_chunks_body_tsv ON clause_chunks USING gin (body_tsv);

m and ef_construction are fixed when the index is built, and changing them means a rebuild. I’d leave both at their defaults until the measurements say otherwise, because the query-time setting is far cheaper to turn.

The body_tsv column is for the searches embeddings are bad at, like “clause 14.2(b)” or a party’s exact name. It’s ordinary Postgres full-text search beside the vector column, behind the same WHERE.

Where each filter runs

-- $1 = query embedding, $2 = tenant, $3 = user
BEGIN;
SET LOCAL hnsw.ef_search = 100;  -- the setting under test

SELECT c.clause_id, c.chunk_no, m.matter_name, c.body,
       c.embedding <=> $1 AS distance
FROM clause_chunks c
JOIN matter_permissions p ON p.matter_id = c.matter_id AND p.user_id = $3
JOIN matters m            ON m.matter_id = c.matter_id AND m.status = 'open'
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $1
LIMIT 10;

COMMIT;
-- Run it under EXPLAIN and look for the hnsw index scan beneath both joins.

-- Exact baseline for the recall check: same SELECT, index scans off.
BEGIN;
SET LOCAL enable_indexscan = off;
-- the SELECT above, unchanged
COMMIT;

The tenant filter is partition pruning. Each client’s partition has its own graph, so another client’s vectors never become candidates.

The permission join and the open-matter check run after the index scan. The scan hands back at most hnsw.ef_search candidates in distance order, 40 by default. Postgres walks down that list, skipping the ones this user can’t see, until it has ten. A partner who’s on every matter loses nothing. An associate on a fifth of the client’s matters keeps about eight of the 40, so she asks for ten rows and gets eight.

That gives ef_search two jobs. It sets how hard the graph is searched, and it sets how many candidates are left to filter. It’s the setting I’d measure.

Both joins read live rows. Take a lawyer off a matter at 10:02 and her 10:03 search can’t return it. A separate vector store would have needed its own copy of matter_permissions, and a job to keep the copy current.

Three settings against an exact scan

Mossback pulls 200 searches from its logs, each with the user who ran it. With index scans switched off, the same statement returns the true top ten, slowly. Then it runs at three ef_search values. Recall at 10 is the share of the true ten that came back.

hnsw.ef_search Recall at 10 p95 latency Short of 10 rows, out of 200 searches
Exact scan, index off 1.00 1,400 ms 0
40 (default) 0.91 24 ms 11
100 0.96 38 ms 2
200 0.98 61 ms 0

At 100 the query finds 96% of the true neighbors with a p95 of 38 ms. Doubling the setting buys two more points of recall for 23 ms and clears the short results. Mossback’s allowance for retrieval is 150 ms, inside a RAG request that then waits a second or more on a language model. So it runs at 200 with more than half the allowance unspent. That unspent part is the headroom, and now it has a number on it.

The four rows are the template, and the numbers in them should be yours. I’d refill them whenever the chunk count doubles, and after any change to the chunker or the embedding model.

What would send me shopping

The index outgrowing the memory I’m willing to pay for is one reading. Five million chunks at 1,536 dimensions gets there.

The table is the other. If its last row misses the recall target, I’d rebuild one partition with a higher m and ef_construction and measure again. If it still misses, or only gets there outside the latency allowance, Postgres has run out of room for this workload.

A user who can see almost nothing isn’t on that list. Her permitted chunks are few enough to fetch through the matter_id index and rank by exact distance, and exact is as good as recall gets.

If it does come to a vendor call, the table comes with me. It says what recall and latency I already get from a database I was paying for anyway, and that’s the number they’d have to beat.