Database sharding is how a growing application keeps answering requests after one database server is no longer enough. Orders, events, messages or tenant data are split across several servers, each holding only its share, so storage and write traffic scale sideways instead of demanding a bigger machine every quarter.
Contents
At CodeWiz we design and implement sharding and partitioning for PostgreSQL, MySQL and MongoDB. We treat a full shard cluster as the last step, not the first: indexes, caching, read replicas and native table partitioning solve many “we need to shard” tickets. When a split is genuinely required, we choose the shard key, the routing layer and the cutover so the application stays online and your team knows which queries are allowed to cross a shard boundary.
This service is for multi-tenant SaaS products, marketplaces, high-volume stores and platforms whose write rate or dataset has outgrown a single primary. If the data is still modest, we will say so and fix the cheaper problems first. If one tenant or one nightly report is already bending the server, design the split before an outage forces it.
Horizontal Database Sharding Across Servers
Horizontal sharding places different rows of the same logical table on different database servers. Orders might be spread by tenant_id, messages by user, a ledger by account. Each shard is an ordinary database that can be backed up and restored on its own. The application, or a proxy in front of it, decides which shard answers each query.
You gain write capacity and disk one server at a time. A single primary has one ceiling for commits and one volume for storage; shards raise that ceiling, which is why they show up in SaaS products that add customers every month. The cost is complexity: a query without the shard key may visit every server, and a transaction that used to be one SQL statement may now cross the network.
Range, hash and directory routing
- Range sharding assigns bands of the key (tenant 1–5,000, then 5,001–10,000). Archival is easy, but the newest range often becomes the busiest.
- Hash sharding spreads neighbouring keys across servers, so writes are more even. A range such as “tenants created this week” no longer sits on one shard.
- Directory sharding keeps a lookup of key to shard. Moving a tenant is an update plus a copy, which helps when one customer outgrows a node.
How we build it
On PostgreSQL we usually reach for Citus: tables are distributed on a column, reference tables are copied to every node, and a coordinator plans queries. On MySQL we use Vitess, where a VSchema routes through VTGate and each shard is an ordinary MySQL primary with replicas. When every request already carries tenant_id, a small router in Laravel or Node.js is enough. MongoDB’s own balancer is the right tool only when the data is already document-shaped.
- A written shard map and the rule that places a new key.
- Routing in the application, in Citus or in Vitess, with a connection pool per shard.
- A policy for cross-shard work: which reports may scatter, and which must be redesigned.
- A rehearsed way to add a shard or move a hot tenant without a long lock.
Vertical Partitioning of Tables and Services
Vertical partitioning splits by shape rather than by rows. A wide product table becomes a narrow row the storefront reads on every page, plus a side table for specifications and translations that only the admin screen needs. At a larger scale, the same idea splits one database into separate databases per area of the business: catalog, orders, billing. Each service writes to its own store, and the others hear about changes through an API or events.
This is often the right move before any horizontal shard. A checkout path that reads eighty columns to display five pays for row width and for indexes on columns it never filters. A reporting schema in the same database as live orders competes for I/O and locks. Moving cold columns aside, or moving analytics to a replica, removes that contention without a routing layer.
Where vertical stops being enough
Vertical splits do not add write capacity for a single hot table. If every insert still lands in one orders table on one primary, column splitting will not raise the commit rate. Foreign keys also stop at the database boundary. We replace that guarantee with explicit checks, idempotent writes and a reconciliation job, and we document which rules moved from the database into the application.
Load Distribution Across Shards and Replicas
A shard cluster only helps if work lands where there is spare capacity. We design routing so the common request is shard-local: the shard key is in the WHERE clause, one pool serves that shard, and its replicas absorb reports and list pages. Scatter-gather queries, which fan out to every shard and merge results, stay on admin screens and scheduled jobs, with a timeout and a row limit.
- PgBouncer or ProxySQL in front of each shard, so application instances do not open hundreds of connections per node.
- Read replicas per shard for reporting, exports and back-office search, with lag visible on a dashboard.
- Statement timeouts and per-tenant rate limits, so one noisy client cannot queue the whole shard.
- Separate pools for migrations and batch jobs, so a schema change does not steal checkout connections.
Hot shards and moving tenants
Even a good hash produces a hot shard when one tenant is a large fraction of traffic. We measure writes and storage per key, not only per server. Remedies are a dedicated shard for that tenant, a finer key (tenant plus a bucket) or a directory entry that sends only that key elsewhere. The move itself is a copy, a short catch-up, a brief write freeze for that key and a routing flip, rehearsed on a staging copy first.
Performance Optimization Before and After Sharding
Sharding a slow schema just creates several slow schemas. Performance optimization comes first, in an order that spends the least money and operational attention:
- Fix the queries that dominate total time: missing indexes, N+1 patterns and reports that scan live tables.
- Give the primary enough RAM, fast storage and a connection pooler before buying a second server.
- Add read replicas and send reporting there.
- Cache hot rows in Redis and move product or site search to a search engine.
- Partition the largest tables inside one database, usually by month, so maintenance touches one chunk at a time.
- Shard only when one primary’s write rate or disk still cannot meet the target.
Native partitioning is the step people confuse with sharding. The table stays on one server; the engine stores it as many physical pieces and skips the ones a query cannot touch. It is often enough for another year of growth. Our guide to sharding versus partitioning walks through that choice. The PostgreSQL partitioning documentation covers the syntax we use:
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_10 PARTITION OF events
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE INDEX ON events_2026_10 (tenant_id, created_at DESC);
SELECT id, payload FROM events
WHERE tenant_id = 4821
AND created_at >= '2026-10-01'
AND created_at < '2026-11-01'
ORDER BY created_at DESC
LIMIT 50;After a shard deployment, tuning continues per node: local indexes, autovacuum that keeps up, and plans checked on each shard. Joins of two large distributed tables on a column other than the shard key are the pattern we remove first.
Choosing a Shard Key You Can Live With
The shard key is the decision you will live with longest. A good key has high cardinality, appears in almost every latency-sensitive query, spreads writes evenly and almost never changes. Changing it later means moving rows between servers. For most SaaS products the tenant or account id is the natural key, because requests are already scoped to one customer.
- Poor keys:
created_atalone (every insert hits the latest shard), a status column with a handful of values, or a random UUID that no query filters on. - Better keys:
tenant_id,account_id, or a compound such as tenant plus a bucket when a single tenant is huge. - Uniqueness: a unique email or order number is unique per shard unless you enforce it in a directory or a dedicated service. We call that out before launch, not after the second duplicate.
- Sequences: database-wide autoincrement ids will collide or couple the shards. We use per-shard sequences with an offset, UUIDv7, or an application-generated id.
Sharding and Partitioning Tools Compared
We pick the tool from the engine you already run and from who will operate the cluster after handover.
| Approach | Engine | Weight | Best fit | Watch out for |
|---|---|---|---|---|
| Native partitioning | PostgreSQL, MySQL | Low | Time-series and huge tables on one server | Does not add write capacity of extra servers |
| Citus | PostgreSQL | Medium | Multi-tenant SaaS that wants distributed SQL | Cross-shard joins and transactions need design |
| Vitess | MySQL | Medium to high | Large MySQL estates that must reshard online | VTGate, tablets and the VSchema to operate |
| Application routing | Any SQL database | Low to medium | Strict tenant isolation, a key already on every request | You own routing, migrations and cross-tenant reports |
| MongoDB sharding | MongoDB | Medium | Document data already stored in MongoDB | Shard key is painful to change; watch jumbo chunks |
Reference data that every shard needs, such as plans and feature flags, is copied to each node or served by a small shared lookup. We decide that explicitly so checkout never crosses the network for a three-row table. Where each shard runs follows the same discipline as the rest of the platform; we have written about deployment across DigitalOcean, AWS, Hetzner and bare metal.
Common Database Sharding Mistakes We Fix
- Sharding a database whose slow queries were missing an index. The fan-out makes them slower, not faster.
- A shard key that is not in the hot queries, so every page view touches every server.
- Cross-shard joins and multi-shard transactions on the checkout or login path.
- No plan for the tenant that produces 40% of writes. The hash looks fair until that customer arrives.
- Assuming a global unique constraint still exists. It does not, unless you built one.
- Resharding left as a slide in the design doc, never scripted and never timed on real data volumes.
- Backups that capture shards at different moments, so a restore cannot rebuild one consistent picture. Consistent recovery matters just as much here as in zero data loss e-commerce.
What Drives the Cost of a Sharding Project
We estimate after a short review of schema, query load and growth. The drivers are predictable:
- Whether partitioning, replicas or caching will do the job instead. That is a smaller engagement, and we will recommend it.
- The number of tables that must be distributed, and how many queries lack the shard key.
- The tool: an application router is less work than Citus, which is less work than Vitess operated by your team.
- How much historical data must move, and whether the cutover has to be zero-downtime.
- Resharding, per-tenant moves, and the observability you want on day one.
- One-off design and migration versus us running the cluster afterwards.
How We Work
- Audit: we measure table sizes, query shapes, write rate, the hottest keys and how the application opens connections.
- Plan: we say whether to stop at indexes, replicas or partitioning, or to shard, and we agree the key, the tool and the rollout.
- Build: we set up routing or the extension, the schema on each shard, pooling, replicas and the jobs that move existing rows.
- Test: we replay production-like traffic, time a tenant move, and check backups and a restore that covers every shard.
- Launch: we cut over in stages, often tenant by tenant or table by table, and watch lag, errors and latency.
- Support: we leave a runbook for adding a shard and can keep tuning plans and capacity as the data grows.
Why CodeWiz
- Application and database together: we build the Laravel, Node.js and WordPress systems that issue the queries, so routing and schema change as one design.
- Sharding last, not first: if a bigger disk, a replica or a monthly partition is enough, that is what we propose.
- Open-source first: PostgreSQL, Citus, Vitess and MongoDB keep the data in engines you can run without a licence surprise.
- Fair pricing: premium development without premium prices, from Banja Luka, working remotely with clients locally and internationally.
- The rest of the stack in the same hands: caching, hosting, Cloudflare and backups sit next to the data layer. Read more about CodeWiz.
Frequently Asked Questions
How much does database sharding cost?
It depends on how many tables move, which tool we use and whether history has to migrate without downtime. A single-table split behind an application router is a focused project. A Citus or Vitess rollout with resharding and per-tenant moves is a larger one. You get a written estimate after the audit, before we start.
Do I need sharding, or a bigger server?
Most applications we review need better indexes, a connection pooler, a read replica or partitioning long before shards. Sharding pays off when one primary’s write rate or disk still misses the target after those steps, or when tenants must sit on separate hardware. We will tell you which case you are in.
What is the difference between sharding and partitioning?
Partitioning splits one table into pieces that still live in one database, so the planner can skip irrelevant months. Sharding splits rows across servers so writes and storage scale out. Partitioning is simpler. Sharding is what you add when one server’s write ceiling is the actual limit.
Can you shard a live database without downtime?
Yes, when the copy is planned. Writes go to both locations during a catch-up window, or we move one tenant at a time while everyone else stays on the original primary. An unrehearsed dump-and-restore is how orders get lost.
Will my ORM and existing queries keep working?
Queries that already filter by the shard key usually need only a routing change. Joins across tenants, a global autoincrement or a unique constraint across all rows need a redesign. We list those queries in the audit.
Citus, Vitess or application-level routing?
Stay on the engine you have. Citus if you are on PostgreSQL and want SQL that can span shards. Vitess if you are on MySQL and need online resharding at large scale. Application routing if every request is already scoped to one tenant and you want the least new infrastructure.
Ready to Scale the Database Without a Rewrite?
Tell us how large the data is, which queries hurt and how fast you expect to grow. We will recommend partitioning, replicas or a shard design that matches the product. Contact us to talk it through, or see the full scope of our database sharding and partitioning services.