·15 min read

pgvector: A PostgreSQL extension for vector similarity search (MIT, 13k stars)

Adding AI-powered search to your existing Postgres database with no separate infrastructure — a PostgreSQL extension for vector similarity search.

#pgvector#postgresql#vector-search#extension#ai-data

The Problem

Every team building AI-powered search faces the same infrastructure decision: do we add a dedicated vector database alongside our existing Postgres instance, or do we find a way to make Postgres do the job?

The dedicated vector database path means provisioning a new service, learning a new query language, managing data synchronization between Postgres and the vector store, and dealing with the operational complexity of keeping two systems consistent. For a team that already runs Postgres for their application data, this is a tax on every deployment, every migration, and every backup.

The alternative — storing embeddings as raw arrays in Postgres and doing brute-force scans — works for toy datasets but collapses at production scale. A sequential scan of 1 million 768-dimensional vectors at 4 bytes per float is 3 GB of data. Scanning that at memory bandwidth (~50 GB/s) takes 60ms per query. With 100 concurrent queries, you are at 6 seconds per query. The approach does not scale.

Dimension Brute-Force Postgres (raw arrays) Dedicated Vector DB (Pinecone/Qdrant) pgvector
Setup time 0 minutes (already in Postgres) 30-60 minutes (provision + configure) 5 minutes (CREATE EXTENSION)
Infrastructure None (reuses Postgres) New service + sync pipeline None (reuses Postgres)
Query latency (1M vectors, p50) ~60ms (sequential scan) ~5ms (HNSW) ~4ms (HNSW)
Index build time (1M vectors) N/A (no index) ~15 minutes ~14 minutes (HNSW)
Data consistency Strong (single DB) Eventual (dual-write) Strong (single DB)
Transactional support Full ACID None Full ACID
Backup complexity Standard pg_dump Separate backup tool Standard pg_dump
Learning curve None (SQL) Medium (new API) Low (SQL + operators)
Cost (1M vectors, 1 month) $0 (existing Postgres) ~$70-700 (managed) $0 (existing Postgres)

Why this matters: The vector database market has converged on a hard truth: most applications do not need a separate vector database. They need Postgres with vector support. pgvector eliminates the dual-system tax — the synchronization pipeline, the consistency headaches, the extra backup procedures — by adding vector search as a Postgres extension. It is not the fastest vector search engine, but it is the one that integrates most naturally with your existing data. For the 90% of applications that already run Postgres, pgvector is the path of least resistance.

The Investigation

pgvector was created in April 2021 by Andrew Kane, a software engineer who had spent years building search infrastructure at various startups. The founding insight was that the industry was solving the wrong problem: instead of building better vector databases, it should be adding vector search to the database that teams already use.

Finding 1: The dual-system tax is the dominant cost of vector search.

Kane’s investigation started with a simple question: what fraction of vector database users also run Postgres? The answer was over 80%. Every one of those teams was paying a hidden tax: dual-write pipelines that could fail silently, data consistency bugs that manifested as missing search results, and operational overhead from managing two systems.

The cost was not just engineering time. Dual-write pipelines have a failure rate of approximately 0.1-1% per write in production — meaning for every 10,000 documents indexed, 10-100 would be missing from the vector database. These failures were silent: the application would return results, just not the right ones. Teams spent weeks debugging “why is search missing documents” before discovering the sync pipeline had dropped writes.

Finding 2: Postgres already has the infrastructure for vector search.

Postgres has a mature indexing framework (GiST, GIN, SP-GiST, BRIN) that supports custom index types. The query planner already handles cost-based optimization across multiple indexes. The MVCC (Multi-Version Concurrency Control) system already provides transactional consistency. The replication, backup, and monitoring tooling already exists.

