pgvector: Postgres for AI

This cover frames the closing post around a hard truth: pgvector's failure modes are quiet, not loud. A query that returns plausible-looking results can still be scanning every row, comparing incompatible vectors, or silently ignoring your carefully built index. Nothing throws an exception.

The post is a field guide to these traps. Each one is a predictable misuse rather than a bug in the extension, which means each one is preventable once you know what to look for.

The first and most damaging trap is the silent full scan. When no index can serve your query, Postgres falls back to scanning every vector and computing every distance. The results are perfectly correct, so functional tests pass and nothing looks wrong — until the table grows and latency quietly climbs into seconds.

Because there is no error, the only reliable defense is to verify the query plan rather than trusting that an index you created is actually being used. Assume nothing about indexing until EXPLAIN confirms it.

This is the diagnostic habit that catches most pgvector performance problems. Running `EXPLAIN ANALYZE` on your similarity query shows exactly how Postgres executes it. You want to see an index scan naming your HNSW or IVFFlat index; seeing a `Seq Scan on docs` means the index is being ignored and you are paying full-scan cost.

Make this check part of your workflow whenever you add or change a vector query. It takes seconds and turns the silent full scan from a production mystery into a one-line confirmation before you ship.

The second trap is the operator-class and metric mismatch, and it is the single most common reason an index is ignored. An index built with `vector_l2_ops` is structured for Euclidean distance and simply cannot serve a query that uses the cosine operator `<=>`. Postgres does not error; it falls back to a sequential scan.

The rule is exact pairing: `vector_cosine_ops` with `<=>`, `vector_l2_ops` with `<->`, and `vector_ip_ops` with `<#>`. Whenever someone files a 'why is my index not used?' ticket, this mismatch is the first hypothesis to test, ahead of everything else.

The third trap is mixing embedding models. Vectors are only comparable if they come from the same model, because each model defines its own coordinate space. Even when two models output the same number of dimensions — say 1536 — the spaces are unrelated, so distances computed across them are meaningless noise.

The defense is to record which model produced each vector and to treat each model's vectors as a separate, non-comparable population. When you upgrade or change models, you must re-embed the affected data rather than letting old and new vectors coexist in the same ranking.

This snippet operationalizes the previous slide. Adding an `embed_model` column tags every row with the model that produced its vector, and constraining queries with `WHERE embed_model = '...'` guarantees you only ever compare vectors from a single space.

This small piece of bookkeeping also makes model migrations safe and incremental: you can insert new-model vectors alongside the old ones, query each space independently, and cut over once the re-embedding completes — without a window where mismatched vectors corrupt your results.

The fourth trap is using the inner-product operator `<#>` on vectors that are not normalized. Inner product only coincides with cosine similarity when all vectors have unit length. If magnitudes vary, longer vectors score higher regardless of their direction, so a document can rank well simply for being 'bigger,' not more relevant.

You have two clean options: normalize vectors to unit length before storing them and then use inner product safely, or sidestep the issue entirely by using cosine distance `<=>`, which normalizes internally. For most teams the second option is simpler and removes a whole category of subtle ranking bugs.

The fifth trap is filtering in a way that defeats the ANN index. A very selective WHERE clause on a column the vector index does not cover can lead the planner to apply the filter first and then perform an exact scan over the survivors, bypassing the approximate index entirely. Sometimes that is even the right plan, but it is rarely what you intended for large tables.

The remedies are to use partial indexes scoped to common filter values, or to design the schema so the filter and the vector search can both be served efficiently. The key is to test the actual plan with representative filters, because the planner's choice depends on selectivity.

This bar chart makes the recall-versus-speed trade tangible for HNSW. At `ef_search = 10` queries are fast but recall is lower; at 40 you get a balanced operating point; at 100 recall climbs toward the high nineties but each query costs more. The exact numbers depend on your data, but the shape is universal.

The mistake this guards against is tuning purely for speed and silently accepting poor recall — returning neighbors that are fast to find but not actually the closest. Always measure recall against an exact baseline when you tune, so you know what accuracy you are trading away.

The sixth trap is forgetting to run ANALYZE after large data changes. The query planner relies on table statistics to estimate costs, and after a bulk load or a fresh index build those statistics can be badly stale. With wrong row-count estimates the planner may misjudge costs and skip the vector index entirely.

The fix is trivial and worth making routine: run `ANALYZE docs;` after any significant load or structural change. It refreshes the statistics so the planner sees reality and reliably chooses the index path you built for it.

This checklist condenses the entire post into a pre-flight you can run before trusting any pgvector query in production. Confirm via EXPLAIN that an index scan is used rather than a sequential scan. Verify the index operator class matches the query operator. Ensure each query compares a single embedding model's space.

Normalize vectors if you use inner product, run ANALYZE after big loads, and tune `ef_search` or `probes` to hit your target recall rather than just chasing speed. Running through these six checks catches essentially every common pgvector failure before users ever see it.

That closes the pitfalls post and the pgvector set. The recurring lesson is that pgvector fails quietly, so verification — of the plan, the operator, the model, and the statistics — is the discipline that keeps it reliable.

The next day continues the Vector Databases track with a fresh topic. Keep the streak going, and carry the habit of checking EXPLAIN with you into whatever store you use next.