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

PostgreSQL and UUID as Primary Key

maciejwalkowiak.com

Post reply on HN
Print view View original

271–280 of 345 posts

Re: PostgreSQL and UUID as Primary Key

#271

Post by stouset »

groestl wrote: post #259
stouset wrote: post #97

Earlier quoted context omitted.

UUIDv7 are good enough that you can just avoid thinking and use it as a “default”. The worst consequences of doing so are some slightly impacted performance and leak of some timing information but these are extremely minor drawbacks. Using completely random UUIDs is IMO the worst choice. It’s fine right up until it isn’t, and then you are stuck in hell with no good way out.

> Using completely random UUIDs is IMO the worst choice Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).

I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.

Re: PostgreSQL and UUID as Primary Key

#272

Post by grey-area »

laserDinosaur wrote: post #260
hu3 wrote: post #255

Earlier quoted context omitted.

This is exceptionally rare in most projects. I know of only one person in my entire career that had to do this. And they managed it just fine despite working with auto-incrementing big ints. Yet some folks advocate that all projects should pay an expensive insurance against this elusive event of two databases being merged.

>And they managed it just fine despite working with auto-incrementing big ints. I wonder how. I've had to do several big merges in my career, and it was always a nightmare because of all the external systems which were already referencing and storing those pre-existing ints. Sure, merging the databases is easy if you don't mind regenerating all the Id's, but it's not usually that simple.

Simplest way is to keep the identifiers from DB A and increment all the identifiers from DB B by an offset. Third parties complicates things of course but internally it can be pretty simple, so maybe they just didn't have too many third parties using the IDs.

Re: PostgreSQL and UUID as Primary Key

#273

Post by SpaghettiCthulu »

ako wrote: post #194
jerf wrote: post #105

Earlier quoted context omitted.

My Dream Web Framework, which for a variety of reasons was never and never will be built, has built-in functionality for obscuring IDs in some session-level map, so you can indicate through some sort of type that something is an ID and it automatically allocates some sort of randomized identifier on the way out and converts it back transparently on the way back in. Thus, not only would DB ids in principle never show…

That wouldn't work for integration scenarios where the other system need to store the ID so it can refer to it later. How would you provide IDs for integration purposes?

Not just integration with other services. It would completely break brower history too!

Re: PostgreSQL and UUID as Primary Key

#274

Post by hu3 »

grey-area wrote: post #272
laserDinosaur wrote: post #260

Earlier quoted context omitted.

>And they managed it just fine despite working with auto-incrementing big ints. I wonder how. I've had to do several big merges in my career, and it was always a nightmare because of all the external systems which were already referencing and storing those pre-existing ints. Sure, merging the databases is easy if you don't mind regenerating all the Id's, but it's not usually that simple.

Simplest way is to keep the identifiers from DB A and increment all the identifiers from DB B by an offset. Third parties complicates things of course but internally it can be pretty simple, so maybe they just didn't have too many third parties using the IDs.

That was it if I recall.

They wrote a small script with the logic involved in the merging. PKs and FKs of only one database had to be incremented by an offset of max(table.pk) + safe margin.

They did this for each table.

Once this script was tested multiple times with subsets of each database, they stopped production and ran the script against it (with backup fallbacks). A small downtime window in a Sunday.

And that was it. The databases never had to pay the UUID tax, before or after.

Re: PostgreSQL and UUID as Primary Key

#275

Post by blackenedgem »

valenterry wrote: post #257
blackenedgem wrote: post #250

Earlier quoted context omitted.

It's not even necessarily it being strictly monotonic. That part does help though as you don't need to skip rows. For me the bigger thing is the randomness. A uid being random for a given row means the opposite is true; any given index entry points to a completely random heap entry. When backfilling this leads to massive write amplification. Consider a table with rows taking up 40 bytes, so roughly 200 entries per pa…

Isn't that solved because UUIDv7 can be ordered by time?

Yeah pretty much, although ids can still be a little better. The big problem for us is that we need the security of UUIDs not leaking information and so v7 isn't appropriate.

We do use a custom uuid generator that uses the timestamp as a prefix that rotates on a medium term scale. That ensures we get some degree of clustering for records based on insertion time, but you can't go backwards to figure out the actual time. It's still a problem when backfilling and is more about helping with live reads.

