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

41–50 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#41

Post by babbledabbler »

gregwebs wrote: post #4

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

I actually have been using a combination of a numeric integer ID as the PK and a UUID as a lookup field to do routing lookups etc. for this purpose in Postgres backed app I'm working on.

I found this approach to be more trouble than it's worth and plan on switching to a UUID PK key and doing away with the integer sequence.

Here are the complications I ran into:

The libraries I'm using for the ORM and API are designed to work with a primary key for single record get access. For example they want you to do `resource.get(ID)` where ID is the primary key, however, I now have to do `resource.find({ where: { uuid: 'myuuid' }})`. This is for all resources on all pages.

In Postgres, the integer PK sequence has a state that keeps track of what number it's at. In certain circumstances it can get out of sequence and this can trip up migrations.

We already have created_at and updated_at fields and these are probably better for ordering than the sequencing.

Had I to do it again, I would just use UUIDv4 until it runs into issues and either date fields or a sequence where necessary. If anyone has better ideas I would be most grateful as this is something I go back and forth on.

Re: Unexpected downsides of UUID keys in PostgreSQL

#42

Post by xkqd »

maxgee wrote: post #35
dewey wrote: post #32

Earlier quoted context omitted.

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

How are integers simpler? Even generation of unique integers is more complicated, let alone migrating them or moving them around.

Re: Unexpected downsides of UUID keys in PostgreSQL

#43

Post by ahoka »

jongjong wrote: post #31
stevesimmons wrote: post #15

Earlier quoted context omitted.

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

These are not versions in the sense that one supersedes the other. More like variants.

Re: Unexpected downsides of UUID keys in PostgreSQL

#44

Post by yes_man »

ceeam wrote: post #36
dewey wrote: post #32

Earlier quoted context omitted.

> 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

And adding greppability of logs in debugging situations. You can just search logs in any systems of yours with the UUID and find exact hits you are dealing with. Whereas with integers you will get all kinds of hits

Re: Unexpected downsides of UUID keys in PostgreSQL

#46

Post by totalhack »

kogus wrote: post #38
dewey wrote: post #32

Earlier quoted context omitted.

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

You can incorrectly join on any columns you want. That's not really the fault of the column types IMO, that's a problem in the layers above.

Re: Unexpected downsides of UUID keys in PostgreSQL

#47

Post by code_biologist »

VWWHFSfQ wrote: post #25
WeAddValue wrote: post #13

Earlier quoted context omitted.

Or use hashids.org. Use it to obfuscate your integer auto increment ids anywhere a user might see it.

It's a good option purely for cosmetics but don't rely on it for any kind of serious obscurity since it's trivially reversed. I've used it to great effect in the past to encode multiple integer values like start/end ID, sort order (0/1), etc. for cursor-based pagination. But that's only because there's nothing secret in those numbers. Just purely for convenience.

Salting your hash should work?

You could also use a 32 or 64 bit block cipher like skip32 if you want to prevent reversal. Or at least, it makes reversing non-trivial.

Re: Unexpected downsides of UUID keys in PostgreSQL

#48

Post by whack »

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…

> The moral of the story is that data locality matters, and it can pop up in the most surprising of places. Using random is typically the worst thing you can do for locality, so if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option

The author isn't suggesting abandoning UUIDs, he is suggesting using something like UUIDv7 which preserves locality. For most use-cases, this seems like a very reasonable recommendation with no significant downsides I can think of.

Re: Unexpected downsides of UUID keys in PostgreSQL

#49

Post by jmull »

gregwebs wrote: post #4

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

In my experience internal auto-increment ids and external random UUID works well.

Re: Unexpected downsides of UUID keys in PostgreSQL

#50

Post by dpcx »

totalhack wrote: post #46
kogus wrote: post #38

Earlier quoted context omitted.

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

You can incorrectly join on any columns you want. That's not really the fault of the column types IMO, that's a problem in the layers above.

True, but you can't incorrectly join on UUID due to its anti-collision nature.
Post reply on HN