From 850ms to 4ms: Optimizing pgvector HNSW for Production AI Agent Search

The Challenge

At a SaaS platform I built for an enterprise client, we needed to route support queries to the most relevant AI specialist out of 31 domain experts. The naive approach — brute-force cosine similarity over all agents on every request — worked in development. In production, it collapsed under load.

Initial benchmarks showed:

• 850ms average query latency at 50 concurrent users
• CPU spiking to 94% on the database server
• Sequential scans on every embedding lookup

The goal: sub-10ms P95 latency at 200 concurrent users without degrading recall.

---

The Investigation: Finding the Bottleneck

Before touching indexes, I profiled every layer. Here's what the flamegraph revealed:

Initial Metrics (no index)

The sequential scan was doing 124 cosine similarity calculations (31 agents × 4 embedding axes) on every single request. With 4,096-dimensional vectors, this is brutally expensive without hardware acceleration.

Root Causes

1. No vector index — pgvector defaulting to exact search via sequential scan
2. Wrong distance operator — using <-> (L2) instead of <=> (cosine) for normalized embeddings
3. Fat connection pool — 50 connections, each holding a transaction open during similarity calc
4. Missing partial index — scanning all agents including inactive ones

---

The Solution: 4-Layer Optimization

1. HNSW Index with Tuned Parameters (Saved 780ms)

The default ef_construction=64 is fine for small datasets. For production with frequent updates and strict recall requirements, I benchmarked different configurations:

Before: Sequential scan, no index

After: HNSW with tuned parameters

Why ef_construction=200 and not the default 64?

I ran a recall@10 benchmark across 1,000 validation queries:

200 is the sweet spot — near-perfect recall with acceptable build time. Index rebuilds happen on corpus updates, not on queries.

Impact:

• Query latency: 780ms → 3.8ms (99.5% reduction)
• Recall@10: maintained at 98%+

---

2. Asyncpg Connection Pool with Statement Caching (Saved 12ms)

The original code used SQLAlchemy sync engine with individual connections. Switching to asyncpg with a properly sized pool and prepared statements eliminated TCP handshake overhead per query.

Before:

After: asyncpg pool with prepared statements

Impact:

• Connection overhead: 12ms → 0.3ms
• Prepared statement compilation: eliminated on repeat calls
• Total: saved ~12ms per request

---

3. Weighted Multi-Axis Similarity (Improved Quality)

The initial approach averaged all 4 embedding axes equally. After analyzing which axes predicted routing accuracy best, I moved to weighted cosine similarity:

Routing accuracy before/after weighting:

---

4. Redis Cache for Hot Queries (Saved 3ms on repeats)

Embedding generation + DB lookup for identical queries is wasteful. I added a Redis L1 cache keyed on the SHA-256 of the query text:

Cache hit rate in production: ~34% (repeated questions in the same session).

---

Final Results

Before vs After Comparison

Zero-Downtime Migration

All indexes were created with CONCURRENTLY — no table locks, no downtime. The migration ran live against the production database during a low-traffic window:

---

Key Takeaways

1. Profile Before Optimizing

The sequential scan was 92% of latency. No amount of connection pool tuning would have fixed that.

2. HNSW ef_construction Is a One-Time Cost

You pay it at index build time, not at query time. Use ef_construction=200 for production — the recall gain is worth the 2-second build.

3. Cosine Ops Need Normalized Vectors

If you're using <=> (cosine distance), normalize your embeddings before insertion. Un-normalized vectors produce incorrect similarity scores:

4. Weighted Axes Beat Equal Averaging

Equal averaging is a reasonable default. But offline evaluation on labeled data will always find better weights — in my case, a +13pp improvement in routing accuracy.

5. Prepared Statements Are Free Performance

asyncpg caches prepared statements server-side. You get the performance of compiled query plans with zero extra code complexity.

---

Tools & Technologies Used

• Database: PostgreSQL 15 + pgvector 0.7
• Index: HNSW with vector_cosine_ops
• Connection Pool: asyncpg 0.29 with min_size=5, max_size=20
• Cache: Redis 7 with msgpack serialization
• API: FastAPI 0.189 async
• Embeddings: 4,096-dimensional dense vectors (bfloat16)
• Benchmarking: k6, pg_stat_statements, pgbench

What's Next?

1. Quantized Embeddings: int8 quantization to halve index size and memory footprint
2. Hybrid Search: BM25 + vector for queries with rare proper nouns
3. Multi-tenant Isolation: per-tenant HNSW indexes with RLS policies
4. Streaming Results: SSE-based progressive ranking as embeddings arrive

---

Conclusion

Moving from sequential scan to HNSW with proper configuration turned a 850ms bottleneck into a 3.8ms operation — a 99.5% reduction. The key insight: vector indexes are not plug-and-play. The defaults work, but tuning ef_construction, choosing the right distance operator, and pairing with asyncpg connection pooling unlocks orders-of-magnitude better performance.

The full implementation runs in production, handling 200+ concurrent users with 7% DB CPU utilization.

If you're building semantic search on PostgreSQL and hitting performance walls, feel free to reach out — I've made most of the mistakes so you don't have to.