Table Partition Methods HASH · MODULUS · REMAINDER

HASH Partition

Spread rows evenly across a fixed number of partitions by hashing the partition column. Best when there's no natural grouping but you still want to slice the table.

What hash partitioning is

Hash partitioning spreads rows evenly across a fixed number of partitions by hashing the partition column. PostgreSQL computes hash(column) mod N; the remainder picks which partition the row goes into.

It's the right choice when there's no natural way to group your data (no dates, no regions) but you still want to split it for parallelism or manageability. Hash partitioning gives you load balance — every partition ends up with roughly the same number of rows.

When to choose HASH

Rows have no logical grouping but the table is too big for one piece.
You want even distribution to spread I/O across multiple partitions.
The partition column is a high-cardinality identifier (user_id, customer_id).

⚠ No range queries. Hashing destroys ordering — WHERE user_id BETWEEN 1000 AND 2000 can't prune partitions because matching values are scattered everywhere. Hash partitioning helps with point lookups, not range scans.

Syntax

-- Parent
CREATE TABLE parent_table (...)
    PARTITION BY HASH (partition_column);

-- Each child — fixed number, picked by remainder
CREATE TABLE child_table
    PARTITION OF parent_table
    FOR VALUES WITH (MODULUS N, REMAINDER r);

You decide the modulus N up front. Then each partition gets a unique remainder from 0 to N−1. Rows whose hash(column) mod N equals a partition's remainder land in that partition.

Example — sales by branch_id, 4 partitions

SQL — parent

CREATE TABLE sales_data3 (
    sales_id   INT,
    amount     NUMERIC,
    branch     TEXT,
    branch_id  INT
) PARTITION BY HASH (branch_id);

SQL — four children with modulus 4

CREATE TABLE sales_branch_id_remainder_0
    PARTITION OF sales_data3
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE sales_branch_id_remainder_1
    PARTITION OF sales_data3
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE sales_branch_id_remainder_2
    PARTITION OF sales_data3
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE sales_branch_id_remainder_3
    PARTITION OF sales_data3
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

SQL — insert & query

INSERT INTO sales_data3 VALUES
(1, 1500, 'Branch A', 101),
(2, 2200, 'Branch B', 102),
(3,  900, 'Branch C', 103),
(4, 3400, 'Branch D', 104),
(5, 1100, 'Branch E', 105),
(6,  750, 'Branch F', 106),
(7, 4200, 'Branch G', 107),
(8, 2900, 'Branch H', 108);

-- Each branch_id ends up in one specific partition,
-- but you can't predict which without computing hash(branch_id) mod 4.

-- Point lookups still prune — partition is computable from the value
SELECT * FROM sales_data3 WHERE branch_id = 105;
-- Plan: scan only the partition whose remainder matches hash(105) mod 4

How rows get distributed

Modulus and remainder rules

The partitions' remainders must be distinct integers from 0 to MODULUS−1.
Every remainder up to MODULUS−1 should be covered, or some hash values will fail to insert.
Different partitions can have different moduli (advanced — usually you keep them all the same).
Number of partitions is fixed at design time. Adding partitions later means re-hashing — plan ahead.

💡 Pick the partition count based on parallelism, not data size. 4 / 8 / 16 are common. The goal is enough partitions to spread I/O across CPU cores, not so many that each partition gets tiny.

What hash gives up

Lose	Gain
Range pruning	Even row distribution
Easy archival of "old" data	Predictable partition sizes
Human-readable partition names	No hot partition

Recap

Hash partitioning distributes rows across N partitions by hash(col) mod N.
Use FOR VALUES WITH (MODULUS N, REMAINDER r) for each child.
Remainders must be distinct and cover 0 to N−1.
Best for evenly distributing high-cardinality columns where no range or list scheme fits.
Range queries can't prune — only equality lookups can.