asmartbear

There are very good reasons for BIGINT.

For example, suppose you want to make a master-master WordPress system, with eventual-consistency (i.e. AP from the CAP theorem). One of the challenges there is merging records created on both sides. A typical solution is to use AUTOINC, but with a step-size of 2, then one server is “even” and the other is “odd.” Another typical solution is to realize you should plan for new servers joining the group, or more than two servers, and thus use a step of 10 or 20 (as many master-master MySQL systems do today). Suddenly you need 20x the number-space, so 4 billion records becomes 40MM records, which actually isn’t that much if you’re considering enterprise-scale sites.

Now let’s get even smarter. AUTOINC is also the wrong choice for master-master, especially if you want to be able to merge from many sources, for example merging two unrelated WordPress sites. In that case you want GUID instead of a number. With 64 bits, you can basically do this. Yes it would be better if it were eg 128-bits, but it’s reasonable, and still compatible with int-oriented systems, and perhaps more efficient in the PHP layer.

Finally, your point about taking up more RAM is true, but not as significant as you’re making it out to be. Using your example of 10M comments, you’re worried about an extra 4 bytes per record. But note that a comment record is already 100x larger than that, especially when you consider the arbitrary-sized text of the comment. So you’re talking about reducing the comment table size by 1%. From that perspective, it’s doesn’t seem that important! 🙂