Performance with sequentially increasing primary key

Question

Looking for guidance on selecting a database provider for a specific key pattern.

The only key field will be a pre-allocated unique sequentially-increasing number. During each day between 50 and 100 thousand items will be added, processed (updated), and then retained for a week or so, after which usually the lowest-numbered records will be deleted. The number of records will not fluctuate by very much from day to day but may drop at weekends. The numbers will probably wrap back to 1 after 100M or so.

I need to find a database implementation where the efficiency of the index lookup, addition and deletion remains constant. Should I be worried that the performance might drop off as the key value range moves continuously upwards?

Answer 1

index lookup, addition and deletion remains constant

You could ensure it remains constant by rebuilding the indexes every insert (just constantly really slow - no performance drop off at all :)), or close to constant by running index maintenance every hour/day etc.

that the performance might drop off as the key value range moves continuously upwards?

As long as you've got an index, it should be logN performance - e.g. having 1,000,000 rows will be around half the speed of 1,000 rows (when searching for an indexed value). (1,000,000,000,000 will be half that speed again).

So no, you shouldn't need to worry about performance.

The numbers will probably wrap back to 1 after 100M or so.

Ok - if you want. Generally, no need really - just use a big int.

As always with performance: test what you want to do. Make a script that inserts 10,000,000 rows, and see what happens.

My point here being that if you're going to wrap ids at 100M records, the worst you can do is actually have them all allocated. This would represent the fragmented index condition as well (where you only have say 100K records, but they're distributed in a space of 10M) - but you will do index/database maintenance right?