CODEWITHSUNDEEP
postgresql
Yuval Kaufman
Yuval Kaufman

Reputation: 677

Postgres - built-in automatic and dynamic partitions

I have already (since I used postgresql 9.6) inherit partition table by range

a partition for date 2019-11-01 looks like that:

client_id,product_id,date,orders
1,1,2019-11-01, 100
1,2,2019-11-01, 200
2,3,2019-11-01,150
2,4,2019-11-01,160

I have understood that since postgresql 10, partitioned table can be handled and defined differently and built-in

Since my current inherited partitions differs only by date, for each client I have a query will pull data from X partitions that hold data for all clients.

I want know if I can create a new master table that will have "children" based on range (date) and client id (list) and then if I query for a specific clientfor range between X and Y the executor will go to specific documents that include only the data for that client and for that time range.

I'd also like to know if there is simple without a need to create partition tables for each date like I do today.

thanks.

Upvotes: 1

Views: 720

Answers (1)

jjanes
jjanes

Reputation: 44167

You could use sub-partitioning to do that (the partitions of a partitioned table can themselves be partitioned) but doing so is likely to lead to an combinatorial explosion in the number of partitions; which is likely to be counterproductive.

I'd also like to know if there is simple without a need to create partition tables for each date like I do today.

No. Declarative partitioning doesn't automatically create the partitions for you. You still need to do it by hand, or script it, or create triggers to do it (not a good option IMO)

Upvotes: 2

Related Questions