[PostgreSQL + pgvector: Using One Database for Relational Data and AI Embeddings]

You already run PostgreSQL in production. Then a RAG feature lands on your roadmap, you open the first tutorial, and step one is "spin up Pinecone." Suddenly, you're operating two datastores, writing to both, keeping them in sync, and reconciling them when they drift.

This guide answers the question of whether that second database is actually necessary. For a large share of real workloads, it isn't: the pgvector extension stores and searches embedding vectors directly inside Postgres, next to the relational data they belong to. We'll build a minimal working example — extension, schema, HNSW index, cosine similarity search — then layer on hybrid search, wire the whole thing into a RAG pipeline, and finish with an honest look at the point where a dedicated vector database starts to earn its keep.

No "what is an embedding" detour. If you can write a CREATE TABLE and you know an embedding is a numeric vector produced by an ML model, you have everything you need.

Why add a second database when Postgres can do it?

Copy link

The pitch for a standalone vector database (VDB) is performance at scale. That pitch is real, but it's answering a question most teams don't have yet. The question they actually have is: where does the vector for this document live relative to the document's other columns?

With a separate VDB, the answer is "somewhere else," and that single fact generates most of the operational cost:

• Dual writes. Every insert and update has to land in two systems. Now you own a consistency problem that Postgres alone would have solved with a transaction.
• Sync and drift. Backfills, re-embeds, and deletes have to be replayed into the VDB. Miss one, and your search index quietly diverges from your source of truth.
• Two round-trips per query. You ask the VDB for the nearest vector IDs, get a list back, then turn around and query Postgres for the actual rows. Filtering by anything relational — price, stock, tenant, permissions — happens after retrieval, which is exactly the wrong order.

pgvector collapses all of that because the vector is just another column. The payoff is that vector search composes with everything SQL already does — JOIN, WHERE, aggregates, transactions, point-in-time recovery — in a single query against a single system.

Consider a product-search feature. "Find the ten items most similar to this one, that are in stock, under $50, and shippable to the user's region." With a separate VDB that's two services, two round-trips, and glue code to intersect the results. With pgvector, it's one statement:

SELECT p.id, p.name, p.price
FROM products p
WHERE p.in_stock
  AND p.price < 50
  AND p.region = 'EU'
ORDER BY p.embedding <=> :query_vec
LIMIT 10;

The filter and the similarity ranking are evaluated together, by the same planner, with the same transactional view of the data. This advantage compounds in any application where vector search is one feature among many rather than the whole product. If you're building a pure semantic search engine over hundreds of millions of vectors, a dedicated VDB may well win. If you're adding retrieval to an app that already has users, orders, and permissions in Postgres, the integration usually matters more than raw queries-per-second.

Setting up pgvector: extension, table schema, and the first similarity search

Copy link

Install and enable

Copy link

pgvector ships preinstalled on most managed providers (Supabase, Neon, RDS, Cloud SQL) and is a package install everywhere else (apt, yum, Homebrew, Docker, conda-forge). The current release, 0.8.2, is worth pinning to: it patched a buffer-overflow CVE (CVE-2026-3172) in parallel HNSW index builds. Once the binary is present, enable it per database:

CREATE EXTENSION IF NOT EXISTS vector;

Schema: vectors next to relational columns

Copy link

The whole point is to keep embeddings with their data, so the vector is just a column in a normal table:

CREATE TABLE documents (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_id   bigint REFERENCES sources(id),
    content     text NOT NULL,
    metadata    jsonb DEFAULT '{}',
    embedding   vector(1536),          -- dimension must match your model
    created_at  timestamptz DEFAULT now()
);

The dimension in vector(1536) is not arbitrary — it has to equal the output dimension of whatever model you embed with. A few common ones:

One trap worth knowing up front: pgvector can store up to 16,000 dimensions, but the standard vector type can only be indexed up to 2,000. The 3072-dim text-embedding-3-large, therefore, can't go straight into an HNSW index. You have two clean options: pass dimensions: 1536 (or lower) to the OpenAI API to get a shorter, still-usable vector, or store the column as halfvec(3072), which indexes up to 4,000 dimensions at half the storage cost. Pick the dimension before you load a million rows, not after.

The first similarity search

Copy link

Distance lives in operators. pgvector gives you four:

For most embedding models, cosine is the right default. Note that <=> returns distance (0 = identical, 2 = opposite), so cosine similarity is 1-(a <=> b):

SELECT id,
       content,
       1 - (embedding <=> :query_vec) AS similarity
