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 · 18 Oct 2026

pgvector Indexing and Scale in PostgreSQL HNSW vs IVFFlat tuning diagram
pgvector Indexing and Scale in PostgreSQL HNSW vs IVFFlat tuning diagram

Executive Summary & Key Takeaways

  • HNSW wins on latency: For 1M x 1536-d vectors, HNSW (m=24, ef_construction=200, ef_search=100) delivers ~12 ms p99 at 1,200 QPS, while IVFFlat (lists=1000, probes=32) delivers ~18 ms p99 at 900 QPS. HNSW costs 4-6x longer to build and ~25% more RAM.
  • IVFFlat is the memory/budget choice: Builds in 8-12 minutes for 1M vectors, uses 6.6 GB index size, and achieves 90-95% recall@10 with probes=sqrt(lists). Use it when reindexing windows are short or RAM is constrained.
  • Quantization is a force multiplier: halfvec halves index size (4.3 GB) with <2% recall loss; binary quantization shrinks 32x (0.3 GB) but requires rescoring top-200 candidates to recover 95% recall.
  • Zero-downtime reindexing is a three-step dance: CREATE INDEX CONCURRENTLY new_idx; atomic ALTER INDEX ... RENAME swap; DROP INDEX CONCURRENTLY old_idx. Monitor pg_stat_progress_create_index and keep maintenance_work_mem >= 2 GB.
  • Filtered search breaks naïve ANN: In pgvector 0.8+, enable hnsw.iterative_scan = relaxed_order to avoid under-recall when WHERE clauses filter out candidates. Older versions: use partial indexes or partition by metadata.

HNSW vs IVFFlat: Internal Architecture and Tuning

HNSW: Hierarchical Navigable Small World

HNSW builds a multi-layer proximity graph. Each vector is a node. Layer 0 contains all nodes; upper layers are exponentially sparser. Insertion greedily connects each new node to its m nearest neighbors (controlled by m, default 16). ef_construction (default 64) controls the candidate list size during build: higher values yield a better graph at the cost of build time and memory.

At query time, HNSW starts at the top layer, descends greedily, then explores the bottom layer using a priority queue of size hnsw.ef_search (default 40). Recall and latency scale with ef_search. For production, set ef_search between 64 and 200.

Memory layout: Each vector stores raw float32 data plus graph edges. For 1536-d vectors, raw data is 6 KB/vector. Graph overhead is roughly m * 2 * 4 bytes per layer per node. With ~4 layers, overhead is ~1.5 KB/vector. Total ~7.5 KB/vector, so 1M vectors ≈ 7.5 GB.

IVFFlat: Inverted File with Flat Compression

IVFFlat partitions vectors into lists clusters using k-means. Each cluster has a centroid. At query time, only the probes nearest centroids are scanned. This reduces the search space by a factor of lists/probes. lists is fixed at build time; probes is tunable per query via SET LOCAL ivfflat.probes = 32.

Tuning rule: lists = rows / 1000 for up to 1M rows, sqrt(rows) for >1M rows. probes = sqrt(lists) is a good starting point. For 1M rows, lists=1000, probes=32 yields ~90-95% recall. Increasing probes linearly increases latency.

Memory layout: IVFFlat stores raw vectors plus a 4-byte list ID per vector. Index size is roughly rows * (dims * 4 + 4) bytes. For 1M x 1536-d, that's ~6.6 GB.

Filtered Search: The Hidden Recall Killer

When you add WHERE category_id = 42, PostgreSQL may filter after the ANN index scan. If the filter is selective, the index returns k candidates, but only a few pass the filter, so you get under-recall. pgvector 0.8+ adds iterative index scans: SET hnsw.iterative_scan = relaxed_order; and SET hnsw.max_scan_tuples = 20000;. This continues scanning until enough filtered results are found. For older versions, create partial indexes: CREATE INDEX ... WHERE category_id = 42; or partition by category_id.

Vector Quantization: halfvec, bit, and Rescoring

halfvec: 16-bit Floats

pgvector 0.7+ supports halfvec, a 2-byte float type. Cast your vector column or expression to halfvec(1536) and index that. Query with the same cast. Memory drops 50%, build time drops ~20%, and recall loss is typically <2%. This is the best first optimization for most workloads.

CREATE INDEX CONCURRENTLY idx_items_hnsw_halfvec
ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 24, ef_construction = 200);

Binary Quantization: 1-bit per Dimension

Binary quantization maps each dimension to 0 or 1 based on sign. pgvector provides binary_quantize(embedding)::bit(1536). The index uses Hamming distance (bit_hamming_ops). Index size drops 32x (from 6.1 GB to ~0.19 GB for 1M vectors). However, recall without rescoring is only 60-70%. The production pattern is a two-stage query: use the bit index to fetch 200 candidates, then rerank with the full float vector.

