Postgres quietly became a real vector database, and in 2026 the question is no longer whether to keep embeddings in Postgres — it’s which index to build. There are now three distinct families in play: pgvector’s HNSW graph, pgvectorscale’s StreamingDiskANN, and an emerging partition-plus-compression design that skips graphs entirely. Picking wrong is expensive in a very specific way: the index either eats your RAM budget, or it quietly returns the wrong nearest neighbours the moment a WHERE clause shows up. (We compared the two extensions themselves back in August; this is the index-level decision.)

HNSW: the default, and why it costs memory

pgvector‘s HNSW index builds a multilayer graph. It has a better speed/recall tradeoff than IVFFlat, but slower builds and a larger memory footprint. There’s no training step, so you can create it on an empty table and let it fill in as rows arrive.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE items (
  id bigserial PRIMARY KEY,
  embedding halfvec(1536)
);

CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 64);

The defaults are honest starting points: m = 16 connections per layer, ef_construction = 64, and hnsw.ef_search = 40 at query time. Raise ef_construction only if recall is measurably low — it costs build time and insert speed. Note the type limits before you commit to a model: vector tops out at 2,000 dimensions, halfvec at 4,000, bit at 64,000, and sparsevec at 1,000 non-zero elements. Storing halfvec instead of vector halves the working set, which matters because HNSW is an in-memory index — your RAM bill scales with vectors, not with rows on disk.

Two build-time levers worth knowing. The graph builds dramatically faster when it fits in maintenance_work_mem; pgvector literally warns you when it stops fitting (“hnsw graph no longer fits into maintenance_work_mem after N tuples”). And in production, always CREATE INDEX CONCURRENTLY, after loading bulk data, not before.

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;

-- compare recall of the index against exact search
BEGIN;
SET LOCAL enable_indexscan = off;   -- forces exact search
SELECT id FROM items ORDER BY embedding <=> '[1,2,3]' LIMIT 5;
COMMIT;

The filter problem you never hit in staging

This is the single most common production complaint about approximate indexes, and it isn’t a bug. With HNSW and IVFFlat, a WHERE clause is applied after the index is scanned. If your filter matches 10% of rows and ef_search is the default 40, you’ll average roughly four rows back from a query that asked for ten. Teams usually discover this the first time a search is scoped to one tenant, facility, or customer.

The fix in pgvector 0.8+ is an iterative index scan, which keeps pulling candidates until it has enough or hits a ceiling:

SET hnsw.iterative_scan = strict_order;   -- or relaxed_order
SET hnsw.max_scan_tuples = 20000;
-- or, per query, inside a transaction:
SET LOCAL hnsw.iterative_scan = strict_order;

strict_order preserves exact ordering; relaxed_order is cheaper and may return rows slightly out of order. If increasing max_scan_tuples stops helping recall, the documented next step is raising hnsw.scan_mem_multiplier. The alternative — often the better one — is to stop filtering after the fact: partial indexes per tenant, or a filtered index that carries the label (see DiskANN below).

IVFFlat: the memory-constrained fallback

IVFFlat partitions vectors into lists and probes the closest ones. It builds faster and uses less memory than HNSW, but loses on the speed/recall tradeoff. Three rules decide whether it works: create the index after the table has data, start with lists = rows / 1000 up to 1M rows and sqrt(rows) above that, and probe sqrt(lists) at query time (SET ivfflat.probes = 10). Get the list count wrong on an empty table and you’ll ship an index that has to be rebuilt.

DiskANN: keep the graph on disk

pgvectorscale complements pgvector with a diskann index (StreamingDiskANN) plus Statistical Binary Quantization, so the graph can live on disk instead of pinned in RAM. It also supports label-based filtered search built on Microsoft’s Filtered DiskANN research — you name the label column in the index, and filtering happens during traversal rather than after it.

CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE;

CREATE TABLE documents (
  id serial PRIMARY KEY,
  embedding vector(1536),
  labels smallint[],
  status text,
  created_at timestamptz
);

-- filtered search: labels are part of the index
CREATE INDEX documents_diskann_idx ON documents
  USING diskann (embedding vector_cosine_ops, labels)
  WITH (num_neighbors = 50);

Tuning is a two-knob tradeoff: diskann.query_search_list_size (default 100) sets how many extra candidates the traversal considers, and diskann.query_rescore (default 50) is how many get rescored against full-precision vectors — raise it for recall. Supported distance operators match pgvector (cosine <=>, L2 <->, inner product <#>), but be aware the index returns relaxed ordering: rows come back approximately ordered by distance, so re-sort in an outer query if exact ordering matters. Timescale’s own benchmark on 50M Cohere 768-dimension embeddings claims 28x lower p95 latency and 16x higher throughput at 99% recall versus Pinecone’s s1 index, at roughly 75% lower cost self-hosted — a vendor number for a specific dataset, so treat it as a reason to benchmark your own, not a promise.

The newest option: partition and compress, no graph

The most interesting 2026 development: the graph may not be the endpoint. Timescale published early alpha code for pg_vectorsearch, a next-generation index that uses partitioning and compression instead of graph-based indexing, aiming for faster searches with less memory. Their own README is blunt that it is early alpha and not production-ready. The takeaway for now is directional: if your index selection keeps trending toward “more RAM to hold the graph,” the ecosystem is actively working on alternatives to that tradeoff.

Quantization: buy back your RAM today

Before you re-architect, shrink the index. Binary quantization indexes a compressed form of each vector and re-ranks with full precision — a smaller, faster-to-build index that stays in memory at scale:

-- index the compressed representation...
CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

-- ...then over-fetch and re-rank with the original vectors
WITH candidates AS (
  SELECT id FROM items
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize('[1,-2,3]')
  LIMIT 20
)
SELECT i.* FROM items i JOIN candidates c USING (id)
ORDER BY i.embedding <=> '[1,-2,3]' LIMIT 5;

-- and keep an eye on what the index actually costs
SELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));

How to choose

• Under a few million vectors, fits in RAM, light filtering: HNSW on halfvec. Defaults first, tune only against measured recall.
• Memory-bound, or 10M+ vectors: DiskANN for a disk-resident graph, or binary quantization with re-ranking.
• Filtered search is core to the product: DiskANN labels, or iterative scans plus partial indexes.
• Prototype with tight RAM: IVFFlat, built after data loads, with lists and probes derived from row count.

For Houston teams — a midstream operator searching years of inspection PDFs, an energy trading desk digging through counterparty documents, a health system querying clinical notes — the draw is that the filter and the similarity search live in one query. Facility, permit, well API number, or tenant ID is a column, not a second system and a sync job. The index you choose should be the one your workload’s recall test passes — not the one with the best launch blog post.