Scaling: Replication, Partitioning & Distributed SQL

One PostgreSQL server can be durable, concurrent, and fast for a long time. Scaling starts when one server, one table, or one location is no longer enough — and each escape hatch solves a different problem with a different bill.

This lesson separates three ideas people blur together: replication makes copies, partitioning splits one logical table into physical pieces, and sharding/distributed SQL splits data across machines. Only partitioning is live in this browser; the rest needs multiple servers and networks, so we will keep those parts structural and honest.

Replication: copies for reads and availability

Streaming replication ships WAL records from a primary server to one or more replicas. The primary accepts writes. Replicas replay the WAL stream and can often serve read-only queries.

That gives you two common benefits:

• Read scaling — send dashboards, reports, or read-heavy endpoints to replicas so the primary has less read work.
• Availability — if the primary dies, a replica can be promoted to become the new primary.

The cost is replica lag. A replica can be behind the primary because it has not received or replayed every WAL record yet. That means a user might write on the primary, then immediately read from a replica and not see their own write. Good systems route "read your own write" paths back to the primary, or design the product to tolerate slightly stale reads.

Replication is not sharding. Every replica has a copy of the same data. It can help read load and failover, but it does not make one huge write workload disappear by spreading writes across many primaries.

Failover and split brain

Failover is the act of moving primary duties to another server. It sounds simple: promote a replica, point traffic at it, move on. The hard part is proving that only one primary exists.

Split brain happens when two servers both believe they are primary and both accept writes. Now you no longer have one ordered history. You have two histories that may conflict: the same order updated twice, two accounts spending the same balance, two rows claiming the same global identity.

Production failover systems need fencing, leases, or consensus-backed election so the old primary cannot keep accepting writes after the new primary is promoted. The operational rule is blunt: a little downtime is usually better than two writable primaries for the same data.

Partitioning: one table, many pieces

Table partitioning keeps one logical table name while storing rows in child tables according to a rule. PostgreSQL supports range, list, and hash partitioning:

• Range — dates, ids, or numbers split into intervals.
• List — explicit values such as regions or tenants.
• Hash — values distributed by a hash function when no natural range/list boundary fits.

Here is a range-partitioned orders table with one partition per month:

CREATE TABLE scale_orders (
  order_id int GENERATED ALWAYS AS IDENTITY,
  placed_on date NOT NULL,
  region text NOT NULL,
  amount numeric(8,2) NOT NULL,
  PRIMARY KEY (order_id, placed_on)
) PARTITION BY RANGE (placed_on);
 
CREATE TABLE scale_orders_2026_01 PARTITION OF scale_orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
 
CREATE TABLE scale_orders_2026_02 PARTITION OF scale_orders
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
 
CREATE TABLE scale_orders_2026_03 PARTITION OF scale_orders
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
 
INSERT INTO scale_orders (placed_on, region, amount)
SELECT DATE '2026-01-01' + (g - 1),
       CASE
         WHEN g % 3 = 0 THEN 'North'
         WHEN g % 3 = 1 THEN 'South'
         ELSE 'West'
       END,
       (18 + (g % 11) * 3)::numeric(8,2)
FROM generate_series(1, 90) AS g;
 
SELECT tableoid::regclass::text AS partition_name,
       count(*) AS orders
FROM scale_orders
GROUP BY tableoid::regclass::text
ORDER BY partition_name;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 90
    partition_name    | orders
----------------------+--------
 scale_orders_2026_01 |     31
 scale_orders_2026_02 |     28
 scale_orders_2026_03 |     31
(3 rows)

The parent table is the name you query. The child tables are where the rows live. tableoid::regclass exposes which physical child each row landed in: 31 January orders, 28 February orders, and 31 March orders.

One schema detail is load-bearing: the primary key is (order_id, placed_on), not order_id alone. PostgreSQL requires every unique constraint on a partitioned table to include the partition key, because each partition enforces uniqueness only within itself.

Partition pruning: skip irrelevant children

The planner can use the partition rule before execution. A February-only predicate does not need to scan January or March:

EXPLAIN (COSTS OFF)
SELECT order_id, placed_on, region, amount
FROM scale_orders
WHERE placed_on >= DATE '2026-02-01'
  AND placed_on < DATE '2026-03-01'
ORDER BY placed_on, order_id;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Sort
   Sort Key: scale_orders.placed_on, scale_orders.order_id
   ->  Seq Scan on scale_orders_2026_02 scale_orders
         Filter: ((placed_on >= '2026-02-01'::date) AND (placed_on < '2026-03-01'::date))
(4 rows)

That is partition pruning: PostgreSQL removed partitions that cannot contain matching rows. The plan still sorts the surviving rows because the query asked for a deterministic order, but the scan touches only scale_orders_2026_02.

Now ask a question with no placed_on boundary:

EXPLAIN (COSTS OFF)
SELECT order_id, placed_on, region, amount
FROM scale_orders
WHERE amount >= 45
ORDER BY placed_on, order_id;

                         QUERY PLAN
-------------------------------------------------------------
 Sort
   Sort Key: scale_orders.placed_on, scale_orders.order_id
   ->  Append
         ->  Seq Scan on scale_orders_2026_01 scale_orders_1
               Filter: (amount >= '45'::numeric)
         ->  Seq Scan on scale_orders_2026_02 scale_orders_2
               Filter: (amount >= '45'::numeric)
         ->  Seq Scan on scale_orders_2026_03 scale_orders_3
               Filter: (amount >= '45'::numeric)
(9 rows)

Append means PostgreSQL scans the relevant child plans and appends their rows. Since amount does not reveal which date range might match, all three partitions remain relevant.

Partitioning is not "make queries fast" dust. It helps when common filters line up with the partition key, when maintenance can happen partition-by-partition, or when old data can be detached cleanly. Pick a key that matches access patterns and lifecycle, not just a column that looks tidy.

Sharding and distributed SQL

Sharding splits data across machines. Instead of every server holding every order, shard A might hold some customers, shard B another group, shard C another. That can scale writes and storage because different machines own different data.

The bill arrives when a query crosses shard boundaries. A single-shard lookup can be clean. A cross-shard join has to move data or ask many machines. A transaction that updates rows on several shards needs coordination, and coordination means waiting, retrying, and deciding what to do when one participant disappears. Global uniqueness, foreign keys across shards, rebalancing hot tenants, and backups all get harder.

Distributed SQL systems try to keep the relational model while automating sharding, replication, and consensus. That is powerful, but it does not erase physics. If a write must be agreed by nodes in different places, the speed of light and the network are in the query path.

CAP without folklore

The CAP theorem is often repeated badly as "pick any two." The precise shape is narrower: when a network partition happens, a distributed system cannot provide both strict consistency and total availability for the partitioned data. A CP design refuses or delays some requests so it never accepts conflicting histories. An AP design accepts more requests during the partition and reconciles conflicts later. If there is no partition, CAP is not the bottleneck you are feeling.

Consensus is how CP systems avoid split brain. Protocol families such as Raft or Paxos make a group agree on one ordered log, usually by requiring a majority quorum. A minority side may be alive but unable to commit writes, because accepting writes there would create the two-primary story failover systems are designed to prevent.

You have now seen the storage layer, the planner, crash recovery, MVCC, and the scaling vocabulary around a database. Section 23 turns from internals to craft: how to write, review, test, and maintain SQL that other people can trust.