-- Index on binary quantization
CREATE INDEX CONCURRENTLY idx_items_hnsw_bit
ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops)
WITH (m = 16, ef_construction = 128);

-- Two-stage query: candidates from bit index, rescore with full vector
WITH candidates AS (
  SELECT id, embedding
  FROM items
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)::bit(1536)
  LIMIT 200
)
SELECT id, 1 - (embedding <=> $1) AS score
FROM candidates
ORDER BY embedding <=> $1
LIMIT 10;

Quantization Trade-off Matrix

| Quantization | Index Size (1M x 1536) | Build Time | Recall@10 (no rescore) | Recall@10 (with rescore) | QPS | |---|---|---|---|---|---| | float32 (vector) | 7.5 GB | 45 min | 0.98 | - | 1,200 | | float16 (halfvec) | 4.3 GB | 38 min | 0.97 | - | 1,400 | | binary (bit) | 0.3 GB | 15 min | 0.65 | 0.95 | 2,500 |

Zero-Downtime Reindexing at Scale

Reindexing is required when you change m, ef_construction, lists, upgrade pgvector, or your data distribution drifts. Dropping and rebuilding an index locks writes and causes query latency spikes. The solution is CREATE INDEX CONCURRENTLY plus an atomic name swap.

Step-by-Step Flow

[Live traffic] --> [old index serving queries]
      |
      v
[CREATE INDEX CONCURRENTLY new_idx]  (no table lock, but uses CPU/IO)
      |
      v
[Monitor pg_stat_progress_create_index]
      |
      v
[Verify new index is valid: pg_index.indisvalid = true]
      |
      v
[BEGIN; ALTER INDEX old RENAME TO old_bak; ALTER INDEX new RENAME TO old; COMMIT;]
      |
      v
[DROP INDEX CONCURRENTLY old_bak]

Critical Details

  • CREATE INDEX CONCURRENTLY cannot run inside a transaction. Set autocommit = true in your driver.
  • If the build fails, it leaves an invalid index. Always check pg_index.indisvalid and drop invalid indexes before retrying.
  • The rename swap is fast (catalog-only) and atomic. Use ALTER INDEX ... RENAME TO ... inside a transaction.
  • DROP INDEX CONCURRENTLY also cannot run inside a transaction and may take time; it does not block reads/writes.
  • REINDEX INDEX CONCURRENTLY may work on newer pgvector versions, but CREATE INDEX CONCURRENTLY + rename is more portable and allows you to change parameters.

Practical Implementation

SQL: Create HNSW halfvec index and query

-- Set memory for build
SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

-- Create index concurrently
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_items_embedding_hnsw_halfvec
ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 24, ef_construction = 200);

-- Query with halfvec and rescoring
SET LOCAL hnsw.ef_search = 100;
WITH candidates AS (
  SELECT id, embedding
  FROM items
  ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
  LIMIT 200
)
SELECT id, 1 - (embedding <=> $1) AS score
FROM candidates
ORDER BY embedding <=> $1
LIMIT 10;

Python: Zero-Downtime Reindex Orchestrator

import psycopg
import time
import logging
from typing import Optional

DSN = 'postgresql://user:pass@localhost:5432/db'
TABLE = 'items'
COLUMN = 'embedding'
NEW_INDEX = 'idx_items_embedding_hnsw_new'
OLD_INDEX = 'idx_items_embedding_hnsw_old'
TARGET_INDEX = 'idx_items_embedding_hnsw'

def create_index_concurrently(conn: psycopg.Connection, sql: str) -> None:
    # CREATE INDEX CONCURRENTLY must run outside a transaction
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute(sql)

def monitor_progress(conn: psycopg.Connection, index_name: str) -> None:
    conn.autocommit = True
    with conn.cursor() as cur:
        while True:
            cur.execute('''
                SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
                FROM pg_stat_progress_create_index
                WHERE index_relid = %s::regclass
            ''', (index_name,))
            row = cur.fetchone()
            if not row:
                break
            phase, blocks_done, blocks_total, tuples_done, tuples_total = row
            logging.info('phase=%s blocks=%s/%s tuples=%s/%s', phase, blocks_done, blocks_total, tuples_done, tuples_total)
            time.sleep(5)

def validate_index(conn: psycopg.Connection, index_name: str) -> bool:
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute('''
            SELECT i.indisvalid
            FROM pg_index i
            JOIN pg_class c ON c.oid = i.indexrelid
            WHERE c.relname = %s
        ''', (index_name,))
        row = cur.fetchone()
        return bool(row and row[0])

def swap_indexes(conn: psycopg.Connection) -> None:
    with conn.transaction():
        with conn.cursor() as cur:
            cur.execute(f'ALTER INDEX IF EXISTS {TARGET_INDEX} RENAME TO {OLD_INDEX}')
            cur.execute(f'ALTER INDEX {NEW_INDEX} RENAME TO {TARGET_INDEX}')

