pgvector

pgvector is a PostgreSQL extension enabling vector similarity search directly within the database, eliminating need for separate vector database infrastructure. Free and open-source (PostgreSQL license). The extension adds vector data type and operators for exact (L2, inner product, cosine) and approximate (IVFFlat, HNSW) nearest neighbor search. HNSW indexes (added in 0.5.0) provide excellent query performance with configurable accuracy/speed tradeoffs. Key advantage: vector search joins naturally with relational data, critical for text-to-SQL where schema context and query examples live alongside metadata. Supported in PostgreSQL 11+, AWS RDS, Azure Database, Google Cloud SQL, Supabase, and Neon. For text-to-SQL: embed schema descriptions, query examples, and documentation in same database as application data, enabling simple JOIN-based context retrieval. Limitations include single-node architecture and memory requirements for large indexes. Page should cover: installation and setup, index type selection (IVFFlat vs HNSW), managed PostgreSQL support matrix, integration with application code, performance tuning, comparison with standalone vector databases, and text-to-SQL RAG implementation pattern.