Vector Search with pgvector

A support desk keeps every ticket in Postgres. When an agent opens ticket 8821, the product wants the five most similar tickets this tenant ever filed sitting next to the reply box. Similarity means cosine distance over 1536-float embeddings, the vectors introduced in Cosine Similarity.

Before pgvector, that request is not one query. The embedding lives in a jsonb column Postgres cannot rank, so the application pulls the tenant's rows, parses them, scores in Python, sorts, and keeps five:

rows = db.execute(
    "SELECT id, embedding FROM tickets WHERE tenant_id = %s", (tenant,)
)  # 40k rows, ~250 MB of JSON parsed on every ticket open
scored = sorted(
    ((cosine(q, json.loads(r["embedding"])), r["id"]) for r in rows),
    reverse=True,
)[:5]

Two failures hide in those six lines. Latency scales with tenant history instead of with k, and every other predicate — status, priority, assignee — now lives in a second statement executing against a result set that can drift from the first.

pgvector makes the vector a value the planner itself can rank:

CREATE EXTENSION IF NOT EXISTS vector;   -- once per database

CREATE TABLE tickets (
  id         bigserial PRIMARY KEY,
  tenant_id  uuid         NOT NULL,
  status     text         NOT NULL,
  body       text         NOT NULL,
  embedding  vector(1536)                 -- length enforced at INSERT
);

INSERT INTO tickets (tenant_id, status, body, embedding)
VALUES ('9c1f5f2e-2b7a-4c11-9f0e-41c6d0b1a7e5', 'open', 'Refund never arrived',
        '[0.012,-0.441,0.088,0.317,...]'::vector);

Input — the agent opens ticket 8821 and $1 carries its query vector:

SELECT id, body, 1 - (embedding <=> $1) AS score
FROM   tickets
WHERE  tenant_id = '9c1f5f2e-2b7a-4c11-9f0e-41c6d0b1a7e5'
ORDER  BY embedding <=> $1
LIMIT  5;

Output

 id |            body             | score
----+-----------------------------+-------
812 | Charged twice for one order | 0.873
903 | Duplicate invoice line item | 0.861
774 | Payment went through twice  | 0.844
(3 rows)

<=> returns cosine distance, which runs from 0 (identical) to 2 (opposite), so 1 - distance in the select list is cosine similarity. The operator you pick is a claim about how the embedding model was trained, not a stylistic preference:

One line of DDL does quiet work: vector(1536) rejects a wrong-length vector at insert time instead of letting it surface later as a mysterious zero-score row. The dimension follows the embedding model, so read Embedding Models before baking it into a migration.

The query above is linear over the tenant's rows: a sequential scan reads every embedding page, scores each vector, and keeps five. Up to a few hundred thousand rows that finishes inside shared buffers and nobody notices. Past roughly a million rows the scan dominates response time and an index becomes mandatory. An approximate index introduces a wrinkle a b-tree never has — two different kinds of predicate sharing one plan.

   no ANN index                         with an ANN index

 seq scan 2.0M rows                  graph walk: read ~4k
 cosine against every row            candidate vectors
 keep a top-5 heap                   then apply WHERE
        |                                    |
        v                                    v
   ~250 ms, exact                  ~6 ms, approximate
                                          |
                             window can run dry here

The index answers nearest; WHERE answers allowed. pgvector examines a bounded candidate window — ef_search neighbors for HNSW, lists / probe clusters for IVFFlat — and applies your filter to those candidates only. Make the filter selective (one tenant out of 4,000, or status = 'open' on a table where most tickets are closed) and the window can empty before LIMIT 5 is satisfied. You get three rows back, no error, and a bug report three weeks later saying search is flaky.

pgvector 0.8 and later can keep scanning instead of stopping:

SET hnsw.iterative_scan = relaxed_order;   -- off (default) | relaxed_order | strict_order
SET hnsw.max_scan_tuples = 5000;           -- ceiling on rows the scan may touch

relaxed_order admits a small chance that a result just past the window outranks one inside it; strict_order preserves exact ordering and scans longer. Either way the missing-rows failure disappears, bounded by max_scan_tuples so a pathological filter cannot scan the table.

Then the index itself. Both options below rank identically; they differ in how they avoid touching every row:

-- IVFFlat: partition the space into lists, probe the closest few
CREATE INDEX tickets_embedding_ivf
  ON tickets USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);              -- rule of thumb: rows / 1000 up to ~1M rows

-- HNSW: a proximity graph navigated in layers
CREATE INDEX tickets_embedding_hnsw
  ON tickets USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

SET ivfflat.probe = 25;             -- clusters touched per query (default 1)
SET hnsw.ef_search = 100;           -- candidate beam width (default 40)

The vector_cosine_ops suffix decides which operator the index can serve. Order by <-> against a cosine index and Postgres does not raise an error — it falls back to the sequential scan you built the index to escape. EXPLAIN (ANALYZE, BUFFERS) is the only reliable way to catch the mismatch, which is why it belongs in the definition of done for this migration. The full recall arithmetic behind probe and ef_search lives in Approximate Nearest Neighbor Indexes.

An index build is the one pgvector operation that can take down the primary. HNSW holds the growing graph in memory while it links nodes; IVFFlat runs k-means across every vector. Both charge against maintenance_work_mem, whose 64 MB default is sized for b-tree pages, not for a twelve-million-row graph.

SHOW maintenance_work_mem;            -- 64MB out of the box
SET  maintenance_work_mem = '4GB';    -- session-local; set it in postgresql.conf
                                      -- because background builds ignore your session

What actually breaks, in the order teams hit it:

• Built on an empty table. IVFFlat trains its centroids on the rows present at build time. Load data first, build second, and REINDEX after any backfill that shifts the distribution.
• Index exists, plan says Seq Scan. Operator and op class disagree, or the column is wrapped in an expression. One EXPLAIN (ANALYZE) line settles it.
• LIMIT 10 returns 6 rows. The candidate window ran dry under a selective filter; iterative scan fixes it.
• The build OOMs a 40 GB host. maintenance_work_mem is too small for HNSW at this row count: raise it, build on a replica, or accept IVFFlat's cheaper build.
• An index at 200k rows. Below roughly a million rows the sequential scan is already single-digit milliseconds, and the index adds write amplification to every upsert while buying nothing. Measure before you build.

When the operational side of running this outgrows a database you already operate, Managed Vectors with Pinecone is the next topic in this phase.