Embeddings search is no longer a novelty feature; it is table stakes for Postgres teams building RAG pipelines, semantic search, and recommendation engines. pgvector, the open-source extension that adds vector columns and nearest-neighbor search to PostgreSQL, is where most of those teams start. The current v0.8.x line supports Postgres 13 and later, with single-precision, half-precision, binary, and sparse vectors plus exact and approximate nearest-neighbor search.

The payoff of doing this inside Postgres instead of standing up a separate vector database: your embeddings live under the same backup and point-in-time recovery story as your operational data, and you can JOIN vectors against the relational tables that give them context. One database, one security model, no second system to babysit. Here is the shortest path from zero to working vector search.

Enable the extension and add a vector column

Installation is one package-manager command on most platforms (apt, yum, Homebrew, Docker), a source build for Postgres 13+, or nothing at all on hosted providers that ship it preinstalled. Then enable it once per database:

CREATE EXTENSION vector;

A vector column has a fixed dimension that must match your embedding model’s output. OpenAI’s text-embedding-3-small emits 1,536 dimensions, so a documents table for semantic search looks like this:

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  title      text NOT NULL,
  body       text NOT NULL,
  embedding  vector(1536)
);

pgvector also supports half-precision (halfvec, up to 4,000 dimensions at roughly half the memory), binary (bit), and sparse (sparsevec) vectors — options that matter as your corpus grows, not for the basic workflow.

Load embeddings with INSERT or COPY

Generate embeddings in your application (or in SQL with an in-database helper such as pgai) and load them like any other rows. Upserts keep re-embedding jobs idempotent:

INSERT INTO documents (id, title, body, embedding)
VALUES (42, 'Well completion report', 'chunk text...', '[0.012, -0.045, 0.118, ...]')
ON CONFLICT (id) DO UPDATE
SET body      = EXCLUDED.body,
    embedding = EXCLUDED.embedding;

For a bulk backfill, COPY ... FROM STDIN WITH (FORMAT BINARY) is dramatically faster than row-by-row inserts.

Query with the right distance operator

pgvector exposes four main distance operators: L2 Euclidean (<->), cosine (<=>), inner product (<#>), and L1 (<+>), plus Hamming and Jaccard for binary vectors. Cosine is the safe default for embeddings from most LLM APIs because it ignores vector magnitude. If you normalize vectors to unit length before storing, inner product and cosine produce identical rankings — and inner product scans are cheaper, which matters at scale:

SELECT id, title,
       embedding <=> '[0.021, -0.012, 0.077, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 5;

Nothing stops you from combining similarity with ordinary filters or JOINs — WHERE published_at > now() - interval '1 year', or a JOIN against an assets table to restrict search to one facility. That hybrid pattern is where vector search in Postgres beats a standalone vector database.

Add an HNSW index before the table grows

Without an index, pgvector performs an exact scan — perfect recall, perfectly fine up to tens of thousands of rows. Past that, add an approximate index. HNSW is the default recommendation: it beats IVFFlat on the speed/recall tradeoff, requires no training pass, and can be created even on an empty table. Create one index per distance operator you actually query with:

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

-- Raise recall for high-stakes queries
SET hnsw.ef_search = 100;

m (default 16) and ef_construction (default 64) trade build time and memory against recall. hnsw.ef_search (default 40) controls per-query recall: raise it for careful searches, lower it for high-throughput ones, and use SET LOCAL inside a transaction when you only want it for a single query.

A Houston-sized example

Picture an energy company downtown with 200,000 well completion reports, equipment manuals, and inspection notes spread across file shares. The pattern that keeps coming up here: chunk each document, embed the chunks, load them into pgvector, and run RAG over the result — “which pumps show a vibration signature similar to this one?” — with answers grounded in the company’s own records. Because the vectors sit next to the relational asset data, the JOINs stay local, compliance backups cover everything, and there is no second database to keep in sync.

When pgvector alone is not enough

Two companion extensions extend it without replacing it: pgvectorscale (Timescale) adds DiskANN-style StreamingDiskANN indexes that hold hundreds of millions of vectors with far less memory, and pgai runs embedding and model calls inside SQL for batch jobs. Both are designed to complement pgvector in the same instance.

Bottom line

Start exact, add HNSW when scans get slow, normalize embeddings if you plan to use inner product, and re-check recall after any index change. The full feature list and current release (v0.8.6) are in the pgvector README on GitHub, and the extension’s docs cover the indexing tradeoffs in more detail.