pgvector Local RAG Store

What It Is

pgvector is an open-source PostgreSQL extension that adds a real vector column type and similarity-search operators to a database you already know how to run, back up, and query. Instead of standing up a separate vector service next to your relational data, you store embeddings in a Postgres table beside their text and metadata, and retrieval becomes an ordinary SQL query with an ORDER BY on a distance operator.

In this loop you will touch the concrete pieces of a RAG store directly: a chunks table with a fixed-dimension vector column, the distance operators (<=> cosine, <-> L2, <#> inner product), an HNSW approximate-nearest-neighbor index, and the query-time and 0.8.0 iterative-scan knobs that govern recall on filtered searches. Everything runs in one local Docker container, so you can evaluate the whole retrieval path without touching a managed service.

Why It Matters

When you are deciding where embeddings live in a production system, pgvector is the option that keeps them in the same transactional store as your documents, tenants, and ACLs. That means retrieval filters (WHERE source = ..., tenant scoping, freshness windows) are plain SQL joined to the vector search, with the same migrations, backups, and observability as the rest of your data. For a small-to-medium local RAG store, that collapses a lot of moving parts into one dependency.

The part worth evaluating up close is filtered search. The classic failure mode is overfiltering: a restrictive WHERE clause eliminates most of the approximate-nearest-neighbor candidates, so a LIMIT 5 query quietly returns two rows. pgvector 0.8.0 added iterative index scans plus better cost estimates specifically to fix this. By the end of this loop you will have run the happy path and seen exactly where the recall and filtering knobs sit, which is enough evidence to judge whether pgvector belongs in your stack.

Guided First Play

You will stand up Postgres + pgvector in a container, ingest a handful of real text chunks as embeddings, run a semantic top-k query you can sanity-check by eye, then add an HNSW index and watch the recall and filtering knobs change behavior. Aim to have a working retrieval query you trust within the timebox.

Stand up the store

The official image bundles Postgres with pgvector already compiled in, so there is nothing to build from source. Pull and run it, exposing the standard port and giving shared memory enough room for parallel index builds.

docker run -d --name pgvector-lab \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  --shm-size=1g \
  pgvector/pgvector:pg18-bookworm

# confirm it is accepting connections
docker exec -it pgvector-lab pg_isready -U postgres

Ingest real chunks

Now create the table and load embeddings from Python. The key move is register_vector, which teaches the driver to bind numpy arrays straight into a vector column so you never hand-format '[...]' strings. The vector dimension must match your embedding model exactly; this example uses a small local model that produces 384-dimensional, L2-normalized embeddings.

pip install "psycopg[binary]" pgvector sentence-transformers

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

# 384-dim, normalized embeddings
model = SentenceTransformer("BAAI/bge-small-en-v1.5")

chunks = [
    ("notes", "pgvector adds vector similarity search to Postgres."),
    ("notes", "HNSW indexes trade slower builds for fast approximate search."),
    ("notes", "Cosine distance is the safe default operator for most embeddings."),
    ("notes", "Metadata filters are plain WHERE clauses next to the ORDER BY."),
    ("notes", "IVFFlat indexes should be created after the table has data."),
    ("faq", "Raise maintenance_work_mem before building an index for speed."),
]

conn = psycopg.connect(
    "postgresql://postgres:postgres@localhost:5432/postgres",
    autocommit=True,
)
conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
register_vector(conn)

conn.execute("""
    CREATE TABLE IF NOT EXISTS chunks (
        id        bigserial PRIMARY KEY,
        source    text,
        content   text,
        embedding vector(384)
    )
""")

texts = [c[1] for c in chunks]
embeddings = model.encode(texts, normalize_embeddings=True)
for (source, content), emb in zip(chunks, embeddings):
    conn.execute(
        "INSERT INTO chunks (source, content, embedding) VALUES (%s, %s, %s)",
        (source, content, emb),
    )

print("rows:", conn.execute("SELECT count(*) FROM chunks").fetchone()[0])

What changed: you now have a relational table where each row carries its text, a source tag, and a 384-dimensional embedding side by side. That co-location is the whole point of pgvector versus a standalone vector store.

Retrieve and read the results

Embed a query the same way and rank rows by cosine distance. Cosine (<=>) is the safe default; because these embeddings are L2-normalized, inner product (<#>) would rank identically and slightly cheaper, but cosine keeps the similarity score easy to read. The score below is 1 - distance, so 1.0 is identical and 0 is unrelated.

import psycopg
from pgvector.psycopg import register_vector
from sentence_transformers import SentenceTransformer

model = SentenceTransformer("BAAI/bge-small-en-v1.5")
conn = psycopg.connect(
    "postgresql://postgres:postgres@localhost:5432/postgres",
    autocommit=True,
)
register_vector(conn)

query = "Which index gives the fastest approximate search?"
q = model.encode(query, normalize_embeddings=True)

rows = conn.execute(
    """
    SELECT content, 1 - (embedding <=> %s) AS similarity
    FROM chunks
    ORDER BY embedding <=> %s
    LIMIT 3
    """,
    (q, q),
).fetchall()

for content, similarity in rows:
    print(f"{similarity:0.3f}  {content}")

Index, tune recall, and filter

With six rows Postgres just does an exact scan, so this is where you see the production-shaped knobs rather than a speedup. Connect with psql and add an HNSW index, whose opclass must match the operator you query with (vector_cosine_ops pairs with <=>).

docker exec -it pgvector-lab psql -U postgres

-- opclass must match the query operator (<=> uses vector_cosine_ops)
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- query-time recall knob: default 40, higher = better recall, slower
SET hnsw.ef_search = 100;

-- metadata filter is just a WHERE clause beside the ORDER BY
SELECT content
FROM chunks
WHERE source = 'notes'
ORDER BY embedding <=> (SELECT embedding FROM chunks WHERE id = 2)
LIMIT 5;

Why this matters more than the query itself: HNSW search walks an approximate graph, so a restrictive WHERE can knock out most candidates before LIMIT is satisfied — the overfiltering problem. pgvector 0.8.0 fixes it with iterative scans, which keep pulling candidates until the limit is met instead of giving up early.

-- off by default; turn on to avoid returning fewer than LIMIT rows
SET hnsw.iterative_scan = strict_order;   -- preserves exact ordering
-- or: relaxed_order;                     -- faster, approximate ordering
-- safety valve on how far a scan will reach:
SET hnsw.max_scan_tuples = 20000;

You have now exercised the full path — setup, ingest, retrieve, index, filter, and the recall controls — which is enough evidence to judge whether pgvector fits your retrieval needs without committing to a larger build.

Your Next Move

You ran a real local RAG store end to end. Did the SQL-native, embeddings-beside-metadata model feel like the right shape for your work, or like overhead you would rather hand to a dedicated vector service?

Go Deeper would spend two hours on the parts six rows can't show: building HNSW versus IVFFlat on a few hundred thousand chunks, measuring the ef_search and probes recall/latency tradeoff with real numbers, using halfvec to index high-dimension embeddings, and stress-testing iterative scans against selective filters. Project would turn this into a scoped ingestion-and-retrieval service — chunking, batched embedding, schema and index migrations, and a benchmarked retrieval API you could drop into a real pipeline.

Sources

This loop is grounded in the official pgvector project documentation — the main extension README (install, distance operators, HNSW/IVFFlat indexing, storage limits, and tuning) and the pgvector-python client guide (psycopg3 with register_vector) — plus the PostgreSQL announcement for pgvector 0.8.0 covering iterative index scans and improved filtered-query handling. Full links are attached as provenance; you do not need to read them before starting.