Md Tarek — Portfolio© 2026

Full-stack Developer

System Design — built to hold up.

dhaka → remote, worldwide

Loading experience

Md TarekTarek
AI

PostgreSQL pgvector Indexing & Scale: HNSW vs IVFFlat, Quantization

By: Md Tarek · 20 Oct 2026

pgvector indexing and scale in PostgreSQL with HNSW and IVFFlat indexes
pgvector indexing and scale in PostgreSQL with HNSW and IVFFlat indexes

Executive Summary & Key Takeaways

  • HNSW is the default for low-latency recall: on 1M x 1536-d cosine embeddings, m=16, ef_construction=64, and hnsw.ef_search=100 typically yields recall@10 >= 0.97 at p95 10-15 ms on a 16 vCPU / 64 GB PostgreSQL 16 instance; IVFFlat needs lists=1000 and probes=20 for ~0.93 recall at p95 15-25 ms.
  • Quantize before you shard: halfvec cuts vector payload ~50% with <1% recall loss; binary quantization plus exact rerank cuts index size ~32x and keeps recall@10 >0.90 when reranking the top 500-1000 candidates.
  • Index build parameters are immutable: changing m, ef_construction, lists, distance opclass, or vector type requires a new index. Use CREATE INDEX CONCURRENTLY on a new name, validate pg_index.indisvalid, then DROP INDEX CONCURRENTLY the old index and rename.
  • Zero-downtime embedding migrations need dual-write: add a new vector column, backfill in idempotent batches, dual-write old and new embeddings, build the new HNSW index concurrently, switch reads, then retire the old column/index. Never run ALTER COLUMN TYPE vector(1536) on a hot table.
  • Budget RAM and WAL: 1M x 1536-d fp32 HNSW needs ~6.3 GB of index storage; set maintenance_work_mem to 2-4 GB, expect 10-30 min build time, and schedule concurrent rebuilds off-peak because both indexes receive writes during the swap window.

HNSW vs IVFFlat: Index Mechanics and Tuning Knobs

pgvector ships two approximate nearest-neighbor indexes with very different failure modes. HNSW builds a multi-layer proximity graph. Each inserted vector is linked to up to m neighbors per layer; layer 0 uses a larger connection budget. Search starts at the top layer, greedily descends, then performs a beam search of width hnsw.ef_search at the bottom layer. IVFFlat runs k-means over the vectors into lists clusters, stores each vector with its centroid assignment, and at query time scans the probes nearest centroids.

HNSW query path
  query vector
       |
       v
  +-------------------+
  | layer L (sparse)  |  greedy hop
  +-------------------+
       |
       v
  +-------------------+
  | layer 1           |  greedy hop
  +-------------------+
       |
       v
  +-------------------+
  | layer 0 (dense)   |  beam search width = ef_search
  +-------------------+
       |
       v
  top-k candidates
IVFFlat query path
  query vector --> nearest centroids (probes)
                       |
                       v
              scan assigned lists
                       |
                       v
                exact distance top-k

Tuning rules that hold up in production:

  • HNSW `m`: default 16. Use 32-64 for high-dimensional or high-recall workloads, but graph memory grows roughly with 8 * m bytes per row. m=32 is a common sweet spot for 1536-d embeddings.
  • HNSW `ef_construction`: default 64. Raise to 128-256 when recall matters more than build time. It affects graph quality, not query latency.
  • HNSW `hnsw.ef_search`: default 40. Set per query to 2-4x LIMIT, and never below LIMIT. Use SET LOCAL hnsw.ef_search = 100 inside the read transaction.
  • IVFFlat `lists`: start with rows / 1000 up to 1M rows, then sqrt(rows) above 1M. Build after bulk load so k-means centroids reflect real data.
  • IVFFlat `ivfflat.probes`: default 1 is rarely enough. Use 10-50 depending on recall target. Higher probes increase both recall and latency roughly linearly.
  • Opclass must match operator: vector_cosine_ops serves <=>, vector_l2_ops serves <->, vector_ip_ops serves <#>; halfvec uses halfvec_cosine_ops, halfvec_l2_ops, and halfvec_ip_ops.

