Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
AI

pgvector Indexing and Scale in PostgreSQL: HNSW vs IVFFlat

By: Md Tarek · 20 Oct 2026

pgvector Indexing and Scale in PostgreSQL: HNSW graph layers versus IVFFlat centroid lists for approximate nearest-neighbour search
pgvector Indexing and Scale in PostgreSQL: HNSW graph layers versus IVFFlat centroid lists for approximate nearest-neighbour search

Every serious RAG stack eventually hits the same wall: the embedding table grows past a few million rows and a sequential <=> scan stops fitting in your p99 budget. This note is the operational playbook we use for pgvector Indexing and Scale in PostgreSQL — how HNSW and IVFFlat actually traverse, where quantization buys you the most memory back, and how to run a full index rebuild on a live table without a single failed request.

Executive Summary & Key Takeaways

  • HNSW wins on recall-per-millisecond; IVFFlat wins on build time. On 1M × 1536-dim float32 vectors, HNSW (m=16, ef_construction=96) holds recall@10 ≥ 0.98 at p95 3–6 ms, while IVFFlat (lists=1000, probes=10) gives ~0.90 recall at 9–14 ms — but builds in ~60 s instead of ~15 min.
  • Index size is dominated by the raw payload, not the graph. 1536 dims × 4 B = 6,144 B/row ≈ 6.1 GB per million vectors. Casting to halfvec(1536) (pgvector ≥ 0.7) halves it to ~3.1 GB with < 1 recall point lost — the single cheapest scale lever available on one node.
  • `hnsw.ef_search` and `ivfflat.probes` are session GUCs, not index properties. Tune recall per request with SET LOCAL, so interactive search runs at ef_search=40 and offline evaluation runs at ef_search=200 against the same index.
  • pgvector 0.8.0 iterative scans fix filtered-search recall collapse. SET LOCAL hnsw.iterative_scan = relaxed_order keeps scanning past the first candidate batch when a WHERE tenant_id = ... predicate discards most neighbours — the root cause of the classic "expect 10 rows, get 3" bug.
  • Zero-downtime reindexing is build-then-swap. CREATE INDEX CONCURRENTLY (outside any transaction block) → validate recall with a brute-force harness → DROP INDEX + ALTER INDEX ... RENAME inside a lock_timeout-guarded transaction with exponential-backoff retries.

Architecture I: How HNSW and IVFFlat Actually Traverse

HNSW — a multi-layer proximity graph

HNSW stores every vector as a node whose level is drawn from an exponentially decaying distribution. Layer 0 contains all N nodes; each higher layer is a sparse sample, which gives the search a logarithmic express lane. Query time is a greedy descent followed by a bounded best-first beam on layer 0.

query q
   |
   v  layer 2  (sparse: ~log N nodes)
 [a]---[b]-------[c]         greedy: hop to the closest neighbour if it improves
        |
        v  layer 1
 [a]---[b]---[d]---[e]
        |
        v  layer 0  (all 1M vectors)
 [x]-[y]-[z]-[w]-[v]-[u]     ef_search-sized candidate heap -> top-k

Two costs matter in production:

1. Insert amplification. Each INSERT walks the graph, then rewires up to 2m neighbours per layer. With m=16 that is ~32 bidirectional link updates per row — sub-millisecond individually, but it means a 5,000-row/s ingest stream is really doing 160k pointer writes/s. Batch ingestion (COPY, then one CREATE INDEX) is 10–20× cheaper than row-at-a-time inserts into an existing HNSW graph. 2. Graph degradation under churn. Deleted tuples are marked and skipped, but the links are never repaired. After ~10% churn on a heavily-updated table we routinely measure a 4–7 point recall drop with no change to ef_search. REINDEX INDEX CONCURRENTLY restores it.

IVFFlat — k-means lists with a probe budget

IVFFlat clusters the corpus into lists centroids at build time and scans only the probes closest lists at query time.

 lists = 1000 centroids learned from k-means on a sample
   (C0)   (C1)   (C2)  ...  (C999)
    |      |      |            |
  ~1000  ~1000  ~1000        ~1000   vectors per inverted list
    ^
    +-- query probes only the nprobe nearest centroids (probes=10 -> ~1% scanned)

