On Mon, Feb 6, 2023 at 1:22 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
On 2023-02-06 20:04:39 +0100, Julian Backes wrote: > I don't really understand what you mean by 'performance'. To me it is not > surprising that incrementing (I know it is not just incrementing) a > 64bit integer is faster than generating 128 bit data with a good amount of > random data even if it seems to be too slow.
But UUIDs are random and that plays havoc with locality. For example consider one table with invoices and another with invoice items. If you want to get all the invoices including the items of a single day, the data is probably nicely clustered together in the tables. But the join needs to look up random ids in the index, which will be spread all over the index. In a simple benchmark for this scenario the UUIDs were about 4.5 times slower than sequential ids. (In other benchmarks the difference was only a few percent)
This is really key.
While many of the people posting here may understand this, all of the databases I've seen that are written with the UUID pattern appear to be written by developers oblivious to this fact. The UUID pattern seems to be popular with developers who see abstract away the database underneath the code and might use an ORM and be weaker in terms of database facing constraint checking. My direct observation is that these databases scale poorly and the developers spend a lot of time building tools that fix broken data stemming from application bugs.
I'm certain this is not the experience of everyone here. I do however find the counter sequence arguments to be somewhat silly; partition safe sequence generation is simple to solve using simple methods. "ID guessing" is not insecure along similar lines; if your application relies on id obfuscation to be secure you might have much bigger issues to contend with IMO.