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**
```sql
-- 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.