SwadhyAI Notes

All System Design notes

System Design Data Access

Database connection patterns

An Instagram-style feed API looks fine in staging: an loads posts, then products, then authors. Under Black Friday traffic the same loop becomes a hundred round trips per request, the pool saturates, and p99 latency spikes long before disk space is the problem.

This note is about how apps talk to relational databases day to day: query shape, pooling, replicas, and migrations that do not lock the world.

Request journey

1

Shape the SQL

Use ORM CRUD for most work; escape to raw SQL on hot paths.

2

Join, eager-load, or batch so related rows arrive in few round trips.

3

Reuse warm sessions and cap max connections per pod hard.

4

Send writes to the primary; use replicas only for safe reads.

What actually happens when the feed hits the database

The Black Friday story above is the default under load, not a corner case. An API must talk to a relational database without melting latency or exhausting sessions, while still shipping features quickly with an . The naive move is to open a new DB connection per request, load associations in a loop, and run ALTER TABLE on the primary at peak. Counterexample: 100 orders become 101 round trips (); 50 pods × 20 connections equals 1000 sessions against max_connections ≈ 100.

Most requests start in application code. An ORM turns objects into SQL; raw SQL or a query builder takes over when plans matter. Either way, every query still needs a live connection, and opening one from scratch is expensive. A hands out warm sessions. When many pods hit one primary, a multiplexes hundreds of app sockets onto fewer database backends.

Reads may go to a , but means the user who just posted may still need the primary. Schema changes at billion-row scale use or so deploys do not become outages. The invariant to keep in mind: every request that needs a DB session obtains one from a bounded pool (per process × replicas ≤ database capacity, usually via a multiplexer); hot paths must not multiply round trips with N+1 loops; after a write, read-your-writes for that user stays on the primary (or a caught-up replica) until lag allows otherwise.

How to choose access patterns

Before diving into pooling math and migration tools, use three questions to decide how a given path should talk to the database.

1. Is this CRUD or a hot path? Use the for straightforward create/read/update/delete. Escape to raw SQL or a query builder when EXPLAIN and p99 say the generated plan is wrong.

2. Will you touch associations in a loop? Assume until you prove otherwise. Prefer , joins, or batch loaders.

3. Does the user need their own write? After a write, stay on the primary (or a caught-up ). Offload anonymous or eventually consistent reads to replicas.

Simple rule of thumb

Instrument SQL, pool connections, route replicas carefully, and treat migrations as paired code-plus-schema deploys. Reserve raw SQL and tools for paths where measurement proves you need them. In simple terms: productivity first, then prove the query plan under load.

ORM vs raw SQL, and the N+1 trap

Those three questions usually lead you to a hybrid: an for CRUD, then deliberate query shaping when you load associations. ORMs accelerate CRUD and help prevent injection when used correctly; they hide SQL until explain plans surprise you. Raw SQL shines on hot paths where milliseconds and clarity matter.

ORM benefits

  • Rapid development: map objects to tables without hand-writing every CRUD query.
  • Type safety: compile-time checks catch many query mistakes before they ship.
  • Database portability across engines, though dialect quirks still appear under load.
  • Migration tooling: schema versioning ships with most mature ORM ecosystems.
  • Security: parameterized queries help prevent SQL injection when used correctly.

ORM drawbacks at scale

  • Generated SQL is often suboptimal: unnecessary JOINs and over-broad SELECTs.
  • query problem: a silent multiplier of round trips under load.
  • Complex analytics still force an escape hatch to raw SQL anyway.
  • Object mapping overhead: roughly 5–15% CPU at high QPS.
  • Hard to optimize: the ORM hides the actual query plan until you inspect it.

Raw SQL benefits

  • Full control over query optimization when EXPLAIN shows a bad plan.
  • Complex analytics queries: window functions, CTEs, and custom joins stay explicit.
  • Explicit about what data is fetched and how the engine will scan it.
  • No object-mapping overhead for bulk reads or large write batches.

Production practice: hybrid

  • ORM for simple CRUD, which covers most day-to-day queries.
  • Raw SQL for performance-critical paths where milliseconds and plans matter.
  • Query builder (Knex, JOOQ) as a typed middle ground between both.
  • Always review generated SQL in development before calling a path hot.
  • Performance test with production-scale data so N+1s surface early.

Code that looks innocent