IVFFlat has one unforgivable failure mode: it must be built after the data exists. Centroids trained on 200k rows do not describe a 40M-row distribution. We once shipped an IVFFlat index (lists=4096) built on the initial 200k-row backfill of a corpus that later reached 12M chunks; recall@10 measured 0.61 six weeks later because most lists contained a mixture of three semantically unrelated clusters. HNSW degrades gracefully under distribution shift; IVFFlat degrades catastrophically.

Rule of thumb: lists ≈ rows / 1000 for up to ~1M rows, lists ≈ sqrt(rows) above that, and probes ≈ sqrt(lists) as a starting point — always verified with the recall harness below, never guessed.

Architecture II: The Quantization Ladder and Memory Budgeting

pgvector 0.7 introduced halfvec, bit, and sparsevec, plus support for indexing *expressions* — which is what makes quantization practical without a schema migration. The ladder, cheapest-to-most-aggressive:

| Type | Bytes/dim | Max indexed dims | Typical recall delta | |---|---|---|---| | vector (fp32) | 4 | 2,000 | baseline | | halfvec (fp16) | 2 | 4,000 | −0.5 to −1.5 pts | | bit (binary) | 0.125 | 64,000 | −10 to −25 pts raw; −2 to −5 pts after rerank | | sparsevec | 8 per non-zero | 1,000 non-zero | ~0 (lexical hybrids) |

Note the hard 2,000-dimension index limit on vector — a 3,072-dim text-embedding-3-large column cannot be indexed as-is. You either index the halfvec cast (4,000-dim limit) or truncate via Matryoshka.

-- fp16 index over an fp32 column: no schema change, no backfill, half the index
CREATE INDEX CONCURRENTLY doc_chunks_emb_half_hnsw
    ON doc_chunks USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
    WITH (m = 32, ef_construction = 128);

Memory budget for a single node:

index_bytes ≈ N × (dims × bytes_per_dim + 8) + N × link_overhead
link_overhead ≈ 2 × 2m × 6 B × ~1.4  ≈  540 B (m=16), 1,080 B (m=32)
build_ram    ≈ index_bytes × 1.2, per parallel worker

The last line is the trap: maintenance_work_mem is per worker during a parallel index build. Setting it to 8GB with max_parallel_maintenance_workers = 7 on a 32 GB box is a guaranteed OOM kill at 70% progress.

Two-stage retrieval with binary quantization

For corpora above ~10M vectors, the winning pattern is a bit HNSW index as a cheap prefilter, then an exact fp32 rerank of the top ~200 candidates from the heap:

SET LOCAL hnsw.ef_search = 200;
WITH candidates AS (
    SELECT id FROM doc_chunks
    WHERE tenant_id = $1
    ORDER BY (embedding::bit(1536)) <~> ($2::vector::bit(1536))   -- hamming
    LIMIT 200
)
SELECT c.id, c.body
FROM doc_chunks c JOIN candidates USING (id)
ORDER BY c.embedding <=> $2::vector                            -- exact rerank
LIMIT 10;

The bit index is 192 B/vector (~0.2 GB for 1M rows) instead of 6.1 GB, and the rerank touches 200 heap rows — a few hundred microseconds.

Architecture III: Zero-Downtime Reindex and Model Migration

A reindex on a live table has three phases, and only the last one is allowed to take a lock.

phase 1  CREATE INDEX CONCURRENTLY         (no write blocking; 2 heap passes)
           |
phase 2  recall harness: ANN vs brute force (read-only, same connection pool)
           |
phase 3  BEGIN; SET LOCAL lock_timeout='3s';
         DROP INDEX old; ALTER INDEX new RENAME TO old; COMMIT;   (< 100 ms)

Operational realities of CREATE INDEX CONCURRENTLY with pgvector:

  • It cannot run inside a transaction block — no BEGIN, no psycopg implicit transaction. Use an autocommit connection or the statement errors immediately.
  • It performs two heap scans and then waits for every transaction that started before the first scan to finish. One idle-in-transaction analytics connection with idle_in_transaction_session_timeout unset can pin the validation phase for hours. Monitor pg_stat_activity where wait_event = 'virtualxid'.
  • On failure it leaves an INVALID index that silently costs you write throughput. Always check pg_index.indisvalid and DROP INDEX CONCURRENTLY the corpse.
  • DROP INDEX takes ACCESS EXCLUSIVE on the table. Without lock_timeout, your swap queues behind a long-running SELECT, and every subsequent query queues behind the DROP — a self-inflicted outage measured in seconds.

