Go back

pgvector for Local Embeddings

By SumGuy 10 min read
pgvector for Local Embeddings
Contents

You Already Have a Vector Database

You’ve probably spent the last two years hearing that you need a specialized vector database. Chroma, Qdrant, Pinecone, Weaviate, the acronyms never stop. Everyone’s got their favorite. But if you’ve got Postgres running in your homelab or cloud account anyway, you’re throwing away the simplest solution on the table.

Postgres with pgvector is a legitimately solid vector database, not a scrappy workaround. It does everything you need for local embeddings, RAG pipelines, and semantic search. And because it’s just Postgres, you get backup strategies, transactions, access control, and monitoring you already understand. No new dashboards. No vendor lock-in. Just SQL.

Let’s talk about why you should care, how to set it up, and when to actually reach for something more specialized.

What pgvector Does (And Doesn’t)

pgvector is a Postgres extension that adds vector data types and indexes. You store embeddings as columns, query them with similarity operations (cosine, L2, inner product), and use indexes to make it not glacially slow. It’s that simple.

The math is straightforward. You’ve got a vector (a list of a few hundred to a few thousand numbers, depending on your embedding model). pgvector can measure similarity between vectors and return the K nearest neighbors fast enough to serve real queries.

What it doesn’t do: it’s not a managed service (Postgres is still your responsibility), it doesn’t have fancy multi-tenancy isolation at the database level, and if you’re running embedding queries on millions of rows with complex filters, you might eventually want to specialize. But for a homelab with 100k documents? For a side project with 50k articles? This is overkill-proof.

The real question isn’t “is pgvector good?” It’s “why do you need a separate tool?”

Install pgvector and Build a Pipeline

You need two things: the pgvector extension on your Postgres instance, and a Python client to generate embeddings.

Step 1: Install the Extension

If you’re running a supported Postgres (13+, and ideally 17 by now, since it’s 2026), installation is one command:

Terminal window
sudo apt-get install postgresql-17-pgvector

If you’re running Postgres in Docker (which you probably are in a homelab), use the official image with pgvector built in:

services:
  postgres:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_PASSWORD: your_password
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
volumes:
  postgres_data:

Connect to your database and enable the extension:

Terminal window
psql -U postgres -d your_database -c "CREATE EXTENSION IF NOT EXISTS vector;"

Done. Seriously.

Step 2: Design Your Schema

Here’s a typical RAG table. You’ve got your documents (the actual text), metadata, and the embedding vector:

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  title VARCHAR(255),
  source VARCHAR(255),
  embedding vector(1536),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

The 1536 is the dimensionality: common for OpenAI’s text-embedding-3-small. If you’re using Ollama’s nomic-embed-text, you’d use 768. If you’re rolling your own model, check the output shape.

Add an index. We’ll talk about which one in a minute:

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

Step 3: Generate and Store Embeddings

Here’s a real pipeline. You’re reading documents, generating embeddings, and storing them:

import psycopg2
from psycopg2.extras import execute_values
from ollama import embeddings as ollama_embed
import json


# Connect to your Postgres instance
conn = psycopg2.connect(
    dbname="your_database",
    user="postgres",
    password="your_password",
    host="localhost",
    port=5432
)
cursor = conn.cursor()


# Load your documents (from files, APIs, whatever)
documents = [
    {"title": "Docker Basics", "content": "Docker is a containerization tool..."},
    {"title": "Compose Guide", "content": "Docker Compose allows you to..."},
    # ... many more
]


# Generate embeddings (using Ollama locally)
records = []
for doc in documents:
    # Generate embedding
    response = ollama_embed(
        model="nomic-embed-text",
        input=doc["content"]
    )
    embedding = response["embeddings"][0]


    # Prepare row for insert
    records.append((
        doc["title"],
        doc["content"],
        embedding,
        doc.get("source", "unknown")
    ))


# Bulk insert
execute_values(
    cursor,
    "INSERT INTO documents (title, content, embedding, source) VALUES %s",
    records,
    template="(%s, %s, %s::vector, %s)"
)
conn.commit()
cursor.close()
conn.close()


print(f"Inserted {len(records)} documents")

Notice the %s::vector cast: Postgres needs to know you’re storing a vector, not a JSON array.

Step 4: Query by Similarity

Now the payoff. Find documents similar to a query:

import psycopg2
from ollama import embeddings as ollama_embed


conn = psycopg2.connect(
    dbname="your_database",
    user="postgres",
    password="your_password",
    host="localhost",
    port=5432
)
cursor = conn.cursor()


# User query
user_query = "How do I use Docker Compose?"


# Generate embedding for the query
response = ollama_embed(
    model="nomic-embed-text",
    input=user_query
)
query_embedding = response["embeddings"][0]


# Find top 5 most similar documents
cursor.execute(
    """
    SELECT id, title, content, embedding <=> %s::vector AS distance
    FROM documents
    ORDER BY embedding <=> %s::vector
    LIMIT 5;
    """,
    (query_embedding, query_embedding)
)