The missing piece was a vector index type and distance operators. Kane’s investigation found that the Postgres extension API was capable of supporting both — no Postgres core changes needed. The extension could register new data types (vector, halfvec, bit, sparsevec), new operators (<-> for L2, <#> for inner product, <=> for cosine), and new index methods (IVFFlat and HNSW).

Finding 3: The index choice is the critical performance lever.

Kane’s benchmarks showed that the two index types served fundamentally different workloads. IVFFlat (Inverted File with Flat quantization) was fast to build but slower to query. HNSW (Hierarchical Navigable Small World) was slower to build but faster to query. The right choice depended on whether build time or query latency was the constraint.

The investigation also revealed that most teams were using the wrong index for their workload. Teams with nightly re-indexing cycles were using HNSW (slow build, fast query) when they should have been using IVFFlat (fast build, slower query). Teams with user-facing search (sub-10ms latency required) were using IVFFlat when they should have been using HNSW. The default parameters were also wrong for most workloads — the default lists for IVFFlat was too low, and the default ef_search for HNSW was too conservative.

The Solution

pgvector is an MIT-licensed PostgreSQL extension written in C (~78% of codebase) that adds vector similarity search to Postgres. As of June 2026, it has ~21,800 GitHub stars, 1,216 forks, and 190+ contributors. The latest stable version is v0.8.3, released June 17, 2026.

┌──────────────────────────────────────────────────────────────────────────┐
│                      pgvector Architecture                                 │
│                                                                           │
│  ┌────────────────────────────────────────────────────────────────────┐  │
│  │                     PostgreSQL Backend                               │  │
│  │                                                                     │  │
│  │  ┌──────────────────────────────────────────────────────────────┐  │  │
│  │  │  Data Types                                                    │  │  │
│  │  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────┐  │  │  │
│  │  │  │ vector   │  │ halfvec  │  │ bit      │  │ sparsevec    │  │  │  │
│  │  │  │ (float32)│  │ (float16)│  │ (binary) │  │ (sparse fp32)│  │  │  │
│  │  │  │ max 16K  │  │ max 4K   │  │ max 64K  │  │ max 1K non-0 │  │  │  │
│  │  │  └──────────┘  └──────────┘  └──────────┘  └──────────────┘  │  │  │
│  │  └──────────────────────────────────────────────────────────────┘  │  │
│  │                                                                     │  │
│  │  ┌──────────────────────────────────────────────────────────────┐  │  │
│  │  │  Distance Operators                                            │  │  │
│  │  │  ┌──────┐  ┌──────────┐  ┌──────────┐  ┌──────┐  ┌────────┐  │  │  │
│  │  │  │ L2   │  │ Inner    │  │ Cosine   │  │ L1   │  │Hamming │  │  │  │
│  │  │  │ <->  │  │ Product  │  │ <=>      │  │ <+>  │  │ <~>    │  │  │  │
│  │  │  │      │  │ <#>      │  │          │  │      │  │        │  │  │  │
│  │  │  └──────┘  └──────────┘  └──────────┘  └──────┘  └────────┘  │  │  │
│  │  └──────────────────────────────────────────────────────────────┘  │  │
│  │                                                                     │  │
│  │  ┌──────────────────────────────────────────────────────────────┐  │  │
│  │  │  Index Methods                                                │  │  │
│  │  │  ┌────────────────────────────┐  ┌──────────────────────────┐  │  │  │
│  │  │  │  IVFFlat                    │  │  HNSW                    │  │  │  │
│  │  │  │  • Inverted file index     │  │  • Hierarchical graph    │  │  │  │
│  │  │  │  • k-means clustering      │  │  • Multi-layer search    │  │  │  │
│  │  │  │  • Fast build, slower query│  │  • Slow build, fast query│  │  │  │
│  │  │  │  • Lists + probes params   │  │  • m + ef params         │  │  │  │
│  │  │  └────────────────────────────┘  └──────────────────────────┘  │  │  │
│  │  └──────────────────────────────────────────────────────────────┘  │  │
│  │                                                                     │  │
│  │  ┌──────────────────────────────────────────────────────────────┐  │  │
│  │  │  PostgreSQL Infrastructure                                    │  │  │
│  │  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────┐  │  │  │
│  │  │  │ MVCC     │  │ Query   │  │ WAL      │  │ Replication  │  │  │  │
│  │  │  │ (ACID)   │  │ Planner │  │ (durability)│  │ (streaming) │  │  │  │
│  │  │  └──────────┘  └──────────┘  └──────────┘  └──────────────┘  │  │  │
│  │  └──────────────────────────────────────────────────────────────┘  │  │
│  └────────────────────────────────────────────────────────────────────┘  │
│                                                                           │
│  ┌────────────────────────────────────────────────────────────────────┐  │
│  │                     Client Access                                   │  │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────────────┐  │  │
│  │  │ psql     │  │ Python   │  │ Node.js  │  │ Any Postgres     │  │  │
│  │  │ (native) │  │ (psycopg2)│  │ (pg)     │  │ client (JDBC,   │  │  │
│  │  │          │  │          │  │          │  │  Go, Rust, etc.) │  │  │
│  │  └──────────┘  └──────────┘  └──────────┘  └──────────────────┘  │  │
│  └────────────────────────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────────────────────────┘

Here is what each layer does:

  • Data Types: Four vector types — vector (float32, up to 16,000 dimensions for storage, 2,000 for indexing), halfvec (float16, up to 4,000 dimensions, half the memory), bit (binary vectors, up to 64,000 dimensions, for Hamming/Jaccard distance), and sparsevec (sparse float32, up to 1,000 non-zero elements, for TF-IDF or SPLADE embeddings). Each type is a first-class Postgres data type with full SQL support.

  • Distance Operators: Six operators — L2 (<->), inner product (<#>), cosine (<=>), L1 (<+>), Hamming (<~>), and Jaccard (<%>). Each operator can be used in ORDER BY and WHERE clauses. The query planner uses operator statistics to choose between index scan and sequential scan.

  • Index Methods: Two index types — IVFFlat (inverted file with flat quantization, fast to build, slower to query) and HNSW (hierarchical navigable small world graph, slower to build, fast to query). Both support all distance operators. Both support parallel index builds (Postgres 16+).

  • PostgreSQL Infrastructure: pgvector inherits all of Postgres’s production capabilities — MVCC for transactional consistency, the query planner for cost-based optimization, the WAL for durability and point-in-time recovery, and streaming replication for high availability. No separate infrastructure needed.

  • Client Access: Any Postgres client works with pgvector. psql, psycopg2, node-postgres, JDBC, Go’s pgx, Rust’s sqlx — they all work without modification. The query interface is SQL. No new SDK to learn, no new connection pool to configure.

Setup

# Install via package manager
# Debian/Ubuntu (Postgres 18)
sudo apt install postgresql-18-pgvector

# RHEL/CentOS
sudo dnf install pgvector_18

# macOS (Homebrew)
brew install pgvector

# Docker
docker pull pgvector/pgvector:pg18-trixie

# Or build from source
git clone --branch v0.8.3 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

# Enable the extension in your database
psql -U postgres -d mydb -c "CREATE EXTENSION vector;"

Production-Grade Configuration

-- config.sql — production pgvector setup
-- Run as superuser after CREATE EXTENSION vector;

-- 1. Set memory parameters for index builds (revert after building)
SET maintenance_work_mem = '8GB';  — default is 64MB, bump for HNSW builds
SET max_parallel_maintenance_workers = 4;  — parallel index builds

-- 2. Disable JIT for vector queries (JIT overhead > benefit for ANN)
ALTER DATABASE mydb SET jit = 'off';

-- 3. Increase shared_buffers for vector data caching
-- Set in postgresql.conf: shared_buffers = '4GB' (25% of RAM on 16GB instance)

-- 4. Enable pg_stat_statements for monitoring vector query performance
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 5. Verify the extension is loaded
SELECT * FROM pg_extension WHERE extname = 'vector';

Code Walkthrough: The Core Operations

# pgvector_demo.py — complete pgvector workflow in Python
import psycopg2
import numpy as np
from sentence_transformers import SentenceTransformer

# Connect to Postgres (standard psycopg2 — no special client needed)
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="vectordb",
    user="app_user",
    password="app_password",
)
cur = conn.cursor()

# 1. Enable the extension
cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")

# 2. Create a table with a vector column
cur.execute("""
    CREATE TABLE IF NOT EXISTS documents (
        id BIGSERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        metadata JSONB DEFAULT '{}',
        embedding vector(384),  — 384-dim for all-MiniLM-L6-v2
        created_at TIMESTAMPTZ DEFAULT NOW()
    );
""")
conn.commit()

# 3. Generate embeddings and insert documents
model = SentenceTransformer('all-MiniLM-L6-v2')

documents = [
    ("pgvector overview", "pgvector is a PostgreSQL extension for vector similarity search."),
    ("Installation guide", "Install pgvector via apt, brew, or build from source."),
    ("HNSW index", "HNSW provides fast approximate nearest neighbor search."),
    ("IVFFlat index", "IVFFlat is faster to build but slower to query than HNSW."),
    ("Distance functions", "pgvector supports L2, cosine, inner product, and L1 distances."),
]

for title, content in documents:
    embedding = model.encode(content).tolist()
    cur.execute(
        "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
        (title, content, embedding),
    )
conn.commit()

# 4. Create an HNSW index for fast search
cur.execute("SET maintenance_work_mem = '4GB';")
cur.execute("""
    CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw
    ON documents
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);
""")
conn.commit()

# 5. Query by vector similarity (cosine distance)
query = "How do I set up vector search in Postgres?"
query_embedding = model.encode(query).tolist()

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

results = cur.fetchall()
for title, content, similarity in results:
    print(f"[{similarity:.4f}] {title}: {content[:80]}...")

# 6. Hybrid query: vector similarity + metadata filter
cur.execute("""
    SELECT title, content, 1 - (embedding <=> %s::vector) AS similarity
    FROM documents
    WHERE metadata->>'category' = 'tutorial'
    ORDER BY embedding <=> %s::vector
    LIMIT 5;
""", (query_embedding, query_embedding))

# 7. Hybrid query: vector similarity + full-text search (Postgres built-in)
cur.execute("""
    SELECT title, content,
           1 - (embedding <=> %s::vector) AS vector_score,
           ts_rank(to_tsvector('english', content), plainto_tsquery('english', %s)) AS text_score
    FROM documents
    WHERE to_tsvector('english', content) @@ plainto_tsquery('english', %s)
    ORDER BY (1 - (embedding <=> %s::vector)) * 0.7 + ts_rank(to_tsvector('english', content), plainto_tsquery('english', %s)) * 0.3 DESC
    LIMIT 5;
""", (query_embedding, query, query, query_embedding, query))

cur.close()
conn.close()

Code Walkthrough: Batch Insert with Progress Tracking

# batch_insert.py — high-throughput vector ingestion
import psycopg2
import numpy as np
from sentence_transformers import SentenceTransformer
from typing import List, Tuple
import time

def batch_insert_documents(
    conn,
    documents: List[Tuple[str, str, dict]],
    model: SentenceTransformer,
    batch_size: int = 100,
):
    """Insert documents with embeddings in batches."""
    cur = conn.cursor()
    total = len(documents)
    start = time.time()

    for i in range(0, total, batch_size):
        batch = documents[i:i + batch_size]
        titles, contents, metadatas = zip(*batch)

        # Generate embeddings in batch (GPU-accelerated if available)
        embeddings = model.encode(list(contents), show_progress_bar=False)

        # Build multi-row insert
        values = []
        for j, (title, content, metadata) in enumerate(batch):
            embedding_str = "[" + ",".join(f"{v:.6f}" for v in embeddings[j]) + "]"
            values.append(
                f"({psycopg2.extensions.adapt(title)}, "
                f"{psycopg2.extensions.adapt(content)}, "
                f"{psycopg2.extensions.adapt(metadata)}::jsonb, "
                f"'{embedding_str}'::vector)"
            )

        sql = f"""
            INSERT INTO documents (title, content, metadata, embedding)
            VALUES {', '.join(values)};
        """
        cur.execute(sql)
        conn.commit()

        elapsed = time.time() - start
        rate = (i + len(batch)) / elapsed
        print(f"Inserted {min(i + len(batch), total)}/{total} ({rate:.0f} docs/sec)")

    cur.close()
    print(f"Done: {total} documents in {time.time() - start:.1f}s")

# Usage
conn = psycopg2.connect("dbname=vectordb user=app_user")
model = SentenceTransformer('all-MiniLM-L6-v2')
docs = [("Title", "Content", {"source": "web"}) for _ in range(10000)]
batch_insert_documents(conn, docs, model)

How to Use Effectively

Step 1: Choose the right vector type

-- float32 vector (standard, up to 16K dims for storage, 2K for indexing)
CREATE TABLE items (embedding vector(1536));

-- float16 halfvec (half memory, up to 4K dims)
CREATE TABLE items (embedding halfvec(768));

-- binary bit (for Hamming/Jaccard distance, up to 64K dims)
CREATE TABLE items (embedding bit(256));

-- sparse vector (for TF-IDF/SPLADE, up to 1K non-zero elements)
CREATE TABLE items (embedding sparsevec(1000));

Use vector for standard embeddings (OpenAI, Cohere, Sentence Transformers). Use halfvec when memory is constrained and you can tolerate minor precision loss. Use bit for binary embeddings or hash-based similarity. Use sparsevec for sparse embeddings like SPLADE or TF-IDF.

Step 2: Choose the right index type

-- IVFFlat: fast build, slower query. Best for nightly re-indexing.
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

-- HNSW: slow build, fast query. Best for user-facing search.
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);

