We have a number of tables that were partitioned primarily to reduce problems when purging data (via sliding-window partitioning / truncate). I have found a number of caveats that come with partitioning and am wondering whether partitioning is worth it in our case.
Thus far from what we have encountered,
The pros are:
- Low overhead (locks, speed) in sliding large amounts of data into separate table for truncation (our primary concern)
- Increased performance.
The cons are:
- Dealing with foreign keys to the table when sliding the partitions around.
- Additional data required in indexes (and queries) to have aligned indexes
- Other limitations associated with aligned indexes? (comments please)
What other types of benefits or limitations should we expect to see as we go forward?
