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.

A fortnight of Performance Insights data splits the load. Reads are 60% of it and writes are 40%. Within the reads, about a third are the same eight queries against event and venue records that change perhaps twice a day, and about half are one reporting screen running wide aggregates that nobody has optimised. The remaining sixth is spread thinly across the rest of the query set. The writes arrive almost entirely inside 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 touches what happens during an on-sale.

What actually matters

Only one of the three options does anything about writes. A cache and a reader both take read work off the writer, which helps it indirectly by returning capacity it can spend on writes. Neither reduces the number of writes. During an on-sale the writes saturate the writer by themselves, so at that moment neither a cache nor a reader is addressing the constraint.

The shape of the reads decides between the two. A small set of keys read over and over gives a high hit rate, a small memory footprint and microsecond reads. A broad spread of varied queries gives a poor hit rate and a large footprint, and a reader serves that spread with no application knowledge of which rows are hot.

Then staleness, and who has to know about it. Aurora replica lag is usually much less than 100 milliseconds, and it varies with the rate of database change, so it is at its widest under heavy writes. That is precisely when an on-sale is running. A cache is stale up to its TTL and up to whatever the write path invalidates. Both are fine for a venue record that changes twice a day. Neither is fine for the seat-availability check.

Last, where the change lands. Repointing reads at the reader endpoint is a connection string and no logic, though Aurora balances connections across readers rather than queries, so a pool opened at deploy time keeps whichever reader it got. A cache is code: read-through, write-through or cache-aside, plus invalidation, plus a plan for the miss path when a popular key expires. Sharding rewrites every query that crosses a shard boundary and adds a routing layer. Account and Region boundaries bite too. A reader lives in the same cluster and the same Region as its writer, Aurora PostgreSQL has no cross-Region Aurora Replica, and a zero-ETL integration can write into a warehouse owned by another account but not one in another Region.

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 take to operate afterwards, and what does it do to the bill?
6. Does it remove load, or move it to a store built for it?

The landscape

Readers. Aurora readers share the cluster volume with the writer, so replication happens in the storage layer instead of by shipping statements. Lag is usually well under 100 milliseconds and grows with the write rate. A cluster takes up to 15 readers, distributed across the Availability Zones the cluster spans, and the reader endpoint balances connections across them. The application changes by connecting somewhere else, which for most frameworks is configuration. Readers do nothing for writes.

ElastiCache. An in-memory cache running Valkey, Redis OSS or Memcached, holding the keys the application chooses to store, at microsecond latency. A hot key served from cache never reaches the database at all, where the same row read from a reader is still a query against a node you are paying for. It needs code: what to cache, for how long, how to invalidate, and what the miss path does when a popular key expires everywhere at once. ElastiCache Serverless removes the node sizing. It removes none of that code. A query set that is all different gains nothing here.

Aurora Serverless. Capacity moves between 0 and 256 ACUs in steps as small as half an ACU, where one ACU is roughly 2 GiB of memory with matching CPU and network. The user guide now calls it Aurora serverless, with the v2 suffix dropped, though the API still spells it ServerlessV2ScalingConfiguration. It does not change the number of writes. It changes how fast capacity scales to serve them, and it removes the need to run peak-sized capacity around the clock. One mechanism matters for a ninety-minute spike: the scaling rate depends on the current capacity, and the higher that capacity the faster it scales up, so a floor set near zero climbs slowly from cold.

Vertical scaling. Making the writer bigger. It is unfashionable and it is often the correct first answer, because it needs no application change and it is reversible. It takes a brief interruption while the instance class changes. Its limit is the largest class available, and you pay for peak capacity around the clock unless something scales it back down.

Write sharding. Splitting the data across independent databases by a partition key, so writes distribute. It is the only option here that raises 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 is the managed form for PostgreSQL, and it is a separate cluster rather than a setting on this one. A Limitless cluster holds no writer or reader instances, requires I/O-Optimized storage, cannot be the source of a zero-ETL integration, and supports neither RDS Proxy nor read replicas nor Aurora Global Database. An account gets five shard groups per Region. Shard key values cannot be updated once rows carry them, so changing a shard key means dropping it and recreating it.

Aurora DSQL. The other route past a single writer, and the wrong shape for seat contention. DSQL distributes writes under optimistic concurrency control: conflicting transactions are never blocked, they fail at commit with a serialization error for the application to retry. An on-sale is thousands of clients competing over a few hundred rows, which is the workload that generates those conflicts. It also fixes isolation at repeatable read, caps a transaction at 3,000 modified rows, and offers no PL/pgSQL and no triggers.

Moving the workload instead. Reporting queries against a transactional writer are in the wrong store. An Aurora zero-ETL integration replicates the cluster into Amazon Redshift continuously and in near real time, so the aggregates run somewhere built for them. The target can be a Redshift namespace in the analytics account, authorised from that side, but it has to sit in the same Region as the source. An Aurora PostgreSQL source needs at least one data filter pattern, and every table inside that filter needs a primary key or it lands in a failed state. While the integration is being created the target warehouse sits in Modifying and cannot be queried, and AWS publishes no figure for how long that takes. Afterwards, a DDL statement against a source table can put that table into a resync, and by default only tables in the Synced state are queryable.

Queueing the writes. Where a write can be accepted and applied slightly later, putting it through SQS and a consumer with controlled concurrency flattens the spike 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 and shares connections, and queues or sheds new ones instead of letting the writer get oversubscribed. It also holds application connections open across a failover, which matters here because a resize and a conversion to Aurora Serverless each take one. Its default endpoint sends every connection to the writer, so reading through a proxy needs a read-only proxy endpoint.

Evaluation

Side by side

