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

161–170 of 215 posts

Re: Unexpected downsides of UUID keys in PostgreSQL

#161

Post by patrec »

ok123456 wrote: post #124
patrec wrote: post #121

Earlier quoted context omitted.

I feel that you don't understand what locality is about: the property of IDs that were generated closely together in time to be close together numerically. You don't get that by "hashing" the UUID (which makes no sense anyway, since you might as well just take some truncation of the UUID). In fact the whole idea behind a hash is to destroy this property of the input data. The reason the numerical locality matters is…

I thought you were talking about cache locality within the CPU. Real cache locality. If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.

How would adding a BRIN index help in any way with reducing the discussed problems, such as write amplification?

Re: Unexpected downsides of UUID keys in PostgreSQL

#162

Post by pgaddict »

rollcat wrote: post #96
willvarfar wrote: post #53

> if you want to use UUID’s, try to use a sequential variant. UUID v7 is a good option. Hopefully it’s coming to PostgreSQL 17 Yeap, UUIDv7 directly addresses the issues in the article. Warmly recommended. (There is the issue that exposing ULIDs to users may 'leak' information about when things were created etc, but that is usually not a problem.)

I also like UUIDv8. It's broadly similar to v7 but the vendor is free to define how the timestamps are represented, how many bits to split between timestamps/randomness, define own packing, etc. So we've started making UUIDs that encode the current ISO8601 date+time in a human-readable format: YYYYMMDD-HHMM-VRRR-RRRR-RRRRRRRRR This is especially useful for things you have few of (no more than a couple per minute), th…

The one problem with this "perfectly" sequential UUIDs is that it can easily lead to index bloat. Imagine you have such sequential UUIDs generated over a year, for example. And then you delete e.g. 99% of old data (say, everything except some records that you're required to keep for audit purposes or whatever).

If there was an index, the "old" part will be 99% empty. For regular UUIDs this would be fine, because new entries would get routed to this part of the index and the space would be reused. Not so for sequential UUIDs (v7/v8).

This is mostly why year ago I wrote "sequential-uuids" extension, doing roughly what v7/v8 do, but wrapping the timestamp once in a while.

Of course, if you don't delete data, this is not an issue and v7/v8 will work fine.

Re: Unexpected downsides of UUID keys in PostgreSQL

#163

Post by marcosdumay »

nine_k wrote: post #152
pgaddict wrote: post #54

Earlier quoted context omitted.

It is not a matter of a couple milliseconds. The loss of locality for reads is bad, especially for data sets that don't fit into cache / RAM (while the active set would). Where it really bites you is writes, because it can trigger pretty massive write amplification. Imagine you have 128 GB index on UUID column, that's ~16M pages (8kB) and insert 1M random values. Congrats! You've probably just wrote 8GB to the WAL, b…

If you want locality, speed, simplicity, etc above all, use an incremented integer and be done with it. UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price. In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lowe…

> Where you e.g. cannot coordinate the creation of your primary keys.

You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online?

> Or where you cannot allow them to be predictable.

You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a different column will still have some of the problems with index maintenance, but it becomes a much smaller problem if only one table cares about the value.

Re: Unexpected downsides of UUID keys in PostgreSQL

#164

Post by samokhvalov »

We discussed some of these problems here during a Postgres.tv session, and developed a patch to support ULID / UUIDv7: https://www.youtube.com/watch?v=YPq_hiOE-N8

And then Andrey proposed a patch in the pgsql-hackers mailing list: https://www.postgresql.org/message-id/flat/CAAhFRxitJv%3DyoG..., https://commitfest.postgresql.org/43/4388/

Everyone who can help (test, discuss, etc.) – please participate in that discussion.

The standard is not finalized yet, but there some expectations that it will be, if it happens, it would be great to have this in future Postgres 17.

Re: Unexpected downsides of UUID keys in PostgreSQL

#165

Post by pgaddict »

jongjong wrote: post #95
catlifeonmars wrote: post #76

Earlier quoted context omitted.

At scale you’re probably sharded across multiple DBs and you’re already operating through replicas. Point being you’re less likely to hit a warm cache as you scale up anyway as your application layer gets load balanced to different DB endpoints.

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…

I respectfully disagree with the notion that sharding makes resource usage somehow less important. Sure, it allows you to overcome the limits that would apply to a single node, but if you stop caring about using resources efficiently (e.g. memory / disk / network bandwidth), it's not very different from building huge and expensive single boxes.

Also, I don't think the blog (or me) suggested going back to using autoincrement IDs. There are other (better) options.

Re: Unexpected downsides of UUID keys in PostgreSQL

#166

Post by nine_k »

marcosdumay wrote: post #163
nine_k wrote: post #152

Earlier quoted context omitted.

If you want locality, speed, simplicity, etc above all, use an incremented integer and be done with it. UUIDs belong where you can't afford that simplicity. Where you e.g. cannot coordinate the creation of your primary keys. Or where you cannot allow them to be predictable. There you pay the price. In practice I noticed that the size of PKs and their poor locality start to play a role only after a huge basket of lowe…

> Where you e.g. cannot coordinate the creation of your primary keys. You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online? > Or where you cannot allow them to be predictable. You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a d…

You can almost always opt for artificial PKs that are more performant.

But sometimes you have to make these keys public, e.g. as user or other resource IDs. You want to make them UUIDs so they won't be predictable. Not having to join everywhere with the UUID-to-artificial-PK table may be a bigger performance win than the losses from larger size of UUIDs.

Sometimes you have a distributed / sharded system, and don't want the keys to clash, and also avoid assigning ranges. Sometimes you have to accept someone else's ID, not originating in your system. In cases like that, large random numbers, e.g. UUID v4, work reasonably well.

Of course when you just have one DB, and a relative slow stream of new rows, it's easy to fully control PK creation. And this covers the majority of practical cases.

Re: Unexpected downsides of UUID keys in PostgreSQL

#167

Post by pgaddict »

patrec wrote: post #161
ok123456 wrote: post #124

Earlier quoted context omitted.

I thought you were talking about cache locality within the CPU. Real cache locality. If you care about the insert location, why not just add a brin index on a timestamp field and use that instead of assuming that the index is sequential.

How would adding a BRIN index help in any way with reducing the discussed problems, such as write amplification?

This is a bit confusing, as it mixes two things - BRIN index and index on a timestamp.

The main source of write amplification comes from updating random pages of the btree index. Imagine inserting 10 random UUID values into a large index - it's pretty likely those will go into 10 different leaf pages. And every first update of a page after a checkpoint (which typically happens every 30 minutes or so), we have to write a FPI (i.e. the whole 8kB page) to WAL. So because btrees are based on ordering, random values end up on random leaf pages, causing write amplification.

If you have BRIN index on UUID column, this does not happen, because the index is not based on ordering but location in the table. If the 10 rows get appended to the same table page, that'll be just 1 write, with one FPI.

This is why BRIN does not have the write amplification issue. But it's also a bit pointless, because BRIN on random data is pretty useless for querying (Well, at least the minmax indexes, are. Let's ignore BRIN bloom indexes here.)

