pgvector on VPS: PostgreSQL Vector Database for AI Embeddings and RAG Applications

pgvector is a PostgreSQL extension that adds vector storage and similarity search — enabling you to store AI embeddings alongside your regular data in the same PostgreSQL database, and query them with cosine similarity, L2 distance, or inner product. Instead of running a separate vector database (Pinecone, Weaviate, Chroma), pgvector lets your existing PostgreSQL handle semantic search, recommendation systems, and RAG pipelines.

Why pgvector Over Dedicated Vector Databases

• Simplicity: One database for everything — vectors live with the data they describe, no sync needed
• SQL power: Combine vector search with SQL filters — “find similar documents written by user X after 2024”
• Transactions: ACID compliance — embeddings stay consistent with their source records
• Cost: No separate service — pgvector runs in your existing PostgreSQL instance
• Scale: Handles millions of vectors on a VPS with HNSW indexing

Step 1: Install pgvector

<code"># Ubuntu 22.04/24.04 — install pgvector from PGDG repository
sudo apt install -y postgresql-16-pgvector

# Verify installation
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS vector;" postgres

# For Docker:
# Use pgvector/pgvector:pg16 image instead of postgres:16

Step 2: Set Up the Database Schema

<code">sudo -u postgres psql

<code">CREATE DATABASE vectordb;
\c vectordb

CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table with embeddings
-- 1536 = OpenAI text-embedding-3-small dimensions
-- 768  = nomic-embed-text (Ollama) dimensions
-- 384  = all-MiniLM-L6-v2 dimensions
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    url         TEXT,
    source      TEXT,
    embedding   vector(1536),    -- Change to match your model's dimensions
    created_at  TIMESTAMP DEFAULT NOW(),
    metadata    JSONB DEFAULT '{}'
);

-- HNSW index for fast approximate nearest neighbor search
-- Better than IVFFlat for most use cases (lower latency, no training needed)
CREATE INDEX ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- For exact search on small datasets (no index needed):
-- Just use ORDER BY embedding <=> query_vector LIMIT 5

Step 3: Generate and Store Embeddings

With OpenAI API

<code">pip install openai psycopg2-binary

<code">import openai
import psycopg2
from psycopg2.extras import execute_values
import os

client = openai.OpenAI(api_key=os.environ['OPENAI_API_KEY'])
conn = psycopg2.connect(os.environ['DATABASE_URL'])

def embed_text(text: str) -> list[float]:
    """Generate embedding using OpenAI."""
    response = client.embeddings.create(
        model='text-embedding-3-small',  # 1536 dims, cost-effective
        input=text,
    )
    return response.data[0].embedding

def store_document(title: str, content: str, url: str = None):
    """Embed and store a document."""
    # Chunk long documents (embedding models have token limits)
    chunks = chunk_text(content, max_tokens=512)

    records = []
    for chunk in chunks:
        embedding = embed_text(chunk)
        records.append((title, chunk, url, embedding))

    with conn.cursor() as cur:
        execute_values(cur, """
            INSERT INTO documents (title, content, url, embedding)
            VALUES %s
        """, [(t, c, u, str(e)) for t, c, u, e in records])
    conn.commit()

def chunk_text(text: str, max_tokens: int = 512) -> list[str]:
    """Split text into overlapping chunks."""
    words = text.split()
    chunks = []
    for i in range(0, len(words), max_tokens - 50):  # 50-word overlap
        chunk = ' '.join(words[i:i + max_tokens])
        if chunk:
            chunks.append(chunk)
    return chunks

With Ollama (Local — No API Cost)

<code">import httpx

def embed_text_ollama(text: str) -> list[float]:
    """Generate embedding using local Ollama."""
    response = httpx.post('http://localhost:11434/api/embeddings', json={
        'model': 'nomic-embed-text',  # 768 dimensions
        'prompt': text,
    })
    return response.json()['embedding']

Step 4: Semantic Search (Core RAG Function)

<code">def semantic_search(query: str, top_k: int = 5, min_similarity: float = 0.7) -> list[dict]:
    """Find documents semantically similar to query."""
    query_embedding = embed_text(query)  # or embed_text_ollama(query)

    with conn.cursor() as cur:
        cur.execute("""
            SELECT
                id,
                title,
                content,
                url,
                1 - (embedding <=> %s::vector) AS similarity
            FROM documents
            WHERE 1 - (embedding <=> %s::vector) > %s
            ORDER BY embedding <=> %s::vector
            LIMIT %s
        """, (query_embedding, query_embedding, min_similarity,
               query_embedding, top_k))

        results = []
        for row in cur.fetchall():
            results.append({
                'id': row[0],
                'title': row[1],
                'content': row[2],
                'url': row[3],
                'similarity': float(row[4]),
            })
    return results

# Example: find documents about VPS security
results = semantic_search("how to secure a VPS server", top_k=3)
for r in results:
    print(f"{r['similarity']:.3f} — {r['title']}: {r['content'][:100]}...")

Step 5: Full RAG Pipeline

<code">def answer_with_rag(question: str) -> str:
    """Answer a question using RAG: retrieve context, then generate."""

    # 1. Retrieve relevant document chunks
    context_docs = semantic_search(question, top_k=5, min_similarity=0.6)

    if not context_docs:
        return "I don't have relevant information to answer this question."

    # 2. Build context string
    context = "\n\n".join([
        f"Source: {doc['title']}\n{doc['content']}"
        for doc in context_docs
    ])

    # 3. Generate answer using LLM with retrieved context
    response = client.chat.completions.create(
        model='gpt-4o-mini',
        messages=[
            {
                'role': 'system',
                'content': """You are a helpful assistant.
                Answer questions based on the provided context.
                If the context doesn't contain the answer, say so.
                Always cite your sources."""
            },
            {
                'role': 'user',
                'content': f"""Context:
{context}

Question: {question}

Answer based on the context above:"""
            }
        ],
        temperature=0.1,  # Low temperature for factual answers
    )

    return response.choices[0].message.content

# Usage
answer = answer_with_rag("What are the best practices for VPS backups?")
print(answer)

Step 6: Metadata Filtering with Vector Search

<code">-- Combine vector similarity with SQL filters
-- Find similar tech articles from the last 30 days
SELECT
    title,
    content,
    1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity,
    metadata->>'category' AS category
FROM documents
WHERE
    metadata->>'category' = 'technology'
    AND created_at > NOW() - INTERVAL '30 days'
    AND 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) > 0.7
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Index Types: HNSW vs IVFFlat

Getting Started

pgvector adds minimal overhead to an existing PostgreSQL installation — the extension itself uses no additional RAM; only the stored vectors and HNSW index consume space. On a 4 GB Ubuntu VPS at VPS.DO, pgvector with 1 million 1536-dimensional vectors uses approximately 6 GB disk space and 500 MB RAM for the HNSW index. For most RAG applications (corporate knowledge bases, documentation search, support systems), 100,000–500,000 vectors is typical — well within a 2 GB VPS capacity.

Conclusion

pgvector transforms your existing PostgreSQL into a capable vector database, eliminating the need for Pinecone ($70/month) or Weaviate for most RAG applications. The combination of vector similarity search with SQL filtering — finding semantically similar documents that also match category, date, or user ownership criteria — is pgvector’s key advantage over standalone vector databases. For teams already running PostgreSQL on a VPS, pgvector is a one-command extension installation away from enabling AI semantic search.