The Database Scaling Ladder: Fix a Slow Database in the Right Order

Indexes, then cache, then read replicas, then a bigger machine, and sharding only at the top.

Say the order page of an online store starts taking a few seconds to load. Someone looks at the Cloud SQL dashboard, sees CPU near the top, and says, "Let's shard." That is usually the most expensive answer to the question.

The problem

A slow database has many possible causes, and each fix costs a different amount of effort and money. Jump to the top fix too early and you pay for a sharding project when a missing index was the real issue. Stay too long at the bottom and you keep tuning queries while a single primary has truly run out of room.

I think of it as a ladder. Climb one rung at a time, cheapest first, and only go up when the current rung stops helping.

The solution: five rungs

Rung 1: indexes and queries

First, confirm the real cause. A query that scans a whole table because a column is not indexed will be slow on any machine size.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
-- if it shows a full table scan:
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

This costs almost nothing and is the first thing I check.

Rung 2: cache

Many reads ask for the same thing again and again: a product's details, a category list. A Memorystore (Redis) cache in front of Cloud SQL answers those repeated reads in memory, and the database only sees misses.

Every key gets a TTL, and when the data changes, the app updates Cloud SQL and then deletes the key. The database stays the source of truth.

Rung 3: read replicas

If reads are still heavy after caching, read replicas take read-only queries off the primary. Writes still go to the primary.

The catch is replication lag: a replica can be slightly behind, so a user who just placed an order might not see it on a replica for a moment. Reads that must be fresh, like "show the order I just placed", should go to the primary.

Rung 4: scale up (vertical scaling)

Now give the same instance a bigger machine. For example, going from 2 vCPU and 8 GB RAM to 8 vCPU and 32 GB RAM. The data, instance name and connection address stay the same. Only the machine type changes.

How I would do it:

• Confirm the cause again. A bad query needs a fix, not a bigger machine.
• Pick an off-peak time and tell the team.
• Take an on-demand backup.
• Edit the instance and choose the new machine type.
• The instance restarts, connections drop briefly, and the app reconnects.
• Watch CPU, memory and latency while the cache warms up again.

A few details matter here. A machine type change needs a restart, so there is brief downtime (HA setups can shorten it; check the current docs for your edition). Growing the disk does not need a restart and can happen automatically. Shrinking a disk has limited support. Read replicas should be resized too, so they keep up with the primary. And a primary does not resize itself: you do it, or you automate it from monitoring.

Rung 5: shard

Stop scaling up when the largest machine type is reached, when cost grows faster than the gain, or when the single primary is still the write bottleneck. Replicas help reads, not writes. That is when sharding makes sense: spread users across several databases so writes are spread too.

Trade-offs and limits

• Measure before climbing. Query insights and slow query logs tell you which rung you are actually on. Guessing is how teams end up sharding an unindexed table.
• Retries are part of scaling up. The restart is only a short blip if the app retries connections. Without retries, it looks like an outage.
• Caching brings staleness. It needs a clear rule for invalidation and a TTL as a safety net. Some reads, like payment amounts or stock at checkout, should never come from cache.
• Replicas bring lag. I would route read-after-write paths to the primary on purpose, not by accident.
• Sharding is a project, not a setting. It needs a routing layer, a migration plan and a new answer for cross-shard reports. That is exactly why it sits at the top.

Takeaway

Check queries and indexes before touching infrastructure. Cache and replicas scale reads; a bigger machine buys time for everything. Shard only when one primary can no longer take the writes.