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 Tuning

By: Md Tarek · 19 Oct 2026

pgvector indexing and scale in PostgreSQL with HNSW vs IVFFlat index architecture benchmark
pgvector indexing and scale in PostgreSQL with HNSW vs IVFFlat index architecture benchmark

Executive Summary & Key Takeaways

  • HNSW wins on recall-per-millisecond above ~1M vectors; IVFFlat wins on build cost. On our 1M x 768-d reference corpus, HNSW with m=16, ef_construction=64, hnsw.ef_search=40 delivered recall@10 = 0.941 at p95 3.4 ms with a 4.4 GB index. IVFFlat with lists=1000, probes=10 reached only 0.812 recall at p95 2.6 ms, despite building 4.5x faster (4 min vs 18 min).
  • `halfvec` is the highest-ROI compression lever. Casting 768-d float32 embeddings to halfvec(768) cut the HNSW index from 4.4 GB to 2.9 GB (~34% smaller) with recall@10 0.938 vs 0.941 and p99 6.8 ms vs 7.9 ms.
  • Binary quantization only works as a two-stage retriever, never as a standalone index. Pure bit(768) Hamming HNSW scored 0.782 recall, but retrieving 400 candidates and reranking them with full-precision <=> recovered 0.964 recall at a 0.25 GB index — roughly 17x smaller than float32 HNSW.
  • Filtered vector search silently collapses recall unless you enable iterative scans or use partial/partitioned indexes. pgvector filters *after* the index scan, so a 1%-selective WHERE tenant_id = $1 can push recall below 0.5 on pgvector < 0.8.0; SET hnsw.iterative_scan = relaxed_order (plus hnsw.max_scan_tuples) is mandatory for multi-tenant tables.
  • Build parameters cannot be `ALTER`ed — plan for a shadow-index swap. Create a new index with CREATE INDEX CONCURRENTLY, verify pg_index.indisvalid, then rename both indexes inside a short transaction guarded by lock_timeout. The rename is metadata-only (<1 ms), but an unguarded ACCESS EXCLUSIVE request queued behind a 60-second analytics query will stall every subsequent write on the table.

Why pgvector Index Tuning Is a Systems Problem, Not a SQL Problem

Approximate nearest-neighbour (ANN) search in Postgres has three independent failure modes that look identical from the application side: recall loss (you get plausible but wrong neighbours), tail latency cliffs (p99 explodes while p50 stays flat), and recall collapse under filters (the index returns k rows, the WHERE clause keeps 3 of them). Each is fixed by a different subsystem — index topology, memory/page-cache budget, and query-plan shape respectively.

Understanding the topology is the prerequisite. The two pgvector index types are structurally unrelated:

HNSW (proximity graph)                    IVFFlat (inverted file + k-means)

layer 2:   A ------------------> F        centroids C1..Ck  (k = lists)
layer 1:   A ----> C ----> F               |
layer 0:   A-B-C-D-E-F-G-H-...             +-- list C1: [vec, vec, vec, ...]
           (every vector, 2*m edges)      +-- list C2: [vec, vec, ...]
                                           +-- list Ck: [vec, ...]
query: greedy descent -> ef beam @ L0     query: scan `probes` nearest lists
cost : O(ef_search * m * log N) visits    cost : O(N/lists * probes) distance ops
build: incremental insert per row         build: k-means over the whole table

HNSW builds a navigable small-world graph. Every node is assigned a level via a geometric distribution (level = floor(-ln(U) * 1/ln(m))), which produces the logarithmic hop count. A query enters at the top layer's entry point, greedily descends to the highest layer where the candidate set can still improve, and then runs a breadth-limited beam search of width hnsw.ef_search on layer 0. Because layer 0 contains *every* vector and each node keeps 2*m neighbours, HNSW recall is remarkably stable across data distributions — but the graph's random page access pattern makes p99 latency a direct function of how much of the index fits in shared_buffers + OS page cache.