Suppose the API loads forty posts in one query, then lazily loads each author in forty additional queries. Even a one-millisecond round trip adds roughly forty milliseconds of serial network waiting before considering query work. Collect distinct author identifiers and fetch them in one bounded batch, or use an appropriate join or eager-loading query. If a join multiplies rows across several one-to-many relationships, separate batches can be clearer and cheaper than one enormous result set.

javascript
orders = Order.findAll({ where: { userId: 123 } })  // 1 query
for (order of orders) {
  order.product = Product.findById(order.productId)  // N queries
}
// 1 query for orders + N queries for products = N+1 total
// 100 orders = 101 database round trips!
// At 1ms per query: 101ms vs 2ms with proper loading

Fix 1:

javascript
orders = Order.findAll({
  where: { userId: 123 },
  include: [{ model: Product }]   // JOIN in single query
})
// 1 query with JOIN: SELECT o.*, p.* FROM orders o JOIN products p...
// 1 database round trip regardless of result count

Fix 2: Batch loading

javascript
// Collect all productIds, query in one batch
productIds = orders.map(o => o.productId)
products = Product.findAll({ where: { id: productIds } })
// 2 queries total: 1 for orders + 1 for all products
// A batching-loader utility does this automatically
// Batches requests within a single event loop tick

Fix 3: Materialized view

sql
CREATE MATERIALIZED VIEW order_details AS
  SELECT o.*, p.name, p.price
  FROM orders o JOIN products p ON o.product_id = p.id;
-- Single table scan, no joins at read time
-- Refresh periodically or on write triggers

Connection pooling and numbers to know

Once query shape is under control, the next failure mode is session budget. Opening a TCP + TLS + auth session per request is expensive; a reuses sockets with sane max limits per process, plus a when many pods hit one primary.

Why pooling matters

  • • Creating a DB connection: TCP + TLS + auth ≈ 20–50ms each time.
  • • Reusing a pooled connection costs under 0.1ms per checkout.
  • • Often 200×–500× faster than opening a fresh session.

Without a pool: 1000 concurrent requests need 1000 connections. Default max_connections ≈ 100 means hundreds rejected.

Pool settings (per instance)

  • min_connections: 5: keep a few warm connections ready.
  • max_connections: 20: hard limit on sockets per instance.
  • connection_timeout: 5s: how long to wait for a free connection.
  • idle_timeout: 300s: close idle sockets after five minutes.
  • max_lifetime: 1800s: force-close every connection after 30 minutes.

Connection multiplexer

50 instances × 20 = 1000 app connections; a pooler may keep only ~100 actual DB connections (≈ 10:1 multiplexing).

session: 1:1 mapping (least benefit)transaction: share between transactions (best default)statement: share between statements (most aggressive)

Transaction mode is usually recommended. Watch when pooling modes change session state.

Numbers to know

Connection open vs pool

20–50ms vs <0.1ms

At 1,000 requests per second, twenty milliseconds of connection setup per request adds about twenty seconds of aggregate waiting per second, or roughly twenty extra in-flight requests in steady state. That is elapsed-time occupancy, not twenty CPU cores of work: part of the setup waits on network and authentication. Pooling removes much of this repeated setup, while query execution and pool waiting still need separate measurement.

Capacity ceiling

pods × max_pool ≤ DB max

50×20=1000 app sockets need a multiplexer to ~100 backends, or scale-out becomes a self-DoS.

N+1 latency

101×1ms ≈ 101ms vs ~2ms

Eager load or batch; multiply by RPS to see why ORM convenience becomes an incident.

Watch: pool wait time, active/idle connections, replica lag, slow-query rate, and DDL lock duration.

Replica routing and query hygiene

Pooling protects the primary's session budget; routing decides which node answers each query. Send writes and consistency-critical reads to the primary; offload read-only traffic to a while respecting .

javascript
function getConnection(query) {
  if (isWriteQuery(query)) return primaryPool
  if (requiresStrongConsistency(query)) return primaryPool
  return replicaPool  // round-robin across replicas
}

Replication lag awareness

A replica can lag behind the primary by a variable amount, especially during large writes, failover, or schema work. After a user changes an address, route their dependent read to the primary or require a replica that has applied at least the write’s known log position. A fixed one-second delay is a heuristic, not proof that the write is now visible.

Measure replica lag and route away from replicas beyond the product’s stale-read budget. Reads that enforce inventory, balance, or authorization rules should use the authoritative transaction path. Read-only queries are not necessarily safe to serve stale merely because they do not modify data.

Prepared statements

At 100K QPS, without parsing can burn ~15% CPU; with them often <1%: large savings at scale.

