Set Up pgvector for RAG on a PostgreSQL VPS

RAG succeeds or fails on one thing: can you pull the right chunks fast enough to fit the model’s context window? This walkthrough shows you how to set up pgvector for RAG on a PostgreSQL VPS. You’ll store embeddings, index them, and query them efficiently from an application.

You’ll end up with a production-shaped baseline: PostgreSQL + pgvector, a clean schema for documents and chunks, an HNSW index for similarity search, and a small API example.

No mystery components. Just infrastructure you control.

What you’ll build (and what you won’t)

• Build: A PostgreSQL database that stores text chunks + embeddings and supports fast nearest-neighbor search for RAG.
• Build: A repeatable schema + indexing strategy and queries you can drop into a web app.
• Won’t build: A full chatbot UI. We’ll focus on the retrieval layer you can wire into any LLM.

Infrastructure requirements (VPS sizing that won’t disappoint)

pgvector doesn’t need a GPU. Your embedding generator might, but you can also generate embeddings on CPU.

Another option is to run embeddings as a separate service.

For the database host, size for RAM, disk IOPS, and steady CPU.

• OS: Ubuntu LTS (recommended), Debian, AlmaLinux/Rocky also fine.
• PostgreSQL: Any supported distro package version is fine; newer generally performs better for indexing and query planning.
• CPU: 2–4 vCPU to start. Similarity search is CPU-heavy under load.
• RAM: 4–8 GB minimum; 16 GB if you expect a lot of concurrent queries.
• Disk: NVMe strongly preferred. Vector indexes can get large, and random reads matter.

If you run your app and database on the same machine, reserve extra RAM.

Plan for your runtime (Node/Python) and any caching you add.

A straightforward option is a HostMyCode VPS dedicated to PostgreSQL. Or use managed VPS hosting if you want OS maintenance and baseline hardening handled while you build.

Step 1: Create a VPS baseline (network + access)

If this PostgreSQL instance exists only for your application, treat it like an internal service.

Don’t treat it like a public endpoint.

• Allow SSH from your admin IPs.
• Allow PostgreSQL (5432) only from your app servers (or a VPN).
• Block public access by default.

On Ubuntu with UFW, a sensible baseline looks like this:

sudo apt update
sudo apt install -y ufw

# SSH
sudo ufw allow 22/tcp

# PostgreSQL: lock down to a specific app server IP
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp

sudo ufw enable
sudo ufw status verbose

If you want the database completely off the public internet, put admin and app traffic over a VPN.

HostMyCode has a walkthrough for WireGuard on an Ubuntu VPS.

Step 2: Install PostgreSQL packages

Install PostgreSQL from your distro packages. On Ubuntu:

sudo apt update
sudo apt install -y postgresql postgresql-contrib

Confirm the service is running:

sudo systemctl status postgresql --no-pager

Create a database and user for your application:

sudo -u postgres psql

CREATE USER raguser WITH PASSWORD 'change-me-long-random';
CREATE DATABASE ragdb OWNER raguser;
\q

Use a password manager and generate a long secret.

Database credentials are production secrets, even for “internal” services.

Step 3: Install pgvector (package route, then verify)

pgvector is usually available as a distro package, but the name can vary.

On Ubuntu/Debian, try:

sudo apt install -y postgresql-pgvector

If your distro doesn’t ship it, you can build from source.

Most teams should stick to packages so updates stay routine.

Enable the extension in your database:

psql "postgresql://raguser:change-me-long-random@127.0.0.1:5432/ragdb"

CREATE EXTENSION IF NOT EXISTS vector;
\dx
\q

You should see vector listed in \dx.

Step 4: Design tables for RAG (documents, chunks, embeddings)

Most RAG systems retrieve at the chunk level. Keep document metadata separate.

Store chunk text and its embedding alongside it.

Create tables like this (pick a dimension that matches your embedding model; common sizes include 384, 768, 1024, 1536).

The example below uses 768:

psql "postgresql://raguser:change-me-long-random@127.0.0.1:5432/ragdb"

BEGIN;

