I think I would use a lightweight 'reference' table with the unique index, put the actual data in the partitioned table, and use a foreign key to enforce a 1:1

I think I would use a lightweight 'reference' table with the unique index, put the actual data in the partitioned table, and use a foreign key to enforce a 1:1 relationship. Something like this: create table orders( order_id int unique , year int , primary key (order_id, year) ); create table order_data( order_id int , year int , LOADS_OF_COLUMNS_HERE jsonb , primary key (order_id, year) , foreign key (order_id, year) references orders(order_id, year) ) partition by list(year);