Semantic Search with pgvector: When Postgres Is All You Need

Introduction

Every AI feature eventually needs similarity search over embeddings, and the reflex is to add a dedicated vector database to the stack. Sometimes that's right. But for a large class of applications, corpora in the thousands to low millions of vectors, embeddings that describe rows you already store, the strongest move is the boring one: pgvector, inside the Postgres you already operate, back up, and know how to secure.

The underrated argument isn't performance; it's consistency. When the document row and its embedding live in the same database, they update in the same transaction. There is no sync pipeline between your source of truth and your search index, which means there is no sync pipeline to break.

Setup and Schema

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint       NOT NULL,
    title      text         NOT NULL,
    body       text         NOT NULL,
    embedding  vector(1024) NOT NULL,     -- dimension must match your model
    updated_at timestamptz  NOT NULL DEFAULT now()
);

Nearest-neighbor queries are just SQL with a distance operator, <=> is cosine distance:

SELECT id, title, 1 - (embedding <=> $1) AS cosine_similarity
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 10;

That WHERE tenant_id = … line is half the reason to be here: filtering by tenant, date, status, or anything else is ordinary SQL composed with the similarity ordering, no separate "metadata filtering" API, no second query to join results by hand.

Indexing: HNSW, and What Its Knobs Mean

Without an index, similarity search is a sequential scan, fine at 50k rows, painful at 5M. pgvector's HNSW index gives you approximate nearest-neighbor search with excellent recall:

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

• m, edges per graph node. Higher = better recall, bigger index. 16 is a solid default.
• ef_construction, build-time search width. Higher = better graph quality, slower builds.
• hnsw.ef_search, the query-time recall/speed dial, settable per session or per query:

SET LOCAL hnsw.ef_search = 100;   -- default 40; raise for recall, lower for speed

Practical notes from running it:

• ANN is approximate. Measure recall on your own data by comparing against exact scan results on a sample; tune ef_search until recall@10 is where you need it (typically ≥0.95 is cheap to reach).
• Index build is the slow part. Building HNSW on millions of rows takes real time; raise maintenance_work_mem and build concurrently. Prefer ivfflat only when build time matters more than query recall.
• Memory matters. HNSW performs best when the index fits in RAM. A 1M × 1024-dim float32 index is roughly 4 GB before graph overhead, size your instance accordingly, or use scalar quantization (halfvec, 2 bytes per dimension) to halve it with minimal quality loss.

Hybrid Search in One Query

Postgres already has full-text search, which means BM25-style keyword retrieval and vector retrieval can be fused in a single statement, no second system:

WITH lexical AS (
    SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, query) DESC) AS r
    FROM documents, plainto_tsquery('english', $1) AS query
    WHERE tsv @@ query
    LIMIT 50
),
semantic AS (
    SELECT id, row_number() OVER (ORDER BY embedding <=> $2) AS r
    FROM documents
    LIMIT 50
)
SELECT id, sum(1.0 / (60 + r)) AS rrf_score      -- reciprocal rank fusion
FROM (SELECT * FROM lexical UNION ALL SELECT * FROM semantic) fused
GROUP BY id
ORDER BY rrf_score DESC
LIMIT 10;

Exact identifiers and rare keywords come from the lexical arm; paraphrase and "vibes" queries from the semantic arm. For a RAG stack this is 90% of what a dedicated hybrid-search service would give you (see my notes on retrieval quality in production RAG).

Operational Wins You Get for Free

• Transactions: insert the row and its embedding atomically; delete a user and their vectors are gone in the same statement, GDPR compliance without a second deletion pipeline.
• Backups and HA: your existing pg_dump/WAL/replica story covers vectors automatically.
• Access control: row-level security applies to similarity queries like any other query.
• Observability: EXPLAIN ANALYZE, pg_stat_statements, and every Postgres tool you already use.

When You'd Genuinely Outgrow It

Honest limits, so this doesn't read like advocacy:

• Scale: past tens of millions of vectors, or thousands of similarity queries per second, dedicated engines (with segment-based architectures, GPU indexes, and distributed sharding) pull ahead.
• Write-heavy churn: constant re-embedding of millions of rows stresses HNSW maintenance; specialized systems handle compaction better.
• Advanced features: multi-vector representations, named-vector spaces per document, and built-in rerankers are places dedicated stores are ahead.

The migration path is graceful, though: because Postgres remained your source of truth, standing up a dedicated index later is a projection of existing data, not a data-model rewrite.

Takeaways

• If your corpus is under a few million vectors and you already run Postgres, pgvector is the highest-leverage choice: one system, transactional consistency, SQL filtering.
• Use HNSW with cosine ops; tune ef_search against measured recall on your data.
• Hybrid retrieval is one CTE away, full-text + vector + RRF in a single query.
• Revisit the decision at real scale thresholds, not out of architecture envy.

The best infrastructure decision is often the one that adds zero new systems. Postgres keeps earning its reputation as the default answer.