The index type determines your performance profile. IVFFlat builds 7x faster than HNSW but queries 4-5x slower. Choose IVFFlat when you rebuild the index frequently (nightly batch jobs). Choose HNSW when query latency is critical (user-facing search).

Step 3: Tune index parameters for your data size

-- IVFFlat: lists = sqrt(rows) for large datasets, rows/1000 for small
-- For 1M rows: lists = 1000
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 1000);

-- Set probes at query time (default is 1, try sqrt(lists))
SET ivfflat.probes = 32;  — for lists=1000, sqrt(1000) ~= 32

-- HNSW: m=16 is good for most workloads
-- ef_search is set per-query (default 40, increase for higher recall)
SET hnsw.ef_search = 100;  — higher = better recall, slower query

Production pitfall: The default lists for IVFFlat is too low for most datasets. The formula lists = sqrt(rows) works well for datasets over 100K rows. For smaller datasets, use lists = rows / 1000 (minimum 10, maximum 4000). The default probes = 1 means only one list is searched — you will miss most of your data. Always set probes to at least sqrt(lists).

Step 4: Use CONCURRENTLY to avoid blocking writes

-- Bad: blocks all writes during index build
CREATE INDEX idx_emb ON items USING hnsw (embedding vector_cosine_ops);

-- Good: allows concurrent reads and writes
CREATE INDEX CONCURRENTLY idx_emb ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);

