pgvector, reviewed: the vector database you do not have to run

pgvector is a PostgreSQL extension that puts vectors in an ordinary table and searches them with ordinary SQL. It is not a vector database with a query language bolted on: it adds four column types, six distance operators and two index types to a database most teams already operate, under the PostgreSQL licence, which is about as permissive as licensing gets. The position taken here is that this is the right default for almost every retrieval workload up to tens of millions of vectors, and that a team standing up a dedicated vector database at that size is buying operational burden rather than capability.

It competes with Qdrant, Weaviate, Chroma and the fully managed vector services, and with every hosted Postgres that now ships the extension by default. What it removes is a whole class of infrastructure: no second service to run, no second wire protocol to authenticate, no second backup schedule, no consistency gap between the rows and the embeddings that describe them. What it keeps is every Postgres constraint, one node's memory, one node's vacuum, one node's write throughput, and those become the design inputs the moment the index stops fitting in RAM.

What it is

The first release, 0.1.0, was published on 20 April 2021, and the current release is 0.8.7, published on 1 October 2026; the repository showed 23,300 stars and 1,300 forks when checked. It supports PostgreSQL 13 and later, ships as Docker image, PGXN, APT, Yum, Homebrew and conda-forge package, and comes preinstalled on a growing list of hosted providers, which matters because a managed provider stuck on an old version is the most common way a team ends up without a security fix it believes it has.

