Pgvector

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.