Add pgvector To PostgreSQL On Ubuntu For RAG

RAG performs best when embeddings live next to your application data. If you already run PostgreSQL for your site or API, storing vectors in the same database removes a moving part (a separate vector DB). Backups get simpler. Monitoring stays in one place. You can also reuse existing roles and access rules.

This walkthrough shows how to install pgvector on Ubuntu, store embeddings, build indexes, and keep resource usage sane on a real VPS.

Assumptions: you manage an Ubuntu server (or a managed setup where you can run apt and edit PostgreSQL config). If you want a clean environment for this stack, a HostMyCode VPS is a good fit. You control packages, firewall rules, and Postgres settings end-to-end.

What you’ll build (and what it’s good for)

You’ll add pgvector to an existing PostgreSQL instance. You’ll create a table for embeddings and query it with cosine distance for semantic search. The result supports the usual RAG loop:

• Store per-document chunk embeddings in Postgres.
• Run a nearest-neighbor query to fetch the best matches.
• Send those chunks to your LLM (local or hosted) as context.

pgvector won’t rescue sloppy chunking or noisy source data. It also won’t make slow storage fast. Treat it as a PostgreSQL extension that performs well when the table is sized sensibly and indexed correctly.

Prerequisites and sizing for a VPS

Start with a VPS that can index without dropping into swap. For a typical “site + API + RAG” workload, these baselines are realistic:

• Minimum: 2 vCPU, 4 GB RAM, NVMe storage (fine for small datasets and low QPS).
• Recommended: 4 vCPU, 8–16 GB RAM, NVMe (steadier latency during index builds and traffic spikes).
• Storage: plan for embeddings. As a rough rule, a 768-dim float vector is 768 × 4 bytes ≈ 3 KB per row, before overhead and indexing.

You can run PostgreSQL on the same host as the app to keep networking simple.

If you split app and DB onto separate VPSes, treat latency as a first-class constraint. Nearest-neighbor queries feel it quickly.

If PostgreSQL isn’t installed yet, follow Install PostgreSQL on Ubuntu and secure remote access first, then come back here.

Step 1: Confirm your PostgreSQL version and Ubuntu release

On Ubuntu, pgvector packages are usually tied to the Postgres major version. Check both before you change anything:

lsb_release -a
psql --version
sudo -u postgres psql -c "SHOW server_version;"

You’ll see a major version like 14/15/16+. You’ll use that number in the package name.

Step 2: Install pgvector packages on Ubuntu

You have two common options:

• Ubuntu repo packages (quick and usually fine): install postgresql-XX-pgvector if your release ships it.
• PGDG repo packages (broader version coverage): install Postgres from the PostgreSQL APT repository, then install the matching pgvector package.

Start with your distro repositories. Replace 16 with your Postgres major version:

sudo apt update
sudo apt install -y postgresql-16-pgvector

If APT can’t find the package, your current repositories probably don’t include pgvector for that Postgres version. At that point you can:

• Upgrade/downgrade Postgres to a version your Ubuntu release packages with pgvector, or
• Install Postgres from the PGDG APT repository and then install the matching pgvector package.

Repository changes and major upgrades are operational work. If this is production, plan a maintenance window before you swap repos or jump Postgres versions.

Step 3: Enable the extension in your database

Installing the package adds files on disk. You still need to enable the extension per database.

sudo -u postgres psql

Inside psql, connect to the target database (example: appdb) and enable pgvector:

\c appdb
CREATE EXTENSION IF NOT EXISTS vector;
\dx

You should see vector in the installed extensions list.

Step 4: Create a table for documents and embeddings

For RAG, store chunked text with a little metadata. Keep the schema predictable. That makes filtering and debugging easier.

Example schema using a 768-dimension embedding (adjust to your embedding model):

