Multi-column partitioning can be a powerful tool for organizing data. In databases like Oracle, you can define a LIST partition on multiple columns without issue.
Unfortunately, PostgreSQL, and by extension, YugabyteDB’s YSQL layer, only allow LIST partitioning on a single column (or a single expression). If you try to use more than one column, you’ll get this error:
yugabyte=# CREATE TABLE sales (
yugabyte(# region TEXT,
yugabyte(# store_type TEXT,
yugabyte(# sale_date DATE,
yugabyte(# amount NUMERIC
yugabyte(# )
yugabyte-# PARTITION BY LIST (region, store_type);
ERROR: cannot use "list" partition strategy with more than one column
Why Oracle Can Do It, but Postgres/YB Cannot
Oracle supports multi-column LIST partitioning directly. You can write:
CREATE TABLE sales (
region VARCHAR2(10),
store_type VARCHAR2(10),
sale_date DATE,
amount NUMBER
)
PARTITION BY LIST (region, store_type) (
PARTITION p1 VALUES (('EAST', 'RETAIL')),
PARTITION p2 VALUES (('EAST', 'ONLINE')),
PARTITION p3 VALUES (('WEST', 'RETAIL'))
);
Oracle’s partitioning engine compares tuples directly in LIST partitioning.
PostgreSQL/YugabyteDB
Postgres supports multi-column RANGE and HASH partitions, but LIST only supports a single column/expression.
This restriction is a design choice in the declarative partitioning framework introduced in PG10 — the LIST partitioning code path handles only scalar equality matching and doesn’t support tuple matching.
YugabyteDB inherits this limitation from PostgreSQL. 😟
Workarounds for Postgres/YugabyteDB
- • Work Around #1: Subpartitioning (Recommended)
Partition by the first column, then subpartition by the second. You can query naturally on region and store_type and YSQL will prune down to the right subpartition.
-- Parent
CREATE TABLE sales (
region text NOT NULL,
store_type text NOT NULL,
sale_date date,
amount numeric
) PARTITION BY LIST (region);
-- EAST -> subpartition by store_type
CREATE TABLE sales_east PARTITION OF sales
FOR VALUES IN ('EAST')
PARTITION BY LIST (store_type);
CREATE TABLE sales_east_retail PARTITION OF sales_east FOR VALUES IN ('RETAIL');
CREATE TABLE sales_east_online PARTITION OF sales_east FOR VALUES IN ('ONLINE');
-- WEST -> subpartition by store_type
CREATE TABLE sales_west PARTITION OF sales
FOR VALUES IN ('WEST')
PARTITION BY LIST (store_type);
CREATE TABLE sales_west_retail PARTITION OF sales_west FOR VALUES IN ('RETAIL');
CREATE TABLE sales_west_online PARTITION OF sales_west FOR VALUES IN ('ONLINE');
-- Sample data
INSERT INTO sales VALUES
('EAST','RETAIL','2025-08-01',100),
('EAST','ONLINE','2025-08-02',120),
('WEST','RETAIL','2025-08-03',90);
-- Partition pruning proof
EXPLAIN SELECT * FROM sales
WHERE region='EAST' AND store_type='ONLINE';
-- Expect only sales_east_online to be scanned.
After running the above DDL, the EXPLAIN output below confirms that partition pruning is working exactly as expected.
yugabyte=# -- Partition pruning proof
yugabyte=# EXPLAIN SELECT * FROM sales
yugabyte-# WHERE region='EAST' AND store_type='ONLINE';
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on sales_east_online sales (cost=0.00..105.00 rows=1000 width=100)
Storage Filter: ((region = 'EAST'::text) AND (store_type = 'ONLINE'::text))
(2 rows)
• Workaround #2: Expression column as the partition key:
This approach, which uses the concatenation operator directly in the partition key, is not supported in either PostgreSQL or YugabyteDB:
yugabyte=# CREATE TABLE sales_combo (
yugabyte(# region text NOT NULL,
yugabyte(# store_type text NOT NULL,
yugabyte(# sale_date date,
yugabyte(# amount numeric
yugabyte(# ) PARTITION BY LIST (region || '|' || store_type);
ERROR: syntax error at or near "||"
LINE 6: ) PARTITION BY LIST (region || '|' || store_type);
Using the CONCAT function instead introduces its own issues:
yugabyte=# CREATE TABLE sales_combo (
yugabyte(# region text NOT NULL,
yugabyte(# store_type text NOT NULL,
yugabyte(# sale_date date,
yugabyte(# amount numeric
yugabyte(# ) PARTITION BY LIST (CONCAT(region, '||', store_type));
ERROR: functions in partition key expression must be marked IMMUTABLE
But there’s a workaround for this limitation… create an immutable helper function!
CREATE OR REPLACE FUNCTION concat_immutable(text, text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
SELECT $1 || '||' || $2;
$$;
CREATE TABLE sales_combo (
region text NOT NULL,
store_type text NOT NULL,
sale_date date,
amount numeric
) PARTITION BY LIST (concat_immutable(region, store_type));
CREATE TABLE sales_east PARTITION OF sales_combo
FOR VALUES IN ('EAST||RETAIL', 'EAST||ONLINE');
CREATE TABLE sales_west PARTITION OF sales_combo
FOR VALUES IN ('WEST||RETAIL');
INSERT INTO sales_combo(region,store_type,sale_date,amount) VALUES
('EAST','RETAIL','2025-08-01',100),
('EAST','ONLINE','2025-08-02',120),
('WEST','RETAIL','2025-08-03',90);
-- Pruning proof (guaranteed)
EXPLAIN SELECT * FROM sales_combo
WHERE concat_immutable(region, store_type) = 'EAST||ONLINE';
Running EXPLAIN after creating the objects and loading data confirms that partition pruning works with this solution as well.
Another option I considered was using a generated column as the partition key, but as in PostgreSQL, this is not supported in YugabyteDB:
yugabyte=# CREATE TYPE region_store_t AS (region text, store_type text);
CREATE TYPE
yugabyte=# CREATE TABLE sales (
yugabyte(# region text,
yugabyte(# store_type text,
yugabyte(# sale_date date,
yugabyte(# amount numeric,
yugabyte(# region_store region_store_t
yugabyte(# GENERATED ALWAYS AS (ROW(region, store_type)::region_store_t) STORED
yugabyte(# ) PARTITION BY LIST (region_store);
ERROR: cannot use generated column in partition key
LINE 8: ) PARTITION BY LIST (region_store);
^
DETAIL: Column "region_store" is a generated column.
Summary
The subpartitioning approach, partitioning first by one column, then subpartitioning by the second, is the recommended solution for multi-column LIST behavior in YugabyteDB. It is the only method that is fully supported without relying on unsupported features or complex workarounds.
Have Fun!
