Pure list partitioning breaks down in two situations: when the set of partition key values is open-ended or unknown at table-creation time, and when a long-tail distribution makes assigning every low-volume key its own partition impractical. LIST DEFAULT HASH partitioning solves both problems by combining dedicated list partitions for high-volume keys with hash sub-partitions that automatically absorb everything else.
Use cases
Use LIST DEFAULT HASH partitioning when either of the following conditions applies:
Partition key values cannot be fully enumerated — the possible values are open-ended or unknown when you create the table.
Long-tail key distribution — a small number of high-volume keys hold the majority of data, while a large number of low-volume keys share the remainder. A common signal is the 80/20 pattern: 20% of partition key values hold 80% of the data, and 80% of the values hold the remaining 20%.
In both cases, forcing every low-volume key into a named list partition wastes partitions and increases maintenance overhead. LIST DEFAULT HASH partitioning lets you assign dedicated list partitions to high-volume keys and route everything else into a set of hash sub-partitions that spread the load evenly.
Multi-tenant example
A multi-tenant order system where a few key accounts generate most of the data is a typical fit. The following table shows a representative distribution:
Tenant | Data volume | Partition |
Key account 1 | 30 million rows | p1 |
Key account 2 | 26 million rows | p2 |
Key account 3 | 24 million rows | p3 |
Key account 4 | 20 million rows | p4 |
Small and medium-sized customers | 30 million rows (combined) | p_others |
The four key accounts each get a dedicated list partition for fast, isolated access. All remaining customers are routed to p_others, which is backed by three hash sub-partitions (DEFAULT PARTITIONS 3) that distribute the combined load evenly.
CREATE TABLE cust_orders
(
customer_id VARCHAR(36),
year VARCHAR(60),
order_id INT,
order_content TEXT
) PARTITION BY LIST COLUMNS(customer_id)
(
PARTITION p1 VALUES IN ('Key account 1'),
PARTITION p2 VALUES IN ('Key account 2'),
PARTITION p3 VALUES IN ('Key account 3'),
PARTITION p4 VALUES IN ('Key account 4'),
PARTITION p_others DEFAULT PARTITIONS 3 -- catches all other customer_id values; split into 3 hash sub-partitions
);