pgvector adds a vector column type, distance operators and approximate indexes to PostgreSQL. If Postgres is already your system of record, it is usually the first store to evaluate, and often the last one you need.
Your application data already lives in PostgreSQL and you want one database, one backup and one set of roles.
Queries must join vectors to business rows: "nearest documents this user may read, from this account, updated this quarter".
Writes need to be transactional. A document and its embedding should commit together or not at all.
The corpus is large enough to matter but still fits the memory of one well-sized primary.
The team knows Postgres and does not want to operate a second distributed system.
We use Postgres with pgvector ourselves, so the advice comes from running it. It suits knowledge-base work where a client's documents, permissions and audit trail already sit in Postgres. Where the scale or latency budget needs more, we say so early and point you to Milvus or the selection guide.
Signs you need more than pgvector
A single table whose index no longer fits in memory, with no room to grow the primary.
Query concurrency that needs more cores than one primary can give.
Embeddings wider than the index types accept, with no appetite for reduced-precision types.
No Postgres today and a team that would be learning it for this one job.
What we deliver
Four things you get from a pgvector engagement
Schema and embedding design
Vector columns sized to your model, metadata columns for filtering, and a version column so re-embedding is a migration rather than a rebuild.
HNSW or IVFFlat chosen on your data, with recall measured against an exact scan rather than assumed. Build and query settings documented with their trade-offs.
Filtering, tenancy and hybrid
Query plans that respect your WHERE clauses, partial indexes where a tenant filter would starve results, row-level security, and keyword search fused with vectors.
Memory sizing for index builds, maintenance windows, monitoring queries, upgrade notes and a runbook your DBA can own. Your existing backups keep working.
How it works
Two index types, two different mechanisms
Without an index, pgvector compares the query against every row. That exact scan is the ground truth: perfect recall, with cost that grows with the table. Both index types trade a little recall for a lot of speed, in different ways.
IVFFlat clusters rows into lists and scans only the lists nearest the query. HNSW walks a layered graph from a fixed entry point, narrowing at each layer until it reaches the nearest neighbours.
IVFFlat clusters first, then scans
IVFFlat groups vectors into lists around centroids. A query finds its nearest centroids and scans only those lists. It is cheap to build and light on memory. The catch: it must be trained on representative data, so you build it after loading. If the data drifts, the centroids stop fitting and recall slips until you rebuild.
HNSW builds a graph you can walk
HNSW builds a multi-layer graph. Top layers hold few points with long hops; each layer down adds points with shorter hops. A query descends greedily, narrowing at every layer. Build cost and memory are higher, but the index can be created on an empty table and updates as rows insert. It gives better recall at a given latency, so it is our default.
Criteria
Exact scan (no index)
IVFFlat
HNSW
Recall
Perfect
Good, drifts as data changes
High, stable
Needs data before build
n/a
Yes, trains centroids
No
Inserts after build
n/a
Assigned to fixed lists
Graph updated incrementally
Build time and memory
None
Lower
Higher
Query speed at equal recall
Slowest, grows with table
Good
Best
Tuning knobs
None
lists at build, probes at query
m and ef_construction at build, ef_search at query
Best fit
Small tables, ground truth for testing
Static corpora, tight memory
Most production workloads
Getting the queries right
Distance, filtering and hybrid search
Three ways to measure distance
pgvector offers cosine distance, Euclidean (L2) distance and inner product. Use the one your embedding model was trained for; for most text models that is cosine. The index must be built with the same operator as the query, or Postgres quietly falls back to a sequential scan. Our embeddings glossary entry explains why the choice matters.
The candidate-set pitfall in filtering
An approximate index returns a fixed number of candidates before your WHERE clause runs. A selective filter, such as one tenant out of a thousand, can leave you with fewer rows than you asked for, or none. The fixes are partial indexes per tenant or category, larger candidate sets at query time, and iterative index scans where your pgvector version supports them. We check the plan with EXPLAIN on realistic data volumes.
Hybrid search without a second engine
Postgres already has full-text search with ranking. We run the keyword query and the vector query side by side and fuse the rankings in SQL with reciprocal rank fusion. Exact matches on codes, names and part numbers then sit alongside semantic recall. The hybrid search page walks through the fusion step.
Security note
Row-level security is the cleanest way to keep tenants apart in one table, and it applies to vector queries like any other. Pair it with the retrieval controls in our secure RAG architecture so a prompt can never widen a user's access.
Limits, honestly
What pgvector will not do for you
It inherits Postgres limits
One primary takes the writes. Index builds need memory and time in proportion to the table. Very wide embeddings may exceed what the index types accept, which pushes you towards reduced-precision vector types or shorter embeddings. Read replicas help read scale; they do not shard a table that has outgrown one machine.
We move clients off pgvector when one of three things happens. The index no longer fits comfortably in the primary's memory. Query concurrency needs more cores than one server can give. Or index rebuilds no longer fit the maintenance window. At that point Milvus, which we run in production, or a managed store such as Pinecone is the honest answer.
None of this is a reason to skip pgvector at the start. A working retrieval pipeline on Postgres teaches you your real query patterns, filter selectivity and growth rate. Those facts make the later choice easy. Our vector database comparison sets the options side by side, and the glossary defines the terms.
Process
How a pgvector engagement runs
Assess (week 1)
Postgres version and hosting, extension availability, data volumes, query patterns and latency targets. You get a fit or no-fit memo with the reasoning.
Design (weeks 1–2)
Schema, index choice, filter strategy, tenancy model and the embedding plan, including how re-embedding will run. We align it with your data engineering pipelines.
Build and tune (weeks 2–4)
Load, index, measure recall against an exact scan, tune query-time settings, verify plans with EXPLAIN and add hybrid queries where keywords matter.
Harden and hand over (weeks 4–6)
Monitoring queries, verified backups, a load test at expected concurrency, a runbook and a walkthrough with your DBA.
It depends on embedding width, the index type and the memory of your primary. Many production knowledge bases run comfortably on one well-sized server. When the index no longer fits in memory, or one primary cannot serve the query concurrency, we move you to a dedicated store such as Milvus.
Should we use HNSW or IVFFlat?
HNSW for most production workloads: it can be created on an empty table, updates as rows insert, and gives better recall at a given latency. IVFFlat when memory is tight, the corpus is static and you can afford periodic rebuilds. We measure both against an exact scan on your data before deciding.
Can pgvector do hybrid search?
Yes. PostgreSQL already has full-text search with ranking. We run the keyword query and the vector query side by side and fuse the two rankings in SQL. See hybrid search for the method.
Does pgvector work on managed PostgreSQL services?
The major managed PostgreSQL services ship the extension, though the available version varies. We check your provider's version first, because index features and filter behaviour differ between releases.
How do we handle multiple tenants in one table?
A tenant column, row-level security policies so a session only sees its own rows, and partial indexes per large tenant where the filter would otherwise starve the candidate set. We test the query plans on realistic data volumes.
How long does a pgvector engagement take?
Two to six weeks. The short end is an existing Postgres with clean data and a single use case; the long end adds hybrid search, tenancy and a handover to your DBA. Ask for a scoping call.
Talk to us about your AI project
Tell us what you are trying to do, which systems are involved and what is in the way. We reply within one business day with an opinion and a suggested next step.