Maintain table with too many insert

Question

I have to create a table. Its size is going to increase very fast.

Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for username

I want to use the key so that after the value reaches the maximum possible value next key is generated with value 1. How can this be achieved? Old data is not useful so I can delete the data at the starting rows. I know UNSIGNED BIGINT has a huge range but is there any way I can create the database with a trigger so that key reuses old values? What can be the downside of trying to write user defined function to accomplish this?

Answer 1

There isn't any way to reset autoincrement such that it goes back to the beginning and just overwrites existing data. Autoincrement isn't built that way.

What you could do is update rather than insert. You'd have to either pre-populate every record with blank data so that every entry is always an update, or you could write a procedure which tries to read the next ID first and then either inserts or updates accordingly. Either way, you will need to track the next ID yourself instead of letting the database handle it for you (with autoincrement). When you get to the maximum value, reset the next ID to 1 and keep going.

Thinking Practically:

The largest value for UNSIGNED BIGINT is 18,446,744,073,709,551,615. Depending on what is in your record you are looking at thousands (even millions) of petabytes of data. Are you sure you aren't going to have space constraints with that much data? You may find that what you need to do is purge out data that is old enough to be discarded as you go just to make room in your database. If you do that, then you can avoid the prepopulation and just insert every time.

If you purge periodically, say every night, then you just need to wait until the gap between where your remaining data is and ID=1 is big enough that you don't have to worry about ID collisions. When that happens, reseed your ID and start inserting from ID=1 again. If you do this, then you can go back to using autoincrement.

Edit: Additional Information about reseeding...

OP mentioned in a comment that the command for reseeding an autoincrement column in mysql is of interest. This command will reset the starting autoincrement value:

ALTER TABLE table AUTO_INCREMENT = 1

I have read that this should be done with caution because it can take a long time in a large table.

See also the documentation for AUTO_INCREMENT.