[bsder]

> Never use UUIDs as your primary keys.

This seems like terrible advice.

For the vast, vast, vast majority of people, if you don't have an obvious primary key, choosing UUIDv7 is going to be an absolute no-brainer choice that causes the least amount of grief.

Which of these is an amateur most likely to hit: crash caused by having too small a primary key and hitting the limit, slowdowns caused by having a primary key that is effectively unsortable (totally random), contention slowdowns caused by having a primary key that needs a lock (incrementing key), or slowdowns caused by having a key that is 16 bytes instead of 8?

Of all those issues, the slowdown from a 16 byte key is by far the least likely to be an issue. If you reach the point where that is an issue in your business, you've moved off of being a startup and you need to cough up real money and do real engineering on your database schemas.

[sgarland]

The problem is that companies tend to only hire DB expertise when things are dire, and then, the dev teams inevitably are resistant to change.

You can monitor and predict the growth rate of a table; if you don’t know you’re going to hit the limit of an INT well in advance, you have no one to blame but yourself.

Re: auto-incrementing locks, I have never once observed that to be a source of contention. Most DBs are around 98/2% read/write. If you happen to have an extremely INSERT-heavy workload, then by all means, consider alternatives, like interleaved batches or whatever. It does not matter for most places.

I agree that UUIDv7 is miles better than v4, but you’re still storing far more data than is probably necessary. And re: 16 bytes, MySQL annoyingly doesn’t natively have a UUID type, and most people don’t seem to know about casting it to binary and storing it as BINARY(16), so instead you get a 36-byte PK. The worst.

[benwilber0]

> contention slowdowns caused by having a primary key that needs a lock (incrementing key)

This kind of problem only exists in unsophisticated databases like SQLite. Postgres reserves whole ranges of IDs at once so there is never any contention for the next ID in a serial sequence.

[sgarland]

I think you’re thinking out the cache property of a sequence, but it defaults to 1 (not generating ranges at once). However, Postgres only needs a lightweight lock on the sequence object, since it’s separate from the table itself.

MySQL does need a special kind of table-level lock for its auto-incrementing values, but it has fairly sophisticated logic as of 8.0 as to when and how that lock is taken. IME, you’ll probably hit some other bottleneck before you experience auto-inc lock contention.