Stage 7 · Master
Advanced PostgreSQL
Connection Pooling and N+1 Queries
Meridian has not connected any service to PostgreSQL yet, so this chapter specifies two complementary rules for the persistence phase: keep each service's pool intentionally small, and remove row-by-row query patterns that no pool can rescue.
Pooling Is a System Budget
A multi-service system shares one PostgreSQL capacity envelope even when each service owns its own schema and repository code. gateway may eventually need tenant-resolution storage; identity-service will hold memberships and roles; community-service will serve resident and unit data; billing-service will hold invoices and payments. Allowing every binary to open a large default pool would turn independent services into a single contention problem. Pool sizing is therefore a platform budget, not a local convenience setting.
No Postgres dependency, DSN config, or pool constructor is present in the current repo. The lesson defines the persistence-phase operating model; it does not describe running code in meridian today.
Small Service-Local Pools
The default should be one modest application pool per service with explicit upper bounds, not an optimistic 'open as many as needed' policy. A future deployment may add PgBouncer or another proxy layer if aggregate connection pressure justifies it, but that does not replace disciplined service-local limits. The first objective is predictable capacity: identity-service should not be able to starve billing-service merely because a burst of membership checks arrived first.
N+1 in HOA Domains
Connection pooling only limits concurrency; it does not fix inefficient query shapes. N+1 patterns are easy to imagine in Meridian's domains: listing units and then loading resident counts one unit at a time, listing invoices and then loading payment summaries per invoice, or enumerating memberships and then resolving role names one row at a time. Those access paths multiply latency and connections simultaneously. The correct fix is set-based retrieval, not a larger pool.
| Pattern | Immediate symptom | Correct remedy |
|---|---|---|
| Loop issuing one query per resident or invoice | High latency and fast pool exhaustion under list endpoints | Replace with JOINs, IN queries, or aggregation queries |
| Single batched query with clear ownership filters | Slightly more SQL complexity upfront | Chosen. Fewer round trips and lower pressure on the pool. |
Batching over Chattiness
apps/community-service/internal/postgres/residents_repository.gocreateResponsibility: Batch resident and unit lookups so roster endpoints avoid per-row database round trips.
Why now: community-service list endpoints are natural N+1 traps.
Connects to: Will serve resident, unit, and complaint ownership queries inside one tenant.
apps/billing-service/internal/postgres/invoices_repository.gocreateResponsibility: Fetch invoice and payment summaries with set-based queries instead of one query per invoice.
Why now: Financial list views tend to grow quickly and punish row-by-row enrichment.
Connects to: Works with the tenant-first indexes planned in the previous lesson.
SELECT i.invoice_id,
i.unit_id,
i.amount_due,
COALESCE(SUM(p.amount), 0) AS amount_paid
FROM invoices i
LEFT JOIN payments p
ON p.tenant_id = i.tenant_id
AND p.invoice_id = i.invoice_id
WHERE i.tenant_id = $1
AND i.unit_id = $2
GROUP BY i.invoice_id, i.unit_id, i.amount_due;One tenant-scoped query retrieves the invoice set and its payment summaries together. That is cheaper than one summary query per invoice row.
The Decision
Meridian's Postgres phase should treat connection pools as a shared platform budget and N+1 elimination as a query-design requirement. Small explicit pools protect the database from aggregate service pressure; set-based SQL protects the pools from wasteful chattiness.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.