FROM documents
ORDER BY embedding <=> :query_vec      -- ascending distance = most similar first
LIMIT 5;

That's a complete, working semantic search. With no index, it runs an exact nearest-neighbor scan — perfect recall, but it reads every row. That's fine for tens of thousands of vectors, but it becomes a problem somewhere in the hundreds of thousands.

Indexing: HNSW vs IVFFlat

Copy link

Approximate nearest-neighbor (ANN) indexes trade a little recall for a lot of speed. pgvector offers two, and the choice is genuinely consequential.

HNSW builds a multilayer navigable graph. It gives the best speed-to-recall tradeoff, can be built on an empty table (no training step), and is the right default for almost everyone:

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

• m — connections per node (default 16). Higher = better recall, more memory, slower build.
• ef_construction — candidate list size during build (default 64). Higher = better recall, slower build.

Recall at query time is tuned per-session with ef_search (default 40). Raise it when you need higher recall, lower it when you need lower latency:

SET hnsw.ef_search = 100;

IVFFlat partitions vectors into lists and only scans the nearest ones. It builds faster and uses less memory than HNSW, but has a worse speed-recall curve and — importantly — needs data present before you build it, because the partitions are trained on your actual vectors:

-- Load your rows first, then:
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);   -- ~rows/1000 up to 1M rows, ~sqrt(rows) beyond

Query-time recall is controlled by probes:

SET ivfflat.probes = 10;

The short version: default to HNSW. Reach for IVFFlat only when index build time or memory is a hard constraint, and you can accept lower recall. Match the operator class to your distance metric — vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>.

The filtered-search fix (pgvector 0.8)

Copy link

This deserves its own callout because it bit a lot of teams. Combining an ANN index with a WHERE clause used to risk overfiltering: the index returned its top-k candidates, the WHERE clause discarded most of them, and you ended up with far fewer rows than you asked for — sometimes zero. pgvector 0.8 fixed this with iterative index scans, which keep walking the index until enough rows survive the filter:

SET hnsw.iterative_scan = strict_order;   -- or relaxed_order for more speed

If your RAG retrieval filters by tenant, document type, recency, or permissions — and most production retrieval does — turn this on. It's the feature that makes the "one query with a WHERE clause" story above actually hold at scale.

From the application code

Copy link

The plumbing is the same in any language; register the type so the driver marshals vectors for you.

Python (psycopg 3):

import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("postgresql://localhost/app")
register_vector(conn)

query_vec = embed("how do I rotate API keys?")   # list[float], len == 1536

rows = conn.execute(
    """
    SELECT id, content, 1 - (embedding <=> %s) AS similarity
    FROM documents
    ORDER BY embedding <=> %s
    LIMIT 5
    """,
    (query_vec, query_vec),
).fetchall()

TypeScript (node-postgres):

import pg from "pg";
import pgvector from "pgvector/pg";

const client = new pg.Client();
await client.connect();
await pgvector.registerType(client);

const queryVec = pgvector.toSql(await embed("how do I rotate API keys?"));

const { rows } = await client.query(
  `SELECT id, content, 1 - (embedding <=> $1) AS similarity
   FROM documents
   ORDER BY embedding <=> $1
   LIMIT 5`,
  [queryVec],
);

That's the minimal reproducible example end-to-end: extension, schema, index, query, from SQL and from code.

Hybrid search: combining BM25 keyword matching with vector similarity

Copy link

Vector search understands meaning but is blind to exact tokens. Ask it for LISTEN/NOTIFY or an error code or a product SKU, and it happily returns "conceptually related" rows that don't contain the literal string you need. Lexical search is the mirror image: it nails exact terms and rare identifiers but has no idea that "real-time notifications" and "webhooks" are related. Hybrid search runs both and fuses the results, and for RAG, it reliably beats either approach alone.

Before the code, one honest caveat that a lot of tutorials skip. People say "BM25" loosely, but Postgres' built-in full-text search is not BM25. Native FTS (tsvector / tsquery with ts_rank / ts_rank_cd) does term matching well, but its ranking lacks the things that make BM25 good: corpus-wide inverse document frequency, term-frequency saturation, and document-length normalization. For a lot of RAG use cases, native FTS is good enough — and it requires zero extra extensions, which matters on locked-down managed Postgres. But know what you're using.

Option A: native FTS + vectors, fused with RRF (no extra extensions)

Copy link

Add a generated tsvector column and a GIN index alongside your vector column:

ALTER TABLE documents
    ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX documents_tsv_idx ON documents USING gin (tsv);

Now fuse the two rankings with Reciprocal Rank Fusion (RRF). RRF is the standard fusion method because it ignores the raw scores entirely — it only cares about rank position, so you never have to normalize incompatible score scales. The formula is score = Σ 1 / (k + rank), with k = 60 as the well-worn default:

WITH semantic AS (
    SELECT id,
           ROW_NUMBER() OVER (ORDER BY embedding <=> :query_vec) AS rank
    FROM documents
    ORDER BY embedding <=> :query_vec
    LIMIT 40
),
lexical AS (
    SELECT d.id,
           ROW_NUMBER() OVER (ORDER BY ts_rank_cd(d.tsv, q) DESC) AS rank
    FROM documents d, plainto_tsquery('english', :query_text) q
    WHERE d.tsv @@ q
    ORDER BY ts_rank_cd(d.tsv, q) DESC
    LIMIT 40
)
SELECT COALESCE(s.id, l.id) AS id,
       COALESCE(1.0 / (60 + s.rank), 0.0)
     + COALESCE(1.0 / (60 + l.rank), 0.0) AS rrf_score
FROM semantic s
FULL OUTER JOIN lexical l USING (id)
ORDER BY rrf_score DESC
LIMIT 10;

A document that ranks well in both lists rises to the top; a document strong in only one still appears, just lower. You can weight the two arms by multiplying each 1/(k+rank) term if you want to favor keywords or meaning.

Option B: real BM25 in Postgres (when ranking quality matters)

Copy link

When ts_rank quality isn't enough, you can get true BM25 inside Postgres via an extension rather than reaching for Elasticsearch. As of mid-2026, the main options are:

• pg_search (ParadeDB) — BM25 backed by Tantivy (Rust), with an Elastic-DSL-like query syntax and a @@@ operator. The most full-featured, ParadeDB also publishes a thorough hybrid-search guide. Note the managed-availability churn: Neon stopped offering pg_search to new projects in March 2026, so check your provider before committing.
• VectorChord-bm25 — a dedicated bm25vector type and Block-WeakAnd index, built like pgvector with a custom operator (<&>). Lightweight and fast; pairs with pg_tokenizer.rs for tokenization.
• pg_textsearch (Timescale/Tiger Data) — a USING bm25(content) index queried with the <@> operator. Note it returns the negative BM25 score (lower = better), the same convention as pgvector's distance operators.

With ParadeDB's pg_search, the lexical arm looks like this:

CREATE EXTENSION pg_search;

CREATE INDEX documents_bm25 ON documents
USING bm25 (id, content)
WITH (key_field = 'id');

SELECT id, content, paradedb.score(id) AS bm25_score
FROM documents
WHERE content @@@ :query_text
ORDER BY bm25_score DESC
LIMIT 40;

You'd then fuse this BM25 ranking with the vector ranking using the exact same RRF pattern from Option A. The decision between A and B is straightforward: start with native FTS, measure retrieval quality on your own corpus, and only add a BM25 extension if ranking quality is demonstrably holding you back. Don't pay the operational cost of an extra extension before you've confirmed you need it.

Wiring pgvector into a RAG pipeline

Copy link

A RAG pipeline is a loop: chunk → embed → store → retrieve → augment → generate. pgvector owns the middle of that loop. Here's the whole thing end to end.

Ingestion: chunk, embed, store

Copy link

from openai import OpenAI
import psycopg
from pgvector.psycopg import register_vector

client = OpenAI()
conn = psycopg.connect("postgresql://localhost/app")
register_vector(conn)

EMBED_MODEL = "text-embedding-3-small"

def embed(text: str) -> list[float]:
    resp = client.embeddings.create(model=EMBED_MODEL, input=text)
    return resp.data[0].embedding

def ingest(source_id: int, chunks: list[str]) -> None:
    with conn.cursor() as cur:
        for chunk in chunks:
            cur.execute(
                """
                INSERT INTO documents (source_id, content, embedding, metadata)
                VALUES (%s, %s, %s, %s)
                """,
                (source_id, chunk, embed(chunk),
                 {"model": EMBED_MODEL}),
            )
    conn.commit()

Two practical notes the tutorials gloss over. First, store the embedding model in metadata. The day you switch models, every vector in the table becomes incomparable to the new ones, and you need to know which rows to re-embed. Second, chunk for retrieval, not for storage — overlapping windows of a few hundred tokens generally retrieve better than whole documents, because a tighter chunk produces a more focused embedding.

