I am curious as to what solutions are out there for partitioning tables that log events, that allow both efficient querying and the advantages of pruning when truncating data.
Suppose I have a simple table that records events from different locations:
tblEvents ( event_id, location_id, start_datetime, end_datetime )
Most queries on this table will take the form of:
SELECT event_id FROM tblEvents WHERE location_id = @queried_location_id AND start_datetime < @queried_end_datetime AND end_datetime > @queried_start_datetime
To date, I have partitioned by the location_id, and simply just indexed the datetime columns. Performance-wise, this has sufficed, and I never planned to keep more than a couple of months worth of data in the database, so it seemed future-proof as well.
The problem arose when I actually went to purge data from this table (it is very large and also involved in replication). Using
DELETE FROM tblEvents WHERE start_datetime < @some_date proved to be very slow, and created problems with requests being made from other clients (not surprisingly).
Partitioning by either of the datetime columns is no good. Although it solves my data purging problem, it makes the query above inefficient, as it still needs to look at multiple partitions depending on the dates being queried.
Am I missing a common solution here? Is there a more efficient way to purge my data? Or is there a smarter way to partition/index that I have missed?