Database Sharding vs Partitioning: When and How to Scale

Q: What is the difference between sharding and partitioning?

Partitioning divides one large table into smaller physical pieces inside a single database: same machine, same query planner, and completely invisible to the application — you query the parent table and the planner routes to the right partitions. Sharding divides the dataset across multiple independent databases on separate machines, and something above them (a proxy like Vitess, an extension like Citus, or application code) must know which shard owns which row. The consequences differ as much as the mechanics: partitioning keeps every database guarantee intact — joins, transactions, unique constraints all still work — and buys you partition pruning, instant retention via DROP PARTITION, and smaller maintenance units. Sharding sacrifices cross-shard joins, cross-shard transactions, and global unique constraints in exchange for the two things one machine cannot give you: horizontal write scaling and a total dataset larger than any single box. The practical rule: partitioning is a schema decision you make at 100 GB; sharding is an architecture decision you make when the biggest machine you can buy is no longer enough.

Q: When should I shard my database?

When a single machine is measurably exhausted on one of three axes — and not before. First, sustained write throughput: a well-tuned Postgres or MySQL primary on serious hardware absorbs tens of thousands of writes per second; if your p95 write volume exceeds what vertical scaling can absorb, sharding is the answer because replicas only scale reads. Second, working set: when the hot data no longer fits in the RAM of the largest instance available, every query becomes disk-bound and latency degrades non-linearly. Third, operational mass: a multi-terabyte single database turns backups, failover, vacuum, and index rebuilds into multi-hour hazards regardless of query performance. Before any of that, exhaust the cheaper ladder: fix queries and indexes (the majority of "we must shard" incidents die here), add caching, add read replicas, partition the biggest tables, and offload jobs, events, and analytics to queues, streams, and warehouses. Each rung is roughly ten times cheaper than the next. Shard when you can state the number that proves the previous rungs are spent — sustained writes per second, working-set size versus maximum RAM, or backup and failover windows breaching your recovery objectives.

Q: How do I choose a shard key?

The shard key is the one sharding decision you cannot cheaply revisit, and it must satisfy three requirements simultaneously. Distribution: high cardinality with even spread, so writes land uniformly across shards — auto-increment IDs fail immediately because sequential values route every insert to the newest range, and low-cardinality keys like country fail because the fleet inherits your traffic skew. Access: your hottest queries must include the key, so they route to exactly one shard; any query lacking it becomes scatter-gather across every shard, which is slower than the single database you left. Stability: the value must never change for a row, because changing it means physically moving the row between shards. For most SaaS workloads a hash of user ID or account ID serves all three. The dangerous seduction is tenant ID: it looks natural and co-locates each tenant beautifully, right up until one enterprise customer generates 100 times the median load and becomes a hot shard no rebalancing can fix — whale tenants need explicit splitting or their own dedicated shards. Validate the candidate key against production query logs and real cardinality and skew measurements, not intuition: the query mix you have, not the one you remember, decides whether the key routes or scatters.

Q: What is partition pruning and how much does it help?

Partition pruning is the query planner skipping partitions that cannot contain matching rows, based on the partition key in your WHERE clause. On a 2 TB events table partitioned by month, a query filtered to the last 7 days scans one partition instead of 24 — routinely the difference between 40 milliseconds and several seconds, achieved without any application change. The same mechanism accelerates maintenance: indexes are per-partition, so rebuilds touch one month of data; vacuum operates on smaller objects and finishes; and retention becomes DROP PARTITION, which completes in roughly 10 milliseconds and reclaims disk immediately, versus a DELETE of 500 million rows that runs for hours and bloats the table. The critical discipline: pruning only fires when queries actually filter on the partition key. Partition by created_at and then query by user_id without a date range, and the planner scans every partition — you have added complexity and per-query overhead for nothing. Before partitioning, audit the hot queries: if they cannot be written to include the intended partition key, either pick a different key or accept that partitioning solves your maintenance problem (retention, vacuum) rather than your query-latency problem.

Q: Can PostgreSQL be sharded?

