2.2.5. Compute Sizing and Connection Optimization
💡 First Principle: Vector workloads are memory-first: an ANN index only delivers its speed when it lives in RAM, and every PostgreSQL connection is a process eating that same RAM. Sizing compute and disciplining connections are one problem — protecting memory for the index.
Flexible Server offers three compute tiers, and the vector-workload guidance follows directly from the memory-first principle:
| Tier | Profile | Vector workload fit |
|---|---|---|
| Burstable | Credits-based, small | Dev/test only |
| General Purpose | Balanced CPU:RAM (1:4) | Moderate corpora, mixed OLTP+RAG |
| Memory Optimized | High RAM ratio (1:8) | Production vector search — index fits in memory |
The sizing heuristic the exam rewards: estimate index size (vectors x dimensions x 4 bytes for float32, plus graph overhead for HNSW), then choose a tier where that fits comfortably in shared buffers/OS cache alongside normal workload. When an HNSW index spills to disk, latency degrades sharply — the symptom behind many "queries got slow as data grew" scenarios. Storage scales IOPS with size; bump it when write-heavy ingestion stalls, not for search latency.
On connections: recall from 2.2.1 that each one is a server process with megabytes of overhead. A fleet of KEDA-scaled containers (Phase 3) each opening ten connections can exhaust max_connections and starve the index of memory — the "more connections = more throughput" fallacy from this section's misconception list inverted into an outage. The fixes, in order: application-side pooling (asyncpg.create_pool) to reuse connections within a process, and PgBouncer — built into Flexible Server on port 6432 — to multiplex thousands of client connections onto few server connections. PgBouncer's transaction pooling mode gives the highest multiplexing, at a price: session-scoped state (session-level prepared statements, temp tables, SET variables) breaks, because each transaction may ride a different server connection.
⚠️ Exam Trap: When autoscaled workers overwhelm PostgreSQL with connections, the answer is PgBouncer on port 6432 (or app-side pooling) — not raising max_connections, which spends the very memory your vector index needs. Watch for the port number in configuration-style questions.
Reflection Question: Why does transaction-mode pooling break prepared statements, and which pooling layer would you choose for an asyncpg-based orchestrator?