Production war story: an 8M-SKU catalog started with IVFFlat lists=3000, probes=1. It looked fast, but recall collapsed from 0.91 to 0.72 after a seasonal catalog shift because centroids were stale. Rebuilding centroids and raising probes to 30 restored recall at p95 28 ms. HNSW with m=24 held recall 0.95 at p95 19 ms, but consumed 38% more memory. The lesson: IVFFlat is not a set-and-forget index when data distribution drifts.

Vector Quantization Strategies in pgvector

pgvector 0.7.x does not implement product quantization or int8 scalar quantization natively. It does provide three practical representations: halfvec for fp16 storage, bit for binary quantization, and sparsevec for sparse embeddings such as SPLADE or BM25-style vectors. The key operational rule is that the index expression must exactly match the query expression, and any lossy representation should be followed by an exact rerank when recall matters.

halfvec is the lowest-risk optimization. A 1536-d fp32 vector is 6,144 bytes; the same halfvec is 3,072 bytes. On a 1M-row table that is roughly 3 GB saved before graph overhead. Recall loss is usually under 1% for cosine search, and cache hit ratio improves because more vectors fit in shared_buffers and the OS page cache.

Binary quantization is a two-stage retrieval pattern: use a bit HNSW index to generate candidates, then rerank those candidates with the original vector column. Binary distance is not a final relevance score. It is a candidate generator. This pattern is appropriate for 10M+ vectors where memory is the binding constraint.

-- Half-precision expression index (or use a generated column)
CREATE INDEX CONCURRENTLY documents_embedding_half_hnsw_idx
ON documents USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Binary candidate index plus exact rerank
CREATE INDEX CONCURRENTLY documents_embedding_bit_hnsw_idx
ON documents USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops)
WITH (m = 16, ef_construction = 64);
WITH candidates AS (
  SELECT id, embedding
  FROM documents
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1::vector(1536))
  LIMIT 1000
)
SELECT id, 1 - (embedding <=> $1::vector(1536)) AS cosine_score
FROM candidates
ORDER BY embedding <=> $1::vector(1536)
LIMIT 10;

Production war story: a 50M-image search cluster moved from fp32 HNSW to bit HNSW plus top-1000 exact rerank. Index size dropped from 28 GB to 0.9 GB, p95 latency fell from 41 ms to 7 ms, and recall@10 moved from 0.944 to 0.912. The migration succeeded because the team kept the original vector column and never used binary distance as the final score. When they briefly tried binary-only ranking, recall@10 fell below 0.62.

Zero-Downtime Reindexing and Embedding Model Migrations

CREATE INDEX CONCURRENTLY is the only safe way to add or rebuild a large pgvector index on a live table. It takes a weaker lock, allows reads and writes, and writes a second index alongside the old one. It also has sharp edges: it cannot run inside a transaction block, it can fail leaving an invalid index (pg_index.indisvalid = false), and it roughly doubles write amplification while both indexes exist.

reindex flow
  app writes --> documents table --> old index (serving reads)
                                 \-> new index (building concurrently)
  after validation: DROP INDEX CONCURRENTLY old; ALTER INDEX new RENAME TO old;

Steps for a safe HNSW parameter change:

1. Set maintenance_work_mem, max_parallel_maintenance_workers, and a long statement_timeout. 2. Build idx_new concurrently with the target m and ef_construction. 3. Verify pg_index.indisvalid and run ANALYZE on the table. 4. Drop idx_old with DROP INDEX CONCURRENTLY. 5. Rename idx_new to idx_old with ALTER INDEX ... RENAME TO ...; the rename is metadata-only and fast.

For an embedding model migration, do not alter the existing column type. Add a new column, backfill, dual-write, then switch reads:

embedding model migration
  1. ALTER TABLE documents ADD COLUMN embedding_v2 vector(3072);
  2. Backfill batches: UPDATE ... WHERE embedding_v2 IS NULL LIMIT 5000; COMMIT; sleep;
  3. App dual-writes embedding and embedding_v2.
  4. CREATE INDEX CONCURRENTLY ... ON documents USING hnsw (embedding_v2 vector_cosine_ops);
  5. Feature-flag reads to embedding_v2; monitor recall and p95.
  6. After rollback window, DROP INDEX CONCURRENTLY old; DROP COLUMN embedding;

