pgvector: Vector Similarity for PostgreSQL

Overview pgvector is an open-source extension for PostgreSQL that enables storing, querying, and indexing vectors. It turns the world's most popular relational database into a Vector Database.

The "One Database" Argument Instead of adding a new piece of infrastructure (Milvus/Pinecone) just for vectors, use your existing primary database. This simplifies:

• ACID Compliance: Transactions cover both data and vectors.
• Joins: Join user tables with embedding tables easily.
• Backups: Standard Postgres backups work.

Features

• Data Type: vector(384) column type.
• Distance Metrics: L2 (Euclidean), Inner Product, Cosine Distance.
• Indexing: IVFFlat and HNSW indexes for speed.

Usage

-- 1. Enable Extension
CREATE EXTENSION vector;

-- 2. Create Table
CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding vector(3)
);

-- 3. Insert
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

-- 4. Query (Nearest Neighbor)
-- Find 5 nearest neighbors to [1,2,3] using L2 distance (<->)
SELECT * FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5;

Performance While dedicated vector DBs might be marginally faster at massive scale (100M+), pgvector is fast enough for 99% of use cases (millions of vectors) and offers vastly superior operability.

Adoption Supported by: Supabase, AWS RDS, Azure Cosmos DB, Google Cloud SQL.