Table Partition Methods multilevel · subpartition · LIST→RANGE

Multilevel Partition

Partitions of partitions — combine RANGE, LIST, and HASH at different levels for tighter pruning. Powerful for time-series with tenants or regions, but two levels is usually plenty.

What multilevel partitioning is

Multilevel partitioning turns each partition into another partitioned table. The first level splits the data one way; the second level splits each first-level partition further. You can mix methods at each level — list at the top, range below, hash at the bottom — to match how your data actually behaves.

Used well, it's powerful: you get partition pruning at every level, with each combined slice small enough for fast scans. Used badly, it's overkill — three levels of empty subpartitions just complicates maintenance for no gain.

A two-level partition tree

Syntax

The parent declares the first-level method. Each child also declares its own PARTITION BY clause, becoming a partitioned table itself. Then you create the leaf partitions under each child.

-- Level 1: parent partitioned by some method
CREATE TABLE parent (...)
    PARTITION BY {LIST | RANGE | HASH} (col_a);

-- Level 2: each child is itself partitioned
CREATE TABLE child
    PARTITION OF parent FOR VALUES ...
    PARTITION BY {LIST | RANGE | HASH} (col_b);

-- Level 3: leaf partitions under each child
CREATE TABLE leaf
    PARTITION OF child FOR VALUES ...;

Example — region (LIST) → amount (RANGE) → branch (HASH)

Three-level partitioning: list at the top, range in the middle, hash at the bottom.

SQL — level 1: parent

CREATE TABLE sales (
    sales_id   INT,
    region     TEXT,
    amount     NUMERIC,
    branch_id  INT
) PARTITION BY LIST (region);

SQL — level 2: each region partitioned by amount

CREATE TABLE sales_north
    PARTITION OF sales FOR VALUES IN ('North')
    PARTITION BY RANGE (amount);

CREATE TABLE sales_south
    PARTITION OF sales FOR VALUES IN ('South')
    PARTITION BY RANGE (amount);

SQL — level 3: each amount-bucket partitioned by branch_id

CREATE TABLE sales_north_low
    PARTITION OF sales_north FOR VALUES FROM (0) TO (1001)
    PARTITION BY HASH (branch_id);

CREATE TABLE sales_north_low_r0
    PARTITION OF sales_north_low FOR VALUES WITH (MODULUS 2, REMAINDER 0);

CREATE TABLE sales_north_low_r1
    PARTITION OF sales_north_low FOR VALUES WITH (MODULUS 2, REMAINDER 1);

-- Repeat for sales_north_high, sales_south_low, sales_south_high ...

Insert a row and PostgreSQL routes it through all three levels automatically:

SQL

INSERT INTO sales VALUES (1, 'North', 500, 105);
-- → sales_north (region match)
--   → sales_north_low (amount in [0, 1001))
--     → sales_north_low_r1 (hash(105) mod 2 = 1)

Pruning at every level

Queries with conditions on multiple partition columns get progressively tighter pruning:

SQL

SELECT * FROM sales
WHERE  region    = 'North'   -- prunes to sales_north
  AND  amount    < 1000      -- prunes to sales_north_low
  AND  branch_id = 105;       -- prunes to one hash partition

-- Plan scans exactly one leaf partition

When NOT to go multilevel

If the inner level barely shrinks the data, the extra metadata costs more than the pruning saves.
Each leaf partition is a real table — many small leaves multiply your maintenance work.
Two levels are usually plenty. Three levels are rare. Beyond that, reconsider the design.
If your queries don't filter on the deeper-level column, the deeper partitioning isn't bought any pruning.

Common multilevel patterns

Top level	Inner level	Why
RANGE (date)	LIST (tenant)	Time-series with multi-tenant isolation
LIST (region)	RANGE (date)	Per-region archival schedules
RANGE (date)	HASH (user_id)	Time-series with per-month load balancing

Recap

Multilevel partitioning makes a partition itself partitioned.
Mix methods freely — LIST → RANGE, RANGE → HASH, etc.
Each level has its own PARTITION BY clause; only the leaves hold rows.
Pruning compounds — queries filtering on multiple keys hit a tiny set of leaves.
Don't overdo it — two levels are usually plenty.