3 ms·
In case you're still curious, I think the reason it isn't included is because auto-increment is a hard problem to do right, ends up being a leaky abstraction fo
by pkteison 7y ago
In case you're still curious, I think the reason it isn't included is because auto-increment is a hard problem to do right, ends up being a leaky abstraction for technical reasons, causes bugs, and is philosophically sub-optimal for basically any use case you can dream up. Want to order? Dates are probably always a better thing to order by, or else some natural property of the data like name or title. Want to link two records together? GUIDs are so much better it's not even a contest.
In development, auto-increment appears to be previous field +1. Developers write code expecting that behavior and relying on continuous IDs without gaps. But, well, for example, Oracle doesn't rollback sequence values. They also pull them in blocks (I think blocks of 20?). So sometimes you can end up with numbering like 1,2,3,20,22 if things were weird at just the right time, and you probably never saw it during development or QA unless you happened to know to try to force it.
Then you get things like SQL Server resets auto increment field values on database export/import, so numbering which used to have gaps in the original will have no gaps in the import, causing the numbers to change. (I have no idea why, just seems like a bug/disaster waiting to happen.) Changing some ID fields tends to cause obvious disasters, as you usually have several tables which store a copy of the old auto ID value to join on.
I've seen several production data loss issues as I learned about the joys of this data type and prefer to avoid them as I learned more.
- sk5t 7y ago> GUIDs are so much better it's not even a contest GUIDs require more storage, and are likely (definitely with v4 GUID) to lead to index fragmentation. Postgres requires an extension to do most useful things with GUIDs whereas SERIAL is ready to go. I'm very pro-GUID yet don't universally prefer them in databases.
- fortran77 7y agoThanks! I also realize that if you have distributed database nodes, it's becomes a very hard problem to have a monotonically incrementing id. But still, mysql/mariadb does it, and so does Microsoft SQL Server (via IDENTITY)
- chx 7y agoMySQL sort of cheats (but it's a good cheat!) by auto_increment_increment/offset which is means as long as you have a fixed amount of nodes , you are good, server #1 uses 1,4,7 etc #2 uses 2,5,8 etc and #3 uses 3,6,9 .. adding a node requires reconfiguring (but not restarting) all nodes.