Partitioning and Sharding

Partitioning splits one logical table into smaller physical tables (partitions) on the same server. Sharding splits data across multiple servers. Both use a key to decide where a row lives. See also sharding and partitioning.

Example 1: range partitioning

Range partitions suit time-series data. Each partition covers a half-open range: FROM is inclusive, TO is exclusive.

CREATE TABLE events (
    id      bigint NOT NULL,
    created date   NOT NULL,
    kind    text   NOT NULL
) PARTITION BY RANGE (created);

CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

INSERT INTO events
SELECT g, date '2026-01-01' + (g % 90), CASE WHEN g % 3 = 0 THEN 'click' ELSE 'view' END
FROM generate_series(1, 9000) g;

SELECT tableoid::regclass AS partition, count(*)
FROM events
GROUP BY 1
ORDER BY 1;

   partition    | count 
----------------+-------
 events_2026_01 |  3100
 events_2026_02 |  2800
 events_2026_03 |  3100
(3 rows)

Example 2: a row with no partition

An insert outside every range fails, unless a DEFAULT partition exists.

INSERT INTO events VALUES (99999, '2026-06-15', 'view');

ERROR:  no partition of relation "events" found for row
DETAIL:  Partition key of the failing row contains (created) = (2026-06-15).

CREATE TABLE events_default PARTITION OF events DEFAULT;
INSERT INTO events VALUES (99999, '2026-06-15', 'view');
SELECT tableoid::regclass AS partition, count(*) FROM events GROUP BY 1 ORDER BY 1;

   partition    | count 
----------------+-------
 events_2026_01 |  3100
 events_2026_02 |  2800
 events_2026_03 |  3100
 events_default |     1
(4 rows)

Example 3: partition pruning

The planner removes partitions that cannot match the WHERE condition. Only the matching partition appears in the plan.

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created = '2026-02-10';

                QUERY PLAN                
------------------------------------------
 Seq Scan on events_2026_02 events
   Filter: (created = '2026-02-10'::date)
(2 rows)

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created >= '2026-02-15' AND created < '2026-03-05';

                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Append
   ->  Seq Scan on events_2026_02 events_1
         Filter: ((created >= '2026-02-15'::date) AND (created < '2026-03-05'::date))
   ->  Seq Scan on events_2026_03 events_2
         Filter: ((created >= '2026-02-15'::date) AND (created < '2026-03-05'::date))
(5 rows)

A filter that does not use the partition key scans every partition.

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE kind = 'click';

                QUERY PLAN                 
-------------------------------------------
 Append
   ->  Seq Scan on events_2026_01 events_1
         Filter: (kind = 'click'::text)
   ->  Seq Scan on events_2026_02 events_2
         Filter: (kind = 'click'::text)
   ->  Seq Scan on events_2026_03 events_3
         Filter: (kind = 'click'::text)
   ->  Seq Scan on events_default events_4
         Filter: (kind = 'click'::text)
(9 rows)

Example 4: pruning breaks when the key is wrapped in a function

Applying a function to the partition column prevents pruning at plan time. Compare with a range predicate on the bare column.

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE extract(month FROM created) = 2;

                          QUERY PLAN                          
--------------------------------------------------------------
 Append
   ->  Seq Scan on events_2026_01 events_1
         Filter: (EXTRACT(month FROM created) = '2'::numeric)
   ->  Seq Scan on events_2026_02 events_2
         Filter: (EXTRACT(month FROM created) = '2'::numeric)
   ->  Seq Scan on events_2026_03 events_3
         Filter: (EXTRACT(month FROM created) = '2'::numeric)
   ->  Seq Scan on events_default events_4
         Filter: (EXTRACT(month FROM created) = '2'::numeric)
(9 rows)

EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE created >= '2026-02-01' AND created < '2026-03-01';

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Seq Scan on events_2026_02 events
   Filter: ((created >= '2026-02-01'::date) AND (created < '2026-03-01'::date))
(2 rows)

Example 5: list and hash partitioning

List partitions hold explicit values. Hash partitions spread rows evenly by hash(key) MOD modulus.

CREATE TABLE customers_by_region (
    id     int  NOT NULL,
    region text NOT NULL,
    name   text
) PARTITION BY LIST (region);
CREATE TABLE cust_south PARTITION OF customers_by_region FOR VALUES IN ('Bangalore', 'Chennai');
CREATE TABLE cust_west  PARTITION OF customers_by_region FOR VALUES IN ('Mumbai', 'Pune');

CREATE TABLE sessions (
    user_id int NOT NULL,
    data    text
) PARTITION BY HASH (user_id);
CREATE TABLE sessions_p0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE sessions_p2 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE sessions_p3 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 3);