Query hygiene checklist

  • ✓ EXPLAIN ANALYZE any query that touches more than 1000 rows.
  • ✓ Index columns used in WHERE, JOIN, and ORDER BY clauses.
  • ✓ Avoid SELECT *: fetch only the columns the response needs.
  • ✓ Use LIMIT with cursor pagination instead of large OFFSET skips.
  • ✓ Partition large tables by date or tenant so scans stay bounded.
  • ✓ Monitor the slow query log; investigate anything over 100ms.

Online DDL and migration discipline

Query shape and routing keep the steady-state healthy; schema change is where availability often dies. ALTER TABLE on a multi-billion-row table can lock for hours. Tools like gh-ost or native exist because naive DDL still hurts production.

pt-online-schema-change

  1. Create a new empty table that already has the desired schema.
  2. Create triggers on the original table so live writes copy across.
  3. Copy existing data in chunks, for example 1000 rows at a time.
  4. Swap the tables atomically with a rename once the copy finishes.

Zero downtime pattern used widely before gh-ost.

gh-ost

  1. Create a ghost table that already has the new schema.
  2. Read the binary log instead of triggers, which is less intrusive.
  3. Copy rows in controlled chunks with backoff when lag rises.
  4. Apply binlog changes to the ghost table continuously during the copy.
  5. Cut over with an atomic rename swap when you are ready.

No triggers, controllable pause/throttle, and cut-over can be postponed. Used on tables with billions of rows.

  • Phase 1 (expand): add the new column while keeping the old column live.
  • Phase 2 (migrate): write to both columns and backfill historical rows.
  • Phase 3 (contract): stop writing the old column, then drop it.

Expand, migrate, and contract are separate deploy phases with different rollback boundaries. During expansion and backfill, old and new representations can coexist so old binaries remain usable. Once the old column has been dropped or new-only values cannot be translated back, rollback may require restoring or transforming data. Rehearse that boundary explicitly; a destructive schema change cannot honestly promise lossless rollback at every phase.

Safe migration rules

For an incompatible rename, add a new field, deploy compatible readers and writers, backfill safely, then remove the old field only after every consumer has migrated. Adding a NOT NULL constraint requires valid existing rows and a database-specific validation plan; a default is one possible tool, not a universal rule. Check the exact engine/version’s lock and rewrite behavior for the planned DDL rather than assuming every ALTER TABLE behaves alike.

Test on a production-size copy first. Measure lock time, replication lag, and disk. A "simple" index build can take hours and spike lag.

Migration tooling

Use versioned migration files (Flyway, Alembic, Knex) with up and down paths. Store migration state in the database to prevent double-application.

Choose native online DDL or a copying migration tool according to the database, version, table shape, and requested change. “Online” can still require a brief exclusive lock at cutover and can generate substantial write-ahead logs, temporary disk usage, and replica lag. Apply lock deadlines and load-aware throttling, verify the copied data, and keep an abort path before cutover. A low-traffic window provides extra margin but does not replace compatibility.

Failure timeline, and what still breaks after the fix

Putting query shape, pool math, and routing together, here is how an N+1 feed collapses under load, then the ways an improved design can still fail (see Numbers to know above for the capacity ceiling).

Timeline: N+1 then pool collapse

T0  Feed API: 1 query for 50 posts + 50 author lookups = 51 RTTs (~50ms)
T1  Black Friday: 2k RPS × 51 queries ≈ 102k QPS to primary
T2  Each app holds connections longer → pool checkout waits climb
T3  Pods scale to 80; without multiplexer: 80×20 = 1600 attempted sessions
T4  DB hits max_connections; new checkouts fail; p99 → multi-second
T5  Clients retry → amplify load; incident is "DB down" but root was query shape + pool math

Fix query shape first, then connection budget, then replicas. Scaling pods without a pooler multiplies the wound.

Still fails when…

  • Transaction pooling requires reviewing session state: prepared-plan support and temporary-table behavior depend on the pooler feature and configuration, so check compatibility explicitly.
  • Read replicas return stale data if you ignore lag right after writes.
  • Aggressive online DDL spikes lag and disk; throttle or you trade a lock for a replica outage.
  • Expand-contract out of order (drop old while old binaries run) looks like random ORM bugs.

Trade-offs

  • ORM speed of development versus direct control of the query plan.
  • Read replicas increase read scale but introduce replication lag to manage.
  • Online migrations add safety but need more operational tooling and monitoring.

