Backend Architecture Decisions for Scalable Systems: Beyond the First Scaling Wall

When Does the Database Layer Become Your First Scaling Wall?

The web tier scales horizontally. You just add a load balancer and another instance, but the database does not scale that way at all because it requires complex state coordination. Once your write rate climbs, the primary node saturates its CPU or disk I/O.

That is the wall. Production database workloads are typically read-heavy by a factor of 10:1 or greater, making read replicas highly effective if you move those reads off the primary. PostgreSQL natively supports asynchronous streaming of the write-ahead log to read replicas, and MySQL, Microsoft SQL Server, and Oracle do the exact same thing.

Managed services like RDS support replica provisioning via a configuration flag. You do not need to run replication yourself. That saves time.

The default PostgreSQL configuration ships with max_connections=100, and that sounds perfectly fine until you horizontally scale your application servers across multiple worker nodes in production. Ten app nodes with a pool of 20 connections each already exhausts the primary. The fix is a pooler like PgBouncer.

It multiplexes connections. According to Ardura, connection pool sizing is the first operational lever you should pull before you ever consider touching your schema or doing engine migrations.

A rewrite is rarely the first answer. Before you touch the schema or migrate engines, you should exhaust the cheaper options that actually solve the immediate bottleneck without adding massive operational overhead. Query tuning means you run EXPLAIN ANALYZE on slow queries, because missing indexes and bad join order cause most pain.

Index tuning adds covering indexes for hot read paths. You must also drop unused indexes that slow down your database writes. Caching moves repeated reads for rarely-changed data into an in-memory layer.

Read replicas separate read-heavy traffic from writes so each side scales independently without competing for resources.

Every time I have seen this fail, the cause was the same: teams jumped to a rewrite before profiling the actual queries that were causing the production bottleneck. The fix was a single composite index and a read replica. We shipped it in an afternoon.

Next wall is replication lag. Once reads are offloaded, stale reads become the real problem, which is exactly where read-your-writes consistency enters the picture to save your users from confusion.

Step 1: Implement Read-Your-Writes Consistency Across Distributed Replicas

Asynchronous replication introduces lag and failover trade-offs, meaning users who just wrote data may need read-your-writes routing to the primary. Asynchronous database replication lag typically sits in the range of tens of milliseconds, according to Ardura. In quiet periods it can be sub-millisecond.

Under load, it spikes. A user who updates their profile and immediately reloads the page expects to see the change. If the read goes to a replica that has not received the WAL record yet, they see stale data.

They refresh, see old data, and assume the write failed. They submit again. Now you have duplicates.

There are three common routing strategies:

• Session-sticky routing: pin the user's session to the primary for some duration after a write. Simple. Breaks if the load balancer is not sticky.
• Time-windowed routing: route reads to the primary for N seconds after the last write by that user. N should be above your observed p99 replication lag.
• Per-record version tracking: store a version number on each row. The read path checks the replica's version against the primary's; if behind, route to primary. Most accurate, most expensive.

Most teams settle on time-windowed routing. It is cheap to implement and good enough. The window is usually 500ms to 2s.

Now the connection pool problem. Horizontal scaling of application instances multiplies database connections. Twenty app nodes with a pool of 25 is 500 connections against a primary that caps at 100.

PgBouncer in transaction mode fixes this by multiplexing, but the cost is real: PgBouncer in transaction mode breaks SET commands, prepared statements, advisory locks, and temporary tables. Anything that relies on session state will silently misbehave. You will not get an error; you will get the wrong result.

Here is a typical PgBouncer config in transaction mode for this pattern:

[databases]
primary = host=10.0.0.12 port=5432 dbname=app
replica = host=10.0.0.13 port=5432 dbname=app