CREATE TABLE IF NOT EXISTS documents (
  id           bigserial PRIMARY KEY,
  source       text NOT NULL,          -- e.g., URL, file path, customer, repo
  title        text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS chunks (
  id           bigserial PRIMARY KEY,
  document_id  bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index  integer NOT NULL,
  content      text NOT NULL,
  -- Keep optional metadata simple; JSONB is fine but don't overdo it
  meta         jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Vector column with fixed dimensions
CREATE TABLE IF NOT EXISTS embeddings (
  chunk_id     bigint PRIMARY KEY REFERENCES chunks(id) ON DELETE CASCADE,
  embedding    vector(768) NOT NULL
);

-- Helpful for filtering / housekeeping
CREATE INDEX IF NOT EXISTS idx_chunks_document_id ON chunks(document_id);

COMMIT;

Why separate embeddings? It lets you re-embed later without rewriting chunk rows.

It also lets you drop and rebuild vector indexes without touching the chunk text table.

Step 5: Create a vector index for fast similarity search

pgvector supports multiple indexing approaches. For most RAG workloads, HNSW is the right starting point.

It gives fast top-K nearest neighbors and stays reasonable as the dataset grows.

Create the index (expect this to take a while if you already loaded a lot of rows):

psql "postgresql://raguser:change-me-long-random@127.0.0.1:5432/ragdb"

-- For cosine similarity use vector_cosine_ops
CREATE INDEX IF NOT EXISTS idx_embeddings_hnsw_cosine
  ON embeddings
  USING hnsw (embedding vector_cosine_ops);

ANALYZE embeddings;

Match the distance metric to your embedding model. Cosine is common.

If you normalize embeddings (unit length), cosine and inner product behave similarly. Don’t assume.

Confirm what your embedding generator expects.

Step 6: Insert a small dataset (manual test before coding)

Before you write application code, prove the schema and query pattern work end to end.

Start with a couple of rows.

psql "postgresql://raguser:change-me-long-random@127.0.0.1:5432/ragdb"

INSERT INTO documents (source, title)
VALUES ('https://example.com/docs/faq', 'FAQ')
RETURNING id;

Assume it returned 1. Add two chunks:

INSERT INTO chunks (document_id, chunk_index, content, meta)
VALUES
  (1, 0, 'Reset your password from the account settings page.', '{"section":"auth"}'),
  (1, 1, 'Billing invoices are available under Billing → Invoices.', '{"section":"billing"}')
RETURNING id;

Now insert embeddings.

For a quick sanity check, you can insert short vectors only if your dimension matches.

Since we defined vector(768), you must insert 768 numbers. In a real pipeline, your app handles this.

If you want a quick manual test without generating real embeddings, create a separate test table with a smaller dimension (for example, 3).

Then validate the query shape:

DROP TABLE IF EXISTS embeddings_test;
CREATE TABLE embeddings_test (
  id bigserial PRIMARY KEY,
  content text NOT NULL,
  embedding vector(3) NOT NULL
);

INSERT INTO embeddings_test (content, embedding)
VALUES
('password reset steps', '[0.9, 0.1, 0.0]'),
('invoice download',     '[0.1, 0.9, 0.0]'),
('refund policy',        '[0.0, 0.8, 0.2]');

CREATE INDEX embeddings_test_hnsw
  ON embeddings_test
  USING hnsw (embedding vector_cosine_ops);

ANALYZE embeddings_test;

Run a similarity search (top 2):

SELECT id, content,
       1 - (embedding <=> '[0.05, 0.95, 0.0]') AS cosine_similarity
FROM embeddings_test
ORDER BY embedding <=> '[0.05, 0.95, 0.0]'
LIMIT 2;

You should see invoice-related rows at the top.

The <=> operator is cosine distance here because the index uses vector_cosine_ops.

Step 7: Query pattern for RAG (similarity + filters + joins)

Retrieval is rarely “nearest vectors only.” In practice, you also need:

• Top-K nearest chunks for a query embedding
• Filters (source, customer, product, language, publish status)
• Stable joins back to chunk text and document metadata

Here’s a query template you can adapt:

-- $1: query_embedding (vector)
-- $2: top_k (int)
-- Optional: filter by document source, tenant, etc.

SELECT
  c.id AS chunk_id,
  d.id AS document_id,
  d.source,
  d.title,
  c.chunk_index,
  c.content,
  1 - (e.embedding <=> $1) AS similarity
FROM embeddings e
JOIN chunks c ON c.id = e.chunk_id
JOIN documents d ON d.id = c.document_id
-- WHERE d.source LIKE 'https://example.com/docs/%'
ORDER BY e.embedding <=> $1
LIMIT $2;

Keep LIMIT tight. Many pipelines work best with top_k around 4–12.

You can optionally follow with a re-ranker.

Pulling 50 chunks often burns tokens and blurs the answer.

Step 8: Wire it into an app (Node.js example)

Below is a minimal Node.js retrieval service. It accepts text, calls an embedding generator, then queries Postgres via pgvector.

Swap the embedding generator for whatever you run.

That might be a self-hosted model endpoint, a local Ollama instance, or a separate embeddings service.

If you plan to self-host the LLM side too, HostMyCode has a guide to running Ollama behind Nginx and TLS.

For a web UI, see Open WebUI with Ollama. pgvector sits behind both cleanly.

Install dependencies:

mkdir rag-pgvector-api && cd rag-pgvector-api
npm init -y
npm install express pg

Create server.js:

const express = require('express');
const { Pool } = require('pg');

const app = express();
app.use(express.json());

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
});

// Replace this with your real embedding call.
// It must return an array of numbers with the same dimension as vector(768).
async function embedText(_text) {
  throw new Error('Implement embedText(): call your embedding model and return a 768-dim vector');
}

