PostgreSQL as a Vector Database: pgvector in Production

The best vector database might be the one you already have.

Why pgvector

The dedicated vector database market — Pinecone, Weaviate, Qdrant, Milvus, Chroma — exploded alongside the AI boom. Each one offers fast approximate nearest-neighbor search for embeddings. Each one is also another service to deploy, monitor, pay for, and maintain. Another connection string. Another failure point.

pgvector is a PostgreSQL extension that adds vector column types and similarity search operators directly to Postgres. If you already run PostgreSQL — and most applications do — you can add vector search without bringing in a new database.

The advantages stack up fast:

One database for everything. Application data, user tables, vector embeddings, all in the same database. JOIN embeddings with metadata using standard SQL.
Existing tooling. pg_dump, pg_restore, connection pooling (PgBouncer), monitoring (pg_stat_statements). Everything you already use for Postgres works on vector columns.
ACID transactions. Insert a document and its embedding in the same transaction. No distributed-consistency headaches between your primary database and a separate vector store.
Familiar operations. Your team already knows SQL, your ORM already talks to Postgres, your deployment pipeline already handles PostgreSQL. The learning curve is the extension, not a whole platform.

Installation and setup

On a managed PostgreSQL service (AWS RDS, Google Cloud SQL, Supabase, Neon), pgvector is typically a one-click extension enable. On self-hosted Postgres:

# Build from source
git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

# Or via package manager (Debian/Ubuntu)
apt install postgresql-16-pgvector

Enable it in your database:

CREATE EXTENSION IF NOT EXISTS vector;

Creating vector columns

Vector columns store fixed-dimension arrays of floating-point numbers. You specify the dimensionality at column creation:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    tags TEXT[],
    status TEXT DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    embedding vector(1536),          -- OpenAI ada-002 dimension
    search_vector tsvector,          -- Full-text search
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

Common embedding dimensions:

1536 — OpenAI text-embedding-ada-002, text-embedding-3-small
3072 — OpenAI text-embedding-3-large
768 — Sentence transformers, many open-source models
384 — MiniLM and other lightweight models
1024 — Cohere embed-v3

Insert embeddings as arrays:

INSERT INTO articles (title, slug, content, embedding)
VALUES (
    'Building a RAG Pipeline',
    'building-rag-pipeline',
    'Full article content here...',
    '[0.0023, -0.0142, 0.0381, ...]'::vector(1536)
);

Indexing strategies

Without an index, pgvector does exact nearest-neighbor search by scanning every row. Fine for thousands of rows. Doesn't scale to millions. Two index types handle that.

IVFFlat (Inverted File Index)

Partitions vectors into clusters (lists) and only searches the nearest clusters at query time.

-- Create an IVFFlat index
CREATE INDEX ON articles
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

How it works: during index creation, vectors are clustered into lists groups using k-means. At query time, the index probes the nearest probes clusters and searches only those vectors. More probes = higher recall but slower queries.

When to use: faster to build than HNSW. Good for datasets that change infrequently, or when you need to rebuild indexes quickly. Works well up to ~1M vectors.

Tuning:

-- Set probes at query time (default is 1)
SET ivfflat.probes = 10;  -- Search 10 of 100 lists

Rule of thumb: lists = sqrt(num_rows) for tables up to 1M rows. probes = sqrt(lists) is a reasonable starting point.

HNSW (Hierarchical Navigable Small World)

Builds a multi-layer graph where each vector is connected to its approximate nearest neighbors. Searches traverse the graph from coarse to fine layers.

-- Create an HNSW index
CREATE INDEX ON articles
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

How it works: the index builds a hierarchy of proximity graphs. m controls the number of connections per node (higher = more connections = better recall but more memory). ef_construction controls index build quality (higher = slower build but better recall).

When to use: better recall than IVFFlat at the same speed. Supports incremental inserts without rebuilding. The default for most production workloads.

Tuning:

-- Set search quality at query time
SET hnsw.ef_search = 100;  -- Default is 40. Higher = better recall, slower.

Practical comparison:

Aspect	IVFFlat	HNSW
Build time	Faster	Slower
Insert without rebuild	Needs reindex	Supports incremental
Memory	Lower	Higher (~2-4x)
Recall at same speed	Lower	Higher
Best for	Static datasets	Dynamic, production use

Distance metrics

pgvector supports three distance operators:

-- Cosine distance (most common for text embeddings)
SELECT * FROM articles
ORDER BY embedding <=> query_embedding  -- cosine distance
LIMIT 10;

-- L2 (Euclidean) distance
SELECT * FROM articles
ORDER BY embedding <-> query_embedding  -- L2 distance
LIMIT 10;

-- Inner product (negative, for max inner product search)
SELECT * FROM articles
ORDER BY embedding <#> query_embedding  -- negative inner product
LIMIT 10;

Cosine distance (<=>) is the standard choice for text embeddings. It's one minus the cosine similarity:

\text{cosine\_distance}(\mathbf{a}, \mathbf{b}) = 1 - \frac{\mathbf{a} \cdot \mathbf{b}}{\lVert \mathbf{a} \rVert \, \lVert \mathbf{b} \rVert}

