pgvector

pgvector is an open-source PostgreSQL extension that adds vector similarity search capabilities directly to the world's most popular open-source relational database. Rather than requiring a separate vector database, pgvector lets you store embeddings alongside your existing relational data and query them using familiar SQL syntax. This "vectors in Postgres" approach has made it one of the most adopted vector search solutions, particularly among teams that already run PostgreSQL.

Installation is straightforward: install the extension, run CREATE EXTENSION vector, and add vector columns to your tables. You can then use operators like <=> (cosine distance), <#> (negative inner product), and <-> (L2 distance) in ORDER BY clauses to find the most similar vectors. Combined with WHERE clauses on regular columns, this enables filtered similarity search using standard SQL — no new query language to learn.

pgvector supports two index types for approximate nearest neighbor search: IVFFlat and HNSW. HNSW (added in pgvector 0.5.0) provides better query performance and recall out of the box, while IVFFlat can be faster to build for very large datasets. Without an index, pgvector performs exact nearest neighbor search (scanning all vectors), which guarantees perfect recall but doesn't scale beyond tens of thousands of vectors.

For AI agent applications, pgvector's strength is architectural simplicity. Instead of managing a separate vector database with its own deployment, monitoring, backup, and security configuration, you add vector capabilities to your existing PostgreSQL instance. This means vector data participates in PostgreSQL transactions, is included in regular backups, respects role-based access control, and can be joined with relational tables in a single query. An agent can retrieve relevant context and user profile data in one SQL statement.

The ecosystem around pgvector is extensive. It works with any PostgreSQL hosting provider (AWS RDS, Google Cloud SQL, Azure, Neon, Supabase, Railway), any PostgreSQL client library, and any ORM that supports custom column types. LangChain, LlamaIndex, and other frameworks provide pgvector-specific integrations. The extension is written in C for performance and is actively maintained with regular releases.

Limitations include performance that plateaus beyond a few million vectors (especially under high-concurrency workloads), lack of advanced features like quantization or multi-vector search found in dedicated vector databases, and the need to tune PostgreSQL memory settings (sharedbuffers, effectivecachesize, maintenancework_mem) for optimal index building and query performance. pgvector is ideal when you want to avoid operational complexity and your vector search needs are moderate in scale.