Re:Auto increment is a terrible idea
by EGreg on Thursday November 30, 2017 @09:29AM
(#15815903)
There is a different reason that auto incrementing ids are bad: they don't scale.
When you need to shard your table, the autoincrementing is is a bottleneck across all shards as you need to hold a lock on it when incrementing. In fact, that's exactly how it's implemented explicitly in POSTGRES.
And if you want something that's way MORE distributed such as CockroachDB or (RIP) Riak then forget about it.
What we do is generate a random 7 character string (alphanumeric lowercase) and then check if it exists in the db and - if not - insert it, using a transaction. Otherwise repeat until one works.
When you need to shard your table, the autoincrementing is is a bottleneck across all shards as you need to hold a lock on it when incrementing. In fact, that's exactly how it's implemented explicitly in POSTGRES.
And if you want something that's way MORE distributed such as CockroachDB or (RIP) Riak then forget about it.
But you don't necessarily need UUIDs. You can use for example https://www.npmjs.com/package/shortid
What we do is generate a random 7 character string (alphanumeric lowercase) and then check if it exists in the db and - if not - insert it, using a transaction. Otherwise repeat until one works.