Hi, I'm currently migrating an oracle schema to postgresql. In the oracle`s schema there is a table partition that has partitions by range(date - for every day) and each partition has a sub partition by list(some values..). Moreover, the data is loaded from a csv in a bulk. One important thing is that some data might be imported twice therefore there must but a unique index on the table.
On PostgreSQL 10.1 I created the main table partitioned by range(date) and I created all the sub partitions. I have 2 problems : 1)In the oracle main table there are global indexes for selects that involve columns that arent part of the range or list partitions. According to the documentation I need to create the indexes on each leaf. I have partition for every day in the year so I'll have about 6(num of global indexes in oracle)*365(days of year)*7(number of sub partitions) = 15330 indexes created every year. I guess that the performance that I will have when I select columns that arent part of the partitions order will be pretty bad. Any idea ? 2)Regarding the uniqueness, the only solution is to create a unique index for every subpartition ? 3)Any suggestions how to improve queries that involve columns that arent part of the paritions order ? Thanks , Mariel.
