What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

Question

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the max signed or unsigned number of the current datatype? (A 32-bit INT)? I'm guessing that the database server would overflow when it tries to assign the (2∧32)-1 number to the next entry, so, how to avoid that happening (without changing the datatype, for the sake of the question) and keep adding records? What would you do?

Why would I use INT's and not, for example, VARCHARS?

It has been several days since I've asked myself this hypothetical question and I would like to know what a professional would do.

Answer 1

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-) The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database. What records are foreign-keyed to this data? Do you still need all the data in that table and the related records? On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1. There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1. No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.

Answer 2

One overlooked point is that many folk start the auto number or identity at 1 thus losing half of the possible range immediately (for signed)

You'd simply redefine the number to start at -1, increment -1 in this case.

Arguably, if you ever expected to fill your identity column then you should have designed this in and used a wider datatype at the start.

See this recent question oon SO: SQL Server 2008: what happened if identity oversteps a maximal value of int?

Answer 3

Overflow BIGINT? Haha. First figure out how to achieve immortality. INT UNSIGNED (4 billion) is hard enough to reach. 100 INSERTs per second would come close to overflowing INT in a year. BIGINT would take several billion years.

To fix: ALTER TABLE foo MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; But that will take hours because it will copy over the table (which has close to 4 billion rows, right?) and rebuild all the secondary indexes. Plan Ahea d.

Generally when you try to store a number too big for a field (eg, 999 in a TINYINT UNSIGNED), it will silently cap it to the max for the field (255 in this case). There may be a "Warning", but most people don't bother to check warnings. If it is a UNIQUE field, or there are FOREIGN KEYS, you might get a more serious error.

CHAR or VARCHAR is silently truncated to the space available.