Go back

pgvector for Local Embeddings

By SumGuy 14 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 handles local embeddings, RAG pipelines, and semantic search. 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.

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

Versions in this article, checked September 2026: pgvector 0.8.6 on Postgres 17. Every error message and default value below came from running it, not from the docs.

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.

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 multi-tenancy isolation at the database level, and its approximate indexes have sharp edges around filtered queries that a purpose-built vector store handles for you. More on those below, because they will bite you.

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

pgvector supports Postgres 13 and up. On a Debian or Ubuntu box with the PGDG repo, the package is versioned to your server’s major version:

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:

compose.yaml
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

A typical RAG table holds your documents (the actual text), metadata, and the embedding vector:

schema.sql
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.

Notice what’s missing: the index. Create the table, load your data, then build the index. Do it in the other order and pgvector tells you exactly how badly you’ve messed up:

Terminal window
NOTICE:  ivfflat index created with little data
DETAIL:  This will cause low recall.
HINT:  Drop the index until the table has more data.

That’s not a warning you can shrug off. IVFFlat picks its cluster centroids by sampling the rows that exist when you run CREATE INDEX. Build it on an empty table and every centroid is garbage, permanently, until you reindex. HNSW has no training step and doesn’t care, which is one of several reasons it’s the better default. We’ll get there.

Step 3: Generate and Store Embeddings

A working pipeline reads documents, generates embeddings, and stores them:

ingest.py
import psycopg2
from psycopg2.extras import execute_values
from ollama import embed


# 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:
    response = embed(model="nomic-embed-text", input=doc["content"])
    embedding = response.embeddings[0]


    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")

Two details in there matter more than they look.

The import is embed, not embeddings. The Python client ships both, they take different arguments, and they return different shapes. ollama.embeddings(model, prompt) is the deprecated one: it takes prompt, not input, and returns a single flat list on .embedding. Call it with input= and you get a TypeError before you ever reach Postgres. ollama.embed(model, input) is the current one, it accepts a string or a list of strings, and it returns .embeddings as a list of lists. Hence the [0].

The %s::vector cast is the other one. psycopg2 adapts a Python list into a Postgres array literal, and Postgres won’t quietly coerce an array into a vector. The cast makes it explicit.

Step 4: Build the Index

Now that the table has rows, build the index:

index.sql
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Step 5: Query by Similarity

Find documents similar to a query:

search.py
import psycopg2
from ollama import embed


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


user_query = "How do I use Docker Compose?"


response = 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)
)


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


cursor.close()
conn.close()

The <=> operator is cosine distance. Lower distance means more similar, so 1 - distance gives you a similarity score. You can also use <-> for L2 (Euclidean) distance or <#> for inner product. Make sure your index’s operator class matches the operator you query with (vector_cosine_ops for <=>), or the planner ignores your index entirely and sequential-scans the table.

Index Strategy: IVFFlat vs HNSW

pgvector supports two index types, and the choice matters for your dataset size and query speed. Both are approximate: they trade recall for speed, and both have a runtime knob that controls that trade.

IVFFlat (Inverted Flat)

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

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

The lists parameter is the number of clusters. Upstream recommends rows / 1000 up to 1M rows, and sqrt(rows) past that. So 10k documents means 10 lists, 100k means 100.

Now the part every IVFFlat tutorial forgets. At query time, the number of clusters actually searched comes from ivfflat.probes, and the default is 1:

probes.sql
SHOW ivfflat.probes;   -- 1
SET ivfflat.probes = 10;

One cluster out of a hundred. Your recall is roughly whatever fraction of the table landed in that single list. Upstream’s own guidance is to start at sqrt(lists), so with lists = 100 you want probes = 10. If you built an IVFFlat index, never touched probes, and concluded that vector search is inaccurate, this is why.

HNSW (Hierarchical Navigable Small World)

HNSW builds a graph where each vector knows its nearest neighbors at multiple scales. Faster queries, slower builds, higher memory overhead, and better recall at any given speed.

hnsw.sql
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 means more accurate, slower build). ef_construction is the search width during build. The query-time knob is hnsw.ef_search, which defaults to 40 and which you raise for better recall.

Start with HNSW. It has better query performance for a given recall level, and it needs no training data, so you can create it before or after loading rows without wrecking it. Reach for IVFFlat when build time or index memory is the constraint you actually have: it builds far faster and uses less memory, which is a real argument on a 2GB VPS. It is not the general-purpose default.

Sizing Your Embeddings

“Should I use 768, 1536, or 3072 dimensions?” comes up constantly, and there’s one hard constraint that settles most of it.

pgvector indexes a maximum of 2,000 dimensions. The vector type will happily store up to 16,000, but try to index past 2,000 and you get:

Terminal window
ERROR:  column cannot have more than 2000 dimensions for hnsw index

