pgvector + HNSW

Vector search inside PostgreSQL. "Good enough" without a separate vector DB.

1. Install

docker run -d -e POSTGRES_PASSWORD=x -p 5432:5432 pgvector/pgvector:pg16

Or existing PG:

CREATE EXTENSION IF NOT EXISTS vector;

2. Schema

CREATE TABLE document_chunks (
  id BIGSERIAL PRIMARY KEY,
  document_id BIGINT NOT NULL,
  chunk_index INT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(768),
  created_at TIMESTAMPTZ DEFAULT now()
);

3. HNSW vs IVFFlat

4. Create HNSW

CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Build after the full load.

5. Query

SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM document_chunks
ORDER BY embedding <=> $1::vector LIMIT 10;

Must sort ASC by distance for HNSW to engage.

6. Runtime tuning

SET hnsw.ef_search = 100;

Filters can exhaust the candidate list

Approximate indexes such as HNSW find candidates first and apply WHERE filters afterward. If none of the global 40 candidates belong to a requested tenant or collection, the query can return zero rows even when matching vectors exist. Raising ef_search globally increases CPU and I/O for every request.

With pgvector 0.8 or later, enable an iterative scan only for the query transaction so the index expands until it finds enough filtered rows.

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;

SELECT id, content
FROM document_chunks
WHERE collection_id = $1
ORDER BY embedding <=> $2::vector
LIMIT 10;
COMMIT;

• strict_order preserves exact distance ordering.
• relaxed_order may improve recall and speed while allowing slight ordering differences.
• Bound extra work with hnsw.max_scan_tuples.
• Validate per-filter result counts and Recall@K, not latency alone.

7. Hybrid (trigram + vector)

SELECT *, similarity(content, $1) AS text_score,
       1 - (embedding <=> $2::vector) AS vec_score
FROM document_chunks
ORDER BY (similarity(content, $1) * 0.3 + (1 - (embedding <=> $2::vector)) * 0.7) DESC
LIMIT 10;

8. Backup

pg_dump stores vectors as text; rebuild indexes on restore (pg_restore --jobs 4).

9. Gotchas

• Missing CREATE EXTENSION vector
• ORDER BY similarity DESC → no HNSW
• Dim mismatch after model swap
• Seq Scan in EXPLAIN → query shape wrong
• Too few filtered HNSW results → candidates were removed after ANN search; check iterative scans and per-filter Recall@K

10. When a dedicated DB?

Closing

Start with pgvector; migrate only after measured bottlenecks.

Next

• 04-redis-five-roles