PostgreSQL pgvector
pgvector is a PostgreSQL extension that adds vector similarity search to the world's most popular open-source relational database. Store embeddings alongside structured data, query with SQL, and leverage PostgreSQL's ACID transactions, replication, and backup tools. Perfect for applications that need both relational and vector data: store user profiles in tables, user embeddings in vector columns, query both in single transaction. Supports exact and approximate search (HNSW, IVFFlat), multiple distance metrics, and scales to millions of vectors. Used by Supabase, Timescale, and thousands of applications already on PostgreSQL.
Overview
pgvector eliminates the need for separate vector databases by adding vector capabilities to PostgreSQL. Store product data in tables, product embeddings in vector columns, query semantically similar products with SQL joins. Key advantages: (1) No new infrastructure—use existing PostgreSQL, (2) ACID transactions—update embeddings atomically with data, (3) Familiar SQL—no new query language, (4) Existing tools—pgAdmin, backups, replication all work. Supports up to 16,000 dimensions, HNSW and IVFFlat indexes, and L2, cosine, inner product distances.
Key Features
- **SQL Integration**: Query vectors with standard SQL—SELECT, JOIN, WHERE all work
- **ACID Transactions**: Update embeddings and data atomically, no consistency issues
- **Multiple Index Types**: HNSW (fast approximate), IVFFlat (memory-efficient), exact search
- **Hybrid Queries**: Combine vector similarity with SQL filters in single query
- **Proven Reliability**: Built on PostgreSQL's battle-tested infrastructure
- **Rich Ecosystem**: Works with all PostgreSQL tools, ORMs, drivers
Business Integration
pgvector enables AI features without infrastructure changes for PostgreSQL users. SaaS applications add semantic search to existing product catalogs without migrating data. Healthcare systems add clinical note similarity search while maintaining HIPAA-compliant PostgreSQL setup. E-commerce platforms add visual product search (image embeddings) alongside existing inventory tables. The key advantage: leverage existing PostgreSQL expertise, backup procedures, monitoring, and high availability setup—no specialized vector database operations team needed.
Implementation Example
Technical Specifications
- **Max Dimensions**: 16,000 dimensions supported
- **Index Types**: HNSW (fast), IVFFlat (memory-efficient), exact (no index)
- **Distance Metrics**: L2 (<->), Cosine (< =>), Inner Product (<#>)
- **Scale**: Millions of vectors per table, billions across sharded setup
- **Query Latency**: 10-100ms typical (disk-based, faster than specialized DBs on small datasets)
- **Integration**: Works with PostGIS, TimescaleDB, all PostgreSQL extensions
Best Practices
- Use HNSW index for >100K vectors, exact search for <10K
- Normalize vectors before storage for cosine similarity
- Use appropriate m and ef_construction for HNSW based on accuracy needs
- Partition large tables (>10M vectors) for better performance
- Combine with PostgreSQL's partitioning, replication for scale
- Monitor index size—HNSW can be 2-3× larger than data