[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

The version of this I keep coming back to does time-windowed routing with a 1s sticky flag stored in Redis, because it survives app node restarts and keeps the routing decision out of the database. The outcome is that users see their writes immediately without pinning all their reads to the primary forever.

The remaining reads go to the replica. The replica handles the bulk of traffic. The primary stays focused on writes. This is the architecture that buys you the next 10x.

Cache-aside Is a Read Pattern: Write-Through and Write-Behind Trade Durability for Performance

Cache-aside is a read pattern. The application checks the cache first, falls back to the database on a miss, and populates the cache on the way out. It is simple because the cache and the database know nothing about each other.

The application owns the logic. This simplicity is also the weakness: the cache can go stale, and the application is the only thing that can refresh it.

Write-through sends every write to the cache and the database synchronously. The DB round-trip is still on the hot path, so write latency does not improve. The benefit is that the cache never holds stale data for the records you write.

Write-behind sends the write to the cache first and the database later, usually via an async flush. This cuts write latency dramatically. The cost is that if the cache node dies before the flush, the data is gone.

According to Ruchitsuthar, in a 95:5 read-heavy system, an aggressive layered caching strategy can serve 99% of reads from the cache, hitting the database for under 1% of requests. That is the prize. But the pattern you choose for writes determines your durability posture.

REST endpoints are highly cacheable via HTTP caching headers. A Cache-Control: max-age=60 on a stable resource works with every CDN and every HTTP client for free. GraphQL is different.

A single GraphQL endpoint with variable query shapes defeats standard HTTP caching. You need custom caching layers and query complexity analysis to prevent N+1 resolver explosions. When cacheability is the primary scaling lever, GraphQL is a poor default.

Security matters here. Redis CVE-2022-0543 was a Lua sandbox escape in Debian-packaged Redis that allowed arbitrary code execution. The fix was a patched package, but the lesson is broader.

Run Redis bound to 127.0.0.1 or behind a network namespace. Enable ACLs, which ship in Redis 6.0 and later. Do not expose Redis to the internet with no authentication.

That is still the most common way teams get compromised.

Caching rarely-changed data in an in-memory layer like Redis takes repeated load off the database. The rules are:

• Start with cache-aside for reads.
• Add write-through only when stale reads are causing user-facing bugs.
• Avoid write-behind unless you can tolerate data loss on the cached path.
• Set bounded TTLs on everything, even with active invalidation.

The last rule matters more than people think. Active invalidation can fail silently. A dead message queue, a crashed worker, a bug in the invalidation hook, and your cache serves stale data forever. A bounded TTL is the safety net. The next section explains why TTLs alone are not enough.

Step 2: Prevent Cache Stampedes with Request Locking and Bounded TTLs

A cache stampede starts simply. Many concurrent requests miss the same cache key at the exact same time, so all of them fall through to the database and run the same expensive query. The database, already under heavy load, tips over completely under the sudden weight.

This takes systems down. This is the exact failure mode that takes systems down during traffic spikes, not steady-state load, and you must prevent it by coalescing concurrent misses using request locking.

The pattern is single-flight. The first request to miss acquires a distributed lock, fetches from the DB, and populates the cache, while the rest wait and then read the now-populated cache. Here is a Redis implementation using SET NX with an expiry.

# Acquire lock for cache key "user:42"
LOCK=$(redis-cli SET lock:user:42 "holder1" NX EX 10)
if [ "$LOCK" = "OK" ]; then
 # This request owns the lock.
Fetch from DB and populate cache.
 DATA=$(psql -c "SELECT * FROM users WHERE id=42")
 redis-cli SET user:42 "$DATA" EX 300
 redis-cli DEL lock:user:42
else
 # Another request is fetching. Sleep and retry. Sleep 0. 1
 # Retry cache read; if still missing, loop or fail fast. Fi

The lock TTL must be short. If the lock holder crashes mid-fetch, the lock needs to expire so other readers are not stalled forever waiting for a response that will never come. Ten seconds is a common ceiling. Sleep-and-retry adds latency to the waiting requests. That is the price of coalescing.

Bounded TTLs should be set on cache entries even when active invalidation exists, because active invalidation fails silently and a bounded TTL is the backstop that guarantees stale data eventually ages out. According to Dev, this is a non-negotiable default for any production cache layer. Consider distributed cache node failure.

When a Redis cluster node dies, all the keys it owned are gone, and the cache-miss spike floods the database in the same failure mode as a stampede, just at a larger scale. The mitigations are the same. Request locking coalesces the flood, and bounded TTLs limit how long any single key can be stale once the node recovers.

The version of this I keep coming back to does request locking with a 10s lock TTL and a 50ms retry, because it collapses the thundering herd without adding noticeable latency for the waiters. The database stays flat. It does not spike to 100% CPU during traffic spikes.

The checklist for a production cache layer: - Set bounded TTLs on every key. - Coalesce concurrent misses using a distributed lock. - Keep lock TTL short to survive a crashed holder process. - Monitor Redis replication lag. - Assume the cache will die eventually. - Test the recovery path.

Once reads are cached and stampedes are handled, the next scaling wall is writes. That is where idempotency enters.

How Do You Implement Idempotency on the Hot Money Path?

Idempotency means a request can be repeated without changing the result beyond the first application. On the hot money path, this prevents double-charges. It also enables safe retries.

If a network blip causes a client to retry a checkout, you want the second request to return the first response, not to charge the card a second time.

There are two common approaches to idempotency keys:

• Deterministic keys: hash of method, path, body, and user. No client cooperation needed. Breaks if the body changes in any way between retries.
• Client-provided UUID keys: the client sends an Idempotency-Key header. The server stores the request ID and the response hash. Stripe uses this pattern, and it is the industry-standard reference implementation.

The Stripe pattern is the better default. The client controls the key, so retries are deterministic even if the client changes other parts of the request between attempts. The server stores the key in a deduplication table.

Before processing, it checks the table. If the key exists, it returns the stored response. If not, it processes, stores the key and response, and returns.

Here is a token-based deduplication check in pseudocode:

async function processCheckout(req, res) {
  const key = req.headers['idempotency-key'];
  if (!key) return res.status(400).send('Idempotency-Key required');


  const existing = await db.query(
    'SELECT response_hash, status FROM idempotency WHERE key = $1',
    [key]
  );
  if (existing.rowCount > 0) {
    return res.status(existing.rows[0].status).send({ replay: true });
  }


  const result = await chargeCard(req.body);


  await db.query(
    'INSERT INTO idempotency (key, user_id, response_hash, status) VALUES ($1, $2, $3, $4)',
    [key, req.user.id, hash(result), 200]
  );


  res.status(200).send(result);
}

Using asynchronous queues for checkout creates a fast user experience but requires consumers to be idempotent due to eventual consistency. At-least-once delivery means the same message will arrive twice. Without idempotent consumers, you process it twice. The deduplication table is the guard.

Every time I have seen this fail, the cause was the same: the deduplication table itself became a write bottleneck. The fix was to partition the table by user ID and add a TTL cleanup job to keep the table from growing forever. The storage grows linearly with request volume.

Without a TTL or cleanup job, the table eventually slows down every insert.

The costs of idempotency:

• The deduplication table is a write bottleneck and must be sharded or partitioned by user ID.
• Storage grows linearly with request volume and requires a TTL or cleanup job.
• The deduplication check adds a DB read to every write path.

These costs are worth paying. Double-charges are worse.

The Uncommon Insight: Sharding Is a Complexity Tax, Not a Scaling Milestone

Sharding is framed as a natural evolution. The framing is wrong. Sharding raises operational and application complexity including routing, rebalancing, cross-shard queries, and recovery.

It is irreversible in practice. Unsharding means a massive data migration back to a single node, and most teams that shard never unshard, even when they could.

Sharding should be introduced only when write throughput, storage, or availability requirements exceed simpler approaches. Before you shard, exhaust vertical scaling by getting a bigger box, faster disks, and more RAM, which is often cheaper than the engineering cost of sharding. 1.

Exhaust vertical scaling with bigger boxes, faster disks, and more RAM. 2. Move reads off the primary.

3. Fix the queries before splitting your data. 4.

Take load off the database entirely with caching.

The choice of sharding key determines everything that comes after. Three anti-patterns recur. Hot shards happen when a key does not distribute writes evenly, like sharding by timestamp where all writes land on the newest shard.

Cross-shard joins happen when a key requires joining data across shards for common queries. Unchangeable keys are keys that cannot be changed without rehashing all data.

The rebalancing operation is the hidden cost. Adding a shard requires rehashing and migrating a portion of all existing data. During the migration, you either dual-write to old and new shards or accept a full maintenance window.

Dual-writing means your application must handle two shards for the same key. That is a class of bug that only appears during migrations, which is the absolute worst time to debug anything. Sharding introduces a routing layer that must be maintained.

You also add a rebalancing procedure that must be tested thoroughly. And you get a set of cross-shard query limitations that permanently constrain your query capabilities.

These are not one-time costs. They are ongoing taxes on every feature you build afterward. The backend architecture decisions for scalable systems that involve sharding are the most expensive decisions you will make. Treat them as such.

Step 3: Measure True Scalability with p95 and p99 Latency Metrics

A flat median latency can conceal a failing tail. P95 and p99 latency metrics are required to measure true system scalability, according to Dev. If your median response time is 50ms but your p99 is 2s, one in a hundred users is having a bad experience.

That one user is the one who writes the bug report.

To measure true scalability, set up load tests that mimic realistic production behavior. Use realistic connection pool sizes. A test with 10 connections tells you nothing about a production pool of 100.

Include downstream dependency latency and do not mock the database. A mock database returns in 1ms, but the real one returns in 20ms under heavy load. That difference is where the cliff lives.

Test at 2x peak traffic. You want to find the cliff before production traffic does.

The methodology is strict. Measure p95 and p99 per endpoint. Correlate tail latency spikes with specific operations like cache misses, replication lag, GC pauses, and lock contention.

Track the p99 to p50 ratio. A rising ratio indicates growing variance and impending instability. A stable system has a p99-to-p50 ratio of 3x to 5x.

A ratio of 20x is a system about to fail.

The cost of this measurement is real. Per-request tracing is required for p95 and p99 instrumentation. This adds 1 to 3% CPU overhead at the application layer. You sample and accept that the tail events you most want to see are the ones you will miss.

Now you face the decision. Do you invest in read-your-writes routing and cache stampede prevention now, or do you accept the replication lag and stale-read risk until the next incident forces the investment? Most teams wait for the incident.

The incident is more expensive than the engineering. But the engineering is not free, and it competes with every other feature on the roadmap. Backend architecture decisions for scalable systems are ultimately decisions about where you spend engineering time and where you accept risk.

There is no risk-free option. There is only the option you chose on purpose.