pgvector 0.8/0.9: HNSW, halfvec, and RAG on Postgres

Problem Context

pgvector turned PostgreSQL into the most-deployed vector database almost overnight. Version 0.8.0 (October 2024) added iterative index scans for filtered ANN, halved memory for HNSW builds, and shipped halfvec (16-bit) plus bit vector types that cut index size by 2-32x. Version 0.9.0 in 2025 brought sparse vectors and StreamingDiskANN-style techniques. Azure Database for PostgreSQL Flexible Server, AWS RDS, and Neon all ship pgvector by default in 2026.

The pitch is simple: keep your relational data, your transactions, your auth, your backups, and your team in one engine — and add vector search as one more index type. The trade-off: pgvector tops out around 50-100M vectors per node before you start fighting RAM and recall. Beyond that, dedicated vector DBs (Pinecone, Weaviate, Qdrant, Milvus) earn their keep.

Concept Explanation

pgvector adds three vector data types and two index types:

• vector(d) — float32, the default. 4 bytes × d. 1536 dims = 6 KB per row.
• halfvec(d) — float16, half the storage and memory with negligible recall loss.
• bit(d) — binary vectors for Hamming distance (1 bit per dim, 32-192x smaller).
• HNSW — hierarchical navigable small world graph. Best recall/latency, slow to build, supports inserts.
• IVFFlat — inverted file with flat lists. Faster to build, worse recall on small datasets, requires training.

flowchart LR
    DOC["Document"] --> CHUNK["Chunk + clean"]
    CHUNK --> EMB["Embed<br/>(text-embedding-3-small / large)"]
    EMB --> STORE["Postgres + pgvector<br/>(HNSW index)"]
    Q["User query"] --> QEMB["Embed query"]
    QEMB --> SEARCH["ORDER BY emb &lt;=&gt; q LIMIT k"]
    STORE --> SEARCH
    SEARCH --> LLM["LLM context"]

    style STORE fill:#0078D4,color:#fff,stroke:#005a9e
    style LLM fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Enable the extension and create a table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doc_id      bigint NOT NULL,
    tenant_id   bigint NOT NULL,
    content     text   NOT NULL,
    embedding   vector(1536) NOT NULL,   -- text-embedding-3-small
    created_at  timestamptz NOT NULL DEFAULT now()
);

Step 2: Build an HNSW index with the right metric

-- Cosine distance is the OpenAI/most-models default
CREATE INDEX idx_chunks_emb
ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- m: links per node (12-48). Higher = better recall, more memory.
-- ef_construction: build-time search width (40-200).

-- Query-time recall knob: SET LOCAL hnsw.ef_search = 100;

Step 3: Query — top-k with metadata filter

-- pgvector 0.8+ supports iterative index scans with filters
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order;

SELECT id, doc_id, content,
       embedding <=> $1 AS distance
FROM chunks
WHERE tenant_id = $2          -- partial / multi-column index helps
ORDER BY embedding <=> $1
LIMIT 10;
-- Operators: <=>  cosine, <->  L2, <#>  inner product (negate for similarity)

Step 4: C# with EF Core 9 + pgvector-dotnet

// dotnet add package Pgvector.EntityFrameworkCore
builder.Services.AddDbContext<AppDb>(opt =>
    opt.UseNpgsql(connStr, o => o.UseVector()));

public class Chunk
{
    public long Id { get; set; }
    public long TenantId { get; set; }
    public string Content { get; set; } = "";
    [Column(TypeName = "vector(1536)")]
    public Vector Embedding { get; set; } = default!;
}

// Top-k semantic search
var query = new Vector(queryEmbedding);     // float[]
var hits = await db.Chunks
    .Where(c => c.TenantId == tenantId)
    .OrderBy(c => c.Embedding.CosineDistance(query))
    .Take(10)
    .ToListAsync();

Step 5: Halve storage with halfvec

-- 1536-dim float32 = 6 KB. halfvec = 3 KB. Recall drop is < 0.5%.
ALTER TABLE chunks
    ALTER COLUMN embedding TYPE halfvec(1536)
    USING embedding::halfvec(1536);

CREATE INDEX idx_chunks_emb_half
ON chunks USING hnsw (embedding halfvec_cosine_ops);

-- Or go further with binary quantization for shortlisting
ALTER TABLE chunks ADD COLUMN emb_bit bit(1536)
    GENERATED ALWAYS AS (binary_quantize(embedding)::bit(1536)) STORED;
CREATE INDEX ON chunks USING hnsw (emb_bit bit_hamming_ops);

Step 6: Hybrid search (vector + full-text)

-- Combine semantic similarity with BM25-style lexical match
SELECT id, content,
       0.7 * (1 - (embedding <=> $1)) +
       0.3 * ts_rank(to_tsvector('english', content),
                    plainto_tsquery('english', $2)) AS score
FROM chunks
WHERE tenant_id = $3
  AND (embedding <=> $1 < 0.5
       OR to_tsvector('english', content) @@ plainto_tsquery('english', $2))
ORDER BY score DESC
LIMIT 20;

Common Pitfalls

1. IVFFlat with too few rows. IVF needs > 50k vectors and explicit training (lists ≈ sqrt(rows)). Below that, HNSW is strictly better.
2. Wrong distance metric. OpenAI embeddings are normalized → cosine and inner product give the same ranking, but the index ops must match (vector_cosine_ops vs vector_ip_ops).
3. Filter without iterative scan. Pre-0.8 pgvector applied filters after ANN, dropping recall. Use 0.8+ and enableiterative_scan for filtered queries.
4. Storing the embedding model output without normalizing. Some models output unnormalized vectors; cosine distance assumes normalized. Normalize at write time or use the right distance.
5. Building HNSW with low maintenance_work_mem. Index build spills to disk and takes hours. Setmaintenance_work_mem = 8GB (or higher) for the duration of CREATE INDEX.
6. Treating pgvector as infinite. Past ~50-100M vectors per node, recall drops and memory pressure dominates. Plan to shard by tenant or move to a dedicated vector DB.

Practical Takeaways

• Default to HNSW + halfvec + cosine for OpenAI-style embeddings.
• Tune ef_search per query for the recall/latency curve you want.
• Iterative scan (pgvector 0.8+) is essential whenever you filter on metadata.
• Hybrid search (vector + full-text) beats pure semantic on factual queries — use both.
• Binary quantization (bit vectors) is a great shortlist stage before re-ranking with full vectors.
• One Postgres for RAG, transactions, and analytics is a real architecture in 2026 — until you cross ~100M vectors.
• Crank maintenance_work_mem before building large HNSW indexes.