As an architect, how would you size connection pools and design the transaction topology of a service so that patterns like nested REQUIRES_NEW cannot deadlock it, and how do you enforce that at scale?

### The governing invariant The deadlock exists only because a single request can hold **more than one** pool connection at once. So the architectural rule is: **bound the maximum connections held simultaneously per request** — ideally to 1. If `maxHeldPerRequest = 1`, then `safeConcurrency = maximumPoolSize` and no circular wait can form. If you allow `k` held at once (nested REQUIRES_NEW gives k=2), then `safeConcurrency = floor(maximumPoolSize / k)` and you must size accordingly. ### Sizing the pool - **Small is correct.** HikariCP's own guidance is that connection pools should be **small**; a database serves throughput best with a modest number of busy connections, not hundreds of mostly-idle ones. A common heuristic is roughly `connections = ((core_count * 2) + effective_spindle_count)` as a starting point, then tune with load tests. - **Respect the database ceiling.** Postgres `max_connections` (often ~100–200) is shared across **all** application instances/pods. Total demand = `pods * (sum of maximumPoolSize across all DataSources per pod)`. If each pod has a 10-connection primary pool plus a 5-connection dedicated pool, 20 pods already demand 300 connections — over a default ceiling. Introduce **PgBouncer** (transaction pooling) to multiplex if you need many app instances. - **Account for every pool.** A dedicated pool for independent writes (the clean way to keep a legitimate second-connection use) adds to the total budget — size and cap it explicitly. ### Transaction topology design 1. **Default to one connection per request.** Keep transactions short; do external calls and non-DB work outside the transaction. 2. **Independent side effects → after commit.** Use `@TransactionalEventListener(AFTER_COMMIT)` or a **transactional outbox** so the side write happens on a fresh connection after the primary releases. This is the Modulith-friendly pattern already established in this codebase (domain events). 3. **Unavoidable in-scope independent write → dedicated DataSource.** Bind a second `PlatformTransactionManager` to a separate small pool so the two never share a pool; the two-connection hold no longer forms a cycle within one pool. 4. **Never nest REQUIRES_NEW inside a long-running / high-fan-out loop** — that multiplies hold time and demand. ### Enforcement at scale (make it un-reintroducible) - **Review + lint:** flag `@Transactional(propagation = REQUIRES_NEW)` called from within another `@Transactional` scope. - **Architecture tests:** ArchUnit / Spring Modulith rules to constrain where REQUIRES_NEW may appear, or to require side effects go through the events module. - **Load/soak tests in CI:** deliberately drive concurrency **past `poolSize/2`** through nested paths so the deadlock manifests in the pipeline, not at 2am. Assert on p99 latency and zero `SQLTransientConnectionException`. - **Observability guardrails:** alert on `hikaricp.connections.pending > 0` sustained and on acquire-timeout counts; set `leakDetectionThreshold` in all environments. - **Capacity budget doc:** a living table of every pool, its max size, and the total against `max_connections` / PgBouncer limits, reviewed when adding pods or pools. ### The principal-level framing This is fundamentally a **capacity and invariants** problem, not a Spring-annotation trivia problem. You (a) choose an invariant (≤ k connections per request), (b) size all pools against the shared DB ceiling with that invariant, (c) prefer topologies (after-commit events/outbox) that keep k=1, (d) isolate the rare legitimate k=2 case onto its own pool, and (e) encode the rule in tests and alerts so it cannot silently regress. Then nested-REQUIRES_NEW pool deadlock stops being a lurking incident and becomes a design constraint the system provably satisfies.