so identical direction gives distance 0 and opposite gives 2. It measures angular similarity, which is what most embedding models optimize for. Normalized embeddings (unit vectors) make cosine distance equivalent to inner product.

L2 distance (<->) measures straight-line distance in embedding space. Useful when magnitude matters (e.g., image embeddings where distance correlates with visual difference).

Inner product (<#>) is fastest computationally but only meaningful as a similarity metric on normalized vectors. Use it when your embeddings are pre-normalized and you want maximum speed.

Hybrid search: vectors + full-text

The killer feature of pgvector-in-Postgres is combining vector similarity with traditional full-text search in a single query. Dedicated vector databases can't do that without a separate search system.

-- Hybrid search: combine semantic similarity with keyword matching
WITH semantic AS (
    SELECT id, 1 - (embedding <=> $1::vector) AS semantic_score
    FROM articles
    WHERE status = 'published'
    ORDER BY embedding <=> $1::vector
    LIMIT 50
),
keyword AS (
    SELECT id, ts_rank(search_vector, websearch_to_tsquery('english', $2)) AS text_score
    FROM articles
    WHERE status = 'published'
      AND search_vector @@ websearch_to_tsquery('english', $2)
)
SELECT
    a.id, a.title, a.slug, a.summary, a.tags,
    COALESCE(s.semantic_score, 0) * 0.7 +
    COALESCE(k.text_score, 0) * 0.3 AS combined_score
FROM articles a
LEFT JOIN semantic s ON a.id = s.id
LEFT JOIN keyword k ON a.id = k.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY combined_score DESC
LIMIT 10;

This query does three things:

Finds semantically similar articles using vector search (understands meaning).
Finds keyword-matching articles using tsvector (catches exact terms).
Combines scores with configurable weights (0.7 semantic, 0.3 keyword).

The weighting depends on your use case. For a blog search, 0.7/0.3 semantic-heavy works well. For code search where exact identifiers matter, you'd flip it to 0.3/0.7.

To support full-text search efficiently, maintain a tsvector column with a GIN index:

-- Add tsvector column with GIN index
CREATE INDEX articles_search_idx ON articles USING gin(search_vector);

-- Auto-update search_vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.summary, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'C');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_search_update
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

Real schema example

A practical schema from a blog with RAG capabilities, similar to what powers this site:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    tags TEXT[],
    status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    reading_time TEXT,
    published_at TIMESTAMPTZ,
    embedding vector(768),           -- Embedding model output
    search_vector tsvector,          -- Full-text search
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes
CREATE INDEX articles_embedding_idx ON articles
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

CREATE INDEX articles_search_idx ON articles USING gin(search_vector);
CREATE INDEX articles_status_idx ON articles (status);
CREATE INDEX articles_tags_idx ON articles USING gin(tags);
CREATE INDEX articles_published_idx ON articles (published_at DESC);

This gets you:

Semantic search across articles via vector similarity.
Full-text keyword search with weighted fields.
Tag-based filtering using array operators (tags @> ARRAY['rag']).
Status filtering for draft/published/archived.
Chronological listing by publish date.
All in a single table with standard SQL access.

Performance tuning

Key parameters that affect query performance:

hnsw.ef_search controls the recall/speed trade-off at query time. Default is 40. For production:

40-100: good balance for most applications.
200+: when you need very high recall and can tolerate ~2x latency.
Profile on your actual dataset to find the right value.

maintenance_work_mem should be set higher when building HNSW indexes. The build is memory-intensive.

SET maintenance_work_mem = '2GB';  -- Before creating HNSW index

shared_buffers matters because the HNSW index needs to fit in memory for best performance. Size your shared_buffers accordingly. Rough estimate: HNSW index size is roughly 1.5-2x the raw vector data size.

Connection pooling matters too. Vector queries are CPU-intensive. Use PgBouncer and avoid saturating all connections with simultaneous vector searches.

Limitations vs dedicated vector DBs

Honesty matters here. pgvector has trade-offs.

Scale ceiling. Dedicated vector databases like Pinecone and Milvus are built for billions of vectors with automatic sharding. pgvector works well up to ~10M vectors per table; past that, you're fighting PostgreSQL's single-node architecture.

No built-in reranking. Dedicated platforms often include cross-encoder reranking. With pgvector, you implement reranking in your application layer.

Index build time. HNSW index creation on large tables (millions of rows) is slow and memory-intensive. Dedicated databases handle this with distributed builds.

No native embedding generation. You generate embeddings in your application and store them. Some vector databases offer integrated embedding APIs.

For most applications — startups, mid-size SaaS products, internal tools, content platforms — pgvector's scale ceiling is more than enough. You're probably not at billions of vectors. The operational simplicity of one database instead of two is worth more than theoretical scalability you don't actually need.

Start with pgvector. If you outgrow it, your embeddings are portable. Migrating to a dedicated vector database is straightforward, because the bottleneck is always the data, not the schema.