Build a RAG Stack on Your Own Server: Postgres, pgvector and an LLM

The vector database is the easy part of retrieval-augmented generation: a PostgreSQL extension and one index. What decides whether the system answers usefully is how documents are split before they are ever embedded, and that stage gets a fraction of the attention it deserves. This covers the whole pipeline, which parts genuinely need a GPU, and why six of the seven common failures are retrieval faults rather than model faults.

One consequence is worth stating before any code: you probably do not need a specialist vector database. If your data already lives in PostgreSQL, adding vector search is an extension and an index rather than another system to operate, back up and secure. That removes a whole category of work from the project, and it is the best decision available here.

MassiveGRID for retrieval-augmented generation: GPU instances with PyTorch, CUDA, Hugging Face libraries and vLLM pre-installed · A100 40 GB from $1,649/mo · A100 80 GB from $2,499/mo · H100 80 GB from $3,999/mo · placement in any of 85+ metros across 30+ countries

GPU cloud · Dedicated VPS for the database and embedding tier

The Four Components

A retrieval-augmented generation stack is a pipeline, and each stage can be swapped independently. Knowing which stage a failure belongs to is most of the debugging.

Only the last stage genuinely wants a GPU. Teams frequently buy GPU capacity for the whole pipeline and then discover the embedding tier was never the bottleneck.

Postgres and pgvector

Install the extension and declare a column with the dimension your embedding model produces. That dimension is fixed once data exists, so changing models later means a migration:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL,
  ordinal     int NOT NULL,
  content     text NOT NULL,
  metadata    jsonb NOT NULL DEFAULT '{}',
  embedding   vector(768) NOT NULL
);

Store the metadata. Source document, page or section, and a timestamp. Without it you can retrieve a relevant chunk and cannot tell the user where it came from, and an answer without a citation is not usable for anything that matters.

Three distance operators exist and they are not interchangeable. Use the one your embedding model was trained for, which for most modern models is cosine:

The Index Choice

Without an index, every query scans every row, which is fine up to a few thousand chunks and unusable past that. Two index types, with a genuine tradeoff:

-- HNSW: better recall, slower to build, more memory
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat: faster to build, less memory, needs data first
CREATE INDEX ON chunks USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);

HNSW is the better default now. IVFFlat has one property that catches people: it clusters existing data, so building it on an empty table produces a useless index, and the recommended lists value depends on row count. Build it after loading, and rebuild it after the table grows substantially.

Both are approximate. Recall is tunable at query time and costs latency:

SET hnsw.ef_search = 100;        -- higher = better recall, slower
SET ivfflat.probes = 20;         -- same tradeoff for IVFFlat

Measure recall against an exact scan on a sample rather than assuming the defaults are adequate. A system quietly returning the third-best chunk instead of the best produces answers that are subtly wrong, which is worse than answers that are obviously wrong.

Chunking Is the Whole Game

This is where retrieval quality is won or lost, and it gets a fraction of the attention the vector store does.

Chunks that are too large dilute the embedding: a 2,000-token chunk covering four topics has a vector that represents none of them well. Chunks that are too small lose the context that made them meaningful, so a sentence saying "this is not recommended" retrieves without whatever "this" referred to.

Practical starting points, to be tuned against your own corpus rather than adopted as truth:

Two techniques repay their cost. Prepend a small amount of context to each chunk, such as the document title and section heading, so the embedding carries where it came from. And overlap adjacent chunks slightly, so a fact spanning a boundary appears whole in at least one of them.

Retrieval That Actually Answers the Question

Pure vector search has a known weakness: it is poor at exact terms. A query containing a product code, an error string or a surname can miss the chunk containing it, because embeddings capture meaning and not spelling.

Hybrid search fixes most of that by combining vector similarity with PostgreSQL's own full-text search, which you already have:

WITH semantic AS (
  SELECT id, 1 - (embedding <=> $1) AS score
  FROM chunks ORDER BY embedding <=> $1 LIMIT 50
),
lexical AS (
  SELECT id, ts_rank(to_tsvector('english', content),
                     websearch_to_tsquery('english', $2)) AS score
  FROM chunks
  WHERE to_tsvector('english', content) @@ websearch_to_tsquery('english', $2)
  ORDER BY score DESC LIMIT 50
)
SELECT c.id, c.content, c.metadata,
       COALESCE(s.score, 0) * 0.7 + COALESCE(l.score, 0) * 0.3 AS combined
FROM chunks c
LEFT JOIN semantic s ON s.id = c.id
LEFT JOIN lexical  l ON l.id = c.id
WHERE s.id IS NOT NULL OR l.id IS NOT NULL
ORDER BY combined DESC
LIMIT 10;

Then rerank. Retrieve 30 to 50 candidates cheaply, pass them through a cross-encoder reranking model, and keep the top handful. Reranking is the single highest-return addition to a mediocre RAG system, because it reads the query and the chunk together rather than comparing two independently produced vectors.

Sizing the Stack

Vector storage is more modest than people expect. At 768 dimensions in single precision, each embedding is about 3 KB, so a million chunks is roughly 3 GB of vectors plus the text and the index.

What needs real memory is the index. HNSW is held in memory to be fast, so give PostgreSQL enough shared_buffers to hold it, and expect index build to be the slowest step in the pipeline.

Note the shape: the database and embedding tier costs tens of dollars and the generation tier costs hundreds or thousands. That asymmetry is the argument for keeping retrieval on a CPU instance and sizing the GPU only for generation, and for measuring whether you need a large model at all once retrieval is good.

The Failure Modes

Notice that six of seven are retrieval or data-preparation faults rather than model faults. The instinct when a RAG system disappoints is to reach for a bigger model, and it is usually the wrong lever.

Why Self-Host This

Because a retrieval system reads everything you point it at. An internal assistant over your documentation, contracts and support history is a system that has been given your whole corpus, and every query includes a fragment of it.

Self-hosting keeps the corpus and the queries on infrastructure you control, which for regulated data is the difference between a project that ships and one that stalls in review. Our guide to GPU hosting in Europe covers the jurisdictional side.

MassiveGRID GPU instances ship with PyTorch, CUDA, cuDNN, Hugging Face libraries and vLLM already installed, so the stack above is configuration rather than assembly. Put PostgreSQL and the embedding tier on a Dedicated VPS with guaranteed cores, because index builds are CPU-bound and contention makes them unpredictable. Both sit on Proxmox high-availability clusters with Ceph storage replicating every block three times.

Size the generation tier with our VRAM requirements guide, and serve it with vLLM once more than one person is asking questions.