pgvector Tutorial: Build RAG with PostgreSQL, Step by Step

RAG (retrieval-augmented generation) means that before an LLM answers, you search your own documents for the passages closest to the question and put them in the prompt, so the answer comes from your data instead of the model's memory. pgvector adds a vector column type and similarity operators to PostgreSQL, so the passages, their embeddings and their metadata live in a database you already know how to run. Below: extension, schema, inserts, cosine search with <=>, an HNSW index, filters and sizing.

How RAG works: two pipelines

• Ingestion (once per document): split it into chunks → embed each chunk → store text, vector and metadata.

• Query (every question): embed the question with the same model → fetch the nearest chunks → put them and their sources in the prompt → the LLM answers with citations.

Answer quality depends mostly on retrieval: if the right chunk is not in the top five, the model cannot use it.

Why pgvector instead of a separate vector database

• One system: text, permissions, metadata and vectors sit in the same row, and filters are plain SQL WHERE and JOIN.

• Transactions: delete a document and ON DELETE CASCADE removes its chunks. No orphan vectors left in another service.

• Familiar operations: pg_dump, roles and monitoring work as before.

• Realistic scale: as a rule of thumb, one well-sized PostgreSQL server with HNSW handles a few million chunks comfortably; a dedicated vector database earns its place at tens of millions.

Step 1: Enable the extension

CREATE EXTENSION IF NOT EXISTS vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

The extension is called vector, even though the project is pgvector, and you enable it once per database. On Debian or Ubuntu with the PostgreSQL apt repository the package is postgresql-16-pgvector (match your major version). The iterative scans used in step 6 need version 0.8.0 or newer.

Step 2: A schema that matches your embedding model

The n in vector(n) must equal your embedding model's output size exactly, or inserts fail. Common sizes: 1536 for text-embedding-3-small, 1024 for multilingual models such as multilingual-e5-large and bge-m3, 384 for the English-focused all-MiniLM-L6-v2. For Arabic or mixed Arabic and English content, choose a multilingual model and test it on your own documents.

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  tenant_id  integer NOT NULL,
  title      text NOT NULL,
  source_url text,
  updated_at timestamptz NOT NULL DEFAULT now()
);

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

CREATE INDEX chunks_tenant_idx ON chunks (tenant_id);

Keep the raw text next to the vector: you need it for the prompt, and to re-embed everything if you ever change models. tenant_id is copied onto chunks so filtering does not need a join.

Step 3: Insert embeddings

pgvector reads vectors as text in square brackets, such as '[0.12, -0.03, 0.88]'. In an application, use the pgvector Python package with psycopg 3, which converts NumPy arrays for you:

pip install "psycopg[binary]" pgvector numpy

import numpy as np
import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("postgresql://rag:password@localhost/rag", autocommit=True)
register_vector(conn)

def embed(texts):
    """Call your embedding model; return one list of 1536 floats per text."""
    raise NotImplementedError

def add_document(tenant_id, title, url, chunks):
    vectors = embed(chunks)  # call the API before opening the transaction
    with conn.transaction():
        doc_id = conn.execute(
            "INSERT INTO documents (tenant_id, title, source_url) "
            "VALUES (%s, %s, %s) RETURNING id",
            (tenant_id, title, url),
        ).fetchone()[0]
        with conn.cursor() as cur:
            cur.executemany(
                "INSERT INTO chunks (document_id, tenant_id, chunk_index, content, embedding) "
                "VALUES (%s, %s, %s, %s, %s)",
                [(doc_id, tenant_id, i, text, np.array(vec))
                 for i, (text, vec) in enumerate(zip(chunks, vectors))],
            )

autocommit=True matters: without it, the type lookup in register_vector opens an implicit transaction and your inserts may never be committed. Embedding runs before the transaction so a slow API call holds no locks. For bulk loads, COPY is much faster than inserts.

Step 4: Similarity search with cosine distance

def search(tenant_id, question, k=5):
    q = np.array(embed([question])[0])
    return conn.execute(
        """
        SELECT c.content, d.title, d.source_url,
               1 - (c.embedding <=> %(q)s) AS similarity
        FROM chunks c
        JOIN documents d ON d.id = c.document_id
        WHERE c.tenant_id = %(tenant)s
        ORDER BY c.embedding <=> %(q)s
        LIMIT %(k)s
        """,
        {"q": q, "tenant": tenant_id, "k": k},
    ).fetchall()

<=> is cosine distance: 0 means the same direction and larger means less similar, so 1 - distance gives cosine similarity. pgvector also has <-> (Euclidean) and <#> (negative inner product); with normalized vectors all three rank the same, and cosine is the safe default. An index is only used when ORDER BY applies the same operator as the index, in ascending order, with a LIMIT.

In the prompt, include each chunk's title and URL, tell the model to answer only from that context and to say when the answer is missing, and drop chunks below a similarity threshold you calibrate on your own questions.