CREATE TABLE IF NOT EXISTS rag_chunks (
  id bigserial PRIMARY KEY,
  source text NOT NULL,
  source_id text NOT NULL,
  chunk_index integer NOT NULL,
  content text NOT NULL,
  content_tokens integer,
  embedding vector(768) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS rag_chunks_unique
  ON rag_chunks (source, source_id, chunk_index);

Notes:

• source could be docs, wiki, support, blog, product.
• source_id is your stable identifier (URL, page ID, ticket ID).
• content_tokens makes it easier to cap context size later.

Step 5: Insert vectors (a practical, copy/paste format)

pgvector accepts vector literals like '[0.1, 0.2, ...]'. In practice, your app generates embeddings and writes them to Postgres.

Minimal insert example (shortened vector for readability; real embeddings must match the declared dimension):

INSERT INTO rag_chunks (source, source_id, chunk_index, content, content_tokens, embedding)
VALUES (
  'docs',
  'getting-started',
  0,
  'How to reset your API token...',
  120,
  '[0.0123, -0.0441, 0.0987]'::vector
);

If you hit a dimension error, it’s usually one of these:

• You declared vector(768) but your app produced 384/1024 dims.
• You accidentally truncated the array during serialization.

If you want an embeddings endpoint you control (so data stays inside your infrastructure), you can run local embeddings with Ollama. That setup pairs cleanly with pgvector. Retrieval and inference can live on your servers. See Self-host a text embeddings API with Ollama.

Step 6: Query similar chunks with cosine distance

pgvector provides distance operators. The two patterns you’ll use most often:

• Cosine distance for semantic similarity (commonly used with normalized embeddings).
• L2 distance for raw vector space distance.

Example: fetch the top 5 nearest chunks using cosine distance (<=>). Pass your query embedding as a vector:

SELECT
  id,
  source,
  source_id,
  chunk_index,
  left(content, 240) AS preview,
  1 - (embedding <=> '[...]'::vector) AS cosine_similarity
FROM rag_chunks
WHERE source = 'docs'
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;

Tip that pays off immediately: filter by source (or tenant/account) before sorting by distance.

This reduces the search space and avoids mixing unrelated content.

Step 7: Add a vector index (IVFFlat) and understand the trade-off

Without an index, Postgres scans every row to find neighbors. That’s fine for a few thousand chunks. At hundreds of thousands, it becomes a bottleneck.

IVFFlat is an approximate nearest neighbor index. It speeds queries up, but you may lose a bit of recall. For RAG, that trade-off is usually acceptable if your chunking is decent and you rerank in the app.

Create an IVFFlat index using cosine distance ops:

CREATE INDEX IF NOT EXISTS rag_chunks_embedding_ivfflat
ON rag_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

How to pick lists:

• Higher lists can improve recall but costs RAM and build time.
• For small datasets, don’t over-tune. Start at 50–200 and measure.

Important: after the index build, run ANALYZE so the planner has fresh stats:

ANALYZE rag_chunks;

At query time, tune probing. More probes increases accuracy but uses more CPU:

SET ivfflat.probes = 10;

SELECT id, source_id
FROM rag_chunks
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;

If the planner ignores your index, check the basics:

• Did you choose the right operator class (vector_cosine_ops vs vector_l2_ops)?
• Did you run ANALYZE?
• Is the table still small enough that a scan is cheaper?

Step 8: Postgres settings that matter for pgvector on a VPS

Vector search stresses memory during index builds and CPU during queries. You don’t need exotic tuning. You do need guardrails so Postgres doesn’t overcommit RAM.

Set a realistic work_mem (and don’t crank it blindly)

work_mem is per operation, per connection. One query can use it multiple times. If you set it too high, a busy server will swap.

Edit postgresql.conf (path varies, commonly /etc/postgresql/<major>/main/postgresql.conf):

sudoedit /etc/postgresql/16/main/postgresql.conf

Conservative starting points:

• 4 GB RAM VPS: work_mem = 16MB
• 8–16 GB RAM VPS: work_mem = 32MB (increase only if you understand connection counts)

Give Postgres enough shared_buffers to avoid disk thrash

A common baseline is ~25% of RAM on a dedicated DB box. On a VPS that also runs web and app services, aim lower.

• 4 GB “mixed role” VPS: try shared_buffers = 512MB
• 8 GB DB-focused VPS: try shared_buffers = 2GB

Limit connections to protect memory

Too many client connections will wreck latency even when CPU looks fine. Set a cap, then add pooling if you need it.

• Small VPS: max_connections = 100 (or less)
• API-heavy apps: consider a pooler (outside this tutorial)

Reload Postgres after config edits:

sudo systemctl reload postgresql

Step 9: Lock down network access (don’t expose your vector DB)

RAG collections are often sensitive. They can include internal docs, support tickets, and customer email. Treat the database like private infrastructure.

• Bind Postgres to private interfaces where possible.
• Allow only app servers (or your VPN) to reach port 5432.
• Use TLS for remote connections if traffic crosses networks you don’t fully control.

If you use UFW on Ubuntu, start with “deny by default, allow from your app server IP.” Example:

sudo ufw default deny incoming
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo ufw enable
sudo ufw status verbose

For public endpoints (LLM APIs, chat UIs), enforce auth and rate limits at the proxy layer. Many teams put Open WebUI behind Nginx for the same reasons. The same pattern applies to anything exposed. You can reuse the proxy structure from Run Open WebUI with Ollama behind Nginx.

Basic automated bans help too if you’re seeing brute-force patterns at the edge. See Set up Fail2ban for Nginx on Ubuntu VPS.

Step 10: Build a simple RAG retrieval function (optional, but useful)

A SQL function gives your app a stable retrieval interface. It also makes later changes easier. This includes tenant filters, language, and doc type.

Example function that returns top matches for a given source:

CREATE OR REPLACE FUNCTION rag_search(
  p_source text,
  p_query vector,
  p_limit integer DEFAULT 5
)
RETURNS TABLE (
  id bigint,
  source_id text,
  chunk_index integer,
  content text,
  score double precision
)
LANGUAGE sql
AS $$
  SELECT
    rc.id,
    rc.source_id,
    rc.chunk_index,
    rc.content,
    1 - (rc.embedding <=> p_query) AS score
  FROM rag_chunks rc
  WHERE rc.source = p_source
  ORDER BY rc.embedding <=> p_query
  LIMIT p_limit;
$$;

Test it (set probes in the session if you’re using IVFFlat):

SET ivfflat.probes = 10;
SELECT * FROM rag_search('docs', '[...]'::vector, 5);

Step 11: Backups you can restore (vectors included)

Vectors are just rows in Postgres. If you use pg_dump, they get backed up with the rest of the table.

The part teams skip is proving the restore works. Build a routine that creates a dump, restores into a scratch database, and checks row counts. For a proven workflow, follow Set up PostgreSQL backups with pg_dump and a restore test.

One operational warning: IVFFlat indexes can be large. Logical restores rebuild indexes, which can take serious time and CPU. Your DR plan should include that rebuild window.

Step 12: Honest limitations (so you don’t paint yourself into a corner)

• Index build time: creating IVFFlat indexes on large tables is not instant. Do it during low-traffic windows.
• RAM pressure: Postgres plus your app plus embedding generation can push a small VPS into swap. If swap starts thrashing, latency spikes.
• Approximate search: IVFFlat trades recall for speed. You’ll need to tune lists and ivfflat.probes for your dataset.
• Multi-tenant hygiene: if you host multiple customers, always filter by tenant before you order by distance.

Where to run this in production (VPS vs managed vs dedicated)

If pgvector supports a small internal RAG tool, a single VPS is often enough. If it becomes customer-facing search, you’ll want steadier I/O and more headroom.

• Single VPS: simplest and cheapest. Good for prototypes and low traffic.
• Split app + DB: cleaner isolation and safer scaling. Common for production SaaS.
• Dedicated server: best when you need sustained CPU and NVMe throughput for indexing and higher QPS.

If you’d rather not handle OS and database updates yourself, managed VPS hosting can reduce the maintenance load. You still get the flexibility to run pgvector and your app stack the way you want.

Summary

You installed pgvector, created a vector column, added an IVFFlat index, and put basic guardrails in place. These steps help PostgreSQL stay stable on a VPS.

Next comes application wiring: embed the user query, call rag_search(), and pass the top chunks to your LLM.

If you’re building this for real users, run it on infrastructure you control. That gives you room to tune memory, backups, and network access. A HostMyCode VPS is a straightforward home for Postgres + pgvector. You can scale up to dedicated servers later if indexing and query load outgrow a virtual machine.

FAQ

Do I need a GPU to use pgvector for RAG?

No. pgvector runs inside PostgreSQL and uses CPU. Your embedding generation step might use a GPU if you run it locally, but retrieval itself is CPU + RAM + disk I/O.

What embedding dimension should I choose?

Match your embedding model output. Common sizes are 384, 768, and 1024. Pick the model first, then declare vector(N) accordingly. Changing dimensions later means rewriting data.

Why is my query slow even with an IVFFlat index?

Common causes: you didn’t run ANALYZE, your ivfflat.probes is too high for your CPU, your storage is slow, or you’re sorting across tenants/sources instead of filtering first.

Should I store vectors in the same database as my app tables?

Often yes, especially for small teams. It simplifies backups and permissions. If vectors grow quickly or indexing affects your OLTP workload, move embeddings into a separate database (or at least a separate Postgres instance) on its own VPS.

How do I keep AI crawlers away from my RAG content pages?

If your content is on the public web, start by controlling crawler access with robots.txt. Use this AI crawlers blocklist approach and tailor it to your site’s risk tolerance.