pgvector

Why It Matters

pgvector brings vector search to PostgreSQL, eliminating the need for a separate vector database in many applications. If you already run PostgreSQL for your application data, adding pgvector means vectors live alongside your existing tables. You can join similarity search results with user data, product catalogs, or any other relational data in a single query.

This architectural simplicity has real benefits. One database to manage, backup, and monitor. Transactional consistency between your application data and embeddings. Familiar SQL for complex queries that combine filtering, aggregation, and similarity search.

For AI engineers, pgvector is often the pragmatic choice for moderate-scale applications. It won’t match the performance of dedicated vector databases at millions of vectors, but for thousands to hundreds of thousands of embeddings, pgvector delivers good performance with minimal operational complexity.

Implementation Basics

pgvector extends PostgreSQL with a vector data type and operators:

Vector columns store embeddings. Create a column with vector(1536) for OpenAI embeddings, then insert embeddings as arrays. Indexes significantly improve query performance.

Distance operators compute similarity. Use <-> for L2 distance, <#> for inner product, <=> for cosine distance. Most text embeddings work best with cosine distance.

HNSW indexes enable fast approximate search. Without an index, pgvector scans every vector, fine for small datasets, slow for large ones. HNSW indexes trade some accuracy for dramatically faster queries.

IVFFlat indexes offer an alternative to HNSW with different performance characteristics. IVFFlat requires more tuning but can be faster to build on large datasets.

Start without an index to validate your approach, then add HNSW for performance. Set the index parameters based on your accuracy requirements. Higher ef_construction and m improve recall at the cost of index size. Combine vector search with WHERE clauses for filtered similarity search. Monitor query performance and consider dedicated vector databases only when pgvector becomes the bottleneck.