Skip to content
AI Engineer Guide.

concept · data · intermediate · concept 12 of 17

Postgres & pgvector

0 of 17 complete

Why it matters

Most RAG systems do not start with a vector database problem. They start with a Postgres database that already holds tenants, documents, versions and permissions, and a requirement to search a few hundred thousand chunks. Adding a second datastore means a second consistency boundary: a document can be deleted in Postgres while its vectors live on, or a permission change can take minutes to reach the search path. Both failures are invisible in a demo and expensive in production.

pgvector keeps the embedding in a column. The retrieval query becomes an ordinary SQL query with a join to the permission table, and a delete is a delete. For a large class of products that is worth more than the last few points of recall per millisecond.

Mental model

One library, two catalogues. The relational tables are the borrower register — who may read what, which edition is current. The vector index is the subject catalogue — which books are about the same thing. Because both live in the same building, a book cannot be removed from one catalogue and left in the other.

The analogy breaks at consistency of relevance: the subject catalogue is approximate by design. Its answer depends on how deeply you search (ef_search, probes), so two runs of the same query can return slightly different neighbours. Nothing in the relational world behaves that way, and it is the source of most surprises.

How it works

Mechanism, step by stepInteractive
  1. 1Types and operators. vector(n) stores n single-precision floats; halfvec, bit and sparsevec cover other storage needs. Distance operators pair with them: for Euclidean, for cosine, for negative inner product, for L1. The operator you write must match the index's operator class, or the index will not be used.
  2. 2Exact search is the default. With no index, ORDER BY embedding $1 LIMIT k is a sequential scan that scores every row. It is exact and predictable. For small tables that is the correct choice; an approximate index mainly adds build cost and recall risk.
  3. 3HNSW is a layered proximity graph. Each vector is a node with a small set of neighbours; the top layers are sparse for fast traversal and the bottom layer is dense. A query descends greedily, then explores the bottom layer with a candidate list controlled by efsearch. Build quality is controlled by m (neighbours per node) and efconstruction. The graph lives in memory, which is why HNSW is fast and why it is memory-hungry.
  4. 4IVFFlat partitions vectors into lists. At build time it runs k-means over the existing data and assigns each vector to a cell. A query probes the nearest ivfflat.probes cells. The index builds faster and uses less memory than HNSW, but it needs representative data present before the build, and recall depends on how many lists you probe.
  5. 5The planner only uses the index for a matching shape. You need the distance operator in ORDER BY, a LIMIT, and an operator class matching the column. Anything else — a cast, an expression, a different metric — silently becomes an exact scan.
  6. 6Filtering is the hard part. An approximate index returns the top k neighbours overall, then the WHERE clause removes some, so you can get fewer rows than requested, or miss relevant rows that sit just outside the unfiltered top k. Three mitigations: raise k and filter afterwards, use a partial index or partition per tenant, or enable iterative scans so the index keeps producing candidates until the limit is met.

Step 1 of 6

Types and operators. vector(n) stores n single-precision floats; halfvec, bit and sparsevec cover other storage needs. Distance operators pair with them: for Euclidean, for cosine, for negative inner product, for L1. The operator you write must match the index's operator class, or the index will not be used.

Step 1/6 · step 1

Figure. The mechanism in this page's own steps. The figure adds sequence, not new claims.

What to notice. A step can be read alone, but the order is the mechanism. The control lets you stop on any one.

Text version