For an embedding-model migration (dimension change, new normalizer), never ALTER COLUMN TYPE — it rewrites the table and rebuilds every index. Use a shadow column (embedding_v2 halfvec(3072)), dual-write from the application, backfill in 5k-row batches with ctid pagination, build the index concurrently, run shadow-read comparisons for 24 h, then swap.

Practical Implementation

Schema and index DDL:

CREATE EXTENSION IF NOT EXISTS vector;   -- pgvector >= 0.8.0

CREATE TABLE IF NOT EXISTS doc_chunks (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id     uuid         NOT NULL,
    doc_id        uuid         NOT NULL,
    chunk_no      integer      NOT NULL,
    model_version smallint     NOT NULL DEFAULT 1,
    body          text         NOT NULL,
    embedding     vector(1536) NOT NULL,   -- v1 model
    embedding_v2  halfvec(1536),           -- shadow column for the next model
    created_at    timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, doc_id, chunk_no)
);

-- Build knobs are session-scoped and per-worker; size them to the box.
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;

CREATE INDEX CONCURRENTLY IF NOT EXISTS doc_chunks_emb_v1_hnsw
    ON doc_chunks USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 96);

-- Runtime path: per-transaction recall knobs + filtered-search safety net.
SET LOCAL hnsw.ef_search = 64;
SET LOCAL hnsw.iterative_scan = relaxed_order;
SET LOCAL hnsw.max_scan_tuples = 20000;

SELECT id, body
FROM doc_chunks
WHERE tenant_id = $1            -- filters out ~99% of candidates
ORDER BY embedding <=> $2::vector
LIMIT 10;

Production Python module (psycopg 3) covering online build, recall validation, and the lock-safe swap:

# pgvector_ops.py - production helpers for scaling pgvector on PostgreSQL 16+
from __future__ import annotations

import logging
import os
import time
from collections.abc import Sequence
from dataclasses import dataclass

import psycopg
from psycopg import Connection, sql

DSN = os.environ['PGVECTOR_DSN']
LOG = logging.getLogger('pgvector.ops')


@dataclass(frozen=True)
class HnswSpec:
    table: str
    column: str
    index_name: str
    m: int = 16
    ef_construction: int = 96
    maintenance_work_mem: str = '8GB'
    parallel_workers: int = 7
    timeout_s: int = 14_400


def _drop_if_invalid(conn: Connection, index_name: str) -> None:
    # CREATE INDEX CONCURRENTLY leaves an INVALID index behind when it fails.
    row = conn.execute(
        'SELECT indisvalid FROM pg_index WHERE indexrelid = to_regclass(%s)',
        (index_name,),
    ).fetchone()
    if row is not None and not row[0]:
        LOG.warning('dropping INVALID index %s', index_name)
        conn.execute(
            sql.SQL('DROP INDEX CONCURRENTLY IF EXISTS {}').format(
                sql.Identifier(index_name)
            )
        )


def build_hnsw_online(spec: HnswSpec, *, opclass: str = 'vector_cosine_ops') -> float:
    ddl = sql.SQL(
        'CREATE INDEX CONCURRENTLY IF NOT EXISTS {idx} ON {tbl} '
        'USING hnsw ({col} {ops}) WITH (m = {m}, ef_construction = {efc})'
    ).format(
        idx=sql.Identifier(spec.index_name),
        tbl=sql.Identifier(spec.table),
        col=sql.Identifier(spec.column),
        ops=sql.SQL(opclass),
        m=sql.Literal(spec.m),
        efc=sql.Literal(spec.ef_construction),
    )
    started = time.monotonic()
    # Autocommit is mandatory: CREATE INDEX CONCURRENTLY cannot run inside a txn.
    with psycopg.connect(DSN, autocommit=True) as conn:
        for guc, value in (
            ('statement_timeout', f'{spec.timeout_s}s'),
            ('maintenance_work_mem', spec.maintenance_work_mem),
            ('max_parallel_maintenance_workers', str(spec.parallel_workers)),
            ('lock_timeout', '5s'),
        ):
            conn.execute(sql.SQL('SET {} = {}').format(sql.Identifier(guc), sql.Literal(value)))
        try:
            conn.execute(ddl)
        except Exception as exc:
            LOG.error('HNSW build failed for %s: %s', spec.index_name, exc)
            _drop_if_invalid(conn, spec.index_name)
            raise
    elapsed = time.monotonic() - started
    LOG.info('built %s in %.1fs', spec.index_name, elapsed)
    return elapsed


