Build a RAG pipeline with Postgres and pgvector, step by step

RAG with pgvector keeps your embeddings in the Postgres database you already run. You add the vector extension, store each text chunk next to its embedding, index the column with HNSW or IVFFlat, and fetch the nearest chunks with an ORDER BY ... LIMIT query. Those chunks go into the model's prompt as context.

The examples below use pgvector 0.8.7, the version in the project's install instructions at the time of writing, and PostgreSQL 18 documentation.

How retrieval-augmented generation works in four steps

Retrieval-augmented generation (RAG) answers a question with your own documents as context. The pipeline has four steps:

1. Chunk and embed. Split documents into passages and turn each passage into a vector with an embedding model.
2. Store. Write each chunk's text, its vector and some metadata to a table.
3. Retrieve. Embed the user's question with the same model and find the chunks whose vectors sit closest to it.
4. Generate. Put the retrieved chunks into the prompt and ask the LLM to answer from them.

Postgres with pgvector handles steps 2 and 3. Steps 1 and 4 happen in your application code.

How do you install the pgvector extension?

pgvector supports Postgres 13 and later. The pgvector README (opens in a new tab) lists builds from source plus packages for Docker, Homebrew, APT, Yum, conda-forge and others. Many hosted Postgres providers ship it preinstalled.

On Debian or Ubuntu with the PostgreSQL APT repository, install the package that matches your server version (18 here):

sudo apt install postgresql-18-pgvector

To build from source on Linux or macOS:

cd /tmp
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # may need sudo

For a container, the project publishes images such as pgvector/pgvector:pg18-trixie. Then enable the extension once in each database that needs it:

CREATE EXTENSION IF NOT EXISTS vector;

A table for chunks and embeddings

A RAG schema needs the chunk text, the vector and enough metadata to filter and to rebuild later. Splitting documents and chunks into two tables keeps re-chunking simple.

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  source_url text NOT NULL,
  title      text,
  tenant_id  int  NOT NULL
);

CREATE TABLE chunks (
  id              bigserial PRIMARY KEY,
  document_id     bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  tenant_id       int    NOT NULL,
  chunk_index     int    NOT NULL,
  content         text   NOT NULL,
  embedding_model text   NOT NULL,
  embedding       vector(1536) NOT NULL,
  UNIQUE (document_id, chunk_index)
);

CREATE INDEX ON chunks (tenant_id);

The dimension in vector(1536) must match your embedding model. OpenAI's embeddings guide (opens in a new tab) lists 1,536 dimensions by default for text-embedding-3-small and 3,072 for text-embedding-3-large. Both models accept a dimensions parameter that returns shorter vectors.

That number matters for indexing. pgvector's HNSW and IVFFlat indexes accept vector columns of up to 2,000 dimensions. A 3,072-dimension column needs one of three workarounds: request fewer dimensions, store halfvec (indexable up to 4,000 dimensions), or index a half-precision expression.

CREATE INDEX ON chunks USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

Each vector value takes 4 * dimensions + 8 bytes, so a 1,536-dimension embedding is a little over 6 KB per row.

A minimal Python loader with psycopg 3 and the pgvector-python (opens in a new tab) package (pip install pgvector) looks like this:

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

client = OpenAI()
conn = psycopg.connect("dbname=app")
register_vector(conn)

def store_chunk(document_id, tenant_id, idx, text):
    emb = client.embeddings.create(
        model="text-embedding-3-small", input=text
    ).data[0].embedding
    conn.execute(
        """INSERT INTO chunks (document_id, tenant_id, chunk_index,
                               content, embedding_model, embedding)
           VALUES (%s, %s, %s, %s, %s, %s)""",
        (document_id, tenant_id, idx, text, "text-embedding-3-small", Vector(emb)),
    )

For large initial loads, the pgvector README recommends COPY and adding indexes after the data is in.

HNSW vs IVFFlat: which pgvector index should you use?

