That's really an important deficiency of Postgres.
Hash index is ideally suited for UUIDs but for some reason Postgres hash indexes cannot be unique.
Avoid UUID Version 4 Primary Keys in Postgres
111–120 of 463 posts
Re: Avoid UUID Version 4 Primary Keys in Postgres
#112Earlier quoted context omitted.
Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.
I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to. I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID. Isn't that clear enough?
That's the creation date of that guid though. It doesn't say anything about the entity in question. For example, you might be born in 1987 and yet only get a social security number in 2007 for whatever reason.
So, the fact that there is a date in the uuidv7 does not extend any meaning or significance to the record outside of the database. To infer such a relationship where none exists is the error.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#113A prime example of premature optimization. Permanent identifiers should not carry data . This is like the cardinal sin of data management. You always run into situations where the thing you thought, "surely this never changes, so it's safe to squeeze into the ID to save a lookup". Then people suddenly find out they have a new gender identity, and they need a last final digit in their ID numbers too. Even if nothing c…
Uuid v7 just has a bias in its generation; it isn't carrying information. You're not going to try and extract a timestamp from a uuid.
Random vs time biased uuids are not a decision to shave off ms that you will regret.
Most likely they will be a decision that shaves off seconds (yes, really - especially when you consider locality effects) and you'll regret nothing.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#114Earlier quoted context omitted.
Your comment is sufficiently generic that it’s impossible to tell what specific part of the article you’re agreeing with, disagreeing with, or expanding upon.
I disagree that performance should be a reason to choose running numbers over guids until you absolutely have to. I think IDs should not carry information. Yes, that also means I think UUIDv7 was wrong to squeeze a creation date into their ID. Isn't that clear enough?
UUID v7 doesn't squeeze creation date in. If you treat it as anything other than a random sequence in your applications, you're just wrong.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#115The article sums up some valid arguments against UUIDv4 as PKs but the solution the author provides on how to obfuscate integers is probably not something I'd use in production. UUIDv7 still seems like a reasonable compromise for small-to-medium databases.
I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…
You shouldn't generally use PKs as public identifiers, least of all UUIDs, which are pretty user hostile.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#116Earlier quoted context omitted.
What are these certain problems, if I may ask?
A major one for me is preventing duplicate records. If the client POSTs a new object to insert it into the database; if there is a connection failure and the client does not receive a success response from the server, the client cannot know whether the record was inserted or not without making an expensive and cumbersome additional read call to check... The client cannot simply assume that the insertion did not happe…
Preferably, you would design you APIs and services to be idempotent (ie. use PUT not POST etc.)
Using idempotency identifier is the last resort in my book.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#117Earlier quoted context omitted.
Especially in larger systems, how does one solve the issue of reaching the max value of an integer in their database? Sure for unsigned bigint thats hard to achieve but regular ints? Apps quickly outgrow that.
OK... but that concern seems a bit artificial.. if bigints are appropriate: use them. If the table won't get to bigint sizes: don't. I've even used smallint for some tables I knew were going to be very limited in size. But I wouldn't worry about smallint's very limited number of values for those tables that required a larger size for more records: I'd just use int or bigint for those other tables as appropriate. The…
Idk I use whatever scales best and that would be an close to infinite scaling key. The performance compromise is probably zeroed out once you have to adapt ur database to a different one supporting the current scale of the product. Thats for software that has to scale. Whole different story for stuff that doesnt have to grow obviously. I am in the UUID camp too but I dont care whether its v4 or v7.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#118My advice is: Avoid Blanket Statements About Any Technology. I'm tired of midwit arguments like "Tech X is N% faster than tech Y at performing operation Z. Since your system (sometimes) performs operation Z, it implies that Tech X is the only logical choice in all situations!" It's an infuriatingly silly argument because operation Z may only represent about 10% of the total CPU usage of the whole system (averaged out…
Yep. We have tables that use UUIDv4 that have 60M+ rows and don't have any performance problems with them. Would some queries be faster using something else? Probably, but again, for us it's not close to being a bottleneck. If it becomes a problem at 600M or 6B rows, we'll deal with it then. We'll probably switch to UUIDv7 at some point, but it's not a priority and we'll do some tests on our data first. Does my experience mean you should use UUIDv4? No. Understand your own system and evaluate how the tradeoffs apply to you.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#119Another interesting article from Feb-2024 [0] where the cost of inserting a uuid7() and a bigint is basically the same. To me it wasn't quite clear what the problem with the buffer cache is but the author makes it much more clear than OP's article:
> We need to read blocks from the disk when they are not in the PostgreSQL buffer cache. Conveniently, PostgreSQL makes it very easy to inspect the contents of the buffer cache. This is where the big difference between uuidv4 and uuidv7 becomes clear. Because of the lack of data locality in uuidv4 data, the primary key index is consuming a huge amount of the buffer cache in order to support new data being inserted – and this cache space is no longer available for other indexes and tables, and this significantly slows down the entire workload.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#120Earlier quoted context omitted.
I tend to avoid UUIDv7 and use UUIDv4 because I don't want to leak the creation times of everything. Now this doesn't work if you actually have enough data that the randomness of the UUIDv4 keys is a practical database performance issue, but I think you really have to think long and hard about every single use of identifiers in your application before concluding that v7 is the solution. Maybe v7 works well for some t…
Out of curiosity, why is it an issue if you leak creation time?
If your system (pseudo-) random number generator (RNG) is compromised to derive a portion of its entropy from things that are knowable by knowing the time when the function ran, then the search space for cracking keys created around the same time can be shrunken considerably.
This doesn’t even rely on your system’s built-in RNG being low quality. It could be audited and known to avoid such issues but you could have a compromised compiler or OS that injects a doctored RNG.