pgvector

pgvector is an open-source extension that turns an ordinary PostgreSQL database into a capable vector store, adding a `vector` column type, distance operators (`<->` for Euclidean, `<=>` for cosine, `<#>` for inner product), and index types (IVFFlat and HNSW) for fast approximate nearest neighbor search. Why it matters for AI/SaaS builders: it lets teams add semantic search or RAG retrieval to a product without introducing a second database system. Most SaaS backends already run Postgres for their core relational data (users, subscriptions, orders); pgvector means embeddings can live in the same database, in the same table even, right next to the rows they describe — joined with a plain SQL `JOIN`, filtered with a plain `WHERE` clause, and covered by the same backups, replication, and transactional guarantees as everything else. That operational simplicity is a major reason pgvector adoption exploded through 2024–2026: one fewer moving part, one fewer vendor bill, one fewer data-sync job to keep the vector store consistent with the source of truth. How it works: after `CREATE EXTENSION vector;`, you add a column like `embedding vector(1536)` to a table, populate it via your application (call an embedding API, `UPDATE` the row), and build an index — `CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);` — for fast approximate search once row counts grow past a few tens of thousands (below that, a sequential scan is often fast enough and simpler). Queries look like ordinary SQL: `SELECT id, body FROM articles ORDER BY embedding <=> $1 LIMIT 5;` returns the 5 nearest neighbors to a query vector by cosine distance. Because it's just Postgres, you can combine vector similarity with relational filtering and metadata filtering in a single query — no separate filter API, no eventual-consistency lag between two systems. Trade-offs versus dedicated vector databases: at very large scale (tens of millions of vectors with high query throughput) or when you need multi-region replication tuned specifically for vector workloads, purpose-built stores like Pinecone or Qdrant still generally out-perform and out-scale pgvector. Worked example: a project-management SaaS adds an "AI search" feature. Instead of standing up Pinecone, they add `embedding vector(1536)` to their existing `tasks` table, backfill it with a batch job calling the embedding API, and build an HNSW index. The search endpoint becomes `SELECT * FROM tasks WHERE workspace_id = $1 ORDER BY embedding <=> $2 LIMIT 10;` — semantic search that respects existing row-level tenant isolation for free, shipped in one migration and one endpoint.