IVFFlat runs k-means to produce lists centroids, then assigns each vector to its nearest centroid. A query computes distances to all centroids (a cheap sequential scan over lists * dims floats), picks the probes closest, and brute-forces those lists. This is a *partitioning* strategy, not a graph: recall depends entirely on whether the true neighbours landed in a probed list, which depends on cluster balance and on how stale the centroids are relative to the current data distribution.

The practical consequences are blunt:

  • IVFFlat must be built after your table is populated. An IVFFlat index built on an empty or 1%-loaded table has centroids that do not represent the final distribution, and recall degrades permanently as inserts skew toward new regions of the embedding space.
  • HNSW handles incremental inserts correctly but expensively: each INSERT walks the graph and rewires neighbours. Bulk-loading 10M rows with five HNSW indexes live is an order of magnitude slower than COPY into an unindexed table, then building the index once.
  • Neither index stores a tombstone-free structure. Heavy UPDATE/DELETE churn inflates HNSW page count and degrades traversal; a periodic rebuild is part of the capacity plan, not an emergency.

The memory math that decides your p99

For 1M rows at 768 dimensions:

| Component | Formula | 1M x 768-d | |---|---|---| | Raw vector payload | N * d * 4 B | 3.07 GB | | Raw halfvec payload | N * d * 2 B | 1.54 GB | | Raw bit payload | N * d / 8 B | 96 MB | | HNSW L0 neighbour arrays | N * 2m * 4 B | 128 MB @ m=16 | | HNSW observed total | payload + arrays + tuple headers + page fill + vacuum bloat | 4.4 GB @ m=16 |

That last row is the one that bites. The algorithmic graph overhead at m=16 is only ~4% of the index, yet observed HNSW indexes run 1.3–1.5x the raw payload because of 8 KB page fill, per-tuple headers, and dead-tuple bloat that VACUUM cannot fully repack (there is no REPACK for HNSW graphs). Budget the index working set at under 50% of total RAM; past that, each beam-search hop can touch a cold page, and a 40-µs NVMe read multiplied by 200 hops is a 8 ms p99 spike. This is exactly the difference between the 7.9 ms and 15.4 ms p99 figures in the benchmark table below.

> War story: a 12M-row support-ticket index at m=16 hit 31 GB on a 32 GB instance. p50 stayed at 2.1 ms while p99 went to 180 ms and the API gateway started shedding load. Moving to halfvec(768) plus m=12 dropped the index to 14 GB and brought p99 back to 11 ms — with no measurable recall change on that corpus.

Vector Quantization: halfvec, Binary Quantization, and Two-Stage Reranking

Quantization in pgvector 0.8.x is not a storage trick bolted on afterwards — it is expressed as an expression index, which means the planner only uses it if your ORDER BY expression matches the index expression byte-for-byte.

Tier 1: `halfvec` (float16, ~2x compression)

halfvec is a first-class type, not a cast hack. It supports up to 4,000 dimensions for indexing (vs 2,000 for vector) and its own opclasses. Recall loss is typically under 0.5 points for 512–1536-d text embeddings, because cosine similarity between unit-normalised embeddings is dominated by the large-magnitude components, which float16 preserves fine.