Step 5: Add an HNSW index

Without an index, PostgreSQL compares the question with every row. That exact search is fine up to tens of thousands of chunks. Beyond that, add HNSW:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 3;

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

-- per session or per transaction (SET LOCAL): more candidates = better recall
SET hnsw.ef_search = 100;

• m and ef_construction (defaults 16 and 64): higher values build slower, with better recall.

• hnsw.ef_search (default 40): candidates per query. Raise for recall, lower for speed.

• Build after the bulk load, and use CREATE INDEX CONCURRENTLY on a table that is serving traffic. Keep maintenance_work_mem well below the server's free RAM.

• Dimension limit: HNSW on vector supports up to 2,000 dimensions; for 3,072-dimension models, index a halfvec cast.

-- only if the column is vector(3072); query with the same cast in ORDER BY
CREATE INDEX ON chunks USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

Confirm the index is used with EXPLAIN ANALYZE: you should see "Index Scan using chunks_embedding_hnsw". IVFFlat builds faster with less memory, but needs data before you build it and usually trades speed and recall worse, so HNSW is the usual default.

Step 6: Metadata filters without losing results

Approximate indexes apply WHERE after the index scan. With ef_search at 40, the index returns about 40 candidates, the tenant filter removes rows from other tenants, and you may get two results instead of five. Fixes, in order of preference:

-- pgvector 0.8.0+: keep scanning the index until enough rows pass the WHERE filter
SET hnsw.iterative_scan = relaxed_order;

• Iterative scan (0.8.0+) continues through the index until enough rows match, up to hnsw.max_scan_tuples. relaxed_order may return results slightly out of order; strict_order keeps exact ordering.

• Small tenants are served by the B-tree on tenant_id: an exact search over just their rows is fast and fully accurate.

• Partial HNSW indexes (... WHERE tenant_id = 7) suit a handful of very large tenants, not hundreds.

Chunking: where RAG quality is won or lost

• Size: as a rule of thumb, 200 to 500 words per chunk with 10 to 15% overlap.

• Split on structure: headings and paragraphs, never mid-sentence. Prefix each chunk with its document and section title.

• One model for both sides: documents and questions use the same embedding model.

• Arabic text: if you normalize (alef forms, tatweel), do it identically for documents and questions, and test with the way your users really phrase things, Egyptian dialect included.

• Exact terms: embeddings are weak on product codes, invoice numbers and names. Combine them with keyword search (PostgreSQL full-text or pg_trgm) and merge the two result lists.

• Measure: keep 30 to 50 real questions with the chunk that should be found, and check the top-five hit rate after every change.

Backups and maintenance

pg_dump includes the vectors, but indexes are rebuilt on restore and HNSW builds take time, so test a real restore. Keep source documents so you can re-embed. When a document changes, replace its chunks in one transaction. For everything else, our guide to PostgreSQL tuning, backups and security on a VPS covers shared_buffers, automated dumps and access control.

How much server a pgvector RAG needs

• Storage per vector: 4 bytes × dimensions + 8 bytes. At 1536 dimensions that is about 6 KB, so 100,000 chunks hold roughly 600 MB of vectors and a million about 6 GB. The HNSW index adds a similar amount or more. halfvec halves the vector size.

• RAM: queries are fast when the index fits in memory. Rule of thumb: 4 GB of RAM for around 100,000 chunks at 1536 dimensions, 16 GB or more for a million.

• Embeddings: generate them through an API or with a small local model in CPU batches. IM HOST plans are CPU servers, which suits this; large local generation models need GPUs.

For the full picture of CPU, RAM and storage by workload, see our article on AI application hosting server requirements. IM HOST AI Agent Hosting comes with PostgreSQL and pgvector already installed, alongside Docker, Python and Node.js, with full root and free daily backups. To run the agent that uses this retrieval around the clock, follow our guide on deploying an AI agent 24/7 with Docker.

Frequently asked questions

What dimension should my vector column have?

Exactly the output size of your embedding model, for example 1536 for text-embedding-3-small or 1024 for bge-m3. Switching models usually means a new column size and re-embedding all chunks.

Is pgvector fast enough for production?

For most small and medium applications, yes. With an HNSW index that fits in RAM, retrieval usually takes a small fraction of the time the LLM spends writing the answer. Consider a dedicated vector database at tens of millions of vectors or very high query rates.

Does pgvector work with Arabic text?

pgvector stores numbers, so language support comes from the embedding model. Use a multilingual model, keep normalization consistent, and test retrieval with real Arabic questions before launch.

If you want a server where PostgreSQL and pgvector are ready on day one, look at AI Agent Hosting plans from IM HOST, starting from $8.99/mo. Bring your own embedding and LLM API keys; our engineers are available 24/7 if the setup gets stuck.