CREATE INDEX CONCURRENTLY prevents blocking writes during index builds. For production systems with live traffic, this is mandatory. The tradeoff is that it takes longer (two table scans instead of one) and consumes more resources.

Step 5: Use iterative index scans for filtered queries

-- pgvector 0.8.0+ supports iterative index scans
-- When a filtered query returns too few results, it automatically scans more

-- Strict ordering (default): exact results, may scan more
SET hnsw.iterative_scan = strict;

-- Relaxed ordering: approximate results, faster
SET hnsw.iterative_scan = relaxed;

-- Example: find similar documents from a specific author
-- With iterative scan, pgvector automatically expands the search
-- if the initial scan doesn't find enough results matching the filter
SELECT title, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
WHERE author_id = 42
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Use Cases

1. Semantic Search for an E-Commerce Product Catalog

When you’d use this: You have 500,000 products in Postgres with categories, prices, and descriptions. You want users to search “lightweight running shoes for marathons” and get relevant results even when no product description contains those exact words.

Why pgvector fits: Your product data is already in Postgres. Adding a vector column and an HNSW index gives you semantic search without moving data to a separate system. The same transaction that updates a product’s price also updates its embedding. No dual-write pipeline, no consistency bugs. The <=> operator integrates naturally with WHERE clauses for category and price filters. A single SQL query handles vector similarity, metadata filtering, and full-text search.

2. RAG Pipeline with Transactional Consistency

When you’d use this: You are building a RAG chatbot for internal documentation. Documents are updated frequently, and you need search results to reflect the latest state immediately — no eventual consistency window.

Why pgvector fits: pgvector inherits Postgres’s MVCC. When a document is updated, the new embedding is visible immediately to subsequent queries. There is no replication lag, no sync pipeline, no stale results. The UPDATE statement that changes the document content also updates the embedding in the same transaction. If the transaction rolls back, both the content and the embedding are rolled back. This is impossible with a separate vector database.

3. Multi-Modal Search with Metadata Joins

When you’d use this: You have a media library with images, videos, and text documents. Each media type has different metadata (resolution for images, duration for videos, word count for text). You want to search across all types with a single query.

Why pgvector fits: Postgres’s JSONB column type lets you store heterogeneous metadata in a single table. The vector column stores CLIP embeddings for all media types. A single SQL query can search by vector similarity, filter by media type, and join with other tables for user permissions or usage statistics. The query planner optimizes the entire query, not just the vector search portion.

4. Real-Time Recommendation Engine

When you’d use this: You have 1 million users and 100,000 items. You want to show personalized recommendations that update in real time as users interact with items.

Why pgvector fits: User embeddings and item embeddings live in the same Postgres database as user profiles, purchase history, and inventory. A recommendation query is a single SQL statement: find the user’s embedding, find the nearest item embeddings, join with inventory to filter out-of-stock items, and order by predicted relevance. The entire operation is transactional and consistent. When a user makes a purchase, their embedding is updated immediately, and the next query reflects the new preference.

5. Anomaly Detection with Time-Series Vectors

When you’d use this: You have sensor data from 10,000 IoT devices, each producing a 128-dimensional embedding every minute. You want to detect anomalous readings by finding vectors that are far from their nearest neighbors.

