PostgreSQL 10.1 partitions and indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux