pgvector —
vector search, built into PostgreSQL.
pgvector is the open-source extension that turns PostgreSQL into a vector database. Store embeddings in a column, build an HNSW or IVFFlat index, and query nearest neighbours in plain SQL — alongside the joins, filters, and transactions you already use. This is the practical guide: what it is, how to use it, and where to run it.
What is pgvector?
A vector type, distance operators, and ANN indexes — inside Postgres.
A vector type and operators
pgvector adds a vector column type to PostgreSQL plus distance operators: <-> (L2), <=> (cosine), and <#> (inner product). You store an embedding as a column on the row it describes and rank by similarity with ORDER BY embedding <=> $1.
ANN indexes — HNSW & IVFFlat
Two approximate-nearest-neighbour index types make search fast at scale: HNSW (a navigable graph, the production default) and IVFFlat (inverted lists, cheaper to build). Both trade a little recall for large speed-ups over exact scans.
Just PostgreSQL underneath
Because it is an extension, everything else is normal Postgres: transactions, joins, WHERE filters, row-level security, backups, replication. Your vectors live next to your data, with no separate vector database to run.
HNSW vs IVFFlat
The two pgvector index types, and when to use each.
| HNSW | IVFFlat | |
|---|---|---|
| Structure | Navigable small-world graph | Inverted lists (k-means clusters) |
| Build time | Slower, more memory | Fast, low memory |
| Query speed | Fastest, best recall/latency | Good, depends on probes |
| Updates | Handles inserts well | Best on static data; re-train lists |
| Key params | m, ef_construction, ef_search | lists, probes |
| Use when | Most production workloads | Static dataset, low build cost |
Using pgvector in three steps
Enable the extension
CREATE EXTENSION vector; — on Rivestack it is already enabled, so this is a no-op. Add a column: ALTER TABLE docs ADD COLUMN embedding vector(1536);
Insert embeddings
Generate vectors with OpenAI, Cohere, or your own model and INSERT them like any other column — in the same transaction as the content they describe.
Index and query
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops); then SELECT ... ORDER BY embedding <=> $1 LIMIT 10 — combined with normal WHERE filters and joins.
pgvector FAQ
Common questions about vector search in PostgreSQL.
pgvector is an open-source PostgreSQL extension that adds a vector data type and vector similarity search. It lets you store embeddings (arrays of floats produced by an AI model) in a regular table column and query for nearest neighbours using distance operators, with optional HNSW or IVFFlat indexes for speed. It turns PostgreSQL into a capable vector database without adding a separate system.
The common uses are retrieval-augmented generation (RAG), semantic search, recommendation systems, deduplication, and image or audio similarity. Anywhere you have embeddings and want to find the most similar items — often combined with relational filters like tenant, language, or recency — pgvector handles it in SQL.
HNSW builds a navigable graph and is the production default: highest recall and lowest query latency, at the cost of slower builds and more memory. IVFFlat groups vectors into lists and is faster and cheaper to build but generally needs tuning (the probes parameter) and suits static datasets. For most workloads, use HNSW; choose IVFFlat when build time and memory matter more than peak query speed.
On self-hosted Postgres you build or install the extension, then run CREATE EXTENSION vector. On managed services it is usually available to enable. On Rivestack, pgvector 0.8.x ships pre-installed and tuned on every database, so CREATE EXTENSION vector is a no-op and you can start inserting embeddings immediately.
pgvector supports up to 16,000 dimensions for the vector type (and up to 2,000 dimensions for an indexed column with the default ops). In practice most teams use 384–1,536 dimensions to match popular embedding models like OpenAI text-embedding-3 or open-source sentence transformers.
Yes. With an HNSW index, pgvector serves tens of millions of vectors with single-digit-millisecond latency on appropriate hardware. The main factors are memory for the index, storage latency on cache misses (NVMe helps a lot here), and the ef_search / m parameters. See our NVMe vs cloud SSD benchmarks for measured numbers.
For most teams, yes. Keeping vectors in PostgreSQL means filters, joins, and tenancy are plain SQL and there is no second datastore to sync. Dedicated vector databases still make sense at very large scale (hundreds of millions to billions of vectors) or for pure vector workloads with no relational context.
Anywhere you run PostgreSQL: self-hosted, Supabase, Neon, RDS, Aiven, or a focused managed service. The differences that matter for vector search are storage latency, memory headroom, which pgvector version is supported, and whether HNSW is tuned. Rivestack runs pgvector on dedicated local NVMe with tuning and backups included — see the managed pgvector page.
pgvector guides
Go deeper on setup, tuning, and trade-offs.
Getting started with pgvector
Enable the extension, create an HNSW index, and run your first similarity query.
pgvector HNSW tuning
Choosing m, ef_construction, and ef_search for recall and latency on managed Postgres.
pgvector vs Pinecone
When Postgres is the right home for vectors, and when a dedicated store wins.
Related: PostgreSQL for AI · managed PostgreSQL · managed pgvector