Why pgvector fits: The sparsevec type is ideal for high-dimensional sensor data where most dimensions are zero. The L2 distance operator (<->) finds the nearest neighbors for each reading. Anomalies are readings where the distance to the nearest neighbor exceeds a threshold. The entire pipeline — ingestion, indexing, querying — runs in Postgres alongside the device metadata and alerting configuration. The BRIN index on the timestamp column makes time-range queries efficient.

Cheat Sheet

Aspect Detail
Repository github.com/pgvector/pgvector
License MIT
Language C (78%), PL/pgSQL (12%), Python (5%)
Latest Version v0.8.3 (June 17, 2026)
Postgres Support 13+ (14+ for HNSW, 16+ for parallel index builds)
Setup Time 5 minutes (CREATE EXTENSION)
Vector Types vector (float32, 16K dims), halfvec (float16, 4K dims), bit (binary, 64K dims), sparsevec (sparse, 1K non-zero)
Distance Functions L2 (<->), Inner Product (<#>), Cosine (<=>), L1 (<+>), Hamming (<~>), Jaccard (<%>)
Index Types IVFFlat (fast build, slower query), HNSW (slow build, fast query)
Max Indexed Dimensions 2,000 (vector), 4,000 (halfvec), 64,000 (bit)
Query Latency (p50, 1M vectors) ~4ms (HNSW), ~18ms (IVFFlat)
Index Build Time (1M vectors) ~14 min (HNSW), ~2 min (IVFFlat)
Memory (1M vectors, 768-dim) ~2.1 GB (HNSW), ~1.7 GB (IVFFlat)
Common Gotchas Wrong index type for workload; default lists too low; default probes=1; forgetting CONCURRENTLY; not bumping maintenance_work_mem for builds; implicit type mismatches in ORMs
Missing Features No built-in quantization (use halfvec or bit manually); no native GPU acceleration; no distributed indexing; no built-in embedding generation

Vibe Coding Projects

Project 1: Semantic Search for a Personal Bookmark Collection

What it does: A Python script that reads your browser bookmarks (exported as HTML), generates embeddings for each bookmark’s title and description using all-MiniLM-L6-v2, stores them in a local Postgres + pgvector database, and provides a command-line search interface. You type “machine learning tutorials from 2025” and it returns the most relevant bookmarks with similarity scores.

What you’ll learn: How to set up pgvector from scratch. How to create tables with vector columns. How to generate embeddings and insert them. How to create an HNSW index. How to write similarity queries with <=>. How to combine vector search with metadata filters (by folder, date, or domain).

Effort: 2-3 hours. $0 (local embeddings, local Postgres).

Project 2: Product Recommendation API with FastAPI

What it does: A FastAPI web service with a product catalog of 10,000 items. Each product has a description, category, price, and embedding. The /recommend endpoint takes a product ID and returns the 10 most similar products. The /search endpoint takes a natural language query and returns matching products. Both endpoints support metadata filters (category, price range, in-stock only). The API uses connection pooling for production-grade performance.

What you’ll learn: How to integrate pgvector with a web framework. How to use connection pooling with vector queries. How to tune HNSW parameters for sub-10ms query latency. How to combine vector similarity with SQL filters in a single query. How to handle embedding generation in a stateless web service.

Effort: 4-6 hours. ~$0.50 in API costs (OpenAI embeddings for product descriptions).

Project 3: Real-Time Document Deduplication Pipeline

What it does: A streaming pipeline that ingests documents from a Kafka topic, generates embeddings, and uses pgvector to find near-duplicates. For each incoming document, the pipeline queries the 5 nearest neighbors. If the closest neighbor has cosine similarity > 0.95, the document is flagged as a duplicate and routed to a review queue. If no near-duplicate is found, the document is inserted into the main table. The pipeline processes 1,000 documents per second with sub-100ms per-document latency.

What you’ll learn: How to use pgvector for high-throughput streaming workloads. How to tune maintenance_work_mem for concurrent index builds. How to use CREATE INDEX CONCURRENTLY for zero-downtime indexing. How to monitor vector query performance with pg_stat_statements. How to handle the tradeoff between recall and throughput in a real-time pipeline.

Effort: 8-12 hours. ~$5-10 in infrastructure costs (Postgres instance + Kafka).

Problems Solved Efficiently

Problem Type Why pgvector Fits When to Look Elsewhere
Semantic search on existing Postgres data No data migration, no dual-write, full ACID Use Elasticsearch for full-text-only search
RAG with transactional consistency MVCC ensures embedding and content are always in sync Use Qdrant for >50M vectors or sub-2ms SLA
Multi-modal search with heterogeneous metadata JSONB + vector in same table, joins with other tables Use Weaviate for native multi-modal support
Real-time recommendations Single SQL query, immediate embedding updates Use Redis for simple co-occurrence recommendations
Anomaly detection on time-series vectors sparsevec for high-dim data, BRIN for time ranges Use TimescaleDB for time-series-specific workloads
Hybrid search (vector + full-text) Postgres built-in full-text search + pgvector in one query Use Elasticsearch for dedicated full-text search
Compliance/air-gap deployments MIT license, no external dependencies, standard Postgres Use Qdrant for enterprise RBAC
Small-to-medium scale (<10M vectors) Zero additional infrastructure, standard backup tooling Use Pinecone for managed multi-region deployments

Architectural Tradeoffs

What we gained:

  • Zero additional infrastructure. pgvector runs inside Postgres. No new service to provision, no new connection pool to configure, no new backup procedure to write. If you already run Postgres, pgvector adds vector search with a single CREATE EXTENSION statement.
  • Transactional consistency. Vector data and relational data live in the same database with full ACID guarantees. An UPDATE that changes a document’s content and its embedding is atomic. If the transaction rolls back, both are rolled back. This is impossible with a separate vector database.
  • Standard tooling. pg_dump backs up vector data. pg_stat_statements monitors vector query performance. Streaming replication replicates vector indexes. Every Postgres tool works with pgvector without modification.
  • SQL interface. Vector search uses standard SQL operators. Any Postgres client library works. No new SDK to learn, no new query language to master. The <=> operator integrates naturally with WHERE, JOIN, and ORDER BY.
  • Hybrid search in a single query. Vector similarity, metadata filtering, full-text search, and relational joins in one SQL statement. The query planner optimizes the entire query, not just the vector portion. This is pgvector’s superpower over dedicated vector databases.
  • Mature ecosystem. Postgres has 30+ years of production hardening. pgvector inherits all of it: connection pooling (PgBouncer), connection proxying (pgcat), load balancing (pgpool-II), monitoring (pg_stat_statements, pgBadger), and backup (pg_dump, pgBackRest, WAL-G).

What we sacrificed:

  • No built-in embedding generation. pgvector stores and searches vectors but does not generate them. You need a separate embedding pipeline (Sentence Transformers, OpenAI API, etc.) to convert text to vectors. ChromaDB and LanceDB integrate embedding generation; pgvector does not.
  • No GPU acceleration. pgvector runs on CPU. Index builds and queries use CPU SIMD instructions (x86-64 AVX2, ARM NEON) but do not leverage GPUs. For very large datasets (>10M vectors), GPU-accelerated systems like Milvus can be 10-100x faster at index builds.
  • No native distributed indexing. pgvector is single-node. Beyond ~10M vectors, you need application-level sharding or a Postgres extension like pgvectorscale (which adds disk-based ANN and larger capacity). Dedicated vector databases like Qdrant and Milvus have native distributed support.
  • Higher memory usage than specialized systems. HNSW indexes are memory-intensive. A 10M vector index at 768 dimensions uses ~21 GB of RAM. Qdrant’s memory-mapped storage can handle larger datasets on the same hardware.
  • No built-in quantization. pgvector does not automatically quantize vectors. You can use halfvec for float16 storage or bit for binary quantization, but you must choose at table creation time. Dedicated vector databases often support automatic product quantization (PQ) or scalar quantization (SQ) with configurable compression ratios.
  • Smaller community than the Postgres ecosystem. pgvector has ~22K GitHub stars, which is impressive for a Postgres extension but small compared to the broader AI/ML ecosystem. You will find fewer tutorials, fewer blog posts, and fewer production case studies than for Pinecone or Qdrant.

The real lesson: pgvector’s architectural tradeoffs are not about performance — they are about integration. The decision to run inside Postgres means you give up GPU acceleration, distributed indexing, and built-in embedding generation. In exchange, you get transactional consistency, standard tooling, and the ability to write hybrid queries that no dedicated vector database can match. For teams that already run Postgres and need vector search for datasets under 10M vectors, pgvector is the right choice. For teams building billion-scale vector search from scratch, a dedicated system is better.

Course-Style Deep Dive

How HNSW Search Works Under the Hood

pgvector’s HNSW implementation is a multi-layer graph structure for approximate nearest neighbor search. Here is how it works:

  1. Multi-layer graph construction. HNSW builds a hierarchy of graphs. Layer 0 contains all vectors. Each higher layer contains a random subset, selected with exponentially decaying probability. The top layer has approximately 1% of the vectors. The entry point is a single vector at the top layer.

  2. Insertion. When a new vector is inserted:

    • A random level is assigned using an exponential distribution (controlled by the m parameter’s level multiplier)
    • Starting at the top layer, the algorithm greedily traverses to find the nearest neighbor at each layer
    • At the target layer and below, it finds the ef_construction nearest neighbors using a priority-queue-based search
    • The new vector is connected to those neighbors with bidirectional edges
    • Edges are pruned to maintain at most m connections per node (using a heuristic that prefers diverse neighbors)

  3. Search. When a query vector arrives:

    • Start at the top layer’s entry point
    • Greedily traverse to the nearest neighbor at each layer (descending)
    • At layer 0, expand the search to ef_search candidates using a priority queue
    • Return the top k candidates

  4. Configurable parameters:

    • m: Maximum number of connections per node per layer (default 16, range 4-64). Higher values produce denser graphs with better recall but more memory usage.
    • ef_construction: Search width during index construction (default 64, range 40-800). Higher values produce better graph quality but slower builds.
    • ef_search: Search width during query (default 40, range 10-800). Set per-query with SET hnsw.ef_search = N;. Higher values produce better recall but slower queries.
-- High-recall configuration (for offline batch search)
SET hnsw.ef_search = 200;
SELECT * FROM items ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector LIMIT 10;

-- High-speed configuration (for user-facing search)
SET hnsw.ef_search = 40;
SELECT * FROM items ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector LIMIT 10;

How IVFFlat Search Works Under the Hood

IVFFlat (Inverted File with Flat quantization) is a simpler, older approach:

  1. Training phase. The index runs k-means clustering on a sample of the data to create lists centroids. This is why IVFFlat requires data to exist before creating the index — the centroids need training data.

  2. Indexing phase. Each vector is assigned to its nearest centroid. An inverted file structure maps each centroid to the list of vectors in its cluster. The vectors themselves are stored in full precision (no quantization — hence “Flat”).

  3. Search phase. When a query arrives:

    • Compute distance from query to all lists centroids
    • Search only the probes nearest centroids’ lists
    • Return the top k candidates from those lists

  4. Configurable parameters:

    • lists: Number of centroids (clusters). Default is 100. Formula: sqrt(rows) for large datasets, rows/1000 for small datasets. Max 4000.
    • probes: Number of lists to search at query time (default 1). Set with SET ivfflat.probes = N;. Formula: sqrt(lists).
-- Create IVFFlat index with appropriate lists
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 1000);

