pgvector Postgres Search Scales — HNSW Guide That Wins

RAG apps need somewhere to put embeddings. Spinning up a dedicated vector database means a new cluster, new auth, new backups, and joins that cross system boundaries. For most teams that's a second database to babysit before product-market fit.

pgvector skips all that. It's a Postgres extension storing vectors beside your relational data — same transactions, same backups, same JOINs. You'll write your first semantic query in minutes, not sprints.

But vectors have sharp edges. Approximate indexes return different results than exact scans, filters apply after the scan, and dimension limits bite the unprepared. This guide covers the setup that stays fast past a million rows.

Why Vectors Belong in Postgres Until Scale Says Otherwise

A RAG feature needs three things: store embeddings, find nearest neighbors fast, and join results with app data. A dedicated vector database handles the first two and punts the third across a network boundary — two systems, two auth models, two backup stories, and application joins in code.

pgvector keeps all three in Postgres. Vectors live in typed columns, nearest-neighbor queries are ORDER BY with distance operators, and results JOIN directly to users, products, and permissions. Transactions cover vectors and rows together; point-in-time recovery covers both.

The honest limit is scale and exoticism: hundreds of millions of vectors or custom sharding still favor dedicated stores. Below that, one database beats two on every operational axis that matters.

Store, Query, Then Index — The Safe Order

Enable the extension, store embeddings in a typed column, and query with ORDER BY plus a distance operator before building any index. Exact search is slow but perfectly correct — it becomes your recall baseline.

Only then add HNSW with the opclass matching your embeddings: vector_cosine_ops for normalized embeddings, vector_l2_ops for raw, vector_ip_ops for inner product. Index and query operators must match or the index sits idle.

Index filter columns separately from day one. That B-tree on category_id looks optional until filtered recall collapses — by then you're debugging in production.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id serial PRIMARY KEY,
  category_id int NOT NULL,
  content text NOT NULL,
  embedding vector(1536) NOT NULL
);

-- Exact search first (perfect recall, no index):
SELECT id, content
FROM docs
ORDER BY embedding <=> '[0.1,0.2,...]'::vector
LIMIT 5;

-- Then accelerate with HNSW (cosine):
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON docs (category_id);  -- filters need their own index

HNSW vs IVFFlat — Indexes, Types, and Build Rules

HNSW builds a multilayer graph: excellent speed-recall balance, slow memory-hungry construction. IVFFlat partitions vectors into lists: fast builds, weaker queries. Type support spans vector (2000 dims), halfvec (4000), bit (64000), and sparsevec (1000 non-zeros).

Build-time parameters m (graph connections, default 16) and ef_construction (candidate list, default 64) trade build cost for recall. Query-time ef_search (default 40) trades speed for recall per query — raise it with SET LOCAL for hard queries without rebuilding.

Operational rules: load data first, index second; raise max_parallel_maintenance_workers for big builds; consider halfvec casts to halve index size. An index built over loaded data beats one maintained through the load, every time.

Filtering, Iterative Scans, and Half-Precision at Scale

Approximate indexes scan first and filter after, so selective filters starve. The fixes compose: B-tree indexes on filter columns for exact-friendly shapes, partial HNSW indexes for hot values, partitioning for many values, and iterative scans that extend the scan until LIMIT fills.

Strict ordering keeps exact distance order; relaxed ordering allows slight disorder for better recall. Pick strict for user-facing ranking, relaxed for candidate generation feeding a reranker.

Half-precision indexing halves size with minor recall cost — the standard move past a few million vectors. Cast both index and query to halfvec or the pair won't meet.

-- Partial index for the hot slice:
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
WHERE (category_id = 42);

-- Iterative scans keep scanning until LIMIT fills (0.8+):
SET hnsw.iterative_scan = strict_order;
-- relaxed_order trades exact ordering for better recall

-- Half-precision index, half the size:
CREATE INDEX ON docs
USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);
SELECT * FROM docs
ORDER BY embedding::halfvec(1536) <=> '[0.1,...]'::halfvec(1536)
LIMIT 5;

Hybrid Search and Beyond — Fusion, Quantization, Monitoring

Semantic search shines combined with keywords: Postgres full-text search (plainto_tsquery plus ts_rank) handles exact terms while vectors handle meaning. Fuse with Reciprocal Rank Fusion or a cross-encoder reranker for results neither method reaches alone.

Subvector and binary quantization extend the range further: index leading dimensions for coarse retrieval, binary vectors with Hamming distance for massive-scale pre-filtering. Each technique trades a little recall for a lot of speed — measure, don't assume.

Monitor pg_stat_progress_create_index during builds and recall@k dashboards after. Index work is visible in Postgres natively, which is another quiet win of staying inside the database you already operate.

ALTER TABLE docs ADD COLUMN textsearch tsvector
GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;
CREATE INDEX ON docs USING GIN (textsearch);

WITH semantic AS (
  SELECT id, row_number() OVER () AS rnk FROM docs
  ORDER BY embedding <=> '[0.1,0.2,...]'::vector LIMIT 20
),
keyword AS (
  SELECT id, row_number() OVER () AS rnk FROM docs
  WHERE textsearch @@ plainto_tsquery('refund policy') LIMIT 20
)
SELECT COALESCE(s.id, k.id) AS id,
  COALESCE(1.0/(60+s.rnk),0) + COALESCE(1.0/(60+k.rnk),0) AS rrf
FROM semantic s FULL JOIN keyword k ON s.id = k.id
ORDER BY rrf DESC LIMIT 5;

When to Leave Postgres — Honest Exit Criteria

Stay in pgvector while one Postgres handles the load with headroom: JOINs stay local, transactions stay simple, and your team operates one system. Move out when vectors exceed comfortable single-node scale, when exotic filtering dominates, or when multitenant isolation needs dedicated infrastructure.

Migration paths stay open because embeddings are portable — the same vectors load into dedicated stores later. Starting in pgvector doesn't trap you; it defers the distributed-systems bill until revenue justifies it.

That deferral is the strategy: ship semantic search this sprint inside the database you already back up, and revisit the architecture when measurements — not hype — demand it.