The application I am currently analysing, which is having, serious performance issues, is using semantic partitioning (range) on one of the main tables.
In order to be able to archive data easily, each partition contains the data of one month. Hence all inserts happen on the same partition. I do not really see the benefit of it.
In addition, each month, they obviously start with an empty partition and due to the high percentage of new data, the statistics are wrong. Therefore they run update stats every hour, in order to get the indexes used.
Main index is on date (which is the partitioning key) and an id-column, which is ascending.
My assumption is, that this index on (partitioning key, id) degenerates and is not really useful.
In addition I think that the performance problems could be solved, by partitioning by a completely different column.
Question: Has someone got experience with this sort of partitioning? Would it not be better to partition by a completely different column that the date of insertion, in order to distribute the load among all partitions.
The disadvantage would be, that the archiving would suffer, but finally the main purpose of the application is to get data in and out and not the archiving.