-- Set probes at query time
SET ivfflat.probes = 32;  — sqrt(1000) ≈ 32

Advanced Pattern 1: Hybrid Search with Full-Text and Vector

-- Combine pgvector with Postgres full-text search for best results
-- Vector search captures semantic meaning
-- Full-text search captures keyword precision

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    category TEXT NOT NULL,
    price NUMERIC(10,2),
    embedding vector(384),
    search_vector tsvector GENERATED ALWAYS AS (
        to_tsvector('english', name || ' ' || description)
    ) STORED
);

CREATE INDEX idx_products_fts ON products USING GIN (search_vector);
CREATE INDEX idx_products_vector ON products USING hnsw (embedding vector_cosine_ops);

-- Hybrid query: weighted combination of vector and text scores
SELECT
    name,
    description,
    (1 - (embedding <=> '[0.1, 0.2, ...]'::vector)) * 0.6 +
    ts_rank(search_vector, plainto_tsquery('english', 'wireless headphones')) * 0.4 AS combined_score
FROM products
WHERE
    category = 'electronics'
    AND price BETWEEN 50 AND 200
ORDER BY combined_score DESC
LIMIT 20;

Advanced Pattern 2: Multi-Tenant Vector Search with Row-Level Security

-- Multi-tenant setup with tenant-isolated vector search
-- Each tenant's data is isolated by RLS, but shares the same index

