Building semantic search with pgvector: embeddings inside your existing Postgres

The default recommendation for AI-powered search in 2024 was 'add Pinecone' — a dedicated vector database alongside your primary datastore. In 2026, for most applications the answer is simpler: pgvector, the Postgres extension that adds vector storage and similarity search directly to your existing database. It's available on every major managed Postgres provider, requires no new infrastructure, and for datasets under tens of millions of vectors, it performs within a few percent of dedicated vector databases.

How it works

pgvector adds a vector column type and distance operators — <-> for Euclidean, <=> for cosine, <#> for inner product. You store embedding vectors alongside your regular data, then search by finding the rows whose embedding is nearest to a query embedding. The query embedding comes from an embedding model — OpenAI's text-embedding-3-small, Anthropic's embedding endpoint, or a locally-hosted model — which converts a search query into the same vector space as your stored content.

-- Enable the extension
CREATE EXTENSION vector;

-- Add an embedding column to an existing table
ALTER TABLE posts ADD COLUMN embedding vector(1536);

-- Find the 10 most semantically similar posts to a query vector
SELECT id, title, 1 - (embedding <=> $1::vector) AS similarity
FROM posts
WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 10;

The HNSW index: make similarity search fast

Without an index, pgvector does a full table scan comparing every row. For small tables (under ~50k rows) this is fast enough. For larger datasets, create a HNSW index — it trades a small accuracy loss (~2%) for a 10–100x query speed improvement. Create it once after the initial data load; Postgres maintains it on inserts automatically.

-- HNSW index for cosine distance
-- Build after initial data load; maintained automatically on inserts
CREATE INDEX ON posts USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- m: graph connections per node (higher = better recall, more memory)
-- ef_construction: search depth at build time (higher = better recall, slower build)

Hybrid search: semantic plus keyword

Pure semantic search misses exact keyword matches — a user searching for a specific error code may get thematically-related results ranked above the exact match. The production pattern is hybrid search: combine vector similarity with Postgres full-text search (ts_rank from tsvector) and rank by a weighted combination. This is the architecture used by Supabase's built-in search and most production RAG systems.

When to use a dedicated vector database instead

• Datasets over 50 million vectors — pgvector's HNSW memory requirements become a constraint at this scale.
• Multi-tenancy with strict namespace isolation requirements.
• Very high concurrent write rates with strict query latency SLAs — HNSW maintenance under heavy writes can cause latency spikes.
• For everything else: use pgvector and eliminate the infrastructure dependency.

Written by Appesto Engineering.