Without an index, pgvector runs an exact nearest-neighbor scan with perfect recall. Approximate indexes trade some recall for speed, and pgvector offers two.

HNSW builds a multilayer graph. The pgvector docs say it has a better speed-recall trade-off than IVFFlat, builds more slowly and uses more memory. It has no training step, so you can create it on an empty table.

IVFFlat splits vectors into lists and searches only the lists closest to the query. It builds faster and uses less memory, with lower query performance. It needs data before you build it, because the lists come from k-means over existing rows.

Create the index with the operator class that matches the operator you will query with. In production, build it concurrently so writes keep flowing:

SET maintenance_work_mem = '2GB';

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

The PostgreSQL CREATE INDEX reference (opens in a new tab) notes that CREATE INDEX CONCURRENTLY cannot run inside a transaction block and takes significantly longer than a normal build. pgvector builds HNSW much faster when the graph fits in maintenance_work_mem, and prints a notice when it stops fitting. Raising max_parallel_maintenance_workers can also shorten the build.

Which pgvector distance operator should you use?

pgvector defines one operator per distance function:

Use whatever distance your embedding model was trained for. OpenAI recommends cosine similarity and states its embeddings are normalized to length 1, so cosine and Euclidean distance produce identical rankings. For normalized vectors, the pgvector README says inner product gives the best performance.

<#> returns the negative inner product because Postgres index scans only run in ascending order. Multiply by -1 if you want the actual value. For cosine similarity, compute 1 - (embedding <=> query) in the select list.

The retrieval query for RAG with pgvector

The query embeds the question, then orders chunks by distance. pgvector uses the index only when the ORDER BY is a distance operator in ascending order with a LIMIT. Writing ORDER BY 1 - (embedding <=> $1) DESC skips the index.

SELECT c.id,
       c.content,
       d.source_url,
       1 - (c.embedding <=> $1::vector) AS cosine_similarity
FROM chunks c
JOIN documents d ON d.id = c.document_id
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $1::vector
LIMIT 8;

The WHERE clause is where many RAG setups lose results. With an approximate index, pgvector applies filters after scanning the index. The README's example: if a filter matches 10% of rows and hnsw.ef_search is at its default of 40, about 4 rows come back on average.

Since version 0.8.0, iterative index scans fix this by reading more of the index until enough rows match:

BEGIN;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SET LOCAL hnsw.ef_search = 100;
-- run the retrieval query here
COMMIT;

strict_order keeps exact distance order; relaxed_order allows slight reordering and gives better recall. Scans stop at hnsw.max_scan_tuples (20,000 by default) or ivfflat.max_probes. For a filter with few distinct values, a partial index works. For strict tenant isolation, pgvector suggests list partitioning or separate tables, because a shared index lets one tenant's vectors affect another's recall and speed.

Pure vector search can miss exact terms such as product codes. pgvector's docs suggest combining it with Postgres full-text search and merging the two result lists with Reciprocal Rank Fusion or a cross-encoder.

Chunking and re-indexing choices

pgvector stores whatever you give it, so chunk boundaries are an application decision. A few constraints are fixed. OpenAI's v3 embedding models accept at most 8,192 input tokens, which caps chunk size for those models. Every chunk you return also uses up prompt space, so LIMIT times chunk length must fit the context you plan to send.

Store the chunk position (chunk_index) and the source so the application can pull neighboring chunks or cite the page. Store embedding_model because vectors from different models are not comparable. Changing models means re-embedding every chunk.

Plan re-indexing around the index type:

• HNSW updates as rows arrive. Vacuuming an HNSW index can be slow; the README suggests REINDEX INDEX CONCURRENTLY before VACUUM.
• IVFFlat fixes its lists at build time. If the table grows far past the row count you sized lists for, drop and recreate the index with a new lists value.
• Model change: write new embeddings to a new column or table, build its index concurrently, switch queries over, then drop the old column.

Check build progress with pg_stat_progress_create_index, and use EXPLAIN (ANALYZE, BUFFERS) to confirm the retrieval query actually uses the index.