CODEWITHSUNDEEP
postgresqlpartitiondatabase-partitioningpostgresql-10postgresql-11
sharadov
sharadov

Reputation: 1088

How do I partition table over two columns?

Table T1 has cols - app_id,start_time,end_time,value. We have multiple applications and would like to partition tables by app_id and start_time (weekly partitions). This is how the child partitions should look like 

T1_part1_2018 
app_id start_time end_time   value
1     10-10-2018  10-11-2018 garbage

T2_part2_2018 
app_id start_time end_time   value
2     10-10-2018  10-11-2018 garbage

T1_part1_2019
app_id start_time end_time   value
1     10-10-2019  10-11-2019 garbage

T2_part2_2019
app_id start_time end_time   value
2     10-10-2019  10-11-2019 garbage

How would I go about accomplishing this?

Upvotes: 1

Views: 2992

Answers (1)

Jaisus
Jaisus

Reputation: 1109

I think what you are looking for is the sub-partition mechanism :

Which partition have to be the mother of the other one is entirely up too your needs : if you never need to request the data of more than one app_id, I would recommend make the app_id the mother partition key.

EDIT : And here is the pg_partman extension project page : https://pgxn.org/dist/pg_partman/doc/pg_partman.html

Upvotes: 1

Related Questions