Postgres + pgvector (Vector Search)
Definition
pgvector is a PostgreSQL extension that adds a native vector column type with similarity operators (cosine, L2, inner product) and approximate-nearest-neighbor indexes (HNSW, IVFFlat). It lets you store embeddings next to your relational data and run semantic search with plain SQL, making Postgres a fully capable vector database for most RAG workloads.
Why it matters for AI jobs
"Postgres + pgvector" appears verbatim in AI job postings because it is the pragmatic default: teams already run Postgres, and keeping vectors in the same database means joins, transactions, and access control come free. Knowing when pgvector is enough — and when to reach for a dedicated vector store — is exactly the judgment senior engineers are hired for.
Key concepts
- Embedding storage —
vector(1536)columns and distance operators (<=>cosine). - ANN indexes — HNSW vs IVFFlat: build time, recall, and memory trade-offs.
- Hybrid search — combining vector similarity with Postgres full-text search (BM25-style) and metadata filters.
- Filtered vector search — WHERE clauses + vector order-by, and their index implications.
- Scaling — table partitioning, connection pooling (pgbouncer), and read replicas.
- Alternatives — Pinecone, Qdrant, Weaviate; when scale or features justify a dedicated store.
Learning path
- Install pgvector locally (Docker), create a table with a vector column, and insert embeddings from an embedding API.
- Query top-k neighbors with cosine distance; add an HNSW index and measure the speedup.
- Implement hybrid search: combine vector scores with
tsvectorfull-text rank in one SQL query. - Wire it into a FastAPI endpoint to complete a minimal RAG search service.
- Load-test with 1M+ vectors to understand index build time, recall, and memory behavior.