Production war story: a support platform with 4.2M tickets ran CREATE INDEX CONCURRENTLY for a 1536-d HNSW index during peak traffic. WAL volume doubled and p99 write latency rose 18% for 22 minutes, but no query errors occurred. The old index was dropped concurrently and the new index renamed instantly. The operation was safe, but it was not free. Schedule the dual-index window off-peak and watch pg_stat_progress_create_index.

Practical Implementation

The following schema uses a generated halfvec column so the application writes one vector(1536) and PostgreSQL maintains the quantized representation. Keep the original vector for exact reranking and future model changes.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id             bigserial PRIMARY KEY,
  content        text NOT NULL,
  embedding      vector(1536) NOT NULL,
  embedding_half halfvec(1536)
    GENERATED ALWAYS AS (embedding::halfvec(1536)) STORED,
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- Run outside a transaction (autocommit). Tune maintenance_work_mem first.
SET maintenance_work_mem = '4GB';
SET statement_timeout = '2h';

CREATE INDEX CONCURRENTLY documents_embedding_half_hnsw_idx
ON documents USING hnsw (embedding_half halfvec_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Optional fp32 index if you need maximum recall during a rollback window.
CREATE INDEX CONCURRENTLY documents_embedding_hnsw_idx
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

ANALYZE documents;

Query path with per-request recall control:

BEGIN;
SET LOCAL hnsw.ef_search = 100;

SELECT id, content, 1 - (embedding_half <=> $1::halfvec(1536)) AS cosine_score
FROM documents
ORDER BY embedding_half <=> $1::halfvec(1536)
LIMIT 10;
COMMIT;

Python reindex orchestrator using psycopg3:

import logging
import psycopg
from psycopg import sql

logging.basicConfig(level=logging.INFO)


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


def drop_index_concurrently(conn: psycopg.Connection, index_name: str) -> None:
    with conn.cursor() as cur:
        cur.execute(
            sql.SQL('DROP INDEX CONCURRENTLY IF EXISTS {}').format(
                sql.Identifier(index_name)
            )
        )


def build_hnsw_concurrently(
    conn: psycopg.Connection,
    index_name: str,
    column_expr: str,
    opclass: str,
    m: int = 16,
    ef_construction: int = 64,
) -> None:
    # conn must be in autocommit mode for CREATE INDEX CONCURRENTLY.
    if index_is_valid(conn, index_name):
        logging.info('index %s already valid', index_name)
        return

    drop_index_concurrently(conn, index_name)

    ddl = sql.SQL(
        'CREATE INDEX CONCURRENTLY {idx} ON documents '
        'USING hnsw ({col} {opclass}) '
        'WITH (m = {m}, ef_construction = {efc})'
    ).format(
        idx=sql.Identifier(index_name),
        col=sql.SQL(column_expr),
        opclass=sql.SQL(opclass),
        m=sql.Literal(m),
        efc=sql.Literal(ef_construction),
    )

    try:
        with conn.cursor() as cur:
            cur.execute(ddl)
    except psycopg.Error:
        logging.exception('index build failed; cleaning invalid index')
        drop_index_concurrently(conn, index_name)
        raise


def swap_index(conn: psycopg.Connection, old_name: str, new_name: str) -> None:
    if not index_is_valid(conn, new_name):
        raise RuntimeError(f'new index {new_name} is not valid')
    drop_index_concurrently(conn, old_name)
    with conn.cursor() as cur:
        cur.execute(
            sql.SQL('ALTER INDEX {} RENAME TO {}').format(
                sql.Identifier(new_name),
                sql.Identifier(old_name),
            )
        )


if __name__ == '__main__':
    with psycopg.connect(
        'postgresql://app:[email protected]:5432/vectors',
        autocommit=True,
    ) as conn:
        with conn.cursor() as cur:
            cur.execute("SET maintenance_work_mem = '4GB'")
            cur.execute("SET statement_timeout = '2h'")

        build_hnsw_concurrently(
            conn,
            index_name='documents_embedding_half_hnsw_new',
            column_expr='embedding_half',
            opclass='halfvec_cosine_ops',
            m=32,
            ef_construction=200,
        )
        swap_index(
            conn,
            old_name='documents_embedding_half_hnsw_idx',
            new_name='documents_embedding_half_hnsw_new',
        )

Monitor concurrent builds with:

SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

Performance Benchmarks & Trade-Offs

Representative benchmark: PostgreSQL 16, pgvector 0.7.4, 16 vCPU, 64 GB RAM, 1M rows, 1536-d embeddings, cosine distance, k=10. Numbers are for capacity planning, not vendor claims.

| Index / representation | Build time | Index size | Recall@10 | p95 latency | Write amplification | Best fit | |---|---:|---:|---:|---:|---|---| | HNSW fp32, m=16, ef_construction=64, ef_search=100 | 18 min | 6.4 GB | 0.972 | 12 ms | High | Low-latency product search | | IVFFlat fp32, lists=1000, probes=20 | 3 min | 6.1 GB | 0.931 | 18 ms | Medium | Cost-sensitive, batch-heavy | | HNSW halfvec, m=16, ef_construction=64, ef_search=100 | 12 min | 3.2 GB | 0.966 | 8 ms | High | Memory-constrained high recall | | HNSW bit + top-1000 rerank | 6 min | 0.4 GB | 0.912 | 6 ms | Medium | 10M+ vectors, two-stage retrieval |

Pro-Tips & Common Pitfalls

  • Build IVFFlat after bulk load. K-means centroids trained on an empty or partial table are stale; recall degrades silently. Rebuild after large ingests or catalog shifts.
  • Never run `CREATE INDEX CONCURRENTLY` inside a transaction. Most migration tools wrap DDL in BEGIN; disable that or the command fails. Use autocommit.
  • Clean up invalid indexes. A failed concurrent build leaves indisvalid = false; check pg_index and DROP INDEX CONCURRENTLY before retrying.
  • Match the opclass to the operator. A vector_cosine_ops index will not serve <-> or <#> queries. The same rule applies to halfvec_cosine_ops.
  • Set `hnsw.ef_search` per query. Default 40 is too low for high-recall search; use SET LOCAL hnsw.ef_search = 100 or higher, and keep it >= LIMIT.
  • Do not trust binary distance as the final score. Use bit HNSW for candidate generation, then rerank with the original vector. Binary recall without rerank is usually unacceptable.
  • Watch `maintenance_work_mem`. A 1M x 1536-d HNSW build with 256 MB spills to disk and can take 5-10x longer. Use 2-4 GB for production builds.
  • Schedule the dual-index window off-peak. Every INSERT/UPDATE maintains both indexes; write amplification and WAL volume roughly double during the swap.
  • Run `ANALYZE` after bulk changes. Planner costs and index selection depend on statistics; pgvector indexes still benefit from fresh table stats.
  • Avoid over-indexing. One HNSW index per distance/representation is usually enough; multiple large indexes multiply RAM and write cost.

Frequently Asked Questions (FAQ)

Is pgvector HNSW faster than IVFFlat?

Usually yes for low-latency, high-recall queries, but not universally. HNSW gives better recall at a given latency because it navigates a graph instead of scanning coarse lists. IVFFlat builds much faster and uses less graph memory, but recall is sensitive to lists, probes, and data drift. For 1M x 1536-d embeddings, expect HNSW p95 10-15 ms at recall@10 ~0.97 versus IVFFlat p95 15-25 ms at recall@10 ~0.93 after tuning. If your write/build budget is tight, IVFFlat is still a valid choice.

How do I change HNSW m or ef_construction without downtime?

Build a new index with a different name using CREATE INDEX CONCURRENTLY, validate pg_index.indisvalid, then DROP INDEX CONCURRENTLY the old index and rename the new one. Both indexes receive writes during the window, so schedule it off-peak and monitor pg_stat_progress_create_index. You cannot change m, ef_construction, lists, opclass, or vector type in place.

Does pgvector support product quantization or int8 quantization?

Not natively as of pgvector 0.7.x. It supports halfvec (fp16), bit (binary), and sparsevec for sparse embeddings. Use halfvec for ~50% storage reduction with minimal recall loss, or bit HNSW plus exact rerank for ~32x reduction at the cost of a two-stage query. For product quantization or int8 scalar quantization, you need an external vector engine or an application-side encoding strategy.