Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Unexpected downsides of UUID keys in PostgreSQL

cybertec-postgresql.com

Post reply on HN
Print view View original

31–40 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#31

Post by jongjong »

stevesimmons wrote: post #15
jamil7 wrote: post #5

Can 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…

I don't have a problem with UUIDv7 but I wish they had created it as a completely separate standard after UUIDv4... The version number increases imply that previous versions have been superseded but in fact, they just have different priorities...

And TBH I'm concerned that UUIDv1 had a timestamp, then it was removed completely in UUIDv4 and now the timestamp concept is being added back to UUIDv7... There are legitimate use cases where you simply don't want to have timestamps in your IDs.

Re: Unexpected downsides of UUID keys in PostgreSQL

#32

Post by dewey »

jongjong wrote: post #20

I 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... Ho…

> sacrifice the beauty and elegance of UUIDs

Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

Re: Unexpected downsides of UUID keys in PostgreSQL

#34

Post by zzzeek »

VWWHFSfQ wrote: post #26

My go-to pattern for many years now is to use a plain bigint autoincrement column for internal database relations and then a uuid for application-level identifiers and natural keys. Basically never use the uuid as the actual primary key because they're enormous and now the DBMS has to copy that gigantic number to every side of the relation. Don't do it

Same, I'm surprised nobody is mentioning this. All the foreign key columns become uuids also and the DB becomes miserable to deal with.

Re: Unexpected downsides of UUID keys in PostgreSQL

#35

Post by maxgee »

dewey wrote: post #32
jongjong wrote: post #20

I 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... Ho…

> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

Nothing. Integers are simpler and faster. They're also incorrect about COUNTs requiring a full record scan.

Re: Unexpected downsides of UUID keys in PostgreSQL

#36

Post by ceeam »

dewey wrote: post #32
jongjong wrote: post #20

I 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... Ho…

> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

Their uniqueness of course.

> To be able to generate keys independently of the database

> To move sets of related records between different databases without having to deal with renumbering everything

Re: Unexpected downsides of UUID keys in PostgreSQL

#37

Post by rjh29 »

bandrami wrote: post #18
robertlagrant wrote: post #7

Is 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.

You can use UUIDv7 or ULID for that. One value with both the timestamp and random data inside.

Re: Unexpected downsides of UUID keys in PostgreSQL

#38

Post by kogus »

dewey wrote: post #32
jongjong wrote: post #20

I 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... Ho…

> sacrifice the beauty and elegance of UUIDs Out of curiosity, what makes UUIDs more elegant or beautiful than just plain old integers?

“5” might be the key for a thousand different records in your database. A uuid is a key for exactly one. Integer keys permit wrong joins to have the appearance of working.

Re: Unexpected downsides of UUID keys in PostgreSQL

#39

Post by plagiarist »

croes wrote: post #11

Can't you simply add a additional timestamp column for time based sorting? No need to raise the collision probability.

If size isn't an issue it seems so. I know of one implementation that uses wall clock to get a "close enough" sorting https://github.com/segmentio/ksuid
Post reply on HN