Choosing and explaining database connection patterns

A complete explanation connects the mechanism to a requirement. Describe what improves when you introduce it, which new failure cases become possible, and how you would know that the trade-off is worthwhile.

Explain the core idea

Discuss how the app issues queries, avoids N+1s, budgets connections, and routes reads safely.

Use ORMs for productivity but measure generated SQL on hot paths.

Treat schema migrations as production workflows, not one-off DDL.

When it belongs

The API depends on relational data and latency matters.

Read replicas, connection pools, or migrations affect availability.

The interviewer asks why the database becomes the bottleneck.

How it fails

N+1 queries multiply DB load.

Replica lag returns stale or inconsistent reads.

Migration locks a large table during peak traffic.

Trade-offs to weigh

ORM speed of development versus query-plan control.

Read replicas increase scale but introduce lag.

Online migrations add safety but require more operational tooling.

Common misunderstandings

Assuming indexes and pools fix every database problem.

Running blocking ALTER TABLE on large tables.

Ignoring transaction scope when using pooled connections.

Try it: ORM access patterns and query count

Compare naive per-row lazy loading with batched eager loading. Each page load adds queries to the total, which drives pool pressure and latency.

ORM round-trip pattern

Active pattern: N+1 lazy loads

Orders per page
5
Queries this load
6
Total queries (session)
0

Load orders for a page. N+1 issues one query per row for related lines; batched loading uses a constant small number of queries.

Going deeper

Production deep dive: connections are a correctness boundary

Pooling, transactions, prepared statements, replica routing, and migrations interact. A fast pool configuration is unsafe if a transaction leaks across borrowers or a retry repeats an ambiguously committed write.

What must stay true

One checked-out connection has one explicit transaction owner, and it is committed or rolled back before the connection returns to the pool with no session state leaking to the next borrower.

How the pieces fit

DB connection cap

600

Reserve 100 for admin, migration, and failover work.

App instances

50

500 usable ÷ 50 gives 10 connections/instance.

Target utilization

70%

Configure about 7 busy-normal slots/instance plus bounded queueing.

Transaction scope

Acquire late, begin immediately before related statements, commit early, and release in finally. Never wait for an HTTP call or queue message while holding a database transaction. Set statement, lock, transaction, and pool-acquire timeouts inside the request deadline.

Unknown commit outcome

If the connection drops after COMMIT was sent, the client cannot infer whether it committed. Do not blindly retry a non-idempotent insert. Reconnect and query by operation/idempotency key; if absent, retry with the same key, otherwise return the recorded result.

Prepared statements through pools

Prepared statements can be session-local. Transaction-pooling proxies may send the next transaction to another backend, so use protocol modes supported by the proxy, named-statement discipline, or driver-side preparation. Reset session variables, temp tables, and advisory locks on release.

Replica lag and read-your-writes

Return a commit LSN/version after write. A following read selects a replica only when replay position ≥ token; otherwise wait within deadline or use primary. Lag thresholds alone are weaker because a low average can hide the exact partition/version the user needs.

Migration rollback

Use expand-contract: add nullable/new structures, deploy code that reads old and writes both, backfill in bounded chunks, switch reads, then contract later. Rollback routes reads to the old field and stops backfill; destructive drops happen only after the rollback window and old binaries are gone.

Worked example: Pool-sizing calculation

  1. 1Database allows 600 sessions; reserve 100 for administration, replication, migrations, and failover, leaving 500.
  2. 2At 50 app instances, hard maximum is 10 each. Configure eight plus a small bounded acquire queue so autoscaling to 60 instances still uses at most 480.
  3. 3If one query averages 20 ms, eight connections theoretically serve 400 queries/s per instance; validate at p95/p99 and DB CPU rather than multiplying the mean blindly.

More pool connections can reduce acquire wait while increasing database contention. Size from the database budget across maximum instances, then load-test the tail.

Database-client failure matrix

ScenarioConsequenceHow to contain it
Connection dies after COMMIT sendOutcome is unknown; retry may duplicate.Lookup stable operation key before repeating the transaction.
Replica behind session tokenUser cannot read acknowledged write.Wait for replay position or route to primary.
New code rolled back mid-backfillRows contain mixed old/new schema.Backward-compatible reads, pause resumable backfill, keep old field authoritative.

What to watch in production

  • Graph pool in-use/idle, acquire wait and timeout, transaction age, statement timeout, unknown outcomes, replica replay position, and migration lag.
  • Kill connections during commit in a test environment and verify operation-key reconciliation.
  • Abort migrations automatically when lock wait, replica lag, or user latency crosses the runbook threshold.

