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

81–90 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#81

Post by Timpy »

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.

Wow I really love that idea. Next time I get stuck on a fix tinkering with my setup I'm going to check this out.

Re: Unexpected downsides of UUID keys in PostgreSQL

#82

Post by [deleted] »

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…

[deleted]

Re: Unexpected downsides of UUID keys in PostgreSQL

#83

Post by marginalia_nu »

daneel_w wrote: post #10

Nothing unexpected about it at all. PostgreSQL's default index in this case is a B-tree, and they don't index disorderly data very well which is the nature of all UUID versions. On the topic of how important it is for B-trees to be "orderly": https://news.ycombinator.com/item?id=34404641 PostgreSQL can do (among other types) 32-bit hash indices which work out better for certain use cases. Personally I would avoid B-t…

> On the topic of how important it is for B-trees to be "orderly": https://news.ycombinator.com/item?id=34404641

I don't think this is the same phenomenon at all, if anything that looks like an implementation problem rather than something that's somehow inherent in B-trees.

Depending on how you construct the tree, it's however still possible to end up with something that's very fragmented and inefficient, but you can always construct a dense b-tree with a space complexity of O(N/(B-1)). To see why you can just lay out the data in a list and manually create the index layers. Each layer will be bounded by N/B, N/B², N/B³, ... (and the sum of 1/B^n over all n is 1/(B-1) )

Considering an actual tree, for N=10, B=3

  1 2 3  4 5 6  7 8 9   10    data
Looking at this, I think it should be apparent that if you change this ordered sequence 1-10 to ten UUIDs ordered in the same way, there would be no change in the structure of the tree.

It would be a bit bigger on disk because UUIDs are bigger than integers, and you may end up with an additional data layer because of that (because you want to align with the disk block size).

One reason you might see a difference even when the implementation makes the appropriate assumptions is that B-trees can do very clever things with somewhat sequential data when it comes to joins, where you can get linear nearly runtimes for the operation. But as mentioned, this only works with relatively ordered data.

Re: Unexpected downsides of UUID keys in PostgreSQL

#84

Post by pfooti »

Weirdly, and this may be a side effect of writing code that uses Cloud Spanner, but I tend to think of uuidv4's random distribution through keyspace as a _good_ thing.

My instinct is to not use sequential-ish indices/primary keys, because I don't want to hotspot one part of the storage with all my writes for today in the same tablet.

Re: Unexpected downsides of UUID keys in PostgreSQL

#85

Post by ramchip »

jongjong wrote: post #66
gradys wrote: post #51

Earlier quoted context omitted.

You can achieve this without UUID keys if you attach UUIDs to the requests. This has the advantage of working for all kinds of mutating requests, not just creates, but requires that you store these request UUIDs for some period of time as well.

It's still not as elegant because it doesn't work in a multi-process or multi-server back end; if the request fails, you need to make sure that the retry request will hit the same server process (assuming you're keeping the UUIDs in memory on the server). You can use some in-memory data store like Redis to share the request UUIDs across multiple processes/hosts but that can add a lot more complexity, latency and risk…

You can use the DB for this as well, just make a table e.g. "requests" with two columns, the user and the request token. Old entries can be purged on a cron job.

If you add a column to store params as well then you can also do better validation:

> Responding when a customer changes request parameters on a subsequent call where the client request ID stays the same

> We design our APIs to allow our customers to explicitly state their intent. Take the situation where we receive a unique client request token that we have seen before, but there is a parameter combination that is different from the earlier request. We find that it is safest to assume that the customer intended a different outcome, and that this might not be the same request. In response to this situation, we return a validation error indicating a parameter mismatch between idempotent requests. To support this deep validation, we also store the parameters used to make the initial request along with the client request identifier.

https://aws.amazon.com/builders-library/making-retries-safe-...

Re: Unexpected downsides of UUID keys in PostgreSQL

#86

Post by danpalmer »

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…

> I can't think of many use cases where I would sacrifice the beauty and elegance of UUIDs...

I find auto-incrementing IDs far more elegant in many ways. They are much easier to make sense of for users, they give you some loose metadata (ordering, sometimes a rough time range) which can be handy in debugging stuff.

At my previous workplace most things were auto-incrementing IDs, and while we got bitten by them a few times, there was significant debugging value in seeing a user ID as an integer. Internally, order IDs and customer support ticket IDs were also auto-incrementing, but we offset them so that even numbers were orders and odd numbers were support tickets, and this helped quite a bit with debugging or even just non-technical users sending each other references. Not that this can't be done with prefixes on UUIDs with a bit of extra work.

I wouldn't necessarily recommend auto-incrementing IDs or UUIDs over the other, but I don't think one is more elegant at all.

> to optimize access times by a millisecond or two. UUID is totally worth the cost.

2ms * 20 queries is 40ms, which could take a typical page load for a web app from 150ms to 190ms, which is quite a regression.

Re: Unexpected downsides of UUID keys in PostgreSQL

#87

Post by insanitybit »

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 locality can be, for some workloads, a significant loss. Where UUIDs reign supreme for performance is in terms of generation - if you have a super high write load and really high latency requirements it may not be viable to have a single integer counter.

Although, in my own testing, I've found Postgres is more than capable of hundreds of thousands of increments per second and if you're willing to allow for gaps/ interleaving in your counter (slight hit to locality) you're really unlikely to hit a bottleneck.

Sequentual uuids help and are desirable but they still can't compete on storage :)

Re: Unexpected downsides of UUID keys in PostgreSQL

#88

Post by babbledabbler »

damethos wrote: post #67
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…

> 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 How soon is soon? And how would you handle it if you already had all PKs as UUIDs

Probably when there is some downtime or as the opportunity presents.

I would use a date field to do ordering or a sequenced field when needed.

Re: Unexpected downsides of UUID keys in PostgreSQL

#89

Post by ok123456 »

insanitybit wrote: post #80
ok123456 wrote: post #75

Earlier quoted context omitted.

Then just make a generated column that's a 32bit integer hash of the uuid for this particular case and create an index on that? Use that during expensive queries that blow up your cache locality if it matters.

A 32bit integer hash won't have locality either.

Ok. Pick a resolution where you won't get collisions and that is a native datatype.
Post reply on HN