When NOT to Shard
Read replicas, partitioning, and bigger boxes solve most scale problems — know when sharding is the wrong answer.
When NOT to Shard is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Sharding Is the Last Resort
Sharding multiplies operational complexity. Most apps never need it. Exhaust simpler options first.
Step 1: Vertical Scaling
Bigger box. Modern cloud instances easily handle:
- 128 cores
- 1 TB RAM
- 50,000 IOPS NVMe
That's 100k-500k QPS on a single Postgres node. Most apps fit comfortably.
Step 2: Read Replicas
If reads dominate, add replicas. One primary + 3 replicas can serve 10× the reads.
Step 3: Caching
Redis/memcached in front of hot queries. Often the cheapest win.
Step 4: Partitioning
Native PG declarative partitioning addresses the "table too big" problem within a single server. Often 100x easier than sharding.
Step 5: Splitting Services
Move different domains to different databases — orders DB, users DB, analytics DB. Each can scale independently.
Step 6: Move Analytics Off
OLTP queries → Postgres. Analytical queries → ClickHouse / BigQuery / Snowflake. Many "we need to shard" stories are actually "analytics is eating our OLTP".
Then, Maybe, Sharding
If you're still running out at 50TB of OLTP-only data and the workload demands writes one server can't handle — start planning shards.
Operational Cost of Sharding
- More servers to monitor and patch
- Backups across shards must be coordinated
- Cross-shard queries hit the app code
- Re-sharding is hard
- Hot shards demand active rebalancing
The "Shard Late" Strategy
Build with sharding-aware patterns (always include tenant_id, never use globally-incrementing counters) so you CAN shard later. But don't shard until forced.
Sharding-Friendly Schema
Even if you stay single-node, design as if you might shard:
- Tenant_id on every row
- UUID or distributed IDs (not auto-increment)
- No globally-unique sequences
- Foreign keys within a tenant's scope
Acknowledge the Tradeoff
Sharding gains capacity at the cost of capability. Joins, transactions, queries get harder. Be sure the gain is worth it.
Recap
Sharding solves a real problem — but it's a heavy one.
- Vertical scale first
- Read replicas + caching
- Partitioning before sharding
- Move analytics off OLTP
- Design for shardability, postpone the actual shard
Quick Check
You're considering sharding because OLTP queries are slow. Before sharding, what step is most likely to help?
Frequently asked questions
Is the “When NOT to Shard” lesson free?
Yes — the full text of “When NOT to Shard” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “When NOT to Shard”?
Read replicas, partitioning, and bigger boxes solve most scale problems — know when sharding is the wrong answer. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “When NOT to Shard” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.