Can't you simply add a additional timestamp column for time based sorting? No need to raise the collision probability.
Unexpected downsides of UUID keys in PostgreSQL
11–20 of 215 posts
Re: Unexpected downsides of UUID keys in PostgreSQL
#12Why not just use auto-increment integers and expose them with something like this: https://www.npmjs.com/package/hashids (The linked library is just an example). Yes, you will need to transform the IDs before returning them to an external caller which is a little bit annoying but it's probably not that a big of a deal.
Also another approach I've seen, which personally I find it a bit complicated, is to use auto-increment primary keys for the internal system and UUIDs for the public facing interactions.
Re: Unexpected downsides of UUID keys in PostgreSQL
#13UUID is also used to avoid leaking information about the underlying system. This includes temporal information that could be used to infer the size of the dataset for all users. If this isn’t a concern, then using a timestamp based approach as recommended in this article is a good approach. That is the default in MongoDB. If it is a concern, one approach is to use random UUIDs to give to end users but then internally…
Or use hashids.org. Use it to obfuscate your integer auto increment ids anywhere a user might see it.
Re: Unexpected downsides of UUID keys in PostgreSQL
#14How can this be unexpected? Isn't this what Percona discussed for MySQL already several years ago? Or am I missing something?
> How can this be unexpected?
If you routinely work with junior developers this will come up because UUID4 seems miraculous at first. There's always a bit of surprise when the question gets asked about why we have so many sequential keys when we could just use UUID4...
Re: Unexpected downsides of UUID keys in PostgreSQL
#15Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?
Yes, UUID v7 can be generated independently. The UUID has three parts, which maintains strict chronological ordering for UUIDs generated in one process, and to within clock skew when UUIDs are generated on different systems.
The three parts are:
- time-based leading bits.
- sequential counter, so that multiple UUID 7s generated very rapidly within the same process will be monotonic even if the time counter does not increment.
- enough random bits to ensure no collisions and UUIDs cannot be guessed.
Re: Unexpected downsides of UUID keys in PostgreSQL
#16Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?
They can be generated anywhere, otherwise they wouldn't be universally unique.
I'm not asking about the uniqueness but about ordering across different clients.
Re: Unexpected downsides of UUID keys in PostgreSQL
#17Can UUID v7 still be created independently, on a client for instance? Or do they ideally need to be generated in the same place?
Yes, UUID v7 can be generated independently. The UUID has three parts, which maintains strict chronological ordering for UUIDs generated in one process, and to within clock skew when UUIDs are generated on different systems. The three parts are: - time-based leading bits. - sequential counter, so that multiple UUID 7s generated very rapidly within the same process will be monotonic even if the time counter does not i…
Thank you.
Re: Unexpected downsides of UUID keys in PostgreSQL
#18Is this a downside of UUID keys in general, or of using them as indexes? Would it be possible to create a primary index on a combination of inserted date and uuid and get opaque UUIDs and good indexing?
The alternative is having two required-unique columns only one of which is definitionally unique. It solves some problems and introduces others.
Re: Unexpected downsides of UUID keys in PostgreSQL
#19That's what created_at and updated_at are for surely
Re: Unexpected downsides of UUID keys in PostgreSQL
#20I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs to optimize access times by a millisecond or two. UUID is totally worth the cost.
UUID actually performs much better than I thought based on the author's example with a COUNT query. COUNT queries aren't very efficient because typically, all records are traversed; here we're talking about a 50% slowdown on 10 million records... How often do you need to access 10 million contiguous records? Most of the time, for user-facing apps, you'll be accessing 100 contiguous records at most and, in fact, most queries will access a single record... I suspect that the average per-query performance loss for a typical app is probably less than 5%. Also, you could simply index by a separate date/timestamp field if you need records to be ordered by time and probably won't incur any performance cost.
IMO, unless you're building an app for high-frequency trading, auto-incrementing IDs aren't worth the pain and lack of flexibility.