So a 3072-dim column from text-embedding-3-large cannot have an HNSW or IVFFlat index at all. Every query against it is an exact sequential scan over the whole table. That’s fine at 5,000 rows and unusable at 500,000. If you need those dimensions indexed, store them as halfvec (16-bit floats, indexable to 4,000 dimensions) and accept the precision loss, or reduce dimensions at the model level.

With that in mind:

Going from 768 to 1536 is noticeable. Going from 1536 to 3072 costs you your index. Pick what your embedding model outputs, within the 2,000 limit. Don’t zero-pad a 768-dim embedding to 1536.

Hybrid Search: Keywords + Vectors, and the Trap

You’ve already got Postgres. You already have full-text search. Combining them is the obvious move, and it’s also where pgvector’s approximate indexes will quietly lie to you.

The pattern looks like this: vector search for relevance, metadata filters for precision.

hybrid.py
cursor.execute(
    """
    SELECT id, title, content,
           embedding <=> %s::vector AS distance
    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)
)

Read that and you’d assume Postgres filters first, then ranks the survivors. It does the opposite. With an approximate index, the filter is applied after the index scan. pgvector walks the HNSW graph for candidates near your query vector, then throws away the ones that fail your WHERE clause, and returns whatever’s left. If your filter is selective, “whatever’s left” can be nothing.

I ran the numbers on a 50,000-row table where 500 rows matched source = 'blog', with an HNSW cosine index and LIMIT 10. With stock settings the query returned 0 rows. Not 10, not 3. Zero. The graph traversal found its candidates and none of them happened to be blog posts.

The fix is iterative scans, which are off by default:

iterative.sql
SHOW hnsw.iterative_scan;          -- off
SET hnsw.iterative_scan = strict_order;

With strict_order, the same query returned all 10 rows. The index keeps scanning until it has enough results that survive the filter. relaxed_order is the faster variant that may return results slightly out of distance order. IVFFlat has the matching ivfflat.iterative_scan, also off by default.

So the honest version of the pitch: Postgres gives you metadata filtering and vector search in one query with one backup strategy, which is a real advantage. But you must turn on iterative scans to get correct results, and a purpose-built vector store does this filtering properly without a session GUC. Anyone telling you Postgres beats dedicated vector databases at filtered search has not tested a selective filter.

When to Stop Using pgvector

There’s a point where you outgrow it. That point is further out than the vendors want you to believe, but here it is:

You probably don’t have any of those problems. You’re running this in a homelab with stable compute and maybe 100k documents. pgvector with a 2GB Postgres instance handles that without breaking a sweat.

The Real Cost Calculation

Every “specialized vector database” pitch includes a cost comparison, usually with a made-up per-vector rate. Pinecone’s actual published pricing, as of September 2026, is a platform fee plus metered usage:

Metered on top of that: $0.33/GB/month for storage, $16 to $18 per million read units, roughly $2.75 per million write units, and $0.10/GB egress with the first 100GB each month included. There is no simple “per million vectors” number, which is exactly why you should price your own workload instead of trusting a blog post’s rate. Mine included.

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

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. 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.

Three settings will save you a bad afternoon. Build the index after loading data. Use HNSW unless build cost forces IVFFlat, and if it does, raise ivfflat.probes. Turn on iterative scans before you ship anything with a WHERE clause. Your 2 AM self will appreciate it.

Common Questions

Do I need a separate vector database if I already run Postgres?

No, not for homelab scale. pgvector handles 100k documents on a 2GB Postgres instance with room to spare, and you reuse your existing backups, monitoring, and access control. Move to Qdrant or Milvus when you hit millions of vectors with strict latency budgets, or need sharding across machines.

What is the maximum number of dimensions pgvector can index?

2,000 dimensions for both HNSW and IVFFlat indexes. The vector type stores up to 16,000 dimensions, but anything past 2,000 can only be queried by exact sequential scan. Use halfvec for 16-bit storage indexable to 4,000 dimensions, or pick a model with smaller output.

Should I use IVFFlat or HNSW in a home lab?

HNSW for most cases. It gives better query speed at a given recall level, and it needs no training data, so index creation order doesn’t matter. Choose IVFFlat when index build time or memory is your binding constraint, then set ivfflat.probes to roughly the square root of your lists value.

Why does my filtered pgvector query return fewer rows than the LIMIT?

Because approximate indexes apply your WHERE clause after scanning the index, not before. A selective filter can discard every candidate the index found. Set hnsw.iterative_scan = strict_order (or ivfflat.iterative_scan) so pgvector keeps scanning until enough rows survive the filter. Both default to off.

Does pgvector work with Ollama embeddings?

Yes. Generate the vector with ollama.embed(model, input) and read response.embeddings[0], then cast the Python list with %s::vector on insert. Avoid the deprecated ollama.embeddings(), which takes prompt instead of input and returns a flat list on .embedding rather than a list of lists.

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