The page's steps: 1. Types and operators. vector(n) stores n single-precision floats; halfvec, bit and sparsev… 2. Exact search is the default. With no index, ORDER BY embedding $1 LIMIT k is a sequential… 3. HNSW is a layered proximity graph. Each vector is a node with a small set of neighbours; … 4. IVFFlat partitions vectors into lists. At build time it runs k-means over the existing da… 5. The planner only uses the index for a matching shape. You need the distance operator in O… 6. Filtering is the hard part. An approximate index returns the top k neighbours overall, th…

  1. Types and operators. vector(n) stores n single-precision floats; halfvec, bit and sparsevec cover other storage needs. Distance operators pair with them: <-> for Euclidean, <=> for cosine, <#> for negative inner product, <+> for L1. The operator you write must match the index's operator class, or the index will not be used.
  2. Exact search is the default. With no index, ORDER BY embedding <=> $1 LIMIT k is a sequential scan that scores every row. It is exact and predictable. For small tables that is the correct choice; an approximate index mainly adds build cost and recall risk.
  3. HNSW is a layered proximity graph. Each vector is a node with a small set of neighbours; the top layers are sparse for fast traversal and the bottom layer is dense. A query descends greedily, then explores the bottom layer with a candidate list controlled by ef_search. Build quality is controlled by m (neighbours per node) and ef_construction. The graph lives in memory, which is why HNSW is fast and why it is memory-hungry.
  4. IVFFlat partitions vectors into lists. At build time it runs k-means over the existing data and assigns each vector to a cell. A query probes the nearest ivfflat.probes cells. The index builds faster and uses less memory than HNSW, but it needs representative data present before the build, and recall depends on how many lists you probe.
  5. The planner only uses the index for a matching shape. You need the distance operator in ORDER BY, a LIMIT, and an operator class matching the column. Anything else — a cast, an expression, a different metric — silently becomes an exact scan.
  6. Filtering is the hard part. An approximate index returns the top k neighbours overall, then the WHERE clause removes some, so you can get fewer rows than requested, or miss relevant rows that sit just outside the unfiltered top k. Three mitigations: raise k and filter afterwards, use a partial index or partition per tenant, or enable iterative scans so the index keeps producing candidates until the limit is met.
  7. Writes and deletes follow normal MVCC. An insert updates the row and the index; a delete marks the row dead, and the graph entry is cleaned up later. Bulk deletes therefore leave the index larger and slower until vacuum and, in some cases, a reindex.

Minimal implementation

CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE chunks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   uuid        NOT NULL,
  document_id bigint      NOT NULL,
  content     text        NOT NULL,
  embedding   vector(1536) NOT NULL
);
 
-- Cosine index. The operator class must match the operator in the query.
CREATE INDEX chunks_embedding_idx ON chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
 
-- At query time, trade recall for latency. SET LOCAL only lasts for the
-- current transaction, so open one; outside a transaction it emits a warning
-- and has no effect. Pooled connections otherwise reuse session settings.
BEGIN;
SET LOCAL hnsw.ef_search = 100;
 
SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 8;
COMMIT;

Pin the extension version and record m, ef_construction and ef_search alongside the index definition. They are part of the retrieval contract: changing them changes results without changing a single document.

The arithmetic

Exact search costs roughly O(n⋅d)O(n \cdot d) for nn vectors of dimension dd — every row is scored. A graph index costs roughly O(log⁡n)O(\log n) hops with a large constant, plus the memory to hold neighbours: about n⋅(d⋅4+m⋅8)n \cdot (d \cdot 4 + m \cdot 8) bytes for vector plus graph links, before per-row overhead. That formula is why a few million 1536-dimensional vectors can exhaust a small instance even when the table itself is unremarkable, and why halfvec or binary quantization is a real option rather than a micro-optimisation.

Recall is a dial, not a property. Raising ef_search or probes increases both recall and latency; the only way to know the curve is to measure it against exact search on your own sample.

Trade-offs

OptionGood forCostFails when
pgvector, no index (exact)Up to a few hundred thousand vectors, correctness-critical searchFull scan per queryLatency budget is interactive and the table keeps growing
pgvector HNSWLow-latency search with transactional metadataMemory-resident graph, slow builds, approximate recallThe graph no longer fits in RAM or filtering is highly selective
pgvector IVFFlatFast builds on large, already-populated tablesNeeds representative data at build, recall varies with probesData arrives continuously or tenants are unevenly sized
Dedicated vector databaseVery large corpora, independent scaling, managed ANN tuningSecond system, dual writes, consistency workThe team cannot operate a second stateful service
Search over a derived index (OpenSearch, etc.)Combined lexical and vector rankingIngestion pipeline and reindexing costFreshness matters and the pipeline lags
Trade-off mapInteractive