A scenario to reason through

The API times out after sending COMMIT for a $50 transfer. Is it safe to rerun the transaction?

One solid approach

Not without reconciliation. Query the transfer by its idempotency/operation ID on a consistency-safe path. Return the existing result if committed; only retry with the same unique key when definitively absent.

Going deeper

Database connection patterns

The mechanism and its boundary

Database connection patterns decide where sessions live, how transactions are scoped, and how application concurrency reaches the database. The core skill is preventing app fanout from becoming database connection exhaustion.

Application sessions to database sessions

1

App pool

2

Proxy pool

3

DB sessions

4

Replica routing

Work through the sizing assumptions

If 30 app instances each open a pool of 40, the database sees up to 1,200 sessions. If the safe limit is 500, the pattern is already overloaded before traffic grows.
Transaction pooling can let many short requests share fewer backend sessions, but it breaks session-level state assumptions.

When it belongs

Many app instances talk to a database with limited session capacity. Serverless or autoscaled compute can create bursty connection storms. Read/write routing, transaction scope, or proxy pooling is part of the design.

Assumptions that matter

Pool limits are calculated across all app instances. Transactions are short and explicit. Read replicas expose lag metrics for routing decisions.

Easy mistakes

An application that relies on session variables or other connection-local state cannot simply switch to transaction pooling without checking how that state is established for each transaction. Routing a confirmation read to a lagging replica can make a committed action appear missing, so use a suitable primary or version-aware routing rule for read-your-writes behavior. Migrations and administrative work need reserved capacity or isolation from saturated application pools so operators can still inspect and recover the database during overload.

How it fails

Connection storm during deploy or cold start. Long transactions hold sessions and block pool reuse. Replica lag makes read routing violate freshness expectations.

Trade-offs to name

Session pooling preserves a client’s backend session across transactions, which supports session state but occupies a backend connection for longer. Transaction pooling reassigns that connection after each transaction and improves reuse, but applications must not depend on arbitrary session state surviving on their next transaction. Read replicas add read capacity, while lag requires routing decisions for requests that must observe a recent write and operational controls for an unhealthy replica.

Wrap-up

You have seen the failure, the mechanism, and the operational signals. Use these exercises to check the ideas stuck, then follow the references for the primary sources.

Exercises

  1. Size the pooler. 120 app pods, each wants max 15 connections, Postgres max_connections=400 (reserve 50 for admin). What transaction-pool backend target do you set, and what happens if you forget and deploy to 200 pods?
  2. Kill an N+1. A feed loads 40 posts then authors and like counts in nested loops. Propose eager load vs batch loader vs materialized view, and name the metric that proves the fix under load.
  3. Expand-contract a rename. Rename users.full_name → users.display_name on a 2B-row table with zero downtime. List deploy phases and which step is forbidden while old binaries still run.

Topic-specific references

Key takeaway

Day-to-day database pain is usually query shape, connection budget, replica routing, and migration locking, not theoretical storage limits. Use ORMs for speed, kill N+1s early, pool aggressively, and change schema the way you change code: expand, migrate, contract.

Practice and further reading

Use these exercises to connect the lesson to a design decision. State your assumptions, trace what happens to one request, and explain the rule that must remain true even during a failure. That rule is the design's correctness invariant.

Focus for this lesson: Define transaction scope, pool/proxy topology, commit ambiguity, retry safety, prepared state, replica-lag routing, migrations, and rollback.

Explain the decision

What should a client do after losing connection during COMMIT?

Read the reasoning

Treat outcome as unknown, query by transaction/idempotency key or reconcile; blindly retrying can duplicate an effect.

Apply it to a production scenario

A read-after-write goes to a lagging replica and appears missing. What contract helps?

Read one approach

Carry a commit LSN/version, pin the session to primary, or wait/select a replica that has replayed that position.

Work through the numbers

One thousand serverless instances each open 10 connections against a 2,000-connection database. Oversubscription?

Read the worked calculation

10,000 requested, 5x capacity; use a proxy, strict pool/admission budget, and concurrency caps.

Reason through a failure

What must a migration rollback preserve?

Read the recovery reasoning

Old and new application versions can operate on the expand-phase schema; destructive contract steps happen only after telemetry proves old consumers are gone.

Sources and further study

Continue with SLI, SLO, SLA & Error Budgets.