Self-Hosted pgvector for RAG Applications

docker run -d \
  --name pgvector \
  -e POSTGRES_PASSWORD=yourpassword \
  -e POSTGRES_DB=ragdb \
  -v pgvector-data:/var/lib/postgresql/data \
  -p 5432:5432 \
  ankane/pgvector:latest

Wait 10 seconds for initialization, then verify:

docker exec -it pgvector psql -U postgres -d ragdb -c "SELECT * FROM pg_extension WHERE extname = 'vector';"

You should see one row with extname = vector.

Connect to the database:

docker exec -it pgvector psql -U postgres -d ragdb

Create the table:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding VECTOR(768),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

The VECTOR(768) dimension matches nomic-embed-text embeddings. Adjust if using a different model.

Exit psql: \q

Pull an embedding model:

ollama pull nomic-embed-text

Create a Python script embed.py:

import requests
import json

def get_embedding(text):
    response = requests.post(
        'http://localhost:11434/api/embeddings',
        json={
            'model': 'nomic-embed-text',
            'prompt': text
        }
    )
    return response.json()['embedding']

# Test
text = "Retrieval-Augmented Generation gives AI agents access to external knowledge."
embedding = get_embedding(text)
print(f"Embedding length: {len(embedding)}")  # Should be 768

Run:

python embed.py

Create store.py:

import psycopg2
import requests

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="ragdb",
    user="postgres",
    password="yourpassword"
)
cur = conn.cursor()

def get_embedding(text):
    response = requests.post(
        'http://localhost:11434/api/embeddings',
        json={'model': 'nomic-embed-text', 'prompt': text}
    )
    return response.json()['embedding']

# Sample documents
docs = [
    "Ollama runs large language models locally on consumer hardware.",
    "pgvector adds vector similarity search to PostgreSQL.",
    "RAG combines retrieval systems with text generation models.",
    "Local LLMs keep data private by avoiding cloud APIs.",
]

for doc in docs:
    embedding = get_embedding(doc)
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
        (doc, embedding)
    )

conn.commit()
print(f"Stored {len(docs)} documents")
conn.close()

Run:

pip install psycopg2-binary requests
python store.py

Create query.py:

import psycopg2
import requests

conn = psycopg2.connect(
    host="localhost",
    database="ragdb",
    user="postgres",
    password="yourpassword"
)
cur = conn.cursor()

def get_embedding(text):
    response = requests.post(
        'http://localhost:11434/api/embeddings',
        json={'model': 'nomic-embed-text', 'prompt': text}
    )
    return response.json()['embedding']

# Ask a question
question = "How can I keep my AI data private?"
question_embedding = get_embedding(question)

# Find most similar documents
cur.execute("""
    SELECT content, 1 - (embedding <=> %s::vector) AS similarity
    FROM documents
    ORDER BY embedding <=> %s::vector
    LIMIT 3;
""", (question_embedding, question_embedding))

results = cur.fetchall()
print(f"Question: {question}\n")
for content, similarity in results:
    print(f"[{similarity:.3f}] {content}")

conn.close()

Run:

python query.py

You should see the "Local LLMs keep data private" document ranked highest.

OpenClaw

Add a skill that queries pgvector:

// In your OpenClaw skill
async function retrieveContext(query: string): Promise<string> {
  const embedding = await getEmbedding(query);
  const result = await pgClient.query(
    "SELECT content FROM documents ORDER BY embedding <=> $1::vector LIMIT 3",
    [embedding]
  );
  return result.rows.map(r => r.content).join("\n---\n");
}

LangChain

from langchain_community.vectorstores import PGVector
from langchain_ollama import OllamaEmbeddings

embeddings = OllamaEmbeddings(model="nomic-embed-text")

vectorstore = PGVector(
    connection_string="postgresql://postgres:yourpassword@localhost:5432/ragdb",
    embedding_function=embeddings,
    collection_name="documents"
)

# Add documents
vectorstore.add_texts(docs)

# Query
results = vectorstore.similarity_search("How to keep data private?", k=3)