-- Store full precision, index half precision. Rerank uses the exact value.
CREATE INDEX CONCURRENTLY idx_chunks_hnsw_half
  ON document_chunks
  USING hnsw ((embedding::halfvec(768)) halfvec_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Query must repeat the cast exactly, or the index is ignored.
SELECT chunk_id
FROM document_chunks
ORDER BY embedding::halfvec(768) <=> $1::halfvec(768)
LIMIT 10;

The cast at query time is nearly free (halfvec conversion is a few hundred nanoseconds per vector), and the reranking step later in the pipeline still uses the float32 column, so you never lose precision in the final ranking.

Tier 2: binary quantization + rerank (up to ~32x compression)

Binary quantization thresholds each dimension to one bit and uses Hamming distance. On its own it is too lossy for high-recall retrieval (0.782 recall@10 on our corpus), because it discards magnitude information entirely. The correct architecture is coarse retrieval + exact rerank: fetch k * 40 candidates from the binary index (orders of magnitude cheaper per hop), then score those candidates exactly with <=> against the full-precision column.

CREATE INDEX CONCURRENTLY idx_chunks_hnsw_binary
  ON document_chunks
  USING hnsw ((binary_quantize(embedding)::bit(768)) bit_hamming_ops)
  WITH (m = 16, ef_construction = 64);

At 400 rerank candidates the extra cost is a 400 x 768 float32 scan — about 1.2 MB of sequential page reads, which measured at roughly +0.7 ms p95 in our harness. That is the entire price of a 17x index reduction. Tune rerankCandidates by measuring recall against brute-force ground truth; for most corpora the curve flattens between 10x and 50x the requested k.

Tier 3: dimensionality reduction

If your embedding provider supports Matryoshka-style truncation (text-embedding-3-* with the dimensions parameter) or you can re-embed, truncating 1536-d to 512-d cuts *payload and graph together* — a larger win than quantization alone — because HNSW traversal cost scales with vector width. Re-normalise after truncation when using cosine distance. This is a modelling decision, not a database one, but it is usually the single biggest lever on cost.

Zero-Downtime Reindexing Without Melting WAL or Blocking Writes

There is no ALTER INDEX ... SET (m = 32). HNSW and IVFFlat bake their build parameters into the index pages, so any parameter change is a full rebuild. The safe pattern has five phases:

[1] pg_try_advisory_lock          -> single-flight guard, exit if held
[2] DROP INDEX CONCURRENTLY any INVALID shadow from a previous failed run
[3] CREATE INDEX CONCURRENTLY     -> autocommit; no tx block; SHARE UPDATE EXCLUSIVE
[4] verify pg_index.indisvalid    -> abort and keep serving the old index if false
[5] BEGIN; ALTER INDEX ... RENAME x2; COMMIT;  -> lock_timeout=5s, retry x3
[6] DROP INDEX CONCURRENTLY old   -> reclaim disk in the background

Four non-obvious constraints drive this design:

1. `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block. That means you cannot wrap it in a PL/pgSQL function (every function body is a transaction), and it means Pool.query must be called on a client that is *not* in an explicit BEGIN. This is the number-one reason teams end up with a half-migrated index. 2. A failed CIC leaves an INVALID index. CREATE INDEX CONCURRENTLY IF NOT EXISTS will then *succeed silently* on the next run without rebuilding anything. Always check pg_index.indisvalid before and after. 3. **REINDEX INDEX CONCURRENTLY (PG 12+) rebuilds with the *stored* parameters. It is useful for reclaiming bloat, but it will never change `m` or `ef_construction`. Also verify AM support on your exact pgvector/Postgres combination before relying on it in a runbook. 4. The rename transaction needs lock_timeout.** The rename itself is a catalog-only operation taking microseconds, but it requests ACCESS EXCLUSIVE. Without a timeout, that request queues behind a long-running read, and every query arriving afterwards queues behind the rename — a full write stall on the table. With lock_timeout = '5s', you either get the lock instantly or you retry.

WAL volume is the other operational hazard: building a 4.4 GB HNSW index on 1M rows emitted ~5.1 GB of WAL in our test, amplified by full_page_writes during the first touch of every page. If you run streaming replicas, check pg_stat_replication lag and consider raising max_wal_size for the build window rather than letting checkpoint storms compound the I/O pressure.

Practical Implementation

1. Schema and indexes

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  chunk_id     bigserial PRIMARY KEY,
  document_id  uuid        NOT NULL,
  tenant_id    uuid        NOT NULL,
  locale       text        NOT NULL DEFAULT 'en',
  content      text        NOT NULL,
  embedding    vector(768) NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

-- Primary recall path for unfiltered / lightly filtered queries.
CREATE INDEX CONCURRENTLY idx_chunks_embedding_hnsw
  ON document_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Coarse path for the two-stage binary retriever.
CREATE INDEX CONCURRENTLY idx_chunks_embedding_binary
  ON document_chunks USING hnsw ((binary_quantize(embedding)::bit(768)) bit_hamming_ops)
  WITH (m = 16, ef_construction = 64);

-- Filtered path: a partial index keeps the graph small and the filter exact.
CREATE INDEX CONCURRENTLY idx_chunks_embedding_hnsw_tenant
  ON document_chunks USING hnsw (embedding vector_cosine_ops)
  WHERE tenant_id = '00000000-0000-0000-0000-000000000000';

2. Production two-stage search (TypeScript + node-postgres)

import { Pool, PoolClient } from 'pg';

export type Metric = 'cosine' | 'l2';

export interface SearchOptions {
  readonly k: number;
  readonly tenantId: string;
  readonly locale?: string;
  readonly efSearch?: number;
  readonly rerankCandidates?: number;
  readonly timeoutMs?: number;
}

export interface SearchHit {
  readonly chunk_id: string;
  readonly document_id: string;
  readonly content: string;
  readonly score: number;
}

/** Whitelisted operators: these are interpolated into SQL, never bound. */
const OPERATOR: Record<Metric, string> = {
  cosine: '<=>',
  l2: '<->',
};

/** Serialise a Float32Array as a pgvector literal, rejecting NaN/Inf. */
export function toPgVector(v: Float32Array): string {
  const parts = new Array<string>(v.length);
  for (let i = 0; i < v.length; i++) {
    const x = v[i];
    if (!Number.isFinite(x)) {
      throw new RangeError(`embedding[${i}] is not finite: ${x}`);
    }
    parts[i] = x.toFixed(6);
  }
  return `[${parts.join(',')}]`;
}

export class PgVectorStore {
  constructor(
    private readonly pool: Pool,
    private readonly dims: number,
    private readonly metric: Metric = 'cosine',
  ) {}

  async search(query: Float32Array, opts: SearchOptions): Promise<SearchHit[]> {
    if (query.length !== this.dims) {
      throw new RangeError(`expected ${this.dims} dims, got ${query.length}`);
    }

    const op = OPERATOR[this.metric];
    const efSearch = opts.efSearch ?? 80;
    const candidates = opts.rerankCandidates ?? opts.k * 40;
    const vectorLiteral = toPgVector(query);

    // AS MATERIALIZED guarantees the LIMIT runs against the binary index first.
    const sql = `
      WITH candidate_pool AS MATERIALIZED (
        SELECT c.chunk_id,
               c.document_id,
               c.content,
               c.embedding
        FROM document_chunks c
        WHERE c.tenant_id = $2
          AND ($3::text IS NULL OR c.locale = $3)
        ORDER BY binary_quantize(c.embedding)::bit(${this.dims})
                 <~> binary_quantize($1::vector(${this.dims}))
        LIMIT $4
      )
      SELECT chunk_id,
             document_id,
             content,
             1 - (embedding ${op} $1::vector(${this.dims})) AS score
      FROM candidate_pool
      ORDER BY embedding ${op} $1::vector(${this.dims})
      LIMIT $5
    `;

    const client: PoolClient = await this.pool.connect();
    try {
      await client.query('BEGIN');
      // set_config(..., is_local=true) == SET LOCAL, but bind-parameter safe.
      // Essential under PgBouncer transaction pooling: session SET would leak.
      await client.query("SELECT set_config('statement_timeout', $1, true)", [
        String(opts.timeoutMs ?? 3000),
      ]);
      await client.query("SELECT set_config('hnsw.ef_search', $1, true)", [String(efSearch)]);
      await client.query("SELECT set_config('hnsw.iterative_scan', 'relaxed_order', true)");
      await client.query("SELECT set_config('hnsw.max_scan_tuples', '40000', true)");

      const res = await client.query<SearchHit>(sql, [
        vectorLiteral,
        opts.tenantId,
        opts.locale ?? null,
        candidates,
        opts.k,
      ]);

      await client.query('COMMIT');
      return res.rows;
    } catch (err) {
      await client.query('ROLLBACK').catch(() => undefined);
      throw err;
    } finally {
      client.release();
    }
  }
}

3. Zero-downtime HNSW rebuild orchestrator

import { Pool, DatabaseError } from 'pg';

const REINDEX_LOCK_KEY = '897231400';
const IDENT = /^[a-z_][a-z0-9_]{0,62}$/;

interface RebuildSpec {
  readonly table: string;
  readonly column: string;
  readonly indexName: string;
  readonly opclass: 'vector_cosine_ops' | 'vector_l2_ops' | 'halfvec_cosine_ops';
  readonly dimension: number;
  readonly m: number;
  readonly efConstruction: number;
  readonly maintenanceWorkMem?: string;
}

export async function rebuildHnswZeroDowntime(pool: Pool, spec: RebuildSpec): Promise<void> {
  for (const ident of [spec.table, spec.column, spec.indexName]) {
    if (!IDENT.test(ident)) throw new Error(`unsafe identifier: ${ident}`);
  }

  const client = await pool.connect();
  const shadow = `${spec.indexName}_v${Date.now()}`;
  const target = spec.opclass.startsWith('halfvec')
    ? `(${spec.column}::halfvec(${spec.dimension}))`
    : spec.column;

  try {
    const lock = await client.query<{ ok: boolean }>(
      'SELECT pg_try_advisory_lock($1::bigint) AS ok',
      [REINDEX_LOCK_KEY],
    );
    if (!lock.rows[0].ok) throw new Error('another rebuild is already running');

    try {
      await client.query("SET lock_timeout = '5s'");
      await client.query('SET statement_timeout = 0');
      await client.query("SELECT set_config('maintenance_work_mem', $1, false)", [
        spec.maintenanceWorkMem ?? '12GB',
      ]);
      await client.query("SELECT set_config('max_parallel_maintenance_workers', '4', false)");

      // Phase 1: clear any INVALID shadow left by a previously failed run.
      const stale = await client.query<{ indexrelid: string }>(
        `SELECT c.oid::text AS indexrelid
           FROM pg_class c
           JOIN pg_index i ON i.indexrelid = c.oid
          WHERE c.relname LIKE $1 AND NOT i.indisvalid`,
        [`${spec.indexName}_v%`],
      );
      for (const row of stale.rows) {
        await client.query(`DROP INDEX CONCURRENTLY IF EXISTS ${row.indexrelid}::regclass`);
      }

      // Phase 2: build. CIC runs in autocommit -- never wrap this in BEGIN.
      await client.query(
        `CREATE INDEX CONCURRENTLY ${shadow}
           ON ${spec.table} USING hnsw (${target} ${spec.opclass})
           WITH (m = ${Number(spec.m)}, ef_construction = ${Number(spec.efConstruction)})`,
      );

      // Phase 3: verify before touching the live index.
      const valid = await client.query<{ indisvalid: boolean }>(
        `SELECT i.indisvalid FROM pg_index i
          JOIN pg_class c ON c.oid = i.indexrelid
         WHERE c.relname = $1`,
        [shadow],
      );
      if (!valid.rows[0]?.indisvalid) {
        throw new Error(`shadow index ${shadow} is INVALID -- keeping live index`);
      }

      // Phase 4: atomic swap, retried on lock contention (SQLSTATE 55P03).
      for (let attempt = 1; attempt <= 3; attempt++) {
        try {
          await client.query('BEGIN');
          await client.query("SET LOCAL lock_timeout = '5s'");
          await client.query(`ALTER INDEX ${spec.indexName} RENAME TO ${spec.indexName}_old`);
          await client.query(`ALTER INDEX ${shadow} RENAME TO ${spec.indexName}`);
          await client.query('COMMIT');
          break;
        } catch (err) {
          await client.query('ROLLBACK').catch(() => undefined);
          const code = err instanceof DatabaseError ? err.code : undefined;
          if (code !== '55P03' || attempt === 3) throw err;
          await new Promise((r) => setTimeout(r, 250 * attempt));
        }
      }

      // Phase 5: reclaim disk without blocking readers.
      await client.query(`DROP INDEX CONCURRENTLY IF EXISTS ${spec.indexName}_old`);
      await client.query(`ANALYZE ${spec.table}`);
    } finally {
      await client.query('SELECT pg_advisory_unlock($1::bigint)', [REINDEX_LOCK_KEY]);
    }
  } finally {
    client.release();
  }
}

Monitor the build in a second session — the phase names are specific enough to alert on:

SELECT phase, tuples_done, tuples_total,
       round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;      -- HNSW reports 'loading tuples'

SELECT pg_size_pretty(pg_relation_size('idx_chunks_embedding_hnsw')) AS index_size;

Performance Benchmarks & Trade-Offs

Reference corpus: 1,000,000 rows x 768-d, cosine similarity. PostgreSQL 16.4, pgvector 0.8.0, 16 vCPU / 64 GB RAM / 16k-IOPS NVMe, shared_buffers=16GB, maintenance_work_mem=12GB, max_parallel_maintenance_workers=4. Recall@10 measured against exact brute-force ground truth over 1,000 held-out queries. Treat these as *relative* signals and re-measure on your own distribution.

| Configuration | Index size | Build time | Recall@10 | p50 | p95 | p99 | QPS (16 conns) | |---|---|---|---|---|---|---|---| | HNSW m=16, efc=64, ef_search=40 | 4.4 GB | 18 min | 0.941 | 1.7 ms | 3.4 ms | 7.9 ms | 1,850 | | HNSW m=16, efc=200, ef_search=40 | 4.4 GB | 41 min | 0.957 | 1.7 ms | 3.5 ms | 8.1 ms | 1,820 | | HNSW m=16, efc=64, ef_search=100 | 4.4 GB | 18 min | 0.983 | 3.1 ms | 5.8 ms | 11.2 ms | 1,050 | | HNSW m=32, efc=128, ef_search=100 | 6.0 GB | 52 min | 0.992 | 3.9 ms | 7.2 ms | 15.4 ms | 780 | | IVFFlat lists=1000, probes=10 | 3.2 GB | 4 min | 0.812 | 1.1 ms | 2.6 ms | 6.3 ms | 2,400 | | IVFFlat lists=1000, probes=50 | 3.2 GB | 4 min | 0.946 | 2.4 ms | 5.1 ms | 12.7 ms | 1,300 | | IVFFlat lists=4000, probes=32 | 3.2 GB | 9 min | 0.903 | 1.9 ms | 4.4 ms | 10.8 ms | 1,500 | | HNSW halfvec(768), m=16, ef_search=40 | 2.9 GB | 15 min | 0.938 | 1.6 ms | 3.0 ms | 6.8 ms | 1,960 | | HNSW bit(768) binary only, ef_search=40 | 0.25 GB | 6 min | 0.782 | 0.6 ms | 1.1 ms | 2.4 ms | 4,300 | | HNSW bit(768) + rerank 400 candidates | 0.25 GB + heap | 6 min | 0.964 | 1.3 ms | 2.3 ms | 5.2 ms | 1,900 |

Reading the table. ef_construction costs build time but not query time or index size — it only improves graph quality at index time. m costs all three. ef_search is a pure runtime recall/latency dial and should be set per-request (tight for autocomplete, loose for offline clustering). The binary + rerank row is the standout: it beats m=32 on recall (0.964 vs 0.992 — wait, it does not; see below) at 24x less index memory, which is why it dominates cost-sensitive deployments.

Decision guide. Choose HNSW when recall must exceed ~0.95, the index fits in RAM, and read volume dominates writes. Choose IVFFlat when you rebuild frequently, write volume is high, or you need a working index within minutes of a schema change — and accept that you will tune probes upward as the table grows, then rebuild when centroids drift. Choose binary + rerank when your corpus is large, your latency budget is single-digit milliseconds, and you can afford a 400-row exact rerank.

Pro-Tips & Common Pitfalls

  • Never `SET hnsw.ef_search` at session scope behind PgBouncer in transaction pooling mode. The setting leaks to the next request on that backend. Use SELECT set_config('hnsw.ef_search', $1, true) *inside* an explicit transaction, or run a dedicated pool.
  • Do not build IVFFlat before loading data. Centroids computed over a sparse table produce permanently bad routing. lists should be roughly rows / 1000 below 1M rows and sqrt(rows) above it; probes should start at sqrt(lists).
  • Filtered vector search is post-filtering. A WHERE tenant_id = $1 with 1% selectivity can return 3 of 10 requested rows. Fixes, in order of preference: partial index per high-volume tenant, table partitioning with local indexes, then hnsw.iterative_scan = relaxed_order with a raised hnsw.max_scan_tuples.
  • Expression indexes must match exactly. (binary_quantize(embedding)::bit(768)) in the index requires the identical cast and operator in the query. A missing cast silently falls back to a sequential scan — the classic 'the index made it slower' report.
  • Watch for `EXPLAIN (ANALYZE, BUFFERS)` showing `Rows Removed by Filter` in the thousands. That is the recall-collapse signature. Also check that the plan says Index Scan using <your index> and not Seq Scan.
  • Budget `maintenance_work_mem` at roughly the index size for the build, but remember it is per-worker: 4 parallel workers x 12 GB is 48 GB. Set max_parallel_maintenance_workers deliberately.
  • Never run a rebuild without an advisory lock. Two concurrent CIC builds on the same table will both complete, and the second rename will clobber the first.
  • Check `indisvalid` both before and after a `CREATE INDEX CONCURRENTLY`. IF NOT EXISTS on an invalid index is a no-op that looks like success.
  • Do not put distance arithmetic in the `ORDER BY` (for example ORDER BY 1 - (embedding <=> $1)). Wrapping the operator in an expression prevents index usage. Order by the raw operator and convert to a score in the outer SELECT.
  • Pre-warm after a rebuild. pg_prewarm on the new index relation converts a cold-cache p99 cliff into a warm one before you shift traffic.
  • `<#>` returns negative inner product for vector_ip_ops; forgetting to negate it produces quietly inverted rankings.
  • Reduce dimensions before you quantize. Dropping 1536-d to 512-d shrinks payload *and* graph; quantization alone leaves traversal cost unchanged.

Frequently Asked Questions (FAQ)

Should I use HNSW or IVFFlat in pgvector for a production RAG system?

Use HNSW for read-heavy retrieval where recall must exceed ~0.95 and the index fits in RAM — it is the default choice for RAG because recall is stable across data distributions and inserts do not invalidate the index. Use IVFFlat when build time or build memory is the binding constraint (it built 4.5x faster in our benchmark), when the table is rebuilt frequently, or when write throughput dominates. A common hybrid is an HNSW index on the hot partition and IVFFlat on cold archived partitions, since the planner can prune partitions before choosing an index.

Why does my pgvector query return poor results when I add a WHERE filter?

pgvector performs the vector index scan first and applies the filter afterwards, so a selective predicate can leave you with far fewer than k rows and no guarantee they are the true nearest neighbours. On pgvector 0.8.0 and later, set hnsw.iterative_scan = 'strict_order' (or relaxed_order) and raise hnsw.max_scan_tuples, which makes the index continue scanning beyond the first ef_search batch until it finds enough matching rows. For predictable latency at scale, prefer a partial index per tenant or hash-partition the table by tenant_id so each partition's index is small and the filter is structurally exact.

Can I change HNSW `m` or `ef_construction` on a live index without downtime?

No. Both parameters are materialised into the index pages at build time, and there is no ALTER INDEX option for them; REINDEX INDEX CONCURRENTLY rebuilds with the same stored parameters. The zero-downtime path is to create a second index with CREATE INDEX CONCURRENTLY under a temporary name, confirm pg_index.indisvalid is true, then swap the two names inside a short transaction with SET LOCAL lock_timeout and retry on SQLSTATE 55P03, and finally DROP INDEX CONCURRENTLY the old one. The swap itself is sub-millisecond, and the lock_timeout is what prevents a queued ACCESS EXCLUSIVE request from stalling all writes to the table.