When to Cache, When to Replicate, and When to Shard

The situation

A ticketing platform runs Aurora PostgreSQL on a single writer with one reader. The writer sits at 85% CPU during business hours and touches 100% during the ninety minutes after a major on-sale opens. The application is a monolith with about 300 distinct queries.

Three things are known from a fortnight of Performance Insights data. Roughly 60% of database load is reads, and of those, about a third are the same eight queries hitting event and venue records that change perhaps twice a day. Another chunk of read load is a reporting screen running wide aggregate queries that nobody has optimised. Writes are 40% of the load and concentrated entirely in the on-sale windows, where they saturate the writer on their own.

The team has been told to “add a caching layer” by one advisor and “shard the database” by another. Both changes are large, and only one of them addresses what happens during an on-sale.

What actually matters

The first thing that matters is that these three options relieve different pressures, and only one of them touches writes. A cache and a read replica both remove read load from the writer, which helps the writer indirectly by giving it back capacity. Neither reduces the number of writes, and if the writer is saturated by writes then neither is the answer to the problem that actually hurts.

The second is the shape of the reads, because it decides between cache and replica. A small set of keys read over and over is what a cache is for: the hit rate climbs, the memory footprint stays small, and the latency drops to microseconds. A broad spread of varied queries has a poor hit rate and a large footprint, and a replica handles it without any application knowledge of which rows are hot.

The third is the consistency each option costs, and who has to know about it. A read replica introduces lag: a read immediately after a write may not see it. A cache introduces staleness bounded by a TTL and by whatever invalidation the application performs. Both are acceptable for a venue record that changes twice a day, and neither is acceptable for the seat-availability check during an on-sale, which is exactly the read that must be current.

The fourth is how much of the change lands in the application. Repointing read traffic at a reader endpoint is a connection-string change and no logic. A cache is code: read-through, write-through or cache-aside, plus invalidation, plus a decision about what happens on a cache miss storm. Sharding is a rewrite of every query that crosses a shard boundary, plus a routing layer, plus an operational story for rebalancing.

Underneath all of it, some of this load should not exist. A reporting screen running wide aggregates against the transactional writer is a workload in the wrong place, and moving it is cheaper than scaling anything to accommodate it.

What we’ll filter on

1. Is the pressure on reads, writes, or both?
2. What shape are the reads: a small hot set, or a broad spread?
3. What staleness can each consumer tolerate, and which reads must be current?
4. How much of the change lands in application code?
5. What does it cost to operate afterwards?
6. Does it remove load, or move it somewhere better suited?

The landscape

Read replicas. An Aurora reader shares the cluster storage volume with the writer, so replica lag is typically in the low milliseconds rather than the seconds a classic RDS read replica can drift. Adding readers is a console operation, and the reader endpoint load-balances across them. The application changes only by sending read traffic to a different endpoint, which for most frameworks is configuration. Readers do nothing for writes, and a workload whose writer is write-saturated gains only whatever read load the readers take off it.

ElastiCache. An in-memory cache, Redis or Valkey or Memcached, serving keys the application chooses to store. Latency is microseconds, and a hot key served from cache costs the database nothing at all, where the same row read from a replica still costs a query. The cost is code: the application decides what to cache, for how long, and how to invalidate, and it has to handle the miss path. It suits a small set of repeatedly-read values and does nothing for queries that are all different.

Aurora Serverless v2 for the writer. Scales writer capacity in fine increments rather than requiring a resize, which suits a workload that is quiet for most of the week and saturated during on-sales. It does not change the number of writes; it changes how quickly capacity arrives to serve them, and it removes the need to run peak-sized capacity around the clock.

Vertical scaling. Making the writer bigger. It is unfashionable and it is often the correct first answer, because it needs no application change at all, it takes a failover, and it is reversible. Its limit is the largest instance available, and its cost is paying for peak capacity continuously unless paired with something that scales.

Write sharding. Splitting the data across multiple independent databases by a partition key, so writes distribute. It is the only option here that increases write throughput past a single writer. It also has the largest blast radius: cross-shard queries, cross-shard transactions, rebalancing, and a routing layer all become the application’s problem. Aurora Limitless Database provides a managed form of this for PostgreSQL, which removes some of the operational burden without removing the need to choose a shard key.

Moving the workload instead. Reporting queries against a transactional writer are a workload in the wrong store. Aurora zero-ETL integration with Redshift replicates transactional data into an analytics warehouse continuously, so the aggregates run somewhere built for them. This removes load rather than accommodating it.

Queueing the writes. Where writes can be accepted and applied slightly later, putting them through SQS and a consumer with controlled concurrency turns a spike into a flat line at the database. It changes the contract the application offers, since the write becomes an acknowledgement rather than a commit, which for a seat reservation is a design decision rather than an optimisation.

RDS Proxy. Pools connections. Relevant when the writer is exhausted by connection churn rather than by query work, which is a different diagnosis. Worth naming so it can be ruled out here.

Evaluation

Side by side