If you create BRIN on timestamp, that's not going to have write amplification problem, and it'll be good for querying. The thing is - BTREE would not have write amplification problem either, because the timestamps are going to be sequential (hence no updates to random leaf pages).

Re: Unexpected downsides of UUID keys in PostgreSQL

#168

Post by apelapan »

michaelsalim wrote: post #78
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…

Interesting take. I do agree there's some visual noise there, but not how you described it. UUID just takes so much screen space compared to its integer counterpart. If you don't have a good UI sorting things out it's quite annoying. Otherwise it's pretty great.

Visual noise and screen space hogging annoy me alot. Especially when you end up with multiple UUID:s on the same log line.

If one doesn't truly need distributed creation of globally unique identifiers, it is so much nicer with base35-encoded integer sequences. Preferably loosely based on a timestamp.

Re: Unexpected downsides of UUID keys in PostgreSQL

#169

Post by ComputerGuru »

arp242 wrote: post #150
michaelsalim wrote: post #78

Earlier quoted context omitted.

Interesting take. I do agree there's some visual noise there, but not how you described it. UUID just takes so much screen space compared to its integer counterpart. If you don't have a good UI sorting things out it's quite annoying. Otherwise it's pretty great.

It's kind of a tooling problem; no reason tooling can't do the same as git and display "1509af9" instead of "1509af9af2634d16f8d9b98e01a0166a49185474". Also I wish these sort of things would get encoded in base-36 (0-9 a-z) instead of base-16; that would help too.

Ulid is an ordered UUID canonically rendered in not Base36 but Crockford's Base32 (to make it easier to read then write or read aloud or parse and error correct).

Re: Unexpected downsides of UUID keys in PostgreSQL

#170

Post by jgraettinger1 »

nine_k wrote: post #166
marcosdumay wrote: post #163

Earlier quoted context omitted.

> Where you e.g. cannot coordinate the creation of your primary keys. You are writing your data into a DBMS. Coordinating the creation of primary keys is one of the cheapest tasks around, if you can't do that, how is your database still online? > Or where you cannot allow them to be predictable. You don't need to export your PKs for the rest of the world. You can have non-predictable data outside of your PK. Yes, a d…

You can almost always opt for artificial PKs that are more performant. But sometimes you have to make these keys public, e.g. as user or other resource IDs. You want to make them UUIDs so they won't be predictable. Not having to join everywhere with the UUID-to-artificial-PK table may be a bigger performance win than the losses from larger size of UUIDs. Sometimes you have a distributed / sharded system, and don't wa…

We use a macaddr8 that embeds a wall-clock timestamp (so they're ascending order, achieving data locality) with some additional shard and sequence-number bits. It's worked really well for us:

https://github.com/estuary/flow/blob/master/supabase/migrati...

we use macaddr8 instead of bigint, because it has a postgres serialization / JSON encoding which lossless-ly round-trips with browsers and it works well with PostgREST. The same CANNOT be said for bigint, which is a huge footgun.

Post reply on HN