INSERT INTO sessions SELECT g, 'x' FROM generate_series(1, 10000) g;

SELECT tableoid::regclass AS partition, count(*) FROM sessions GROUP BY 1 ORDER BY 1;

  partition  | count 
-------------+-------
 sessions_p0 |  2489
 sessions_p1 |  2527
 sessions_p2 |  2530
 sessions_p3 |  2454
(4 rows)

EXPLAIN (COSTS OFF) SELECT * FROM customers_by_region WHERE region = 'Pune';

                QUERY PLAN                 
-------------------------------------------
 Seq Scan on cust_west customers_by_region
   Filter: (region = 'Pune'::text)
(2 rows)

Equality on a hash key prunes to one partition; range predicates on a hash key cannot prune.

EXPLAIN (COSTS OFF) SELECT * FROM sessions WHERE user_id = 42;

            QUERY PLAN            
----------------------------------
 Seq Scan on sessions_p2 sessions
   Filter: (user_id = 42)
(2 rows)

Example 6: primary keys must include the partition key

A unique constraint on a partitioned table must contain every partition column, because each partition enforces uniqueness only locally.

CREATE TABLE bad (id int PRIMARY KEY, created date) PARTITION BY RANGE (created);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "bad" lacks column "created" which is part of the partition key.

CREATE TABLE good (id int, created date, PRIMARY KEY (id, created)) PARTITION BY RANGE (created);

Example 7: partition maintenance

Removing old data from a partitioned table is a metadata operation instead of a slow DELETE that leaves dead rows to vacuum.

ALTER TABLE events DETACH PARTITION events_2026_01;

CREATE TABLE events_2026_04 (LIKE events INCLUDING DEFAULTS);
INSERT INTO events_2026_04 VALUES (1, '2026-04-10', 'view');
ALTER TABLE events ATTACH PARTITION events_2026_04 FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

SELECT c.relname AS partition
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'events'::regclass
ORDER BY 1;

   partition    
----------------
 events_2026_02
 events_2026_03
 events_2026_04
 events_default
(4 rows)

The detached table events_2026_01 still exists as a normal table and can be archived or dropped. MySQL supports RANGE, LIST, and HASH partitions with ALTER TABLE ... DROP PARTITION. SQL Server and Oracle also offer partitioning (Oracle as a separately licensed option).

Example 8: sharding strategies

flowchart TD
    A["Application"] --> R["Router / shard map"]
    R --> S1["Shard 1"]
    R --> S2["Shard 2"]
    R --> S3["Shard 3"]

Example 9: consistent hashing worked example

Place nodes and keys on a ring numbered 0 to 99. A key belongs to the first node clockwise from its position.

Add node D at position 65. Keys in the range 51 to 65 move from C to D, so k3 (position 65) now belongs to D. k1, k2, and k4 keep their owners, and A and B are untouched.

With plain hash MOD N, going from 3 nodes to 4 changes a key's owner whenever h MOD 3 differs from h MOD 4. For hash values 0 to 11 this query counts the keys that move.

SELECT count(*) FILTER (WHERE h % 3 <> h % 4) AS moved, count(*) AS total
FROM generate_series(0, 11) h;

 moved | total 
-------+-------
     9 |    12
(1 row)

Nine of the 12 keys (75%) change owner with MOD, against about a quarter of keys with consistent hashing. Virtual nodes (each physical node placed at many ring positions) smooth the load. Cassandra, DynamoDB, and Riak use consistent hashing.

Example 10: shard key, hot shards, cross-shard operations, resharding

Good shard key: high cardinality, evenly distributed, present in most queries, rarely updated.

Hot shard fixes: pick a better key, add a random suffix to hot keys (salting), split the hot range, or cache the hot data.

PostgreSQL has no built-in sharding. Extensions such as Citus, and systems such as Vitess (MySQL), CockroachDB, and MongoDB, provide it.

Interview points

• Partitioning is within one server; sharding is across servers.
• Pruning requires the partition key in the filter without wrapping functions.
• Dropping a partition replaces a bulk DELETE.
• Hash MOD N reshuffles most keys when N changes; consistent hashing does not.
• Cross-shard joins and transactions are the main cost of sharding, so delay it: scale up, add replicas, and cache first.

Summary

• PostgreSQL supports range, list, and hash partitioning; the planner prunes partitions using the partition key.
• Detach, attach, and drop partitions to maintain data cheaply; primary keys must include the partition key.
• Sharding strategies are range, hash, directory, and consistent hashing; consistent hashing moves about 1/N of keys when a node is added.
• Shard key choice decides balance and hot shards; queries without the key fan out to all shards.
• Cross-shard joins, transactions, and resharding are the hard parts of sharding.