Optimizing HNSW Indexing in pgvector for High-Recall Similarity Search

The Production Problem: Beyond Default pgvector HNSW Settings

You've successfully integrated pgvector into your PostgreSQL instance. Your application, whether a RAG pipeline, an e-commerce recommendation engine, or an image similarity search service, is generating embeddings and storing them. You've created an HNSW index with the default parameters, and initial tests look promising. Then, you hit production traffic.

Query latency becomes erratic. The p99 latency for a simple nearest neighbor search spikes from 50ms to 500ms under concurrent load. More alarmingly, your recall metrics, meticulously measured offline, are dropping in production; the system is returning less relevant results than expected. Your default HNSW index, CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);, is failing you.

This is a common scenario for teams scaling vector search workloads. The default parameters for HNSW in pgvector are a safe starting point, but they are rarely optimal for high-performance, high-recall production systems. Achieving consistent low latency and high accuracy requires a deep understanding of the trade-offs governed by three key parameters: m, ef_construction, and ef_search.

This article is not an introduction to HNSW. It assumes you understand the fundamentals of approximate nearest neighbor (ANN) search and the graph-based nature of HNSW. We will dive directly into the advanced tuning strategies, edge cases, and operational patterns required to run pgvector at scale.

HNSW Tuning Levers: A Trilemma of Recall, Latency, and Cost

The performance of an HNSW index is a constant balancing act between three competing factors:

The parameters m, ef_construction, and ef_search are the primary levers you control to navigate this trilemma.

1. `m`: The Graph's Connectivity (Build-Time)

- Index Size: The index grows proportionally to m. A larger m means more edges stored per node.

- Build Time: Constructing a denser graph is computationally more expensive, leading to significantly longer index creation times.

- Query CPU: Traversing a denser graph can sometimes, though not always, increase CPU usage per query.

Production Strategy: The default m is 16. This is often too low for high-dimensional data (>768 dimensions) where high recall is critical.

Implementation Example:

-- For a high-recall document search system
-- Note: This will be slower to build and larger than the default.
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops) WITH (m = 32, ef_construction = 128);

2. `ef_construction`: The Graph's Quality (Build-Time)

Production Strategy: The default ef_construction is 64. This is often insufficient for achieving >98% recall.

Benchmarking ef_construction vs. Build Time:

Let's simulate this with a Python script. Assume a table items with 1 million 768-dimensional vectors.

import psycopg2
import time
import os

# --- Configuration ---
DB_CONN = os.environ.get("DB_CONN_STRING") # "postgresql://user:pass@host/db"
VECTOR_DIM = 768
TABLE_NAME = "items_benchmark"

# --- Test Parameters ---
M_VALUE = 32
EF_CONSTRUCTION_VALUES = [64, 128, 256, 400]

def run_benchmark():
    conn = psycopg2.connect(DB_CONN)
    conn.autocommit = True
    cur = conn.cursor()

    print("--- HNSW ef_construction Benchmark ---")
    for efc in EF_CONSTRUCTION_VALUES:
        index_name = f"idx_hnsw_m{M_VALUE}_efc{efc}"
        
        # Drop previous index if it exists
        cur.execute(f"DROP INDEX IF EXISTS {index_name};")
        print(f"Building index with m={M_VALUE}, ef_construction={efc}...")

        start_time = time.time()
        try:
            cur.execute(f"""
                CREATE INDEX {index_name} 
                ON {TABLE_NAME} 
                USING hnsw (embedding vector_l2_ops) 
                WITH (m = {M_VALUE}, ef_construction = {efc});
            """)
            end_time = time.time()
            build_time = end_time - start_time
            print(f"  -> Success! Build time: {build_time:.2f} seconds")

            # Get index size
            cur.execute("SELECT pg_size_pretty(pg_relation_size(%s));", (index_name,))
            index_size = cur.fetchone()[0]
            print(f"  -> Index size: {index_size}")

        except Exception as e:
            print(f"  -> FAILED: {e}")

    cur.close()
    conn.close()

if __name__ == "__main__":
    # This assumes you have a table populated with random vectors.
    # See pgvector docs for populating sample data.
    run_benchmark()

Expected Outcome: You will observe a non-linear increase in build time. Moving from ef_construction=64 to 128 might double the build time, while moving from 128 to 256 could triple it. This benchmark is critical for planning your indexing strategy and maintenance windows.

3. `ef_search`: The Query-Time Precision Knob

Production Strategy: Never leave this at the default (40). You must tune it based on your application's specific requirements.

Implementation Example: Dynamic ef_search in a Multi-Tenant App

Imagine a SaaS product where 'Premium' tier users get more accurate results than 'Free' tier users. You can implement this by setting ef_search dynamically per transaction.

