Database Sharding vs Partitioning vs Replication: A Decision Guide

Three terms, three different problems

Partitioning, in PostgreSQL's words, splits what is logically one large table into smaller physical pieces, by range, list or hash, inside one database server. Sharding splits data across independent servers, each owning a subset of rows; MongoDB defines it as distributing data across multiple machines, with a router directing each query. Replication keeps copies of the same data on more servers; PostgreSQL standbys that apply the primary's write-ahead log can answer read-only queries when hot standby is enabled.

Map each term to its problem. Partitioning helps maintenance and scans: dropping an old month replaces a slow bulk DELETE, and queries skip irrelevant partitions, but it adds no CPU, memory or disk beyond one machine. Sharding adds write and storage capacity because each server handles only its subset. Replication adds read capacity and failover but not write capacity, since every replica applies every write. Splitting a table's columns, sometimes called vertical partitioning, is a separate technique again.

• PostgreSQL 17 documentation: Table Partitioning
• MongoDB documentation: Sharding
• PostgreSQL 17 documentation: Comparison of Different Replication Solutions

Run PostgreSQL partitioning and watch pruning

This script ran on a throwaway PostgreSQL 17.9 cluster. It creates an events table partitioned by month on created_at, inserts one row per minute for 90 days, and asks for three plans. The per-partition counts it printed were 43,200 for September, 44,640 for October and 41,760 for November, matching 30, 31 and 29 days of minutes. EXPLAIN uses COSTS OFF so the plans stay stable. The SET timezone line matters: in a UTC+5 session the same date literals mean 19:00 UTC on the previous day, and the October query scanned both September and October.

The October query planned a single Seq Scan on events_2026_10: the planner proved the other partitions could not contain matching rows. The user_id query planned an Append over all three partitions, because user_id is not the partition key. The prepared statement, forced onto a generic plan, printed an Append with Subplans Removed: 2 and only events_2026_10 left, which is pruning at executor startup. Two rules follow from the docs: unique keys must include the partition key, and detaching a partition beats a bulk delete.

SET timezone = 'UTC';
CREATE TABLE events (
  user_id    bigint      NOT NULL,
  created_at timestamptz NOT NULL,
  kind       text        NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- one event per minute for 90 days, spread over 1,000 users
INSERT INTO events
SELECT g % 1000, timestamptz '2026-09-01' + g * interval '1 minute', 'view'
FROM generate_series(0, 129599) AS g;
ANALYZE events;
SELECT tableoid::regclass AS partition, count(*) FROM events GROUP BY 1 ORDER BY 1;

EXPLAIN (COSTS OFF) SELECT count(*) FROM events
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE user_id = 42;

-- a generic plan cannot prune at planning time, so it prunes at executor startup
SET plan_cache_mode = force_generic_plan;
PREPARE by_month(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE created_at >= $1 AND created_at < $2;
EXPLAIN (COSTS OFF) EXECUTE by_month('2026-10-01', '2026-11-01');

• PostgreSQL 17 documentation: Table Partitioning

Choose the shard key from the access pattern

Hash sharding spreads keys evenly and keeps equality lookups targeted, but MongoDB's documentation notes that neighboring key values land on different shards, so range queries are likely to be broadcast. Range sharding keeps neighbors together for range scans, but a monotonically increasing key such as a timestamp sends most inserts to the single shard holding the top range. Directory sharding stores a key-to-shard map, which lets you move one tenant at a time, at the cost of a lookup service you must cache and keep available.

Work an example. A multi-tenant SaaS stores orders, and nearly every query filters by tenant. Sharding on tenant_id keeps each tenant's queries and transactions on one shard. The risk is one very large tenant becoming a hot shard; a directory can place it on dedicated hardware. Poor candidates for this workload are created_at alone, which concentrates inserts, and country, which has low cardinality and heavy skew. Judge any key by cardinality, skew, monotonicity, and how many queries include it.

• MongoDB documentation: Hashed Sharding

Even data is not even traffic

A shard can hold a fair share of rows and still receive most of the requests. DynamoDB makes the limit concrete: as of November 2026, AWS documents that each partition is designed to deliver at most 3,000 read units and 1,000 write units per second. Traffic concentrated on one item, or on keys that share a partition, therefore hits that ceiling regardless of the table's total capacity, which is why AWS advises designing for uniform activity across partition keys.

Mitigations depend on whether the heat comes from reads or writes. Hot reads can be absorbed by a cache in front of the shard. Hot writes can be split by appending a suffix, such as key#0 through key#9, and merging on read; AWS calls this write sharding. Very large tenants can be isolated through a directory. Monitor requests and size per shard rather than cluster averages, because an average hides the one shard that is saturated.

• Amazon DynamoDB: Best practices for designing partition keys
• Amazon DynamoDB: Using write sharding to distribute workloads evenly

Cross-shard queries, transactions, and resharding

A query without the shard key must ask every shard. MongoDB calls these broadcast or scatter-gather operations and warns they can run long, since the answer waits for the slowest shard. Two common fixes are a lookup table, such as email to user_id, that turns one broad query into two targeted ones, and co-locating related rows under the same key so that joins stay on one shard.

Writes that span shards need two-phase commit or a saga with compensating steps. PostgreSQL's PREPARE TRANSACTION exists for an external transaction manager, and its documentation advises keeping max_prepared_transactions at zero unless you run one. Resharding is the other long-term cost: hash mod N moves most rows when N changes, so plan for many logical buckets mapped onto fewer servers, or use consistent hashing. MongoDB has supported changing a collection's shard key since version 5.0.

• PostgreSQL 17 documentation: PREPARE TRANSACTION
• Consistent hashing explained: ring and virtual nodes

A decision rule for the interview

Ask what the bottleneck is before choosing. If reads saturate the primary, add replicas and say how stale a replica read may be. If old rows make deletes and vacuuming slow, partition by time. If the write rate or dataset exceeds what one primary can handle after indexing and tuning, shard. PostgreSQL's documentation gives a rule of thumb for partitioning: it usually pays off only when the table would otherwise exceed the server's physical memory.

The common mistakes are calling replicas shards, sharding before exhausting a single well-indexed node, choosing a shard key from the data model rather than the query mix, and forgetting that asynchronous replicas can return stale data. Real deployments often combine all three techniques: MongoDB requires each shard to be deployed as a replica set, and a PostgreSQL shard can still partition its own large tables by time.

Practice: explain it on one diagram

Draw the three columns from the figure and narrate one write and one read through each. Then rerun the SQL, add a WHERE clause on a column that is not the partition key, and predict the plan before running EXPLAIN. If an interviewer asks you to shard the events table, choose a key, name one query that becomes scatter-gather, and explain how you would reshard later.

Keep the symptom table as a short list of decision rules; if you can rebuild it from memory, the follow-up questions become much easier. Practice saying the three definitions in one breath each, without the word scale doing all the work. The PostgreSQL articles below go deeper into reading plans and the internals behind partitioning and replication, and the question bank covers what interviewers usually ask next.

• PostgreSQL EXPLAIN ANALYZE: read plans and choose indexes
• PostgreSQL internals and optimization interview questions
• 52 system design interview questions and answers
• Practice the backend interview track

Quick answers

Frequently asked questions