6.3 pgvector Indexing: Latency & Compute Overhead

6.3 pgvector Indexing: Latency & Compute Overhead

Quick Answer: Use HNSW when you need low-latency, high-recall similarity search and can afford memory; use IVFFlat when build time and memory matter more and you can tune lists/probes. On Azure Flexible Server, right-size compute, normalize vectors, and avoid full sequential scans of embeddings.

Section 6.2 introduced when to add a pgvector index. This section focuses on optimizing query latency and reducing compute overhead—core AI-200 skills for Azure data management.

Exact search vs approximate search

Without an ANN index, ORDER BY embedding <=> $1 LIMIT k performs an exact scan: distance is computed for every row that passes filters. Exact search is fine for thousands of vectors; it becomes CPU-heavy at millions of rows. HNSW and IVFFlat trade a small recall loss for dramatically less distance compute per query.

Distance operators:

Always build the index with the matching operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops). A metric mismatch wastes CPU and returns wrong neighbors.

HNSW vs IVFFlat

Creating HNSW

CREATE INDEX chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

• m: max connections per layer node—higher improves recall, raises memory.
• ef_construction: build-time candidate list—higher improves graph quality, slows index build.
• At query time, set SET hnsw.ef_search = 40; (or higher) to improve recall at some extra CPU cost.

Creating IVFFlat

CREATE INDEX chunks_embedding_ivfflat
  ON chunks USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

• lists: number of clusters—rule of thumb often scales with rows/1000 (within documented bounds).
• At query time, SET ivfflat.probes = 10; controls how many lists are searched—more probes → better recall, more CPU.

IVFFlat should be built when the table already has representative data; empty or tiny tables produce weak centroids and poor recall until you rebuild.

Reducing latency

Practical levers on Azure Database for PostgreSQL:

1. Prefer HNSW for user-facing chat/RAG p95 targets when memory allows.
2. Filter early with selective B-tree predicates (tenant_id) so ANN explores fewer candidates.
3. Keep k (LIMIT) small—retrieving top 5–20 chunks is typical; large k increases work.
4. Raise ef_search / probes only as far as recall metrics require—blindly maxing them burns vCores.
5. Pin embedding dimension and model—re-encoding the corpus invalidates assumptions baked into the index.
6. Use EXPLAIN (ANALYZE, BUFFERS) to confirm the planner uses the vector index instead of a sequential scan.

If EXPLAIN shows a sequential scan on a large embedding table, check that the ORDER BY uses the indexed operator, statistics are fresh (ANALYZE chunks), and PostgreSQL version/pgvector supports your index type.

Reducing compute overhead

Compute overhead shows up as Flexible Server CPU credits exhausted, elevated IO, or connection pile-ups during ingestion.

Strategies:

• Normalize vectors at write time when using cosine/IP so distance math stays consistent and you avoid redundant application-side rework.
• Batch inserts during embedding backfills; drop or defer secondary indexes until load completes, then rebuild.
• Connection pooling (section 6.1) prevents process storms that amplify CPU from setup/teardown.
• Right-size SKU: Memory Optimized tiers help HNSW-resident graphs; undersized Burstable SKUs throttle under ANN load.
• Avoid duplicate indexes (both HNSW and IVFFlat on the same column) unless you are A/B testing—each insert maintains both graphs/lists.
• Partial vector indexes when only “active” rows are searchable, shrinking graph size.

Maintenance windows matter: rebuilding a large HNSW index is CPU-intensive—schedule it like any other heavy DDL on Flexible Server.

Worked decision guide

Observability

Track:

• p50/p95 query latency for the similarity statement
• Flexible Server CPU and memory metrics in Azure Monitor
• Recall@k on a labeled query set when you change ef_search or probes
• Index size vs storage limits

A change that cuts latency 30% but drops recall@10 from 0.92 to 0.70 is usually a product regression—even if CPU looks healthier.

Exam framing

When AI-200 asks how to optimize query latency or reduce pgvector compute overhead, map the answer to index choice (HNSW vs IVFFlat), knob tuning (ef_search/probes), selective filters, pooling, and SKU sizing—not to turning off SSL or switching away from parameterized SDK queries.