Choose the right datatype

Recently I read about the outage at Basecamp 3 that occurred when they ran out of integers.

https://m.signalvnoise.com/update-on-basecamp-3-being-stuck-in-read-only-as-of-nov-8-9-22am-cst-c41df1a58352

This reminded me of a time when I was working on a very busy web site that was growing very quickly.

The initial database design had an identity column (1,1) using an int.

We ran out of integers.

There were two choices, do a reseed of the identity column and start using a negative increment (-1)

OR

Change the datatype to a bigint.

The first choice to me was a bit of a quick and dirty. The better architectural choice was to take the outage and change the datatype. After quick testing this change was made.

Here are the different int datatypes.

Datatype Range Storage bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) 8 Bytes int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) 4 Bytes smallint -2^15 (-32,768) to 2^15-1 (32,767) 2 Bytes tinyint 0 to 255 1 Byte

Moral to the story is to choose the right datatype and be aware of the integer ranges and the space required. Sometimes it may be better to waste a bit of space and therefore never have to worry about running out.