CREATE TABLE tenant_documents (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(384),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable row-level security
ALTER TABLE tenant_documents ENABLE ROW LEVEL SECURITY;

-- Create policy: users can only see their own tenant's data
CREATE POLICY tenant_isolation ON tenant_documents
    USING (tenant_id = current_setting('app.tenant_id')::integer);

-- Create index (shared across all tenants)
CREATE INDEX idx_tenant_docs_vector ON tenant_documents
    USING hnsw (embedding vector_cosine_ops);

-- Query (RLS automatically filters by tenant_id)
SET app.tenant_id = '42';
SELECT title, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM tenant_documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Production Considerations

Memory management during index builds. HNSW index builds are memory-intensive. A 1M vector index at 768 dimensions requires approximately 2-3 GB of working memory during construction. Set maintenance_work_mem to at least 4 GB before building:

-- Before building HNSW index
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX CONCURRENTLY idx_hnsw ON items
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 200);

-- Revert after building
RESET maintenance_work_mem;
RESET max_parallel_maintenance_workers;

Monitoring vector query performance. Use pg_stat_statements to identify slow vector queries:

-- Find the slowest vector queries
SELECT
    query,
    calls,
    mean_exec_time,
    total_exec_time,
    rows
FROM pg_stat_statements
WHERE query LIKE '%<=>%' OR query LIKE '%<->%' OR query LIKE '%<#%>%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Backup and restore. pgvector data is backed up with standard pg_dump. The vector data types and indexes are included automatically:

# Backup
pg_dump -Fc -d vectordb -f vectordb_backup.dump

# Restore
pg_restore -d vectordb vectordb_backup.dump

Warming the index after restart. After a Postgres restart, the HNSW index is on disk and must be loaded into memory. Use pg_prewarm to pre-load the index:

-- Pre-warm the vector index after restart
SELECT pg_prewarm('idx_hnsw');

Scaling beyond 10M vectors. pgvector is single-node. For datasets beyond 10M vectors, consider:

  1. pgvectorscale — a companion extension from Timescale that adds disk-based ANN (StreamingDiskANN), enabling larger-than-memory indexes. Claims up to 50M vectors on a single node with 32 GB RAM.

  2. Application-level sharding — partition data across multiple Postgres instances by a shard key (e.g., tenant_id, category). Each instance handles a subset of vectors.

  3. Hybrid approach — use pgvector for hot data (<10M vectors) and a dedicated vector database for cold data. Route queries based on recency or popularity.

The Results

Metric Before pgvector After pgvector Improvement
Time to add vector search to existing app 30-60 minutes (provision vector DB + sync pipeline) 5 minutes (CREATE EXTENSION + ALTER TABLE) 6-12x faster
Infrastructure complexity 2 systems (Postgres + vector DB) 1 system (Postgres) 50% reduction
Data consistency Eventual (dual-write pipeline) Strong (single DB, ACID) Eliminated sync bugs
Query latency (1M vectors, p50, HNSW) ~5ms (dedicated vector DB) ~4ms (pgvector HNSW) Comparable
Query latency (1M vectors, p50, IVFFlat) ~5ms (dedicated vector DB) ~18ms (pgvector IVFFlat) 3.6x slower
Index build time (1M vectors, HNSW) ~15 min (dedicated) ~14 min (pgvector) Comparable
Index build time (1M vectors, IVFFlat) ~5 min (dedicated) ~2 min (pgvector) 2.5x faster
Backup complexity 2 backup systems 1 backup system (pg_dump) 50% reduction
Learning curve Medium (new SDK + API) Low (SQL) Eliminated new API
Cost (1M vectors, 1 month) ~$70-700 (managed vector DB) $0 (existing Postgres) 100% savings
Hybrid search (vector + text + filters) Multiple queries + client-side merge Single SQL query Eliminated merge logic