app.post('/search', async (req, res) => {
  try {
    const { query, top_k = 6 } = req.body;
    if (!query || typeof query !== 'string') {
      return res.status(400).json({ error: 'query must be a string' });
    }

    const embedding = await embedText(query);

    const sql = `
      SELECT
        c.id AS chunk_id,
        d.source,
        d.title,
        c.chunk_index,
        c.content,
        1 - (e.embedding <=> $1::vector) AS similarity
      FROM embeddings e
      JOIN chunks c ON c.id = e.chunk_id
      JOIN documents d ON d.id = c.document_id
      ORDER BY e.embedding <=> $1::vector
      LIMIT $2;
    `;

    const { rows } = await pool.query(sql, [embedding, top_k]);
    res.json({ results: rows });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(3000, () => console.log('RAG search API listening on :3000'));

Run it:

export DATABASE_URL='postgresql://raguser:change-me-long-random@127.0.0.1:5432/ragdb'
node server.js

Important limitation: pgvector stores and searches vectors. It doesn’t create them.

You still have to implement embedText() with an embeddings model and keep the dimension consistent with your vector(N) column.

Step 9: Production hardening for a pgvector-backed RAG database

RAG pipelines often touch sensitive text: internal docs, tickets, customer messages, code.

Treat your vector database like a primary datastore. Don’t treat it like a throwaway cache.

• Network isolation: Don’t expose Postgres to the public internet. Restrict 5432 with UFW/security groups or a VPN.
• TLS for Postgres: Enable SSL for client connections if traffic crosses a network boundary.
• Least privilege: Give your app user only what it needs (often SELECT/INSERT on specific tables).
• Backups: Dump the database or use physical backups; test restore monthly.
• Retention: Decide how long to keep chunks and embeddings, especially for user-generated uploads.

If you want a structured restore plan, follow a runbook approach similar to this disaster recovery tutorial and adapt it for PostgreSQL.

Step 10: Performance tuning that matters for similarity search

Generic Postgres tuning tips only go so far.

For RAG retrieval, you’ll hit a few specific bottlenecks first.

• IOPS first: Put your database on NVMe storage. Vector indexes amplify random reads.
• Keep top-K small: Large LIMIT values waste CPU and increase tail latency.
• Analyze regularly: Run ANALYZE after bulk loads so the planner stays honest.
• Batch inserts: Insert chunks and embeddings in batches to reduce transaction overhead.

Two quick diagnostics to run during load testing:

-- Check if queries are using the vector index
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM embeddings_test
ORDER BY embedding <=> '[0.05, 0.95, 0.0]'
LIMIT 5;

-- Basic view of slow queries if you enable pg_stat_statements later
SELECT now();

On a VPS, add monitoring early. You want visibility into CPU, RAM pressure, and disk IO before users complain.

Netdata is a solid default. See HostMyCode’s monitoring setup guide.

Common pitfalls (and how to avoid them)

• Embedding dimension mismatch: If your model outputs 1024 dims and your column is vector(768), inserts fail. Choose the model first, then lock the schema.
• Mixing models in one column: Don’t store embeddings from different models in the same vector column unless you also separate them by table or a strict filter.
• No chunking strategy: Stuffing whole documents into one embedding hurts retrieval. Chunk by headings/paragraphs and keep chunks roughly consistent in size.
• Indexing too early: Bulk load first, then build HNSW indexes. Rebuilding is sometimes faster than incrementally updating during massive imports.

Where to go next (RAG stack choices that fit a VPS)

pgvector is a strong default if you already trust PostgreSQL with application data and want fewer moving parts.

If you decide you want a dedicated vector engine, Qdrant is a common choice and works well behind a reverse proxy; HostMyCode has a tutorial for running Qdrant on a VPS.

If you plan to expose an internal “AI endpoint” for embeddings or chat, put it behind TLS, rate limit it, and log requests.

The same Nginx patterns you use for web apps apply here; see Nginx rate limiting.

Summary

You’ve set up PostgreSQL with pgvector, created a RAG-friendly schema, built an HNSW index, and validated the query pattern you’ll use in a chat or search app.

The operational rules are simple: keep your embedding model choice stable, keep top-K tight, and watch disk IO like a first-class metric.

If you want this on predictable infrastructure with room to grow, start with a HostMyCode VPS, and move to dedicated servers once your vector index and query volume justify it.

FAQ

Should I run Postgres + pgvector on the same VPS as my app?

You can for small deployments. Over time, you’ll likely hit self-inflicted “noisy neighbor” issues (CPU spikes, memory pressure, disk contention).

For steadier latency, split app and database once traffic picks up.

Do I need a GPU to use pgvector for RAG?

No. pgvector runs on CPU. GPUs help with embedding generation speed and LLM inference, which you can run on a separate host.

How big will my embeddings table get?

Rule of thumb: embedding storage scales with (number of chunks × dimension × 4 bytes) plus index overhead.

Plan for index growth; NVMe usually matters more than raw CPU once the dataset grows.

What’s the safest way to expose this to my application?

Keep PostgreSQL private, allow 5432 only from your app server IPs or over WireGuard, and use a dedicated DB user with least privilege.

Avoid public database endpoints.

Can I do multi-tenant RAG in one database?

Yes, but enforce tenant filtering in every query (and ideally at the row level).

Don’t rely on the application layer alone if you’re storing customer data.