Good for

Up to a few hundred thousand vectors, correctness-critical search

Cost

Full scan per query

Fails when

Latency budget is interactive and the table keeps growing

  1. Latency budget is interactive and the table keeps growing
  2. The graph no longer fits in RAM or filtering is highly selective
  3. Data arrives continuously or tenants are unevenly sized
  4. The team cannot operate a second stateful service
  5. Freshness matters and the pipeline lags

Figure. Each choice with what it buys, what it costs, and the case where it loses.

What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.

Text version

Four trade-offs: pgvector, no index (exact) is good for Up to a few hundred thousand vectors, correctness-critical search, costs Full scan per query, and fails when Latency budget is interactive and the table keeps growing. pgvector HNSW is good for Low-latency search with transactional metadata, costs Memory-resident graph, slow builds, approximate recall, and fails when The graph no longer fits in RAM or filtering is highly selective. pgvector IVFFlat is good for Fast builds on large, already-populated tables, costs Needs representative data at build, recall varies with probes, and fails when Data arrives continuously or tenants are unevenly sized. Dedicated vector database is good for Very large corpora, independent scaling, managed ANN tuning, costs Second system, dual writes, consistency work, and fails when The team cannot operate a second stateful service. Search over a derived index (OpenSearch, etc.) is good for Combined lexical and vector ranking, costs Ingestion pipeline and reindexing cost, and fails when Freshness matters and the pipeline lags

Failure modes

  • The index is not used. Check EXPLAIN for a sequential scan. Usual causes: an operator class that does not match the operator, a missing LIMIT, an expression like embedding::vector(1536), or a parameter typed as text. Confirm the plan node, then fix the type or the operator.
  • Too few rows after filtering. Compare the count returned with the requested LIMIT. If it is lower, the filter is removing most candidates. Add a tenant partition or partial index, or enable iterative scans, and re-measure recall on a labelled set.
  • Index build exhausts memory. HNSW builds are memory-hungry. Raise maintenance_work_mem for the session, build during a quiet window, and prefer CREATE INDEX CONCURRENTLY to avoid blocking writes. If it still fails, build on a replica and promote, or reduce m.
  • Recall drifts after bulk changes. Large delete-and-reinsert cycles leave dead entries and a graph that no longer reflects the corpus. Watch index size against row count, and schedule a reindex when the ratio moves.

In production

Treat the vector index as a capacity item, not a feature flag. Measure p95 retrieval latency and recall@k on a fixed labelled sample; watch index size, heap size and cache hit ratio separately, because a graph that spills to disk loses most of its advantage. Set hnsw.ef_search per query shape rather than globally — an internal evaluation job can afford a deeper search than an interactive endpoint.

Operationally, pgvector inherits Postgres: backups, point-in-time recovery, replication and access control all work the same way, which is the main reason teams choose it. The costs it inherits too: a large index makes base backups and restores slower, and a major-version upgrade means rebuilding the index.

People think: pgvector is a toy that cannot handle production search.

Actually: It runs real HNSW and IVFFlat indexes with the same transactional guarantees as the rest of the database. Whether it fits is a question of corpus size, memory and filtering shape — not of whether it is serious.

People think: Adding an index always makes vector search better.

Actually: An approximate index trades recall for latency and costs memory and build time. Below a certain size, exact search is faster to operate and strictly more correct.

Interview angle

The expected question is "why not just use a dedicated vector database?" A strong answer starts from the consistency boundary: with pgvector, deletes, permission changes and embedding updates commit together, and there is one backup and one connection pool. Then name the exit condition — index working set exceeding memory, search traffic needing independent scaling, or filters so selective that a global graph cannot serve them. Finish with the measurement that would justify the move: recall@k and p95 at the target corpus size. See vector databases, ANN indexes, and the data interview category.

Sources