pgvector: Postgres for AI

This cover sets up the mechanical heart of pgvector: indexing. A `LIMIT 5` similarity query reads as harmless, but its performance is entirely determined by what happens beneath it. On a small table, a naive scan is fine. On a million rows, the same query without the right index becomes a latency disaster.

The post pulls back the cover on how nearest-neighbor search is actually executed, so that your observed latency numbers become explainable and tunable rather than mysterious.

Without any index, pgvector performs exact search. It literally computes the distance from your query vector to every row in the table, then sorts to find the smallest values. The answer is guaranteed correct — these are truly the closest neighbors.

The catch is cost. The work scales linearly with the number of rows, so a query that is instant on ten thousand vectors becomes sluggish on ten million. Exact search is the right tool for small tables and for cases where perfect recall is non-negotiable, but it does not scale on its own.

Approximate nearest neighbor (ANN) indexes make a deliberate trade. They abandon the guarantee of always returning the exact top-k in exchange for examining only a small fraction of the vectors. Occasionally a true neighbor is missed and a slightly-less-close one takes its place.

For semantic search this trade is almost always worth it. The 'meaning' of relevance is already fuzzy, so missing one borderline result among the top five is invisible to users, while the latency improvement is often two or three orders of magnitude. Recall in the high-90s percent is typical and entirely acceptable.

This comparison crystallizes the choice. Exact search with no index gives perfect recall at O(n) cost per query and is excellent below roughly fifty thousand rows. Approximate search with an ANN index visits only a subset of vectors, delivers recall in the ninety-five to ninety-nine percent range, runs in sub-linear time, and scales to millions of rows.

The threshold is fuzzy and depends on your hardware and latency budget, but the shape of the decision is stable: small and accuracy-critical favors exact; large and latency-sensitive favors ANN.

IVFFlat is the first ANN index pgvector offered, and it works by partitioning. At build time it runs a clustering step that groups vectors into a configured number of `lists`, each represented by a centroid. At query time it identifies the centroids nearest the query and then searches only within the closest `probes` of those clusters.

The critical operational detail is that IVFFlat must be built after representative data is loaded, because the clusters are derived from the actual vectors. Build it on an empty table and the partitioning is meaningless. The `probes` setting is your recall dial: more probes search more clusters, raising recall at the cost of speed.

This snippet shows the two halves of IVFFlat tuning. At creation time, `lists = 100` sets how many clusters to form — a common rule of thumb is roughly the square root of the row count, adjusted by experiment. The `vector_cosine_ops` operator class ties the index to cosine distance.

At query time, `SET ivfflat.probes = 10;` controls how many clusters each query inspects. Probes of 1 is fastest but may miss neighbors near cluster boundaries; raising it trades latency for recall. The comment that data must be loaded first cannot be overstated — it is the number-one IVFFlat mistake.

HNSW (Hierarchical Navigable Small World) is the more modern default. Instead of clusters, it builds a layered graph: every vector is a node connected to its near neighbors, with sparse long-range links in the upper layers and dense local links at the bottom. A search enters at the top, greedily hops toward the query, and descends layer by layer to refine.

HNSW does not require pre-loaded data, generally delivers higher recall at a given speed than IVFFlat, and degrades gracefully as you insert more rows. The costs are higher memory usage and slower index builds, which for most workloads are an acceptable price for its robustness.

This snippet shows HNSW's build-time parameters. `m = 16` sets how many neighbor links each node keeps — higher m improves recall and graph connectivity but increases memory and build time. `ef_construction = 64` controls how hard the builder works to find good neighbors during construction; higher values build a better graph more slowly.

Separately, `SET hnsw.ef_search = 40;` is the per-query recall dial. It governs how many candidates the search keeps in its working set as it traverses the graph. Raising it improves recall at the cost of query latency, and it can be tuned per query without rebuilding the index.

This flow diagram illustrates the layered descent that makes HNSW fast. The search begins in a sparse top layer where a few long hops cover huge distances cheaply, moves to denser middle layers to refine the region, and finishes in the fully connected base layer doing local search to pin down the actual nearest neighbors.

The layering is what gives HNSW its sub-linear behavior: most of the search space is eliminated in the first couple of hops, so only a tiny neighborhood is examined in detail. Visualizing this makes the `ef_search` knob intuitive — it widens how thorough that final local search is.

These bullets gather the knobs that actually move the needle. IVFFlat is tuned with `lists` at build time and `probes` per query. HNSW is tuned with `m` and `ef_construction` at build time and `ef_search` per query. Across all of them, the universal trade is that higher recall costs query speed.

The last bullet is a correctness constraint rather than a tuning knob: the index's operator class must match the distance metric used in your queries, or the index simply will not be used. Keeping these distinctions clear lets you tune deliberately instead of by trial and error.

This mistake is subtle because it produces no error. If you build an index with `vector_l2_ops` but query with the cosine operator `<=>`, Postgres cannot use that index and silently falls back to a sequential scan. Your results are still correct, so nothing looks broken — until you check the latency or the query plan.

The fix is mechanical discipline: pair `vector_cosine_ops` with `<=>`, `vector_l2_ops` with `<->`, and `vector_ip_ops` with `<#>`. Whenever an index seems to be 'ignored,' a metric mismatch is the first thing to check.

That closes the mechanics post. You now understand exact versus approximate search, how IVFFlat clusters and probes, how HNSW navigates a graph, and which knobs trade recall for speed.

The next post is the hands-on build: a complete end-to-end example covering schema, embedding text in Python, inserting vectors, building the index, and running a filtered similarity query you can copy and adapt.