Option	Helps reads	Helps writes	App change	Consistency	Operational cost
Add a reader	✓ broad	✗	Endpoint only	Lag under ~100ms, widest under write load	Low
ElastiCache	✓ hot keys only	✗	Read, write and invalidate logic	Stale up to the TTL	Medium
Aurora Serverless writer	✓	✓ capacity, not throughput	None	None	Low
Vertical scaling	✓	✓ to a ceiling	None	None	Low
Sharding (Aurora Limitless)	✓	✓ genuinely	Routing, cross-shard queries	Cross-shard transactions	High
Aurora DSQL	✓	✓ genuinely	Retry on commit conflict	Repeatable read, OCC	Medium
Zero-ETL to Redshift	Removes the load	Indirect	Reporting repoints	Near real time	Low
Queue the writes	✗	✓ smooths spikes	Async write contract	Eventual	Medium

Five rows do something about writes, and they do three different things. Vertical scaling and Aurora Serverless add capacity without raising what one writer can sustain. Sharding and DSQL raise it, and both rewrite the application. Queueing leaves the ceiling where it is and flattens the arrival rate instead. That is the trade in this scenario, and it is why the reads come first: taking 60% of the load off the writer may leave it far enough below its ceiling that the ceiling stops being the problem.

The solution

Take the reads off the writer, biggest win first, then decide whether the writes still need anything. Three read fixes, each removing less load than the one before it, and a decision at the end that may never have to be made.

Move the reporting screen first. It is half the read load and it is in the wrong store. A zero-ETL integration replicates the cluster into a Redshift namespace owned by the analytics account, in the same Region, and the aggregates run there. Set the data filter to the schemas the reports read, confirm every table inside it has a primary key, and expect the warehouse to be unqueryable while the integration is created. The reporting consumers tolerate a lag they do not control: AWS describes the replication only as near real time, and a dashboard number that trails the writer is not a defect. Not one query gets optimised and the wide scans leave the writer.

Cache the eight hot queries next. The shape fits: a small set of event and venue records, read constantly, changed twice a day. Give them a TTL of a few minutes as a backstop, invalidate explicitly from the two admin endpoints that write those records, and the hit rate sits above 95%. A hit never reaches the database. Keep the cached set small and deliberate, because the failure mode is caching everything, landing on a low hit rate, and paying for the memory anyway.

Point the remaining varied reads at the reader endpoint, and add a second reader. This is the connection-string change. It is worth doing for the failover target and the headroom as much as for the last sixth of the read load. Two things need care. Name the reads that must be current, the seat-availability check first, and pin them to the writer: Aurora lag is small, but it widens exactly when an on-sale is running, and a stale seat check reads as a business-logic failure rather than as replication. Second, if those readers are serverless, keep them in promotion tier 0 or 1 so each floor tracks the writer’s current capacity. A reader in tier 2 or above scales independently of the writer, and a floor set too low there can leave it short of the capacity it needs to apply the writer’s changes, which surfaces as replication lag.

Then re-measure, and only then decide about writes. With the reads gone the writer sits well below its ceiling outside on-sales, which turns “the database is too small” into “the database is too small for ninety minutes a fortnight”. Aurora Serverless fits that shape. Set the floor at business-hours capacity rather than near zero, because the scaling rate follows the current capacity and a cold floor gets to the peak slowly, and set the ceiling well above the on-sale peak. Put RDS Proxy in front before converting, so the failover the conversion takes does not drop the application’s connections.

Sharding stays on the shelf. It is the only thing here that raises the write ceiling, and for this cluster it is a rebuild rather than a resize. A Limitless cluster carries no reader instances, cannot feed the zero-ETL integration, and does not work with RDS Proxy, so all three read fixes would need somewhere else to live. The shard key is both the decision that matters most and the one that cannot be changed afterwards.

Why not lead with the cache. It is the advice the team was given and it reaches a third of the reads. The reporting screen is half of them, and repointing one screen is less code than an invalidation path.

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 a long way round to somewhere a reader endpoint reaches in an afternoon.

Worked example

The reporting move lands first and takes business-hours writer CPU from 85% to 59%. It surprises the team. Nobody had attributed a third of the writer to a screen four people use.

The cache goes in next and settles at a 97% hit rate across the eight query patterns, taking another 16 points off and leaving the writer at 43%. Invalidation is wired into the two admin endpoints that change event and venue records. The TTL is five minutes, as a backstop rather than the primary mechanism.

Moving the remaining varied reads to two readers takes business-hours CPU to 34%. During the migration one bug surfaces where expected. The seat-availability check went to the reader in 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. It becomes the example in the team’s runbook for why sending 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 that load is almost entirely writes. Aurora Serverless on the writer, floor at business-hours capacity and ceiling well above the on-sale peak, absorbs the spike without a resize. The monthly bill falls, because the fixed instance was sized for ninety minutes that happen twice a month.

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

What’s worth remembering

1. Caches and readers leave writes untouched. A writer saturated by writes needs sharding, a distributed engine or a queue instead.
2. Read shape picks cache or reader. A small hot set suits a cache, a broad spread a reader; a low hit rate wastes the node.
3. Pin fresh reads to the writer. Aurora replica lag is usually well under 100 milliseconds but widens with the write rate.
4. Move reporting off the writer. Same-Region zero-ETL to Redshift, possibly in the analytics account, removes the load instead of scaling to carry it.
5. Scaling up adds capacity, not ceiling. Vertical scaling and Aurora Serverless leave one writer’s sustained limit alone, which is enough for a ninety-minute peak.
6. Limitless is a separate cluster. It drops reader instances, zero-ETL and RDS Proxy, and a shard key cannot change once rows carry it.