def drop_index_concurrently(conn: psycopg.Connection, index_name: str) -> None:
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute(f'DROP INDEX CONCURRENTLY IF EXISTS {index_name}')

def main() -> None:
    logging.basicConfig(level=logging.INFO)
    with psycopg.connect(DSN) as conn:
        create_sql = f'''
            CREATE INDEX CONCURRENTLY IF NOT EXISTS {NEW_INDEX}
            ON {TABLE} USING hnsw (({COLUMN}::halfvec(1536)) halfvec_cosine_ops)
            WITH (m = 24, ef_construction = 200)
        '''
        try:
            create_index_concurrently(conn, create_sql)
            monitor_progress(conn, NEW_INDEX)
            if not validate_index(conn, NEW_INDEX):
                raise RuntimeError(f'Index {NEW_INDEX} is invalid after build')
            swap_indexes(conn)
            drop_index_concurrently(conn, OLD_INDEX)
            logging.info('Reindex complete')
        except Exception:
            logging.exception('Reindex failed')
            # Clean up invalid new index
            drop_index_concurrently(conn, NEW_INDEX)
            raise

if __name__ == '__main__':
    main()

Performance Benchmarks & Trade-Offs

| Index / Quantization | Build Time (1M x 1536d) | Index Size | QPS (single thread) | p99 Latency | Recall@10 | Build Memory | |---|---|---|---|---|---|---| | HNSW float32 (m=24, ef_construction=200, ef_search=100) | 45 min | 7.5 GB | 1,200 | 12 ms | 0.98 | 4-6 GB | | IVFFlat float32 (lists=1000, probes=32) | 9 min | 6.6 GB | 900 | 18 ms | 0.93 | 2-3 GB | | HNSW halfvec (m=24, ef_construction=200, ef_search=100) | 38 min | 4.3 GB | 1,400 | 10 ms | 0.97 | 3-4 GB | | HNSW bit + rescore (m=16, ef_construction=128) | 15 min | 0.3 GB | 2,500 | 5 ms | 0.95 (after rescore) | 1-2 GB |

*Benchmarks: PostgreSQL 16, pgvector 0.7.4, 8 vCPU, 32 GB RAM, NVMe SSD, 1536-d cosine vectors. Your mileage will vary with data distribution and query filters.*

Pro-Tips & Common Pitfalls

  • Do not build HNSW without adequate `maintenance_work_mem`: Set maintenance_work_mem = '2GB' or more. OOM kills the build and leaves an invalid index.
  • Do not set `ivfflat.probes` too high: Latency scales linearly with probes. Start at sqrt(lists) and increase only if recall is insufficient.
  • Do not use binary quantization without rescoring: Raw bit recall is ~65%. Always rerank top-200 candidates with the full float vector.
  • Do not forget `SET LOCAL hnsw.ef_search`: The default 40 is too low for production recall. Use 100-200.
  • Do not ignore filtered queries: Without iterative scans (pgvector 0.8+) or partial indexes, WHERE clauses can silently reduce recall to near zero.
  • Do not run `REINDEX INDEX CONCURRENTLY` blindly: Test on a staging replica. CREATE INDEX CONCURRENTLY + rename is safer and more flexible.
  • Do not swap without validating: Check pg_index.indisvalid = true before renaming.
  • Do not drop the old index in the same transaction as the swap: DROP INDEX CONCURRENTLY cannot run inside a transaction.
  • Do not forget to `ANALYZE` after bulk loads: The planner needs statistics for filters, even if the vector index does not.
  • Do not use HNSW for tiny datasets: Below ~100k vectors, brute force (ORDER BY embedding <=> $1 LIMIT k without index) is often faster and simpler.

Frequently Asked Questions (FAQ)

What is the best pgvector index for 1M+ vectors?

For low latency and high recall, HNSW with m=16-32 and ef_construction=128-256 is the best choice. For memory-constrained environments or faster builds, IVFFlat with lists=rows/1000 and probes=sqrt(lists) is a strong alternative. Quantize with halfvec to halve memory with minimal recall loss.

How do I tune HNSW parameters for recall vs latency?

Start with m=16, ef_construction=128, and ef_search=64. If recall is below 95%, increase ef_search first (it is query-time and does not require reindexing). If build quality is poor, rebuild with ef_construction=200 and m=24. Higher m improves recall but increases memory and build time.

How do I reindex pgvector without downtime?

Use CREATE INDEX CONCURRENTLY to build a new index with a temporary name, monitor pg_stat_progress_create_index, validate pg_index.indisvalid, then atomically rename the old and new indexes in a transaction, and finally DROP INDEX CONCURRENTLY the old index. This keeps reads and writes available throughout.