Re: PostgreSQL and UUID as Primary Key

#276

Post by thiht »

cqqxo4zV46cp wrote: post #238
thiht wrote: post #200

Earlier quoted context omitted.

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

I don’t understand this thinking. If you understand what’s at play, you can infer the potential security implications. What you’re advocating for is being entirely reactive instead of also being proactive.

No, I don’t. Even with a timestamp uuids are not enumerable, and honestly I don’t care that the timestamp they were created at is public. Is the version of uuid used being a part of the uuid considered a leak too?

Re: PostgreSQL and UUID as Primary Key

#277

Post by thiht »

whynotmaybe wrote: post #244
thiht wrote: post #200

Earlier quoted context omitted.

I don’t understand how that’s an issue. Do you have an example of a possible attack using UUIDv7 timestamp? Is there evidence of this being a real security flaw?

The draft spec for uuid v7 has details about the security considerations : https://www.ietf.org/archive/id/draft-peabody-dispatch-new-u... The way I see it is that uuid v7 in itself is great for some use but not for all uses. You always have to remember that a v7 always carries the id's creation time as metadata with it, whether you want it or not. And if you let external users get the v7, they can get that metadata.…

That just seems overly cautious. I’d rather use UUIDv7 unless I have a reason not to. The convenience of sortable ids and increased index locality are very much worth the security issues associated with UUIDv7. Maybe I wouldn’t use UUIDv7 for tokens or stuff like that, but DB IDs seem pretty safe.

Re: PostgreSQL and UUID as Primary Key

#278

Post by groestl »

stouset wrote: post #271
groestl wrote: post #259

Earlier quoted context omitted.

> Using completely random UUIDs is IMO the worst choice Can't say that without context. I've worked in systems where even the version bits were randomized, and for good reason (i know, technically, no UUID anymore).

I’m talking about default choices. UUIDv7 has few significant downsides for most cases. UUIDv4 has serious downsides. There are obviously cases where the latter is appropriate but that will come from unique requirements.

How about secure by default?

Re: PostgreSQL and UUID as Primary Key

#279

Post by hu3 »

stoperaticless wrote: post #218
scotty79 wrote: post #9

Isn't B-Tree with UUIDv4 keys getting more balanced than with UUIDv7? Doesn't longer insert time result in faster searches later?

Related pondering: - UUIDv4 causes page fragmentation (ideally, on disk pages of data would be stored in sorted fashion; HDDs still exist) - if in your app fresh records have more reads, having them close will help with read speed (being cpu cache friendly)

To contribute if I may:

Page fragmentation from UUIDv4 can be desirable for SQLite because marking the same page as dirty can give SQLite little opportunity to sync it to disk. Causing contention. In this scenario your Write Ahead Log (WAL) file just grows and grows.

SQLite introduced WAL2 which means switching between 2 WAL files instead of one, helping to mitigate unbounded contention. But it's recent. Most tools don't have SQLite supporting that yet.

Re: PostgreSQL and UUID as Primary Key

#280

Post by sgarland »

big_whack wrote: post #143
sgarland wrote: post #122

Earlier quoted context omitted.

Postgres doesn't necessarily pad to 8 bytes; it depends on the next column's type. EDB has a good writeup on this ( https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/ ), but also here's a small example: CREATE TABLE foo (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid INT NOT NULL); CREATE TABLE bar (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, iid BIGINT NOT NULL); CREATE TABLE baz (id BIGINT GENERATED…

You seem to think you're disagreeing with me but afaict you're just demonstrating my point, unless your point is just about how (int, int) will get packed. That's what I meant about the column order of indexes. If you have two ints and a bigint, but you need to index it like (int, bigint, int), then you aren't gaining anything there either. As your example shows, there is no benefit in index size (e.g for supporting…

I misunderstood your post, I think. I re-ran some experiments with a single-column index on SMALLINT, INT, and BIGINT. I'm still not sure why, but there is a significant difference in index size on SMALLINT (7.3 MB for 1E6 rows) vs. INT and BIGINT (21 MB for each), while the latter two are the exact same size. I could get them to differ if I ran large UPDATEs on the table, but that was it.
Post reply on HN