An application with ten million users has a database bottleneck. In what order do you intervene, and why is sharding last?

What is being tested

Whether you exhaust cheap reversible interventions before expensive irreversible ones, and whether you can justify the ordering rather than reciting it.

The order

1. Fix the queries. Query plans, missing indexes, N+1 patterns in application code, SELECT * over wide rows, deep offset pagination, COUNT(*) for UI purposes. The majority of database problems are resolved here, the fix takes hours, and it is completely reversible.

Rank queries by total time — frequency times duration — not by worst case. A 20 ms query run 400 times per request dominates a 2-second report run hourly, and it never appears in the slow query log.

2. Fix connection handling. A pooler between application and database. Without one, many instances each holding a pool exhausts the connection limit, and connection setup dominates the actual work. Configuration change, immediate effect.

3. Cache. Move repeated reads out of the database entirely, with an explicit staleness decision per data type. Days of work, large effect, and reversible.

4. Read replicas. Move read load off the primary. Accept replication lag and handle read-after-write for the acting user by routing their reads to the primary briefly after they write. Moderate effort, introduces a real consistency consideration.

5. Functional separation. Move one heavy domain — search, analytics, audit — to its own instance. Significantly cheaper and more reversible than sharding, and frequently sufficient. This step is skipped far too often.

6. Shard.

Why sharding is last

Because it is the only step that changes the application's model of the world, and the change is effectively permanent:

• No joins across shards. Queries that do not include the shard key must fan out and merge.
• No cross-shard transactions. Business operations that were atomic become sagas with compensating actions — new code, new failure modes, new correctness risk.
• N schema migrations, which will be in different states, so every migration must be compatible with both.
• No cross-shard uniqueness without a separate mechanism.
• The shard key cannot practically be changed. Choosing it wrongly — usually because the access patterns were not yet understood — is a fault you live with.
• Operational complexity multiplies: backups, monitoring, rebalancing, and a hot shard problem that adding shards does not fix.

Every step before it is a configuration change or an additive component. Sharding is a one-way door through which the application's data model must fit forever.

The specific things to check while diagnosing

Long transactions holding locks (especially one held across a call to a third party), inconsistent lock ordering producing deadlocks, stale statistics after a bulk load, an index that was unnecessary until the table grew, maintenance or vacuum falling behind under write load, and a reporting query running on the primary during business hours.

What a strong answer adds

That the plan must be re-derived after each step, because relieving one bottleneck moves it. And that at ten million users, steps 1 to 5 are very likely sufficient — the number of systems that genuinely need sharding is far smaller than the number that adopt it.