Yes, at three levels of ambition. Native declarative partitioning is not sharding — it stays on one machine — but it is the right first move and covers manageability up to a surprisingly large scale. True sharding on Postgres today usually means Citus, the extension (now open source and the engine behind Azure Cosmos DB for PostgreSQL) that distributes tables across worker nodes by a distribution column, routes single-shard queries at low latency, parallelizes cross-shard analytics, and handles rebalancing — you keep SQL, transactions within a shard, and most of the Postgres ecosystem. The lighter alternative, foreign data wrappers with postgres_fdw plus manual partitioning, works for read-mostly federation but leaves routing, resharding, and consistency entirely to you, which is a hand-rolled router with extra steps. The honest comparison point outside Postgres is Vitess for MySQL, which pioneered the pattern of logical shards managed by a proxy layer at YouTube scale. For most teams the sequence is: exhaust one Postgres box (indexes, replicas, partitioning), then evaluate Citus when write volume or working set genuinely exceeds vertical limits — and treat a move to a NewSQL system as a separate, bigger decision about consistency semantics, not just a scaling step.

Q: How does resharding work without downtime?

The mechanics that make live resharding survivable are decided on day one, before any resharding is needed: hash rows to a large fixed number of logical shards (256, 512, 4096) and map logical shards onto physical machines, many-to-one. Growth then means moving whole logical shards to new machines — copy the shard while streaming its changes (CDC or replication), cut over reads, cut over writes, update the routing map — rather than re-splitting data, which would rewrite every row placement. Systems that hash directly to physical machines face the brutal version: adding a machine changes the modulus, remapping nearly every row; consistent hashing softens this but still moves data proportional to the change. Mature tooling packages the safe sequence — Vitess resharding workflows and Citus shard rebalancing both do copy, catch-up via change streams, verify, and atomic cutover with a rollback window, the same expand-and-contract discipline as any zero-downtime migration. The planning failure to avoid: choosing shard count equal to current machine count with no logical layer. That converts your first capacity event into a re-split crisis under production load, which is precisely when you have the least appetite for a weeks-long migration project.

Q: What is the difference between horizontal and vertical partitioning?

Horizontal partitioning splits a table by rows: each piece has the same columns but holds a subset of rows — January events in one partition, February in another, or user IDs hashed across pieces. Everything commonly called partitioning or sharding is horizontal: table partitioning is horizontal splitting inside one database, sharding is horizontal splitting across databases. Vertical partitioning splits by columns: one table holding hot, narrow columns (id, status, balance) and a companion table holding wide or rarely-read ones (JSON blobs, long text, images metadata), joined by primary key when needed. Vertical splitting earns its keep when a frequently scanned table carries wide columns that inflate every page read — moving a 4 KB description column out of a table scanned thousands of times per second shrinks the working set dramatically and lets far more rows fit per page and per unit of cache. Its architectural big sibling is splitting by domain: moving orders and inventory into separate services with separate databases is vertical partitioning at system scale, which is where the database-per-service pattern and CDC-based integration take over. Most scaling journeys use both: vertical splits to keep the hot path narrow, horizontal splits when row volume itself is the problem.

Q: Do DynamoDB and MongoDB shard automatically?

They automate the mechanics, not the design — and the design is where sharding succeeds or fails. DynamoDB partitions data by the partition key you declare, splits partitions as they grow, and spreads them across its fleet invisibly; but throughput is bounded per partition (roughly 1,000 write units and 3,000 read units per second), so a skewed key — one hot tenant, one celebrity user, a date key during a spike — throttles exactly as a hot shard would in a hand-built system, and adaptive capacity only softens the penalty. MongoDB automates chunk splitting and balancing across shards once you choose a shard key per collection, and the documentation is blunt that a poor key (low cardinality, monotonically increasing, mismatched to query patterns) yields jumbo chunks, hot shards, and scatter-gather queries that no balancer can rescue; hashed keys fix write distribution while sacrificing efficient range queries. The lesson transfers unchanged from self-managed sharding: managed sharding removes the router, the rebalancer, and the ops burden — genuinely valuable — but the three shard-key requirements (distribution, access, stability) remain entirely your problem, and getting them wrong in a managed system is arguably worse because the fixes (key redesign, table migration) are just as disruptive with less visibility.