Most teams shard too early. I genuinely believe this. I’ve watched companies with a few hundred thousand rows in their biggest table start planning a sharding strategy because someone read a blog post about how Discord does it. Discord has billions of messages. You have a Django app with three microservices. Sit down.
At the fintech startup we dealt with real volume. Financial news, market data, user watchlists, story relevance scores – all of it flowing into PostgreSQL around the clock. Tens of millions of rows across our core tables, write-heavy ingestion pipelines running 24/7. We had every reason to think about sharding early. We didn’t. And that restraint saved us months of engineering time.
What sharding actually solves
Sharding splits your dataset across multiple database instances. That’s it. It gives you more write throughput and more total storage capacity by distributing the load.
What it doesn’t solve: slow queries, missing indexes, bad schema design, lack of connection pooling, or the fact that your ORM is generating six joins for a page that should be one SELECT. Sharding won’t fix your application. It will make your application’s problems harder to debug.
At the fintech startup, before we ever discussed sharding, we squeezed everything out of a single Postgres instance. I mean everything:
-- Finding our worst offenders
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
That query alone – run regularly – told us more about our scaling problems than any architecture whiteboard session. Nine times out of ten the fix was an index, a rewritten query, or moving a hot path behind a cache.
When sharding is actually justified
You need sharding when all of these are true simultaneously:
- Your primary is pegged on CPU or disk I/O from writes, not reads
- Read replicas are already handling your read traffic and it’s not enough
- You’ve tuned
work_mem,shared_buffers,effective_cache_size– the obvious Postgres knobs - Vertical scaling has hit a wall or the cost curve has gone exponential
- Your query patterns naturally partition along a single key
If you’re missing even one of those conditions, you’re probably not ready. Go back and fix the basics.
The checklist before you shard
This is the order I’d recommend. We followed it at the fintech startup and it bought us a long time on a single primary.
1. Fix your queries. Use EXPLAIN ANALYZE. Actually read the output. Look for sequential scans on large tables, nested loop joins where hash joins would work, and sorts that spill to disk.
EXPLAIN ANALYZE
SELECT s.id, s.title, r.score
FROM stories s
JOIN relevance_scores r ON r.story_id = s.id
WHERE r.user_id = 42
AND s.published_at > NOW() - INTERVAL '7 days'
ORDER BY r.score DESC
LIMIT 50;
If that’s doing a seq scan on relevance_scores, you don’t have a sharding problem. You have an indexing problem.
2. Add proper indexes. Composite indexes aligned to your query patterns. Partial indexes for hot subsets. Don’t just throw a B-tree on every column.
CREATE INDEX CONCURRENTLY idx_relevance_user_score
ON relevance_scores (user_id, score DESC)
WHERE score > 0.5;
3. Introduce caching. We cached story metadata, user watchlists, and computed relevance scores in Redis. This alone dropped our read load by 60-70%.
4. Connection pooling. PgBouncer. Set it up in transaction mode. PostgreSQL is terrible at handling thousands of connections natively. This is free performance.
5. Archive cold data. We partitioned our historical data by month and moved anything older than six months to separate tables. Queries on recent data got dramatically faster.
6. Read replicas. Route all reporting, analytics, and non-critical reads to replicas. Your primary should only handle writes and the reads that absolutely need consistency.
Only after exhausting all of that should sharding enter the conversation.
Choosing a shard key
This is the decision that will haunt you. Get it wrong and you’re looking at a painful re-sharding down the road. The shard key determines which instance holds which rows.
Good shard keys share these traits: high cardinality, even distribution, stability over time, and presence in nearly every query you run.
For us at the fintech startup, user_id was the obvious candidate for user-facing data. All watchlist queries, relevance scores, and personalization features were scoped to a user. Everything a user needed lived together.
For the ingestion pipeline – stories, sources, raw market data – that was a different story. Literally. We kept that on a single large instance with partitioning rather than sharding because the access patterns didn’t have a natural partition key. Stories get read by many users. There’s no clean split.
Bad shard keys I’ve seen people pick:
created_at– all new writes go to one shard. Congratulations, you’ve created a hotspotcountry– massively uneven. One shard gets 40% of trafficstatus– three distinct values. That’s three shards at most, and one of them has 90% of the data
Sharding strategies in practice
Hash-based
-- Application-level routing
shard_id = hash(user_id) % num_shards
Even distribution. No hotspots on write. But range queries become fan-out queries across all shards, which is expensive. Adding a new shard means rehashing and moving data around. Consistent hashing helps but doesn’t eliminate the problem.
Range-based
shard_1: user_id 1 - 1,000,000
shard_2: user_id 1,000,001 - 2,000,000
Simple to reason about. Range queries stay local. But new users always hit the latest shard, creating a write hotspot. Rebalancing means splitting ranges and moving data.
Directory-based
A lookup table maps each key to a shard. Maximum flexibility – you can move individual users between shards. The cost is an extra lookup on every single query and another system (the directory) that has to be highly available. If the directory goes down, everything goes down.
Cross-shard operations are where it hurts
Here’s what nobody tells you until you’re already committed: the moment you shard, joins become your enemy.
-- This worked fine on a single instance
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.region = 'EU'
GROUP BY u.name;
On a sharded setup with user_id as the shard key, this might still work if users and orders are co-located on the same shard. But what if you need orders joined with products, and products live on a different shard? Now you’re doing application-level joins. Pulling data from multiple shards into your app server and joining in memory. It’s slow and it’s ugly.
Cross-shard transactions are worse. Two-phase commit exists, but it kills throughput and introduces failure modes that will wake you up at 3am. At the fintech startup we avoided this entirely by designing our data model so that everything a single request needs lives on the same shard. Sometimes that meant denormalization. Sometimes it meant copying reference data to every shard. Both are fine tradeoffs.
Routing: who decides which shard?
Three options, each with costs.
Application-level routing – your code computes the shard and talks to the right database. Full control. But now every service that touches the database needs routing logic. We preferred this at the fintech startup because it was explicit and debuggable.
Proxy routing – something like PgBouncer with custom routing, Vitess, or ProxySQL sits between your app and the databases. Simpler application code but you’re adding another piece of infrastructure to monitor, scale, and debug when things go wrong at 2am.
Database-native sharding – Citus for PostgreSQL is the one I’d look at if I were doing this today. It handles routing and distributed queries within Postgres itself. Less custom code. But it has its own performance characteristics and operational model that you need to understand deeply before committing.
The operational reality
Sharding isn’t a one-time project. It’s a permanent increase in operational complexity.
Every shard needs monitoring. Every shard needs backups. Every shard needs failover testing. If you have 8 shards, you have 8 databases to manage, and the failure of any single one affects a subset of your users.
You need to plan for rebalancing before shards fill up. If shard 3 is at 85% capacity and growing 5% per month, you have roughly three months to act. Automating this is hard. Most teams don’t, and then it becomes a fire drill.
You also need runbooks for partial failures. What happens when shard 5 goes down? Do those users see errors? Degraded functionality? Do other shards pick up the load? These are questions you need answered before going to production, not during an incident.
Bottom line
Sharding is a last resort that works. When you genuinely need it – when a single instance can’t keep up and you’ve already optimized, cached, replicated, and partitioned – it’s the right tool. But it permanently changes your operational model, your data model, and how every engineer on the team thinks about the database.
At the fintech startup we pushed a single PostgreSQL instance far further than most people think is possible. Proper indexing, pg_stat_statements, PgBouncer, aggressive caching, table partitioning. That got us to the scale we needed without the overhead of a sharded architecture.
If you’re reading this and considering sharding, run EXPLAIN ANALYZE on your slowest queries first. The answer is probably in there.