_ANN = sql.SQL(
    'SELECT id FROM {tbl} WHERE tenant_id = %s ORDER BY {col} <=> %s::vector LIMIT %s'
)


def recall_at_k(
    conn: Connection,
    *,
    table: str,
    column: str,
    tenant_id: str,
    queries: Sequence[Sequence[float]],
    k: int = 10,
    ef_search: int = 64,
) -> float:
    # Micro-averaged recall@k of the ANN plan against a forced brute-force scan.
    stmt = _ANN.format(tbl=sql.Identifier(table), col=sql.Identifier(column))
    hits = 0
    total = 0
    for vec in queries:
        literal = '[' + ','.join(f'{x:.6f}' for x in vec) + ']'
        with conn.transaction():
            conn.execute(
                sql.SQL('SET LOCAL hnsw.ef_search = {}').format(sql.Literal(ef_search))
            )
            ann = [r[0] for r in conn.execute(stmt, (tenant_id, literal, k))]
        with conn.transaction():
            for guc in ('enable_indexscan', 'enable_bitmapscan', 'enable_indexonlyscan'):
                conn.execute(sql.SQL('SET LOCAL {} = off').format(sql.Identifier(guc)))
            exact = [r[0] for r in conn.execute(stmt, (tenant_id, literal, k))]
        hits += len(set(ann) & set(exact))
        total += k
    return hits / total if total else 0.0


def swap_indexes(new_index: str, live_index: str, *, attempts: int = 5) -> None:
    # Promote the freshly built index. lock_timeout prevents our ACCESS EXCLUSIVE
    # DROP from queueing behind long readers and stalling the whole table.
    for attempt in range(1, attempts + 1):
        try:
            with psycopg.connect(DSN) as conn:
                conn.execute(
                    sql.SQL('SET LOCAL lock_timeout = {}').format(sql.Literal('3s'))
                )
                conn.execute(
                    sql.SQL('DROP INDEX IF EXISTS {}').format(sql.Identifier(live_index))
                )
                conn.execute(
                    sql.SQL('ALTER INDEX {} RENAME TO {}').format(
                        sql.Identifier(new_index), sql.Identifier(live_index)
                    )
                )
            LOG.info('promoted %s as %s', new_index, live_index)
            return
        except psycopg.errors.LockNotAvailable:
            backoff = min(0.5 * 2 ** attempt, 8.0)
            LOG.warning(
                'lock contention on attempt %d/%d, retrying in %.1fs',
                attempt, attempts, backoff,
            )
            time.sleep(backoff)
    raise RuntimeError(f'gave up swapping {new_index} in after {attempts} attempts')


if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)s %(message)s')
    spec = HnswSpec(table='doc_chunks', column='embedding', index_name='doc_chunks_emb_v1_hnsw_new')
    build_hnsw_online(spec)
    with psycopg.connect(DSN) as conn:
        score = recall_at_k(
            conn,
            table='doc_chunks',
            column='embedding',
            tenant_id=os.environ['CANARY_TENANT_ID'],
            queries=[[0.013] * 1536],   # replace with 25-50 sampled queries
            k=10,
            ef_search=64,
        )
        LOG.info('recall@10 = %.3f', score)
    if score >= 0.95:
        swap_indexes('doc_chunks_emb_v1_hnsw_new', 'doc_chunks_emb_v1_hnsw')
    else:
        raise SystemExit(f'recall gate failed: {score:.3f} < 0.95')

Performance Benchmarks & Trade-Offs

Representative single-node measurements, 1M rows × 1536 dims, PostgreSQL 16.3 + pgvector 0.8.0, 16 vCPU / 64 GB RAM / NVMe, warm cache, k=10, cosine distance. Expect ±10–15% variance across hardware; the ratios are what matter.

