Postgres - built-in automatic and dynamic partitions

Question

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.

You may be interested in pg_partman.

klin
– klin

2019-11-24 18:31:52 +00:00
Commented Nov 24, 2019 at 18:31 — klin
– klin, Commented Nov 24, 2019 at 18:31

jjanes · Accepted Answer · 2019-11-24 18:24:34Z

2

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)

answered Nov 24, 2019 at 18:24

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - built-in automatic and dynamic partitions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related