Unexpected downsides of UUID keys in PostgreSQL • Hacker News Board

Re: Unexpected downsides of UUID keys in PostgreSQL

#111

Post by egeozcan » Thu, Jun 22, 2023, 2:35 PM UTC

coredog64 wrote: post #103

jongjong wrote: post #95

Earlier quoted context omitted.

Yes exactly, with proper sharding, raw performance is not as important; to some extent, you trade it away for improved concurrency. In fact, I struggle to see how one would implement sharding with auto-incrementing integers (you would get ID collisions for different resources across different shards/database instances); there needs to be a way to uniquely refer to resources across potentially multiple databases and U…

We did auto-increment integers with multiple servers like 20 years ago: The caveat is that you have to know how many servers are in the set in advance. Each server increments by the population size, and their starting number is their position within the pool. Not hyper scale, but good enough for failover or a 3-5 node setup.

Hi/Lo algorithm also works fine for most cases

Re: Unexpected downsides of UUID keys in PostgreSQL

#112

Post by insanitybit » Thu, Jun 22, 2023, 2:36 PM UTC

fabian2k wrote: post #110

insanitybit wrote: post #87

Another (potentially!) significant problem with uuids is that they're inefficient to store. Compared to an 8 byte value obviously they double the storage size. But it's actually worse - if you have loosely ordered integers, even with gaps, you can compress those down to even less, like practically 1 byte on average. That makes uuids ~16x worse for disk storage, ~2x worse for memory storage (cache). That + Losing loca…

I'm not aware of any situation where Postgres (without extensions) would compress ints that way? That is more of a column-oriented or time-series DB thing to me. Are there any cases in Postgres where this actually plays a role and sequential ints get compressed?

Sorry, let me clarify. I used Postgres to generate the counters as external IDs into other data stores (so instead of `give me a new uuid` I said `hey postgres give me a new counter`. Postgres was only used to maintain the counters themselves in a tiny database where compression isn't relevant anyways.

The other data stores where I used this were a combination of Parquet on S3 (where you get compression) and ScyllaDB (where you also get compression).

Re: Unexpected downsides of UUID keys in PostgreSQL

#113

Post by adverbly » Thu, Jun 22, 2023, 2:36 PM UTC

Pet_Ant wrote: post #73

Timpy wrote: post #71

For me, the visual noise is a downside for UUIDs. A lot of time investigating data issues means glancing at query results and deciding if something looks unexpected. I just can't parse a UUID with my eyes that quick. I've come up against this at my current job a little too often and I'm cursing the decision to switch to UUIDs. I know, it's a balancing act of competing concerns. But for us moving to UUIDs was future p…

Something I saw a longtime ago was hashing UUIDs into a RGB value and then colouring the background of the cell (at least when viewing in a DB IDE). That way you can quickly at a glance tell if it's worth going over character by character.

Holy crap this is brilliant. I'd just change the text color of the uuid itself though - no need to change the background(although you'd need to make sure that the hash function avoided your existing background color to maintain a high enough contrast ratio for readability).

I want this as a vscode extension.

Or maybe as a terminal plugin or something? Is that possible? Could tmux do it maybe?

Re: Unexpected downsides of UUID keys in PostgreSQL

#114

Post by babbledabbler » Thu, Jun 22, 2023, 2:37 PM UTC

gregwebs wrote: post #102

babbledabbler wrote: post #41

Earlier quoted context omitted.

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…

I personally find the numeric id extremely valuable for internal data analysis and sharing. I can refer to rows by the numeric id, including a range of rows, and seeing the ids gives exactly that intuitive information about it's relation in the set that we are hiding from end users. Numeric ids can also be used for the same reason in an admin-only UI. On the efficiency side, joining and querying by id is generally mo…

This was generally the reason I went with numeric ID as PK originally. It makes working with and analyzing the data as well as cross referencing relations easier.

For all my tables I have a base schema that looks something like this.

id: integer sequence PK uuid: uuidV4 created_at: datetime updated_at: datetime

The concern I have is when I have to distribute my system when scaling. Those numeric IDs will have to be replaced with the UUIDs so I figure I might as well do it now.

Re: Unexpected downsides of UUID keys in PostgreSQL

#115

Post by yesimahuman » Thu, Jun 22, 2023, 2:41 PM UTC

xmdx wrote: post #19

That's what created_at and updated_at are for surely

I was going to ask, when would you only rely on a sequential id for temporal locality, wouldn't most people be indexing and sorting on `created_at` or something equivalent?

Re: Unexpected downsides of UUID keys in PostgreSQL

#116

Post by gmhafiz » Thu, Jun 22, 2023, 2:43 PM UTC

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…

Expanding on temporal information leak

1. Size: If a client receives a record with id=10004578, they can guess that 4578 orders have been made.

2. Rate of growth: Receiving two different orders means they can track the growth rate of record insertion.

And also

Iteration attack: If your API endpoints do not have authorization, an attacker can try to access with GET /api/users/1, GET /api/users/2, GET /api/users/3, etc. UUID makes this next to impossible.

Re: Unexpected downsides of UUID keys in PostgreSQL

#117

Post by gmhafiz » Thu, Jun 22, 2023, 2:45 PM UTC

rjh29 wrote: post #33

zero_iq wrote: post #23

Article should be called "Totally Expected Downsides..." If you want temporal locality, use ULIDs instead.

I was going to use UUID with the time portion at the start (also known as UUIDv7) but this looks better.

One potential downside is ULID does not have an RFC, unlike UUID V7

Re: Unexpected downsides of UUID keys in PostgreSQL

#118

Post by remram » Thu, Jun 22, 2023, 2:47 PM UTC

fauigerzigerk wrote: post #77

whack wrote: post #48

Earlier quoted context omitted.

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

I agree that UUIDv7 will usually be the best choice. The one downside I can think of is that it may sometimes be necessary to let people know the identity of a thing without also telling them exactly when that thing was created.

Encrypt it. Block ciphers are bijective so they will turn sequential IDs into unique random IDs in a way you can easily reverse.

You can also do that to get opaque identifiers from an auto-increment primary key.

Re: Unexpected downsides of UUID keys in PostgreSQL

#119

Post by babbledabbler » Thu, Jun 22, 2023, 2:50 PM UTC

moonchrome wrote: post #74

babbledabbler wrote: post #41

Earlier quoted context omitted.

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…

That sounds like a very simplistic framework and I'm sure you could do some metaprogramming to abstract boilerplate. Like you couldn't do database multitenancy with those constraints. I've used the int keys and UUID public keys on multiple projects - it wasn't an issue for EF core or RoR

I'm simplifying a bit for brevity and we can do some abstraction to handle it so it's not that the framework is simplistic. I'm just having trouble justifying adding this complexity with two types of IDs.

Re: Unexpected downsides of UUID keys in PostgreSQL

#120

Post by remram » Thu, Jun 22, 2023, 2:56 PM UTC

jongjong wrote: post #64

ahoka wrote: post #43

Earlier quoted context omitted.

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

I hope devs and security consultants will see it that way. I can easily envision a future where security consultants would write reports flagging systems as 'unsecure' on the grounds that they use 'an outdated UUID version'. Security consultants can be quite blunt in their approach and companies will often yield to their every demand for the sake of easy compliance and to avoid having to explain stuff.

I wish they could feel that way about IP versions...