• Licence: the PostgreSQL licence, the same permissive text PostgreSQL itself uses. No open core, no commercial tier, no feature gated behind a paid plan.
• Version 0.8.7 of 1 October 2026; the 0.8 line has carried iterative index scans since 0.8.0 in October 2024, which is the feature that made filtered search predictable.
• Four types: vector at 4 bytes per dimension, halfvec at 2, bit at one bit per dimension, and sparsevec for sparse vectors with up to 1,000 non-zero elements.
• Two index types: HNSW for the best speed-recall trade-off and no training step, IVFFlat for faster builds and a smaller memory footprint.
• Six operators usable directly in ORDER BY: L2 (<->), inner product (<#>), cosine (<=>), L1 (<+>), Hamming (<~>) and Jaccard (<%>).
• Storage and access come from Postgres itself: ACID, WAL replication, point-in-time recovery, JOINs and row-level security on the same table as the embeddings.

How it works

Without an index, a vector query is a sequential scan with an ORDER BY over the distance function: exact results, perfect recall, and a cost that tracks the table. An approximate index changes that bargain. HNSW builds a multi-layer graph over the vectors and walks it, trading a little recall for a query cost that no longer grows with the table; IVFFlat groups vectors into lists and probes a subset of them. The detail that decides everything is that the planner only reaches for an index when the query looks like ORDER BY embedding <=> $1 LIMIT n. Write the same query as ORDER BY 1 - (embedding <=> $1) DESC and the README states plainly that no index will be used.

The second detail is where the WHERE clause lands. An approximate index produces candidates and the filter is applied to them afterwards, so selectivity and recall interact: with the default hnsw.ef_search of 40 and a condition matching 10% of rows, about four rows come back. Nothing is broken, the index simply never saw the rows that were dropped. That behaviour is the most common source of reports claiming the extension returns fewer results, and it has a documented fix rather than a workaround.

Filtered search

Filtering is a first-class problem here rather than an afterthought, and the documentation works through four moves in the order a reviewer would try them. Which one applies depends on how selective the filter is, how much recall the application actually needs, and how many distinct values the filter takes: a tenant id with 50,000 values behaves nothing like a country code with eight.

• Index the filter column with a plain B-tree first. When the condition matches a small share of rows this returns exact nearest neighbours without touching an approximate index, and the README names it as the starting point.
• If the filter stays broad, turn on iterative index scans with SET hnsw.iterative_scan = relaxed_order: the graph is walked until the LIMIT is full, and strict_order is there when the distance order has to be exact.
• Bound the work. hnsw.max_scan_tuples defaults to 20,000 and hnsw.scan_mem_multiplier to one multiple of work_mem, so a selective filter degrades into a bounded scan rather than an open-ended one.
• For many distinct values, use partial indexes per value or list-partition the table. The README also notes that tenants sharing one approximate index affect each other's recall, which is a partitioning argument rather than a tuning argument.

Getting started

The whole surface is SQL, which is the reason to prefer it over a system with its own API. The snippet below is the shape of a production table: a fixed-dimension column, an exact index on the filter, an approximate index on the vector, and the one setting that decides whether a filtered query comes back short.

CREATE EXTENSION IF NOT EXISTS vector;

-- The dimension is part of the type, so every row has to match it.
CREATE TABLE chunks (
  id         bigserial PRIMARY KEY,
  tenant_id  text       NOT NULL,
  embedding  vector(1536)
);

-- Exact index on the filter first: for a selective tenant it answers the
-- whole query and the approximate index is never consulted.
CREATE INDEX ON chunks (tenant_id);

-- Bulk load with COPY, then build the approximate index on top of the data.
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- A filter matching 10% of rows with ef_search = 40 returns about four rows,
-- so the scan has to be allowed to continue past its first pass.
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.ef_search = 100;

SELECT id
FROM chunks
WHERE tenant_id = 'acme'
ORDER BY embedding <=> (SELECT embedding FROM chunks WHERE id = 42)
LIMIT 10;

Three choices in that snippet are worth defending. The dimension is part of the type, so a row written by a different embedding model fails at INSERT instead of at query time. The approximate index is built after the data, because an HNSW graph created on an empty table has nothing to link and would be rebuilt anyway. And the probe vector comes from a subselect, which is the form the planner accepts; the same query with an expression inside the ORDER BY falls back to a sequential scan without warning.

Performance

Memory is the whole performance story. A vector column costs 4 bytes per dimension plus an 8-byte header, so 1,536 dimensions are about 6 KB per row before any index, and a full-precision HNSW index over 100 million 768-dimensional vectors measured 367 GB in AWS's benchmark, roughly 3.7 GB per million vectors. The alternative types exist to attack exactly that number.

AWS published the clearest public numbers on this, running VectorDBBench v0.3.4 at top_k=100 on Aurora PostgreSQL 18.4 with pgvector 0.8.0. On LAION 100M at 768 dimensions, an r8g.4xlarge with 128 GB held a 367 GB full-precision HNSW index it could not keep in cache: 3.4 queries per second cold at concurrency 10, 3,336 once warm, recall 0.965, 16.1 hours to build. Binary quantisation with reranking cut the index to 38 GB and the build to 1.1 hours, reached 13.5 cold and 895 warm queries per second, and paid for it in recall: 0.931.

The same benchmark carries the counter-example, which is why its numbers should be read with their methodology. On Cohere 10M, whose 768-dimensional embeddings cluster near zero, binary quantisation needed a 3,000-candidate rerank to reach 0.93 recall and collapsed to 16 queries per second with a p99 of 1,640 ms, while full-precision HNSW on a 384 GB instance delivered 6,930 queries per second at 0.952 recall. Quantisation is distribution-dependent: validate recall on your own embeddings, or take halfvec and halve the index instead of guessing.

• Raise maintenance_work_mem before building an HNSW index; Postgres prints a notice when the graph stops fitting, and the README warns against raising it until the server runs out of memory.
• Load with COPY and index afterwards, and use CREATE INDEX CONCURRENTLY in production so the build does not block writes.
• VACUUM on an HNSW index can take a while; the documented speed-up is REINDEX INDEX CONCURRENTLY first, vacuum after.
• Horizontal scale is borrowed rather than built: replication and point-in-time recovery come from the WAL, and the README points at Citus, PgDog or list partitioning for sharding.

Where it falls short

The weaknesses are structural rather than unfinished. Everything runs on one Postgres node, so the index, the heap and the buffer cache compete for the same memory, and an index that stops fitting becomes an I/O problem before it becomes a recall problem. Approximate search and selective filters still fight even with iterative scans, because a bounded scan is bounded. Vacuum and index maintenance are the database's chores rather than someone else's. And there is no built-in sharding: horizontal scale means replicas, partitioning or an extension.

The honest boundary: pgvector wins while the vector work is a column of data the team already stores, and starts losing when one query has to hold a large graph, a filtered scan and the rest of the application's working set in the same memory. AWS measured that boundary at 367 GB of index for 100 million vectors and worked around it with quantisation and partitioning. Teams that do not want to own that trade have four exits, halfvec, binary quantisation with reranking, partitioning by tenant, or a dedicated store, and the first two are cheap enough that they should be tried before the fourth is discussed.

Verdict

pgvector should be the default answer to where these embeddings go for any team that already runs Postgres, and a dedicated vector database should have to argue its way past it. The extension has the unusual property that its failure modes are the failure modes of a database the team already understands: memory pressure, maintenance windows, one node's write throughput. Choose something else deliberately, at a scale or a latency target that can be named.

1. Take pgvector when the vectors describe rows the team already stores, and tenant isolation, cascading deletes or a JOIN with the source table have to be transactional.
2. Take it when the corpus is up to a few tens of millions of vectors and the filter is selective enough that a B-tree on the filter column carries most of the query.
3. Take it when the alternative is a second production system: the extension inherits backup, replication, monitoring and access control that already exist and adds nothing new to operate.
4. Do not take it when a single query has to keep a multi-hundred-gigabyte graph plus the application's working set in memory, unless halfvec or binary quantisation has already been measured on the actual embeddings.
5. Do not take it when the requirement is sustained multi-node write throughput or low-latency filtered recall across hundreds of millions of vectors; that is partitioning or a purpose-built store, and postponing it costs a migration later.

Not all embedding models produce vectors that quantize well. Validate on your data before committing. — AWS Database Blog, 18 August 2026

Sources

1. pgvector README: types, indexing, filtering and scaling
2. pgvector changelog, 0.1.0 through 0.8.7
3. PostgreSQL news: pgvector 0.8.2 released (CVE-2026-3172)
4. AWS: Scale pgvector with binary quantization on Aurora PostgreSQL
5. pgvector licence: the PostgreSQL licence