A connection limit is not a capacity plan. Budget every pool, define which reads may be stale and keep disposable cache separate from business obligations.
Auteur
Anichur Rahaman
Partie 3 de 5
Engineering for High VolumeAt 9:02 in a teaching scenario, checkout is slow while a report, a document-index rebuild and background jobs are running. The connection dashboard looks busy. Buying a larger database might help, but first the team needs to know whether requests are waiting for a connection, a row lock, disk access or useful CPU work.
Part 3 of Engineering for High Volume follows one principle: the authoritative order path needs an explicit resource and consistency budget. A cache, replica or semantic index can help that path; none should quietly become permission to sell unavailable stock or expose a private record. No customer benchmark or personal load-test result is claimed.
Measure offered and completed business transactions, latency tails, pool wait, active/idle/idle-in-transaction sessions, long transactions, blocking locks, CPU, I/O, WAL and maintenance pressure together. PostgreSQL connection limits protect finite slots and consume resources; exhaustion can itself be an incident, not merely a harmless symptom. Four vCPUs do not mean only four queries can be in progress, since work may wait on I/O or use parallel execution.
Use pg_stat_statements for cumulative query cost and call counts over a known interval. Inspect representative plans with EXPLAIN; ANALYZE actually executes the statement, so do not run a modifying or expensive plan casually against production. A rollback does not undo every possible external or nontransactional side effect.
A list plus one extra relation query for each of 50 rows makes 51 queries in that simple N+1 pattern. Appropriate eager loading, indexes and shorter transactions can help, but indexes also cost writes and storage. Validate plans and realistic data skew. Emergency resizing can be sensible mitigation; “never resize before a new load test” is too absolute. Fix the measured bottleneck and then retest.
Illustratively, three web nodes allowing 40 database clients each, 16 workers and 4 operational clients could request 140 connections if each opens one. That is potential client demand, not simultaneous useful queries or transactions per second. Pooling changes how clients lease backend connections; it does not make SQL, locks or storage free.
Assume a database limit of 150, with 10 slots deliberately reserved for emergency access and 20 budgeted for direct/maintenance use. The application budget is 120. These are teaching allocations, not suggested PostgreSQL settings. PgBouncer’s configuration reference defines ordinary pools per user/database pair. Sum every pooler, pair, reserve and direct route, including overlap during deploys and failover.
Two pooler processes × three active user/database pairs ×20 ordinary backend connections =120. Adding five reserve connections to each of those six pools can raise application demand to 150; adding the 30 other allocations totals 180 against 150. A local pool cap is not a global database cap. Database/user caps within one pooler are not shared across all pooler instances.
| Illustrative allocation | Connections |
|---|---|
| Database ceiling | 150 |
| Emergency allocation | 10 |
| Direct/maintenance allocation | 20 |
| Application ordinary pools: 2×3×20 | 120 |
| Additional pool reserve: 2×3×5 | 30 |
| Potential total with reserves: 120+30+20+10 | 180 |
In a separate steady-state example, 300 transactions/s × mean backend lease 0.04 s =12 leased connections on average. This is not a maximum-pool recommendation or a 4,000 TPS promise. Lease time includes time spent holding the transaction; long tails and idle transactions change the picture. Bound waiting, request deadlines and admission so a pool does not merely relocate an unbounded queue.
PgBouncer’s feature map distinguishes session pooling, transaction pooling and statement pooling. Session mode holds a backend for the client session; transaction mode releases it after the transaction; statement mode disallows multi-statement transactions. Choose by required behaviour, not a universal “transaction is always fastest” rule.
Test listeners, session-level advisory locks, temporary objects, settings and drivers before changing modes. Protocol-level prepared plans can be supported with the appropriate version and nonzero max_prepared_statements; SQL PREPARE/DEALLOCATE is a different case. NOTIFY and LISTEN are not interchangeable in the compatibility table.
Never leave tenant identity or authorisation in a session setting that may leak to the next client. Enforce scoped queries and database privileges; deliberately transaction-scoped context must be established and cleared correctly. A restricted direct or session route may serve listeners or administrative tooling, with its own capacity and security budget. It is not an unrestricted bypass for every migration.
Route lag-tolerant work only when its business definition permits it. Hot-standby documentation covers read-only queries and recovery conflicts; a long report can be cancelled or affect replay behaviour. “Read replica” does not mean permanently fresh, independent or free to run unlimited analytics.
For this design, keep authoritative stock reservation, payment state and access decisions on the primary; read-after-write paths need primary routing or a proven consistency mechanism. Define measured freshness limits and what happens when lag exceeds them. Blindly moving every failed report to the primary can overwhelm checkout.
PostgreSQL replication documentation notes asynchronous streaming can lose acknowledged changes on failover. Synchronous choices have latency/availability trade-offs and must be tested for the chosen acknowledgement policy. A replica is not a backup or a complete HA system: promotion, fencing the former primary, routing and recovery need an exercised owner and RPO/RTO.
Prefixes and logical database numbers help organise keys; they do not create separate memory limits, eviction policies, persistence or fault domains within one Redis/Valkey instance. FLUSHALL crosses logical databases, and a wrongly scoped prefix deletion can still destroy unrelated data. Give roles needing different policies separate appropriately controlled instances or services, then consider shared-host failure and resource contention.
Redis eviction documentation explains that noeviction rejects memory-growing writes at the limit; it does not guarantee queue correctness. A rejected enqueue must remain a visible, recoverable business obligation. Valkey persistence documentation describes recovery choices, not a substitute for tested durability and restore behaviour.
Rebuildable cache may use an eviction policy selected for its access pattern; not every key requires a TTL for LRU to work. Invalidation, tenant/currency/permission scope and bounded cache-miss concurrency still matter. Sessions, revocation state and queue payloads need explicit loss/expiry policies, not “evicting sessions only logs people out”. Preserve critical accepted records and publication intent durably; retry transient failure with backoff, age/attempt limits and an accountable exception path.
| Role | Must be explicit |
|---|---|
| Rebuildable cache | Scope, invalidation and bounded cold-start load |
| Session / access state | Expiry, revocation and safe recovery on loss |
| Queue / accepted obligation | Rejected publication, durability and bounded recovery |
| Semantic index | Freshness, deletion, recall and current source permissions |
Check the actual product and version: Redis 8 licensing includes AGPLv3 alongside RSALv2 and SSPLv1; Valkey’s history records its BSD lineage. Do not assume every command, module or managed service is interchangeable. This is a compatibility reminder, not licensing advice.
A second database on the same PostgreSQL instance still shares CPU, memory, I/O and failure boundaries. A separate instance/service with measured budgets offers a stronger resource boundary, with extra operations and synchronisation costs. Choose from evidence, not a promise that “separate database means AI cannot slow checkout”.
Generate document embeddings asynchronously when suitable, keeping an authoritative source, revision/hash and model/dimension metadata. A delayed old job must not overwrite a newer revision. After-commit dispatch alone does not close the record-to-enqueue failure gap; critical publication needs an atomic design or recoverable outbox. Handle deletes, revoked access, re-embedding and failed jobs; incompatible embedding spaces cannot simply be mixed.
pgvector’s documentation distinguishes exact search from HNSW/IVFFlat approximate indexes. Test the recall, build memory and query cost of the actual workload. Filtering may leave fewer results; version-compatible bounded iterative scans can help, not guarantee enough results or perfect recall.
For one teaching query with at least ten eligible documents, suppose exact filtered search defines the top 10 and an approximate search returns ten eligible documents, eight in that exact set. Recall@10 is 8/10=80%. That is one example, not a measured service result. Test many representative tenant/filter shapes, deletion and empty-result cases, alongside latency. Never remove an authorisation filter to make the count reach ten.
Apply entity/object permissions in retrieval and recheck current source access before exposing snippets or following a result. The index can lag stock, deletion or permission changes. Do not leak hidden titles, embeddings, cached results or counts. Checkout validates authoritative current price/stock separately from a semantic suggestion.
Images, invoices and exports need durable, shared access when web nodes are replaceable. Object storage is one option; a correctly operated shared filesystem is another. Placement should meet latency, residency, recovery and cost needs, not an unconditional “same region is always right”. CDN/public media and protected documents have different access rules. Streaming may reduce buffering, but SDKs and transformations can still buffer; measure actual peaks.
Test the planned mix with cold caches, pooler loss and deploy overlap, database failover, blocked writes, rejected enqueue and simultaneous vector rebuild. Verify real order outcomes, privacy and recovery backlog. Keep capacity for maintenance and failure. Another green connection graph is useful only when checkout completes correctly and every delayed obligation still has a recoverable record.
Reviewed 5 November 2026. Anichur Rahaman, creator of StoreConsole. Capacity and recall numbers are teaching examples, not installed configuration or measured performance.
À propos de l'auteur
Anichur Rahaman
Continuer la lecture
