91–100 of 345 posts
It would be nice for these comparisons to also include 'int64' so people can see how much of an overhead UUID's are compared to the traditional approach.
The problem here is that auto increment ints are guessable. The size of that problem depends on your situation
Call me old fashion but I really like integer autoincrement primary keys. It's easy to understand and obviously simple to sort. Furthermore when working on large batch projects you can just store the last primary key as your high water mark and get everything greater than that. I suppose TSID works to this end, but certainly more complicated.
It is simpler but like everything else it depends on the application. For a private app you can generally get away with it. Something that's more public facing? The ID will most likely leak information. As systems mature and you introduce things like replication, having IDs that are more universal starts looking good. In general, starting off with a uuid like uuid v4 or nanoid is a good bet.
Earlier quoted context omitted.
Information leakage since they have a timestamp component. Some people may not care, but plenty of folks do. As others have said, anything security related likely should UUIDv4. UUIDv7 is basically an engineering compromise on security (they leak a timestamp) vs performance (random reads and writes to an index aren’t as performant as localized reads and writes).
Thanks, I get it. Do you have any example where leaking a timestamp could pose a security risk? I can't think of any.
If you're generating random UUIDs as the primary key, how do you not run into key collisions? Having to search the entire table before inserting is slow, and catching the error and trying again is also annoying.
So if you are using it as a PK value, only ever insert and if there is a duplicate, blow up loudly.
Performance wise, that isn’t particularly impactful IMO.
Earlier quoted context omitted.
I haven't maintained any sizable database system without any issues, least of all performance ones. I call BS.
lol, I of course don't mean that I have no issues, performance or otherwise. Only that I have never had an issue with UUIDs in postgres
And memory is the key factor of any sizable database.
Earlier quoted context omitted.
It’s the only reason I could think of for why you wouldn’t use a bigserial column. Maybe there is another?
Information leakage - it leaks how many records have been created [ https://en.m.wikipedia.org/wiki/German_tank_problem ]
> IMO using bigserial by default is wrong. Use whatever data type is appropriate. Not every table will grow to 4 billion rows and not every table will grow to even 60k rows
The implication I took from that was that they were suggesting using serial over bigserial. My comment was pushing back on that.
My somewhat naive understanding was that random UUIDs were not that big of a deal in Postgres because it does not cluster by primary key. And of course a UUID (16 bytes) is larger than a serial (4 bytes) or bigserial (8 bytes) by a factor of 2-4 . This certainly might matter for an index, but on a whole table level where you have 20+ bytes overhead per row this doesn't seem that big of a deal for anything except very…
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.
The article had a link to the PostgreSQL commitfest for UUIDv7 support, but as far as I can't tell it looks unlikely that it will actually be in PostgreSQL 17. The most recent action was the committer being removed from the task and I believe version 17 is already well past feature freeze.... Is my understanding correct? This is what I think is going on, but I can't find any substantiated facts too point me to a defi…
Just a heads up about UUID 7.. be careful when using. par the RFC If UUIDs are required for use with any security operation within an application context in any shape or form then [RFC4122] UUIDv4 SHOULD be utilized.
Unfortunately, UUIDv4 is often specifically prohibited for some sensitive applications due to many cases of broken RNGs undermining the security guarantees of UUIDv4. Deterministic generation plus encryption is a common choice in these cases.
Another day, another article saying not to use UUIDs as PKs. I've maintained systems using UUIDs stored as char(36) with million record tables without issue - This is not an endorsement, just explaining that this is bikeshedding. Should you use v7 when you can? Sure. Would int/bigint be faster in your benchmarks? Sure. But the benefits totally outweigh the speed differences until you get to a very large system. But i…
If you are using PG, simply using it's native UUID type instead of char(36) seems like a no-opportunity-cost obvious optimization choice at least though, if you have a choice?