pgvector: Using Postgres as a Vector Database

pgvector (22,000+ GitHub stars, available on every major managed-Postgres provider) turns the database you already run into a vector store: an embedding vector(1536) column, an HNSW index, and nearest-neighbor search with an ORDER BY embedding <=> query. The architectural win is colocation — embeddings live in the same rows as the data they describe, so a RAG query is one SQL statement with ordinary WHERE filters instead of a fan-out to a second system plus the pipeline that keeps them in sync. The performance story holds up too: on 50 million 768-dimension embeddings, Postgres with the pgvectorscale extension benchmarked 28× lower p95 latency and 16× higher throughput than Pinecone's storage-optimized index at 99% recall, at 75% lower monthly cost ($835 vs $3,241 self-hosted) — vendor-run numbers, but with published methodology.

Why keep embeddings in Postgres?

Because embeddings are column data, not a separate universe. Every vector you store describes a row you already have — a document chunk, a product, a support ticket. Put it in a dedicated vector database and you inherit the classic two-system problems: a pipeline to keep vectors in sync with source rows, tombstones for deleted content that still ranks in search, and permission filters reimplemented in a second query language. Keep it in Postgres and a deleted row's embedding disappears in the same transaction, and "only documents this user may see" is the WHERE clause it always was. This is the Just Use Postgres argument at its strongest, because vector workloads are almost never standalone — they are joins waiting to happen.

How do you set up pgvector?

Three statements: enable the extension, add the column sized to your embedding model, index it. HNSW is the default index choice — slower to build than IVFFlat, faster and more accurate to query:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id    bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  content   text NOT NULL,
  embedding vector(1536) NOT NULL
);

CREATE INDEX chunks_embedding_idx ON chunks
  USING hnsw (embedding vector_cosine_ops);

-- 10 nearest chunks by cosine distance
SELECT content, embedding <=> $1 AS distance
FROM chunks
ORDER BY embedding <=> $1
LIMIT 10;

The operator must match the index opclass: <=> is cosine distance (vector_cosine_ops), <-> is L2 (vector_l2_ops), and <#> is negative inner product. Standard vectors go up to 16,000 dimensions; halfvec halves storage for models that tolerate it. ON DELETE CASCADE is the entire "keep the index consistent" story — there is no second system to reconcile.

What does a real RAG query look like?

Production retrieval is never "nearest ten vectors, period" — it is nearest ten among rows this tenant may see, from documents that are published, weighted toward recent content. In SQL that is one statement:

SELECT c.content, d.title,
       c.embedding <=> $1 AS distance
FROM chunks c
JOIN documents d ON d.id = c.doc_id
WHERE d.tenant_id = $2
  AND d.status = 'published'
ORDER BY c.embedding <=> $1
LIMIT 10;

One gotcha worth knowing: an HNSW index scan walks the graph first and applies your filter afterwards, so a highly selective WHERE can leave fewer than LIMIT results. Recent pgvector releases added iterative index scans that keep walking until the limit is satisfied — if filtered recall matters, test on your real filter selectivity. For keyword-plus-semantic hybrid search, combine this with the tsvector machinery and merge the two ranked lists — both live in the same database, so the merge is SQL, not application glue.

How does pgvector performance compare to Pinecone?

The reference numbers come from Timescale's published benchmark: 50 million Cohere embeddings (768 dimensions) on a single r6id.4xlarge EC2 instance running Postgres with pgvectorscale, whose StreamingDiskANN index descends from Microsoft's DiskANN research. Against Pinecone's storage-optimized index at 99% recall: 28× lower p95 latency, 16× higher throughput, $835 vs $3,241 per month. Against Pinecone's performance-optimized tier at 90% recall the gap narrows to 1.4×/1.5× — still ahead, still 75–79% cheaper. Treat vendor benchmarks with the usual skepticism, but the direction is corroborated by how many providers now ship pgvector by default: the "Postgres can't do vectors seriously" claim is a 2023 take.

For plain pgvector HNSW, the tuning surface is small: m and ef_construction at build time, hnsw.ef_search per session to trade recall against speed. Most applications never touch the defaults.

When do you need a dedicated vector database?

• Billions of vectors — index build times and memory pressure on a single Postgres node become the constraint; purpose-built distributed stores shard this transparently.
• Extreme multi-tenant isolation — thousands of tenants each needing isolated indexes and per-tenant scaling is an operational pattern serverless vector platforms sell directly.
• Vector search with no relational data at all — if there is genuinely nothing to join against and no Postgres in the stack, colocation buys you nothing.

The typical RAG application — thousands to low millions of chunks, filters on every query, embeddings tied to rows that change — sits squarely in Postgres territory. Same rule as the rest of the series: escalate on a measured limit, not a hypothetical one.

References

• pgvector — the extension: index types, operators, and dimension limits
• pgvectorscale — StreamingDiskANN index and statistical binary quantization
• Timescale — pgvector vs Pinecone benchmark — full methodology behind the 28×/16×/75% numbers
• Postgres Is Enough — the wider extension ecosystem this guide is one chapter of