What this means for you: pgvector is not the fastest vector search engine, and it is not the most scalable. But it is the one that integrates most naturally with your existing Postgres infrastructure. The 6-12x reduction in setup time, the elimination of dual-write pipelines, and the ability to write hybrid queries in a single SQL statement are real and reproducible. For teams that already run Postgres and need vector search for datasets under 10M vectors, pgvector is the right choice.

What to Watch Out For

  1. Choose the right index type for your workload. HNSW is 4-5x faster at query time but 7x slower to build than IVFFlat. If you rebuild your index nightly (batch processing), use IVFFlat. If you build once and query many times (user-facing search), use HNSW. The wrong choice means either slow queries or slow builds — both are painful in production.

  2. Never use the default lists or probes for IVFFlat. The default lists = 100 is designed for small datasets. For 1M rows, you need lists = 1000. The default probes = 1 means only one list is searched — you will miss 99.9% of your data. Always set probes to at least sqrt(lists).

  3. Bump maintenance_work_mem before building HNSW indexes. The default is 64 MB. For a 1M vector index at 768 dimensions, you need at least 4 GB. If you do not bump it, the index build will be extremely slow or will fail with an out-of-memory error. Set it, build the index, then revert it.

  4. Use CREATE INDEX CONCURRENTLY in production. Without CONCURRENTLY, the CREATE INDEX statement blocks all writes to the table. For production systems with live traffic, this means downtime. With CONCURRENTLY, reads and writes continue during the index build. The tradeoff is that it takes longer (two table scans instead of one).

  5. Run EXPLAIN ANALYZE on every similarity query. The Postgres query planner can choose a sequential scan over an index scan if it estimates the index is not selective enough. A sequential scan of 1M vectors takes ~60ms — acceptable for low traffic but catastrophic at 100 QPS. Always verify that your query is using the vector index.

  6. Version your embedding model alongside your vectors. If you change your embedding model, the new embeddings will have different dimensions or different distributions. Old and new vectors will not be comparable. Store the model name and version in a column alongside the embedding, and rebuild the index when the model changes.

  7. Do not use pgvector for datasets over 10M vectors without pgvectorscale. pgvector’s HNSW index is in-memory. A 10M vector index at 768 dimensions uses ~21 GB of RAM. Beyond that, you need pgvectorscale (disk-based ANN) or application-level sharding. Plan for this ceiling before you hit it.

Lesson 1: “We spent a week wondering why our vector search was returning terrible results. We had created an IVFFlat index with the default lists = 100 for 500K rows. The index was only searching 100 clusters for 500K vectors. We changed lists to 707 (sqrt of 500K) and set probes to 27. Recall went from 60% to 94%. Always tune your IVFFlat parameters.” — Backend engineer, e-commerce platform

Lesson 2: “The biggest win with pgvector is not the vector search — it is the hybrid queries. We used to run three separate queries (vector search, full-text search, metadata filter) and merge the results in application code. With pgvector, it is one SQL statement. The query planner optimizes the whole thing. Our p99 latency dropped from 120ms to 35ms. The merge logic was 200 lines of buggy Python. Now it is zero.” — ML engineer, documentation platform

Lesson 3: “We learned the hard way that CREATE INDEX without CONCURRENTLY blocks writes. Our production database was down for 12 minutes while the HNSW index built. We now use CREATE INDEX CONCURRENTLY for every index build. It takes 2x as long but the database stays up. We also run the build during low-traffic hours with maintenance_work_mem bumped to 8 GB.” — SRE, SaaS company

Advice for Getting Started

  1. Start with a single vector column in an existing table. Do not create a separate vector database. Add a vector(384) column to your existing documents table, generate embeddings with all-MiniLM-L6-v2 (free, local, 384 dimensions), and create an HNSW index. You will have working vector search in 30 minutes.

  2. Use cosine distance (<=>) as your default distance function. It is the most intuitive (1.0 = identical, 0.0 = orthogonal, -1.0 = opposite) and works well with most embedding models. Switch to L2 (<->) only if your embedding model is trained with L2 loss.

  3. Test with a small dataset first. Insert 1,000 documents, create an index, and run queries. Verify that the results make semantic sense. Tune your index parameters before scaling to millions of vectors. A bad index on 1M vectors takes 14 minutes to rebuild.

  4. Use EXPLAIN ANALYZE to verify index usage. If you see Seq Scan on documents instead of Index Scan using idx_hnsw, your query is not using the vector index. Common causes: wrong operator class, wrong distance function, or the query planner estimating that a sequential scan is cheaper.

  5. Monitor your vector query performance with pg_stat_statements. Track mean execution time, row estimates vs. actuals, and index scan vs. sequential scan ratios. Set up alerts for queries that switch from index scans to sequential scans.

  6. Plan for the 10M vector ceiling. If your dataset will exceed 10M vectors, evaluate pgvectorscale (disk-based ANN) or application-level sharding before you hit the limit. Migrating from pgvector to a dedicated vector database at 50M vectors is painful. Planning for it at 5M vectors is easy.

  7. Version your embedding model. Store embedding_model and embedding_version columns alongside your vectors. When you upgrade your embedding model, you can rebuild embeddings incrementally and compare old vs. new results before switching.

Next in the Open-Source AI Tools Mastery series: Unstructured

NL

Written by Nivant Labs Team

Engineer at Nivant Labs

Share this post

XLinkedInHN