List Partitioning

Not all data is time-ordered. Not all data needs even distribution. Sometimes your data has natural categories, and you want each category in its own partition.

An e-commerce platform operates in 5 regions: North America, Europe, Asia-Pacific, Latin America, and Middle East/Africa. Each region has its own compliance requirements, its own support team, and its own reporting. Queries almost always filter by region. North America accounts for 60% of the data, but that's fine, the NA partition just needs a bigger server.

A SaaS platform has enterprise customers who each want their data physically isolated. Customer A's data in one partition, Customer B's in another. When Customer A churns, drop their partition and their data is gone.

List partitioning assigns rows to partitions based on an explicit list of values. It's the simplest partitioning strategy to understand, no math, no ranges, just "this value goes to that partition."

In PostgreSQL:

CREATE TABLE orders (
 id UUID DEFAULT gen_random_uuid(),
 user_id UUID,
 amount DECIMAL(10,2),
 region VARCHAR(10) NOT NULL,
 created_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE orders_na PARTITION OF orders FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('DE', 'FR', 'IT', 'ES', 'NL', 'GB');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('IN', 'JP', 'AU', 'SG', 'KR');
CREATE TABLE orders_latam PARTITION OF orders FOR VALUES IN ('BR', 'AR', 'CL', 'CO');
CREATE TABLE orders_mea PARTITION OF orders FOR VALUES IN ('AE', 'SA', 'ZA', 'NG');

Partition pruning:

-- Fast: region is the partition key, prunes to orders_eu
SELECT * FROM orders WHERE region IN ('DE', 'FR');

-- Slow: no partition key filter, scans all 5 partitions
SELECT * FROM orders WHERE amount > 100;

The DEFAULT partition: What happens if a row has a region value not listed in any partition? PostgreSQL rejects the insert with an error. Unless you create a default partition:

CREATE TABLE orders_other PARTITION OF orders DEFAULT;

Any region not explicitly listed goes to orders_other. This is a safety net, you should monitor it and create proper partitions for new regions as they appear.