- Book: AI That Reads
- The series: AI in TypeScript — 5 books, from your first LLM call to agents in production — all five here
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
pgvector is the right default for most teams already running
Postgres. One database, one backup story, joins between your vectors
and your business tables. The demo takes an afternoon.
The trouble arrives later, and it arrives from the Node side more
often than from Postgres. Three things in particular: how the driver
sends your embedding, whether an index exists and which one, and what
a slow vector query does to a connection pool sized for fast queries.
Sending the vector
The first surprise is that pg does not know what a vector is.
const embedding: number[] = await embed(query);
const { rows } = await pool.query(
"SELECT id, text FROM chunks ORDER BY embedding <=> $1 LIMIT 10",
[embedding],
);
That fails. pg serialises a JavaScript array as a Postgres array
literal — {0.1,0.2,...} — and the vector type wants
[0.1,0.2,...]. Braces versus brackets.
Two ways out. Format it yourself:
const literal = `[${embedding.join(",")}]`;
await pool.query(sql, [literal]);
Or register the type so it happens once, centrally:
import pgvector from "pgvector/pg";
await pgvector.registerTypes(client);
Prefer the registration. Hand-formatting works until someone adds a
second query and forgets, and the error message — a type mismatch
deep in the driver — does not point at the missing brackets.
If you are on Drizzle or Prisma, check how their vector column type
serialises before assuming. This is the layer where an ORM either
handles it for you or hands you a string to build, and the two look
identical at the call site.
Dimension mismatches fail late
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
text text NOT NULL,
embedding vector(1536) NOT NULL
);
The column is dimensioned. Switch embedding models — a smaller
variant, a different provider, a newer version — and every insert
fails with a dimension error, at write time, in a background ingest
job, at 2am.
Encode the model in the schema so the failure is a constraint
violation you expect rather than a mystery:
ALTER TABLE chunks ADD COLUMN embed_model text NOT NULL;
CREATE UNIQUE INDEX ON chunks (content_hash, embed_model);
Now two models coexist during a migration, and a query filtered to
one model cannot accidentally compare vectors from another. Comparing
across models produces distances that are arithmetic nonsense — and
no error at all.
The index is the whole performance story
Without an index, Postgres does an exact scan: every row, every
query. At ten thousand rows that is a few milliseconds and nobody
notices. At a million it is a sequential scan over gigabytes, and the
same query that felt instant in staging takes seconds.
The trap is that it is correct the whole time. Exact search returns
the true nearest neighbours. There is no error, no warning, just a
number that grows with your table.
CREATE INDEX ON chunks
USING hnsw (embedding vector_cosine_ops);
Two index types, and the choice is a real one.
HNSW builds a graph. Better recall at a given speed, holds up under
insert-heavy workloads, no training step. Costs more memory and takes
longer to build.
IVFFlat partitions into lists and searches the nearest few.
Faster to build, smaller. Its catch is that it must be built on
representative data — build it on an empty table and the partitions
are meaningless, and you get poor recall with no indication anything
is wrong.
Default to HNSW unless build time or memory rules it out.
The operator class must match your query operator. vector_cosine_ops
goes with <=>, vector_l2_ops with <->, vector_ip_ops with
<#>. Mismatch them and the index is not used at all — the query stays
correct and gets slow, which is the hardest kind of slow to spot.
EXPLAIN ANALYZE
SELECT id FROM chunks ORDER BY embedding <=> $1 LIMIT 10;
If that says Seq Scan, your index is not being used. Check the
operator, and check that your ORDER BY uses the same expression the
index was built on.
Recall is a dial, and it has a Node-side default
Approximate indexes trade recall for speed, and the trade is
configurable per session:
await client.query("SET LOCAL hnsw.ef_search = 100");
Higher searches more of the graph: better recall, slower. The default
is tuned for speed.
SET LOCAL scopes it to the transaction, which matters when you are
on a pool. SET without LOCAL changes the session — and a pooled
connection is reused, so you have silently changed the setting for
whatever handler picks up that connection next. This is a genuinely
confusing bug: recall varies by request depending on which pooled
connection you drew.
Either use SET LOCAL inside an explicit transaction, or set it once
on connection creation:
const pool = new Pool({
max: 10,
options: "-c hnsw.ef_search=100",
});
Pool starvation
Here is where it stops being a database problem.
Your pool is sized for the queries you had before — primary key
lookups measured in single-digit milliseconds. Ten connections is
plenty when each is held for two milliseconds.
A vector query over a large table with a high ef_search might hold
a connection for two hundred milliseconds. Ten connections at two
hundred milliseconds each is fifty queries per second, total, for the
whole service. Request eleven waits for a connection. Under load the
queue grows, and what you see in monitoring is timeouts on every
endpoint, including the ones that never touch a vector.
The symptom points at the wrong place. Your /health check times
out, so it looks like the database is down. Postgres is fine and
mostly idle. You have run out of connections.
Three things that help:
const search = new Pool({
max: 20,
connectionTimeoutMillis: 2_000,
statement_timeout: 5_000,
});
A separate pool for vector queries, so a slow search cannot
starve your CRUD paths. connectionTimeoutMillis so a caller waiting
for a connection fails fast with a clear error instead of hanging.
And statement_timeout so a pathological query is killed rather than
holding its connection until something else gives up.
If you sit behind PgBouncer, transaction pooling and SET LOCAL
interact in ways worth checking before you rely on per-query tuning.
Filter before you search, when you can
The common shape is search within a tenant or a document set.
SELECT id, text FROM chunks
WHERE tenant_id = $2 AND embed_model = $3
ORDER BY embedding <=> $1
LIMIT 10;
An approximate index plus a restrictive filter can return fewer rows
than your LIMIT, because the index traversal visits candidates that
the filter then discards. You asked for ten and got four, with no
error.
Partial indexes per tenant work when tenants are few and large.
Over-fetching then filtering in Node works when they are many and
small. Measure which shape you have before choosing — this is the
decision that most often gets made once, early, on the wrong
assumption.
The three checks
Run EXPLAIN ANALYZE on your real query at production table size and
confirm it is not a sequential scan. Watch pool wait time as a metric
in its own right, separately from query duration. And re-run recall
against a small labelled set whenever you change ef_search, the
index, or the embedding model — all three move it, and none of them
tell you.
If this was useful
AI That Reads covers
retrieval from the Node side end to end — chunking, embedding,
storage, index choice, and the operational details that only appear
once the table is large.
The full series, from first LLM call through agents in production, is
at xgabriel.com/ai-in-typescript.
Top comments (0)