| Strategy | Index size | Build time | p95 latency | recall@10 | Write amplification | Best for | |---|---|---|---|---|---|---| | Exact seq scan (no index) | 6.1 GB heap | n/a | 480–900 ms | 1.00 | none | < 50k rows, strict correctness | | IVFFlat lists=1000, probes=10, fp32 | 6.3 GB | 45–75 s | 9–14 ms | 0.88–0.92 | low | append-only bulk corpora, tight RAM | | HNSW m=16, ef_construction=96, fp32 | 6.9 GB | 12–18 min | 3–6 ms | 0.98 | high | default for read-heavy RAG | | HNSW m=32, ef_construction=128, halfvec | 3.9 GB | 9–14 min | 2.5–4.5 ms | 0.96–0.97 | high | memory-constrained, needs high recall | | Binary bit(1536) HNSW prefilter + fp32 rerank@200 | 0.35 GB | 3–6 min | 2–4 ms | 0.95–0.98 | high | 10M+ vectors, cost-tuned | | HNSW halfvec + iterative_scan (tenant filter ~1% selectivity) | 3.9 GB | 9–13 min | 5–9 ms | 0.97 | high | multi-tenant filtered search |

Reading the table: IVFFlat is a build-time optimization, HNSW is a query-time optimization. If your corpus is rebuilt nightly and queries are acceptable at ~90% recall, IVFFlat is 15× cheaper to maintain. If recall is a product requirement, pay the build time.

Pro-Tips & Common Pitfalls

  • Never build IVFFlat on an empty or partially backfilled table. Centroids trained on 5% of the corpus produce lists that lie about the remaining 95%. Gate the build on rows >= expected_rows * 0.95.
  • `maintenance_work_mem` is per worker. 8GB × 7 workers on a 32 GB instance OOMs. Cap total build RAM at ~50% of physical memory.
  • Don't insert row-by-row into a live HNSW index. Bulk COPY into a staging table plus CREATE INDEX CONCURRENTLY is 10–20× faster than 5k single-row inserts.
  • `REINDEX` after 5–10% churn. Graph links rot under deletes and updates; recall drifts down silently while latency stays flat, so alert on recall, not latency.
  • Set `hnsw.ef_search` ≥ `LIMIT`. Filtering happens after graph traversal, so a LIMIT 10 with ef_search = 10 returns fewer than 10 rows on any selective predicate. Use iterative_scan instead of brute-forcing ef_search to 1000.
  • `CREATE INDEX CONCURRENTLY` needs autocommit and must not be wrapped in an ORM migration transaction (Django atomic=True, Alembic transaction_per_migration) or it fails instantly.
  • Always `SET LOCAL lock_timeout` before DROP INDEX / ALTER TABLE. Without it, one long reader turns your swap into a table-wide outage.
  • Index `halfvec` before you shard. Halving bytes/dim is free recall; adding a second node costs a monthly invoice and a distributed-query layer.
  • Verify with `EXPLAIN (ANALYZE, BUFFERS)` that you got an Index Scan using ...hnsw and not a seq scan — a mismatched opclass or a ::vector cast on the indexed column silently disables the index.
  • Watch `pg_stat_progress_create_index` during builds. A stalled blocks_total with phase = building index usually means you are swapping to disk.

Frequently Asked Questions (FAQ)

Should I use HNSW or IVFFlat in pgvector? Use HNSW as the default for anything read-heavy or above ~1M vectors: it delivers roughly 0.98 recall@10 at single-digit-millisecond p95 and degrades gracefully when the distribution shifts. Choose IVFFlat only when index build time or memory is the binding constraint — for example a corpus rebuilt nightly where ~0.90 recall is acceptable and you need the build to finish in under two minutes. IVFFlat also requires the data to exist before the index is created, so it is a poor fit for continuously growing tables.

How do I make a pgvector HNSW index build faster on a large table? Three levers, in order of impact: raise maintenance_work_mem (cap total at ~50% of RAM across all workers), raise max_parallel_maintenance_workers to about vCPU - 1, and reduce vector width by indexing halfvec instead of vector, which halves both build time and final index size for under one recall point. If you only need an index on a subset — say the last 90 days or a single tenant — build a partial index (WHERE created_at > now() - interval '90 days') and the build cost scales with the filtered row count, not the table.

Can I change embedding model or vector dimensions without downtime? Yes, with the shadow-column pattern. Add embedding_v2 halfvec(3072), dual-write from the application for both models, backfill historical rows in paginated batches, then CREATE INDEX CONCURRENTLY on the new column while the old index keeps serving traffic. Validate with a recall harness and shadow-read comparison for at least 24 hours, then flip reads in a feature flag and drop the old column during a quiet window. Never ALTER COLUMN ... TYPE vector(3072) on a live table — it rewrites the heap and rebuilds every index under an ACCESS EXCLUSIVE lock.