pgvector: PostgreSQL as a Vector Database

What is pgvector?

pgvector is an open-source PostgreSQL extension for high-dimensional vectors and similarity search. It lets you use PostgreSQL as a vector database without adding separate systems like Pinecone or Weaviate.

Why does it matter? Most companies already run PostgreSQL in production. With pgvector, they implement RAG and semantic search without adding operational complexity or extra infrastructure costs.

Capabilities:

• Vectors of up to 16,000 dimensions
• Exact (kNN) and approximate (ANN) search
• IVFFLAT and HNSW indexes for millions of vectors
• Metrics: L2, cosine, inner product
• Filters combined with standard SQL

Installing on Docker and Ubuntu

Docker (fastest):
docker run -e POSTGRES_PASSWORD=password -p 5432:5432 ankane/pgvector

Ubuntu/Debian:
sudo apt install postgresql-15-pgvector
psql -c "CREATE EXTENSION IF NOT EXISTS vector;"

Vector distance operators

pgvector uses special operators for search:

• <->: L2 (Euclidean) distance — for normalized embeddings
• <=>: Cosine distance — the most used for text
• <#>: Negative inner product — to maximize similarity

HNSW index: fast search at scale

For large collections (>100K vectors), the HNSW index enables approximate searches in milliseconds:

Key parameters:

• m (16-64): Connections per node. Higher = better quality, more memory.
• ef_construction (64-200): Index quality at build time.
• ef_search (40-200): Quality during search.

Code example

-- pgvector: create table and search for similar embeddings
CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table with embeddings
CREATE TABLE documents (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT NOT NULL,
    content    TEXT,
    category   VARCHAR(50),
    embedding  vector(1536),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW index for fast search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

-- Semantic search: top-5 most similar filtered by category
SELECT title, category,
       1 - (embedding <=> '[0.031, -0.401, ...]'::vector) AS similarity
FROM documents
WHERE category = 'HR'
ORDER BY embedding <=> '[0.031, -0.401, ...]'::vector
LIMIT 5;

-- Python: insert an embedding generated with OpenAI
import psycopg2
from openai import OpenAI

def index_document(title, content, category, conn):
    client = OpenAI()
    embedding = client.embeddings.create(
        model="text-embedding-3-small",
        input=content
    ).data[0].embedding

    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO documents (title, content, category, embedding) VALUES (%s,%s,%s,%s)",
            (title, content, category, embedding)
        )
    conn.commit()