Earlier quoted context omitted.
So the UUID could be used as an imperfect indicator of a records created time?
UUIDv7 but not UUIDv4.
I suppose timing attacks become an issue too.
Avoid UUID Version 4 Primary Keys in Postgres
41–50 of 463 posts
Re: Avoid UUID Version 4 Primary Keys in Postgres
#42To summarise the article: in PG, prefer using UUIDv7 over UUIDv4 as they have slightly better performance. If you're using latest version of PG, there is a plugin for it. That's it.
You might have missed the big H2 section in the article: "Recommendation: Stick with sequences, integers, and big integers" After that then, yes, UUIDv7 over UUIDv4. This article is a little older. PostgreSQL didn't have native support so, yeah, you needed an extension. Today, PostgreSQL 18 is released with UUIDv7 support... so the extension isn't necessary, though the extension does make the claim: "[!NOTE] As of Po…
Sticking with sequences and other integer types will cause problems if you need to shard later.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#43Earlier quoted context omitted.
*edit: sorry, misread that. My answer is not valid to your question. original answer: because if you dont come up with these ints randomly they are sequential which can cause many unwanted situations where people can guess valid IDs and deduce things from that data. See https://en.wikipedia.org/wiki/German_tank_problem
So We make things hard in the backend because of leaky abstractions? Doesn't make sense imo.
Decades of security vulnerabilities and compromises because of sequential/guessable PKs is (only!) part of the reason we're here. Miss an authorization check anywhere in the application and you're spoon-feeding entire tables to anyone with the inclination to ask for it.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#44"if you use PostgreSQL" (in the scientific reporting world this would be the perennial "in mice")
The space requirement and index fragmentation issue is nearly the same no matter what kind of relational database you use. Math is math. Just the other day I delivered significant performance gains to a client by converting ~150 million UUIDv4 PKs to good old BIGINT. They were using a fairly recent version of MariaDB.
If they can live with making keys only in one place, then sure, this can work. If however they need something that is very highly likely unique, across machines, without the need to sync, then using a big integer is no good.
if they can live with MariaDB, OK, but I wouldn't choose that in the first place these days. Likely Postgres will also perform better in most scenarios.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#45Earlier quoted context omitted.
The article mentions microservices, which can increase the likelihood of collisions in sequential incremental keys. One more reason to stay away from microservices, if possible.
Always try to avoid having two services using the same DB. Only way I'd ever consider sharing a DB is if only one service will ever modify it and all others only read.
Good luck enforcing that :)
Re: Avoid UUID Version 4 Primary Keys in Postgres
#46From the fine article: > Random values don’t have natural sorting like integers or lexicographic (dictionary) sorting like character strings. UUID v4s do have "byte ordering," but this has no useful meaning for how they’re accessed. Might the author mean that random values are not sequential, so ordering them is inefficient? Of course random values can be ordered - and ordering by what he calls "byte ordering" is exa…
The point is how closely located data you access often is. If data is roughly sorted by creation time then data you access close to one another in time is stored close to one another on disk. And typically access to data is correlated with creation time. Not for all tables but for many.
Accessing data in totally random locations can be a performance issue.
Depends on lots of things ofc but this is the concern when people talk about UUID for primary keys being an issue.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#47My 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…
Wasn't choosing uuids as ids falling for the deceptive argument in the first place?
Not really, no. They’re very convenient for certain problems and work really well in general. I’ve never had a performance issue where the problem boiled down to my use of UUID.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#48Earlier quoted context omitted.
You might have missed the big H2 section in the article: "Recommendation: Stick with sequences, integers, and big integers" After that then, yes, UUIDv7 over UUIDv4. This article is a little older. PostgreSQL didn't have native support so, yeah, you needed an extension. Today, PostgreSQL 18 is released with UUIDv7 support... so the extension isn't necessary, though the extension does make the claim: "[!NOTE] As of Po…
Sticking with sequences and other integer types will cause problems if you need to shard later.
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.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#49Hi, a question for you folks. What if I don’t like to embed timestamp in uuid as v7 do? This could expose to timing attacks in specific scenarios. Also is it necessary to show uuid at all to customers of an API? Or could it be a valid pattern to hide all the querying complexity behind named identifiers, even if it could cost a bit in terms of joining and indexing? The context is the classic B2B SaaS, but feel free to…
Wouldn't you need to expose UUID if you want to make use of optimistic locking?
I feel that this is among the good reasons to keep exposing UUID in the API.
Re: Avoid UUID Version 4 Primary Keys in Postgres
#50"if you use PostgreSQL" (in the scientific reporting world this would be the perennial "in mice")
The space requirement and index fragmentation issue is nearly the same no matter what kind of relational database you use. Math is math. Just the other day I delivered significant performance gains to a client by converting ~150 million UUIDv4 PKs to good old BIGINT. They were using a fairly recent version of MariaDB.
I think the author means all dbs that fit a single server. Because in distributed dbs you often want to spread the load evenly over multiple servers.