Ship pgvector for Production Embedding Search

Here is what we are building: a Postgres-backed vector store that ingests embeddings from any model, indexes them with HNSW, and serves cosine-similarity search at sub-50ms latency on a million-row corpus. No Pinecone, no Qdrant, no extra service. Your DB is your vector DB — and you already know how to operate it.

1. Install and enable the extension

Most managed Postgres providers (Neon, Supabase, RDS 16+) ship pgvector pre-installed. On bare metal, install the package and turn the extension on per database. The vector type behaves like a fixed-length array — that is why it stays cache-friendly compared to JSON-blob hacks.

-- apt install postgresql-16-pgvector
CREATE EXTENSION IF NOT EXISTS vector;

2. Create the table with the right dimensions

Pick your embedding dimension once and never change it. The column type encodes it — vector(1536) for OpenAI text-embedding-3-small, vector(1024) for voyage-3, vector(768) for many open models. Mismatched dimensions will reject inserts, so lock it down early.

CREATE TABLE docs (
  id         bigserial PRIMARY KEY,
  source     text NOT NULL,
  content    text NOT NULL,
  embedding  vector(1536) NOT NULL,
  created_at timestamptz DEFAULT now()
);

3. Build the HNSW index

Flat scan is fine for the first 50k rows. After that you need HNSW — a graph structure that skips 99%+ of rows during a nearest-neighbor search. vector_cosine_ops matches the similarity function you will actually use.

CREATE INDEX docs_embedding_hnsw ON docs
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
-- At query time, raise candidate fan-out for better recall:
SET hnsw.ef_search = 100;

Watch out: HNSW is memory-hungry. For 1M 1536-dim vectors expect 4-6 GB of RAM. On smaller boxes, drop to m = 8 and ef_construction = 32 and accept ~5% recall loss.

4. The query you will actually run

Top-k cosine similarity with a metadata filter — the bread-and-butter RAG retrieval call. The <=> operator returns cosine distance (0 = identical, 2 = opposite), so order ASC and subtract from 1 in app code if you want a 0-1 similarity score.

SELECT id, source, content,
       1 - (embedding <=> $1) AS similarity
FROM docs
WHERE source = ANY($2)
ORDER BY embedding <=> $1
LIMIT 10;

Bind $1 as a stringified vector literal: '[0.012, -0.044, ...]'. Pass ::vector casts on the client side and sanitize the sources array — the parser rejects malformed vectors but not malicious strings inside it.

5. The Python search loop

The connection pool keeps pgbouncer happy. Install with pip install "psycopg[binary]" pgvector, then register the type adapter once per connection and query.

import psycopg
from pgvector.psycopg import register_vector
DSN = "postgresql://user:pass@host:5432/db"
def search(query_embedding: list[float], sources: list[str], k: int = 10):
    with psycopg.connect(DSN) as conn:
        register_vector(conn)
        with conn.cursor() as cur:
            cur.execute(
                """SELECT id, content, 1 - (embedding <=> %s) AS sim
                   FROM docs WHERE source = ANY(%s)
                   ORDER BY embedding <=> %s LIMIT %s""",
                (query_embedding, sources, query_embedding, k),
            )
            return cur.fetchall()

For batch ingestion, swap to COPY docs (source, content, embedding) FROM STDIN with a CSV stream — you will jump from ~200 rows/sec to 20k+.

What you have

A Postgres-backed vector store that survives restarts, plays nicely with your existing backups, replays through logical replication, and answers nearest-neighbor queries in milliseconds. One fewer service on your architecture diagram.

Ship next

Wire this into your agent on every chunk -> embed -> upsert pipeline and run it inside a transaction. Add a last_embedded_at column with a partial index so re-embedding jobs stay cheap. Once you cross 5M rows, benchmark ivfflat against hnsw — on read-heavy workloads IVFFlat can actually win on cost.