Background
A technology company needed a platform for semantic document search — finding documents by meaning rather than keyword match. The source documents included technical specifications, support tickets, and knowledge base articles, totalling approximately 2 million entries with 1536-dimensional embeddings (OpenAI text-embedding-3-small).
The requirement was to serve these similarity searches with p95 latency under 100ms at 50 concurrent users, with full-text fallback available.
pgvector was chosen over dedicated vector databases based on:
- The team’s existing PostgreSQL expertise
- The ability to combine vector similarity search with relational filtering (e.g., filter by department, date range, document type)
- Licensing and operational cost
Architecture
Database Layer
A dedicated PostgreSQL 16 database cluster was deployed with:
- Primary server: 32 vCPU, 128 GB RAM — sized to hold the full vector index in shared buffers
- Two streaming replication standbys (one synchronous at the same site, one asynchronous at DR site)
- Patroni for HA and automated failover
- PgBouncer for connection pooling (transaction-mode, 1000 client pool)
pgvector was installed as an extension on PostgreSQL. All 2 million document embeddings were stored in a single table with a vector(1536) column.
Index Selection
Both IVFFlat and HNSW were benchmarked:
| Index | Build Time | p95 Query (ms) | Recall @ top-10 |
|---|---|---|---|
| IVFFlat (lists=1000) | 12 min | 45ms | 89% |
| HNSW (m=16, ef=128) | 4 hours | 22ms | 97% |
| HNSW (m=32, ef=200) | 11 hours | 31ms | 99% |
HNSW with m=16, ef_construction=128 was selected: the recall and latency profile met requirements, and the build time was acceptable for the planned maintenance window. The index was approximately 18 GB in memory.
Connection Pooling
At 50 concurrent users generating multiple queries per session, direct PostgreSQL connections would exhaust max_connections quickly. PgBouncer in transaction-mode was placed in front of the PostgreSQL cluster:
- PgBouncer pool: 50 client connections → 20 server connections
- PostgreSQL
max_connectionsset to 100 (20 pool + 20 replica + 60 reserved for admin/monitoring)
PgBouncer’s transaction mode is compatible with pgvector queries but requires that session-level settings (like hnsw.ef_search) be sent in each query or as a PgBouncer-level parameter.
Monitoring
A snapshot-based monitoring table (as described in the PostgreSQL historical monitoring article) was implemented, capturing pg_stat_user_indexes every 5 minutes. Alerts were configured for:
idx_scanrate dropping to zero on the HNSW index (index not being used)- HNSW index size growing unexpectedly (uncontrolled inserts)
- Patroni replica lag exceeding 10 seconds
Index Maintenance
After 3 months in production, query latency on some document types increased. Investigation identified that the HNSW graph had degraded — approximately 15% of index entries were deleted vectors from document updates, reducing effective graph connectivity.
A monthly REINDEX CONCURRENTLY job was implemented:
psql -c "REINDEX INDEX CONCURRENTLY documents_embedding_idx;" postgres://...Post-reindex, latency returned to baseline. The job runs at 02:00 on the first Sunday of each month during the observed low-traffic window.
Lessons Learned
Memory sizing is critical: The HNSW index must fit in shared_buffers for consistent performance. When the index spills to disk, latency increases from 22ms to 400ms+. The 128 GB RAM allocation was sized specifically to hold the index (18 GB) plus working data and OS overhead.
ef_search is a query-time parameter: Setting ef_search globally via ALTER SYSTEM is not appropriate — different query types have different recall/latency requirements. It must be set per-query or per-connection in the application layer.
PgBouncer transaction mode with session parameters: The application had to be refactored to send SET hnsw.ef_search = 80 at the beginning of each connection check-out from the pool, since transaction-mode PgBouncer does not preserve session state between transactions.
Recall depends on data distribution: The 97% recall figure was measured against a representative sample. Some document categories with tight clustering showed lower recall at the same ef_search setting. Per-category recall testing was added to the acceptance criteria for index rebuilds.