pgvector turns PostgreSQL into a capable vector store for RAG and semantic search. Here is the threshold: where HNSW indexes, halfvec, and iterative scans make Postgres enough for production, and where a dedicated vector database earns its keep.
Every team building a RAG pipeline or semantic search feature eventually asks the same question: do we need a dedicated vector database, or can we just put the embeddings in the database we already run? For a large share of production workloads, the answer is the one nobody selling a vector database wants to give you. PostgreSQL with the pgvector extension is enough.
It is not enough for everyone. There is a threshold, and it is concrete: corpus size, recall targets, write churn, and how much memory you are willing to pay for. This post walks that boundary: the index types pgvector ships, the tuning knobs that move recall and latency, the 0.8.x features that changed the calculus since late 2024, and the failure modes that should push you toward Pinecone, Qdrant, or OpenSearch instead.
What pgvector Actually Gives You
pgvector is a PostgreSQL extension that adds vector data types and approximate nearest neighbor (ANN) search to a standard Postgres instance (13 and up; 0.8.0 dropped Postgres 12). You store embeddings in a column, build an index over them, and query with distance operators inside ordinary SQL. No separate service, no second consistency model, no extra thing to back up.
The extension defines several column types. The core vector type holds single-precision floats and supports up to 16,000 dimensions for storage, though indexing caps at 2,000 dimensions. To index higher-dimensional embeddings, pgvector 0.7.0 (April 2024) added halfvec, a half-precision (16-bit float) type that can be indexed up to 4,000 dimensions while cutting index size roughly in half. There is also sparsevec for sparse vectors and bit for binary vectors used in binary quantization. Distance is expressed through operators: <-> for L2/Euclidean, <=> for cosine, <#> for negative inner product, <+> for L1, and for bit vectors <~> (Hamming) and <%> (Jaccard).
A minimal end-to-end setup looks like this:
CREATE EXTENSION vector;
CREATE TABLE documents (
id bigserial PRIMARY KEY,
tenant_id int,
body text,
embedding vector(1536)
);
-- HNSW index using cosine distance
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- nearest-neighbor query
SELECT id, body
FROM documents
ORDER BY embedding <=> '[0.012, -0.43, ...]'
LIMIT 10;
The operator class in the index (vector_cosine_ops, vector_l2_ops, vector_ip_ops, or the halfvec_* variants) must match the operator you query with. Index on cosine and query with <=>. Mismatch the two and the planner silently skips the index and falls back to a sequential scan. In our experience this is the most common reason people conclude that pgvector is slow.
HNSW vs IVFFlat: Pick HNSW Almost Every Time
pgvector offers two ANN index types, and the choice between them is mostly settled. HNSW (Hierarchical Navigable Small World) arrived in pgvector 0.5.0 in August 2023 and made the extension usable for anything beyond a demo. IVFFlat predates it.
HNSW builds a multi-layer proximity graph. It delivers better recall-versus-latency than IVFFlat at the cost of slower builds and higher memory use. It needs no training step, so you can create the index on an empty table and let it fill incrementally as rows arrive. IVFFlat partitions vectors into lists clusters and probes a subset at query time. It builds faster and uses less memory, but it must be built (or rebuilt) against representative data to choose good cluster centers, and recall degrades as the data drifts from those centers.
| Dimension | HNSW | IVFFlat |
|---|---|---|
| Recall at low latency | Higher | Lower |
| Build speed | Slower | Faster |
| Memory footprint | Higher | Lower |
| Training / data needed at build | None | Yes (clusters from data) |
| Handles incremental inserts well | Yes | Degrades; needs rebuild |
| Key build params | m, ef_construction |
lists |
| Key query param | hnsw.ef_search |
ivfflat.probes |
For most production RAG and semantic-search workloads, HNSW is the right default. Reach for IVFFlat only when build time and memory are the binding constraints and you can tolerate periodic rebuilds. We go deeper on the algorithmic trade-offs in HNSW vs IVFFlat: How to Choose the Right Vector Index.
Tuning Recall and Latency
ANN search trades exactness for speed. Tuning is deciding how much recall to give up. pgvector exposes that through a handful of parameters, split between build time and query time.
For HNSW, m (default 16) sets the number of connections per node in the graph. Higher m improves recall and increases both index size and build time. ef_construction (default 64) is the size of the candidate list during index construction; raising it builds a better graph at the cost of longer builds. The most important runtime knob is hnsw.ef_search (default 40), the candidate list size at query time.
-- widen the search to lift recall on this session
SET hnsw.ef_search = 100;
SELECT id FROM documents
ORDER BY embedding <=> '[0.012, -0.43, ...]'
LIMIT 10;
Raising ef_search increases recall and latency together; it is a per-query speed/accuracy slider you can tune without rebuilding. For IVFFlat the analogous runtime knob is ivfflat.probes, the number of lists scanned, paired with the build-time lists count. The README suggests lists = rows / 1000 up to a million rows and sqrt(rows) beyond that, with probes starting at sqrt(lists) and raised until recall meets target.
Recall in ANN search is the fraction of true nearest neighbors that the approximate index actually returns. A recall of 0.95 means the index found 95 of the 100 closest vectors. Tuning pgvector is buying recall with latency and memory, then stopping at the cheapest point that meets your quality bar.
Building an HNSW index over a large table with a low maintenance_work_mem is painfully slow because the build spills out of memory; raise it for the duration of the build. And do not benchmark recall against a tiny dataset, ship defaults, and then watch recall collapse at scale. Measure recall against a held-out query set on production-sized data, not on a 10,000-row sample.
Filtered Search, Quantization, and the 0.8.x Inflection
The hardest problem in production vector search is not pure nearest-neighbor lookup. It is filtered search: "find me the closest documents for this tenant and in this date range." Naively, an ANN index returns the top candidates by distance and then a WHERE clause throws most of them away, so you can ask for 10 results and get 2. This "overfiltering" was pgvector's sharpest edge for years.
pgvector 0.8.0, released in October 2024, addressed it with iterative index scans. When a filtered query does not return enough rows, the index keeps scanning until it does, governed by hnsw.iterative_scan (off, strict_order, or relaxed_order), a ceiling of hnsw.max_scan_tuples (default 20,000, and the limit people actually hit), and hnsw.scan_mem_multiplier to bound memory. IVFFlat gets the equivalent through ivfflat.iterative_scan (which supports only relaxed_order) and ivfflat.max_probes. The same release improved the planner's cost estimation so Postgres more often picks a plain B-tree index when a filter is highly selective, instead of forcing the vector index.
The point releases since then, 0.8.1 (September 2025, Postgres 18 support) through 0.8.6 (July 2026), have been stability work: fixes for HNSW vacuuming errors, index corruption, buffer overflows in parallel builds, and memory use when indexing small tables (CHANGELOG). If you are on any 0.8.x older than 0.8.3, upgrade before you tune anything.
The numbers AWS published for pgvector 0.8.0 on Amazon Aurora PostgreSQL show why iterative scans mattered. On a 10-million-product dataset with 384-dimensional embeddings, they report filtered-query result completeness rising from as low as 1% to 100% versus the 0.7.4 baseline, and p99 latency on a top-10 query dropping from 123.3 ms to 13.1 ms at ef_search=40 with relaxed_order. Their top-1,000 query improved far less, 104.2 ms to 83.5 ms (AWS Database Blog). These are vendor figures on a favorable workload, so treat them as direction rather than a promise.
Quantization is the other way to fit more vectors in memory. Half-precision with halfvec halves index size; in our tests the recall loss is small enough to ignore for typical embedding models, and it is usually the easiest win. Binary quantization goes further, casting embeddings to bit via binary_quantize() and searching with <~> for very large indexes where a fast first pass with re-ranking on full precision is acceptable. If you have not internalized the underlying mechanics, our primer on what a vector database is covers the concepts these features implement.
Where pgvector Breaks, and What to Use Instead
The threshold comes down to RAM. HNSW only delivers good latency while the index is memory-resident, and the arithmetic is simple: a 1536-dimensional float32 embedding is about 6 KB, so 10 million vectors is roughly 60 GB of vectors before graph overhead, or roughly 30 GB with halfvec. That fits on a large single Postgres instance; 100 million does not, and once the index pages from disk, query latency falls off a cliff. Building that 10M index at m=16 also takes hours unless maintenance_work_mem is sized to hold it, and every rebuild costs the same again.
Below that line, pgvector is the right call when you already run PostgreSQL and value one operational story (one backup, one replication setup, one access-control model, and transactional consistency between vectors and the rows they describe), when the workload is read-heavy with moderate write churn, and when vectors need to be joined to relational data. Multi-tenant SaaS is the canonical case, and the pattern that makes it work is partitioning or partial indexes per tenant so each query touches a small index:
-- one small HNSW index per large tenant
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WHERE tenant_id = 42;
-- or partition the table by tenant and index each partition
CREATE TABLE documents (...) PARTITION BY LIST (tenant_id);
Move to a dedicated vector database when you are at tens to hundreds of millions of vectors or beyond, where purpose-built systems shard the index, manage memory tiers, and keep tail latency flat; our walkthrough on scaling vector search from millions to billions covers that regime. Move when you need first-class hybrid search that fuses BM25 lexical scoring with dense vectors at scale, which is OpenSearch and Elasticsearch territory. And move when write churn is high and constant: sustained thousands of inserts or updates per second into an HNSW index shows up as VACUUM lag, dead tuples the graph still has to walk, and index bloat, all competing with query traffic. Multi-region replication of a large dense index through physical replication is the same problem in another form.
| Factor | pgvector (PostgreSQL) | Dedicated or search-engine vector store (Pinecone, Qdrant, OpenSearch) |
|---|---|---|
| Corpus sweet spot | Up to ~10M vectors (fits in RAM) | 10M to billions |
| Operational overhead | Reuse existing Postgres | New service to run and learn |
| Transactional consistency with relational data | Native | External / eventual |
| Filtered + multi-tenant search | Strong (0.8.0+) | Strong |
| Hybrid lexical + vector at scale | Limited | First-class (OpenSearch) |
| Horizontal scale of the index | Manual / limited | Built-in sharding |
| High write churn | Index maintenance pressure | Designed for it |
The option most teams overlook is the split: keep metadata, access control, and the source of truth in PostgreSQL, and offload only the dense index to a specialized engine once you cross the threshold. The document and its embedding do not have to live in the same store. Postgres stays the system of record, a change-data-capture stream or a batch job pushes embeddings to the vector engine, and the application joins IDs back to Postgres for the rest. You give up transactional consistency between the two, but you keep everything else that made Postgres attractive. If you are still deciding the broader data architecture, How to Choose a Database for Your Next Project frames that decision, and Vector Database Comparison 2026 lines up the dedicated options side by side.
Key Takeaways
- Up to roughly 10 million vectors that fit in RAM, with moderate write churn, pgvector 0.8.x with HNSW is enough for production RAG and semantic search.
- Match the index operator class to the query operator, tune
hnsw.ef_searchat query time, and measure recall on production-sized data. - Past the RAM line, or for hybrid BM25+vector and heavy write churn, keep Postgres as source of truth and move only the dense index out.
If you are weighing pgvector against a dedicated vector database for a RAG or search workload, BigData Boutique can help you find the threshold for your data.