Option	Helps reads	Helps writes	App change	Consistency cost	Operational cost
Add a reader	✓ broad	✗	Endpoint only	Replica lag (low on Aurora)	Low
ElastiCache	✓ hot keys only	✗	Read/write/invalidate logic	Staleness up to the TTL	Medium
Aurora Serverless v2 writer	✓	✓ capacity, not throughput	None	None	Low
Vertical scaling	✓	✓ to a ceiling	None	None	None
Write sharding	✓	✓ genuinely	Routing, cross-shard queries	Cross-shard transactions	High
Zero-ETL to Redshift	Removes the load	Indirect	Reporting repoints	Seconds behind	Low
Queue the writes	✗	✓ smooths spikes	Async write contract	Eventual	Medium

Reading it against the on-sale: only three rows do anything about a write-saturated writer, and two of them (vertical scaling, Serverless v2) buy capacity rather than throughput. Sharding is the only one that raises the ceiling, and it is also the only one that rewrites the application. That is the real trade in this scenario, and it is why the reads get dealt with first: relieving 60% of the load may move the writer far enough below the ceiling that the ceiling stops mattering.

The solution

Take the reads off the writer in the cheapest order, then decide whether writes still need anything. Three read fixes, each smaller than the one after it, and a decision point at the end that may not need to be reached.

Move the reporting screen first, because it is load in the wrong place. Aurora zero-ETL to Redshift replicates the transactional data continuously and the aggregates run in a store built for them. The reporting consumers accept data a few seconds behind, because a dashboard reading a number from ten seconds ago is not a defect. This removes the wide scans without optimising a single query.

Cache the eight hot queries next, because the shape is exactly right: a small set of event and venue records, read constantly, changed twice a day. ElastiCache with a TTL measured in minutes and an explicit invalidation on the admin write path gives a hit rate that will sit above 95%, and each hit costs the database nothing rather than costing a cheap query. Keep the cached set small and deliberate; the failure mode here is caching everything, ending up with a low hit rate and a large bill.

Point the remaining varied reads at the reader endpoint, and add a second reader. This is the connection-string change, and Aurora’s shared-storage replication keeps the lag low enough that it is invisible for everything except reads that must be current. Identify those explicitly, the seat-availability check above all, and pin them to the writer. Getting this wrong is the classic Aurora bug: a user reserves a seat, the confirmation page reads from a replica, and the seat looks free.

Then re-measure, and only then decide about writes. With 60% of load gone the writer may sit comfortably below its ceiling outside on-sales, which turns the problem from “the database is too small” into “the database is too small for ninety minutes a fortnight”. That is the shape Aurora Serverless v2 fits: capacity arrives when the on-sale opens and leaves afterwards, with no resize and no peak-sized bill for the other 99% of the time.

Sharding stays on the shelf. It is the only option that raises the write ceiling, and it costs a routing layer, cross-shard query rewrites, and a rebalancing story, so it is worth reaching for when the writer is saturated by writes after the reads have gone, not before. If it does become necessary, Aurora Limitless Database is the managed route and the shard key is still the decision that matters most.

Why not lead with the cache. It is the advice the team was given and it addresses a third of the reads, which is a third of 60%. The reporting screen is a bigger win for less code.

Why not shard now. The writer is at 85% with a workload that is 60% reads. Sharding a database to fix a read problem is an expensive way to arrive somewhere a reader endpoint reaches in an afternoon.

Worked example

The reporting move lands first and takes writer CPU from 85% to 71% during business hours, which surprises the team, because nobody had attributed that much load to a screen four people use.

The cache takes another 9 points off, settling at a 97% hit rate on the eight query patterns. Invalidation is wired into the two admin endpoints that change event and venue records, and the TTL is set at five minutes as a backstop rather than as the primary mechanism.

Moving varied reads to two readers takes business-hours writer CPU to 34%. During the migration one bug surfaces exactly where expected: the seat-availability check was moved to the reader by a blanket configuration change, and a test on-sale produces two customers holding the same seat. Pinning that query back to the writer fixes it, and it becomes the example in the team’s runbook for why “send reads to the reader” is not a blanket rule.

The on-sale is still the problem, but a different one. The writer now reaches 92% during an on-sale rather than 100%, and the load is almost entirely writes. Aurora Serverless v2 on the writer, with a floor sized to business-hours load and a ceiling well above the on-sale peak, absorbs the spike without a resize and costs less than the fixed instance did, because the fixed instance was sized for a peak that happens twice a month.

Nobody shards anything. The decision is revisited when a scheduled on-sale is expected to be four times larger than any so far, and at that point the conversation is about a shard key rather than about whether the database is too small.

What’s worth remembering

1. A cache and a read replica both relieve reads and neither reduces writes; if the writer is write-saturated, both are indirect help at best and sharding is the only option that raises the ceiling.
2. The shape of the reads picks between them: a small hot set repeatedly read is a cache, and a broad spread of varied queries is a replica, because a cache with a low hit rate is a component you operate for nothing.
3. Name the reads that must be current before moving anything to a reader. Replica lag on Aurora is low but not zero, and the read-after-write bug it produces looks like a business logic failure.
4. Ask whether the load should exist where it is at all: reporting aggregates on a transactional writer move to an analytics store through zero-ETL, which removes the load rather than scaling to accommodate it.
5. Vertical scaling and Aurora Serverless v2 buy capacity rather than throughput, which is enough when the constraint is a short peak rather than a sustained ceiling.
6. Sharding is the largest change on the page: routing, cross-shard queries, cross-shard transactions and rebalancing all become the application’s problem, so reach for it only after the reads have been dealt with.