Retrieval: where the JOIN advantage pays off

Copy link

This is the step that justifies the entire "one database" thesis. Retrieval isn't just nearest-neighbor — it's nearest-neighbor filtered by everything else you know: tenant, permissions, recency, document type. With pgvector, that's one query, and with iterative scans (above), the filter won't starve your results:

def retrieve(query: str, tenant_id: int, k: int = 5) -> list[dict]:
    query_vec = embed(query)
    with conn.cursor() as cur:
        cur.execute("SET hnsw.iterative_scan = strict_order")
        cur.execute(
            """
            SELECT d.content,
                   d.metadata,
                   1 - (d.embedding <=> %s) AS similarity
            FROM documents d
            JOIN sources s ON s.id = d.source_id
            WHERE s.tenant_id = %s
              AND s.is_published
            ORDER BY d.embedding <=> %s
            LIMIT %s
            """,
            (query_vec, tenant_id, query_vec, k),
        )
        cols = [c.name for c in cur.description]
        return [dict(zip(cols, row)) for row in cur.fetchall()]

Against a standalone VDB this is the two-roundtrip, filter-after-retrieval problem from the intro — now made concrete: you over-fetch candidates and hope enough survive the filter.

Augment and generate

Copy link

def answer(query: str, tenant_id: int) -> str:
    context_rows = retrieve(query, tenant_id)
    context = "\n\n---\n\n".join(r["content"] for r in context_rows)

    resp = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {"role": "system",
             "content": "Answer using only the context provided. "
                        "If the context doesn't cover it, say so."},
            {"role": "user",
             "content": f"Context:\n{context}\n\nQuestion: {query}"},
        ],
    )
    return resp.choices[0].message.content

For higher-quality retrieval, swap retrieve for the hybrid RRF query from the previous section — the rest of the pipeline doesn't change. That swappability is the practical reward of keeping everything in SQL: improving retrieval is editing one query, not re-architecting a service.

At what point does a dedicated VDB actually pay off?

Copy link

pgvector isn't always the answer, and pretending otherwise does no one any favors. Here's an honest map of where it stops being the obvious choice.

Where pgvector is comfortably enough. Up to a few million vectors, an HNSW index typically returns results in single-digit milliseconds — enough for the large majority of RAG features, recommendation systems, and semantic search. A single well-provisioned Postgres instance can serve on the order of thousands of queries per second on typical-dimension vectors. Treat these as order-of-magnitude estimates, not benchmarks: real numbers depend on dimensionality, ef_search, hardware, and filter selectivity, so measure on your own corpus. As a rough sense of scale, even a workload in the low tens of millions of vectors at moderate QPS — with latency and recall targets that aren't aggressive — still fits on a single Postgres instance, where cost is driven by ordinary database sizing rather than anything vector-specific, and with zero new systems to operate.

Where a dedicated VDB starts to earn it. A few signals, any one of which is worth taking seriously:

• Scale. Past ~10–50M vectors with demanding latency-and-recall targets, purpose-built engines (Qdrant, Milvus, Weaviate) pull ahead on the speed-recall curve, and Milvus specifically targets the very-large-scale, distributed end.
• Throughput. If you need tens of thousands of QPS at tight p99s, a dedicated engine is built for that; Postgres can be pushed there but it's not its home turf.
• Advanced retrieval features. Built-in cross-encoder reranking, multi-vector / late-interaction (ColBERT-style) retrieval, and managed autoscaling are first-class in some VDBs and bolt-on or absent in Postgres.
• You don't run Postgres. If Postgres isn't already in your stack, adding it just for vectors removes pgvector's main advantage, and a standalone VDB may be the simpler choice.

The middle path before you migrate. Before concluding "pgvector can't scale," look at pgvectorscale, which adds a StreamingDiskANN index and stays inside Postgres — it pushes the comfortable ceiling considerably higher without taking on a second datastore.

The decision usually comes down to operations more than benchmarks. If your team already runs Postgres, staying in Postgres means no new system to deploy, monitor, back up, secure, and reason about at 3 a.m. during an incident. That operational simplicity is worth a meaningful amount of raw QPS — and it's the part teams consistently under-price when they reach for a dedicated engine because a getting-started guide assumed they would.

The bottom line: if Postgres is already your source of truth, make it your vector store too. You get ACID guarantees, transactional consistency between your data and its embeddings, and filtered retrieval in a single query — one system instead of two. Start there; migrate to a dedicated VDB only when measured evidence says you must.