-- A PL/pgSQL function to wrap our vector search
CREATE OR REPLACE FUNCTION get_similar_documents(
    query_embedding vector(768),
    user_tier TEXT,
    result_limit INT
)
RETURNS TABLE (id UUID, title TEXT, similarity REAL)
AS $$
DECLARE
    ef_search_value INT;
BEGIN
    -- Set ef_search based on user tier
    IF user_tier = 'premium' THEN
        ef_search_value := 150; -- High recall for premium users
    ELSE
        ef_search_value := 75;  -- Good balance for free users
    END IF;

    -- Use SET LOCAL to scope the parameter to this transaction only
    EXECUTE 'SET LOCAL hnsw.ef_search = ' || ef_search_value;

    -- Perform the search
    RETURN QUERY
    SELECT
        d.id,
        d.title,
        1 - (d.embedding <=> query_embedding) AS similarity -- Cosine similarity
    FROM
        documents d
    ORDER BY
        d.embedding <=> query_embedding
    LIMIT
        result_limit;
END;
$$ LANGUAGE plpgsql;

-- Usage from your application:
-- For a premium user:
SELECT * FROM get_similar_documents(ARRAY[...], 'premium', 10);

-- For a free user:
SELECT * FROM get_similar_documents(ARRAY[...], 'free', 10);

This pattern provides fine-grained control over your performance SLAs without requiring separate indexes or database clusters.

Advanced Topic: Handling Index Bloat from `UPDATE` and `DELETE`

A critical operational detail often missed by new pgvector users is how HNSW indexes handle data modification. The HNSW graph structure in pgvector is immutable. When you UPDATE a vector or DELETE a row:

• The old entry in the index is simply marked as "dead."

The Consequence: Index Bloat and Performance Degradation

Over time, with frequent updates or deletes, your HNSW index will accumulate a large number of these dead tuples. This has two severe impacts:

The Solution: Proactive Re-indexing

Standard PostgreSQL VACUUM will clean up dead rows in the table (the heap), but it cannot restructure the HNSW index itself to remove the dead entries from the graph. The only way to truly reclaim this space and restore performance is to rebuild the index.

REINDEX is the command, but a standard REINDEX takes an exclusive lock on the table, causing downtime. The production-safe solution is REINDEX CONCURRENTLY.

Production-Grade Re-indexing Strategy:

    -- Check for dead tuples in your table
    SELECT 
        relname,
        n_live_tup,
        n_dead_tup,
        n_dead_tup * 100.0 / (n_live_tup + n_dead_tup) as dead_tup_pct
    FROM pg_stat_user_tables
    WHERE relname = 'your_vector_table';

    -- This command builds a new, clean index in the background.
    -- Once complete, it swaps it with the old one atomically.
    -- It requires more disk space temporarily but avoids locking.
    REINDEX INDEX CONCURRENTLY your_hnsw_index_name;

This operational procedure is non-negotiable for any pgvector deployment with a non-trivial rate of data modification.

Architectural Decision: HNSW vs. IVFFlat

pgvector offers another popular index type: IVFFlat. Senior engineers must understand when to choose one over the other.

IVFFlat (Inverted File with Flat Compression):

Here is a decision matrix for production systems:

Pragmatic Choice: Start with HNSW. For most applications under 10 million vectors, its superior recall-latency curve is worth the operational overhead. Only consider IVFFlat if you are building an index on a truly massive dataset and your build times with HNSW are becoming operationally infeasible.

Diagnosing Performance with `EXPLAIN (ANALYZE, BUFFERS)`

When a vector query is slow, you must be able to diagnose it. EXPLAIN ANALYZE is your most powerful tool.

Let's analyze a query:

SET hnsw.ef_search = 100;
EXPLAIN (ANALYZE, BUFFERS) 
SELECT id FROM items ORDER BY embedding <-> '[...]' LIMIT 10;

Sample Output and What to Look For:

Limit  (cost=... actual time=25.34..25.35 rows=10 loops=1)
  Buffers: shared hit=15320
  ->  Index Scan using items_embedding_idx on items (cost=... actual time=25.33..25.34 rows=10 loops=1)
        Order By: (embedding <-> '[...]'::vector)
        Buffers: shared hit=15320
Planning Time: 0.150 ms
Execution Time: 25.400 ms

Key Insights from this Output:

If your vector search is slow, check for shared read. If it's high, it means your index is not fitting into your shared_buffers, and you need to either increase PostgreSQL's memory allocation or provision a machine with more RAM. This is a common scaling bottleneck.

Final Recommendations for Production

By moving beyond the default configuration and embracing these advanced operational and tuning patterns, you can build pgvector-based systems that are not only powerful but also scalable, reliable, and performant under the pressures of production traffic.