results = cursor.fetchall()
for doc_id, title, content, distance in results:
    print(f"{title} (similarity: {1 - distance:.3f})")
    print(f"  {content[:100]}...\n")


cursor.close()
conn.close()

The <=> operator used above is cosine distance. Lower distance = more similar. You can also use <-> for L2 (Euclidean) distance or <#> for inner product. Just make sure your index’s operator class matches the operator you query with (vector_cosine_ops for <=>).

Index Strategy: IVFFlat vs HNSW

This is where people get confused. pgvector supports two index types, and the choice matters for your dataset size and query speed.

IVFFlat (Inverted Flat)

IVFFlat divides your vectors into clusters (you specify the number), then does a brute-force search within the closest few clusters. Fast to build, memory-efficient, but lower recall than HNSW, it only searches the clusters nearest your query, so it can miss neighbors that landed just over a cluster boundary.

Use IVFFlat if:

Configuration:

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

The lists parameter is the number of clusters. The official pgvector recommendation is rows / 1000 for up to 1M rows, and sqrt(rows) once you’re over 1M. So 10k documents → 10 lists, 100k → 100 lists. The lists = 100 above is a fine default until you’re well past 100k rows.

HNSW (Hierarchical Navigable Small World)

HNSW is fancier: it builds a graph where each vector knows its nearest neighbors at multiple scales. Faster queries, slower builds, higher memory overhead. Think of IVFFlat as a filing cabinet and HNSW as a hyperlinked map.

Use HNSW if:

Configuration:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

m is the number of connections per node (higher = more accurate, slower build). ef_construction is the search width during build.

For most homelabs, stick with IVFFlat. It’s the right tradeoff.

Sizing Your Embeddings

This is a common question: “Should I use 768, 1536, or 3072 dimensions?”

The honest answer: it doesn’t matter as much as people think.

The trade-off is straightforward: higher dims = slightly better accuracy, more storage, slower queries. In practice, going from 768 to 1536 is noticeable. Going from 1536 to 3072? You won’t feel it in most RAG setups.

Pick what your embedding model outputs. Don’t try to squeeze a 3072-dim embedding into 1536 dims or zero-pad a 768-dim to 1536. Use the model’s native output.

Hybrid Search: Keywords + Vectors

Here’s where pgvector shines compared to specialized vector DBs. You’ve already got Postgres. You already have full-text search. Why not use both?

A common pattern: vector search for relevance, keyword filters for precision. Find the top 50 semantically similar documents, then filter to ones published in the last month and tagged “docker”.

cursor.execute(
    """
    SELECT id, title, content,
           embedding <=> %s::vector AS similarity
    FROM documents
    WHERE source = %s
      AND created_at > NOW() - INTERVAL '30 days'
      AND to_tsvector('english', content) @@ plainto_tsquery('english', %s)
    ORDER BY embedding <=> %s::vector
    LIMIT 10;
    """,
    (query_embedding, "blog", "docker compose", query_embedding)
)

This is a RAG system’s best friend: get vectors, filter by metadata, then rank by relevance. No specialized vector DB can beat Postgres at metadata filtering because Postgres IS a real database.

When to Stop Using pgvector

There’s a point where you outgrow it. Honestly, that point is pretty far out, but here it is:

But you probably don’t have any of those problems. You’re running this in a homelab. You’ve got stable compute. Your dataset is maybe 100k documents. pgvector with a 2GB Postgres instance handles that in its sleep.

The Real Cost Calculation

Every “specialized vector database” pitch includes a cost comparison:

If you’re self-hosting Postgres on a home server that costs you nothing, pgvector wins by default. If you’re renting cloud compute, you pay Postgres rates anyway, why add another service?

The only exception: if you’re building an AI startup and need something shiny to show investors, go ahead and use a specialized vector DB. Honestly, the resume value alone might be worth it.

Getting Started

Set up a test project. Throw 1,000 documents at pgvector. Build a simple semantic search API. See how it feels.

You’ll probably realize: this is fine. Better than fine. It’s just Postgres. Your backups work. Your monitoring works. You can write a SQL query. There’s no API quirk to learn, no rate limits, no “vector db is down” Slack message.

Start with IVFFlat, stay with IVFFlat until you’ve got a real performance problem (which you won’t). Use OpenAI’s embedding model or Ollama locally depending on your privacy requirements.

That’s it. You’ve got a vector database now.

Full example code is on GitHub (when I write it up): I’ll add a working Python RAG pipeline with Docker Compose + Postgres + pgvector to sumguy-examples soon. For now, the snippets above are production-ready: copy them, swap your connection details, and go.

Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email

Send a Webmention

Written about this post on your own site? Send a webmention and it'll show up above once verified.

Previous Post
PostGIS for Self-Hosted Mapping
Next Post
Postgres Replication: Streaming + Logical

Discussion

Powered by Garrul . Sign in with GitHub or Google, or post anonymously.

Related Posts