Hacker News Reader

Comment by pjscott | Hacker News Reader

I'm not worried about collisions and I agree that being able to put metadata in the UUID is a big meh of a feature. The problem with v4 is what happens when people try using them as database keys: the random sort order can really hurt performance. You might argue that the fix for this is to simply not use UUIDs as database keys... but so many people are already doing this, and will continue to do it, that they should probably be given better standard options.

View on HN

> random sort order can really hurt performance

I've long thought it was a problem that PostgreSQL doesn't have a real clustering key like MySQL or SQL Server.

But PostgreSQL users have told me it doesn't really matter.

It's much less important in postgres as in some other databases, but it still hurts.

Mainly because indices have to be updated and searched all over the place with UUIDs. The locality of the data on disk itself is fine because its inserted sequential.

> But PostgreSQL users have told me it doesn't really matter.

Those users were very wrong, unless they explicitly only talked about the case where the uuids are not indexed.

They'd likely argue to use natural keys instead. Use the data that matters to you, index it across more than one column.

For cases where one sensibly used uuids, I don't see natural keys being an alternative. What's e.g. the natural key of an order reference that should be accessible without auth, when the key shouldn't allow enumeration and that should be allocatable without a single point of coordination?

This is a fairly significant issue for mysql, but does the same issue exist when using postgres with a UUID pk type?

At my last job, this was managed by using two ID's on each row - a serial one that was basically only used for database optimization purposes, and the "real" UUIDv4 ID. It felt gross then, and it feels gross now, but it seemed to do the trick for our needs.

It doesn’t, since Postgres doesn’t actively use the primary key for clustering.

It still increases write rate a lot over more sequential values / reduced cache hit ratio.

With sequential-ish values indexed by something like b-trees the same index pages get modified over and over again because new table rows will be in a narrow part of the index. As databases / operating systems typically buffer writes for a while, this reduced the number of writes hitting storage substantially.

Conversely, with random values, every leaf page will be modified with the same probability. That's not problem with a small index, because you'll soon dirty every page anyway. But as soon as the index gets larger you'll see many more writes hitting storage.

FYI - for anyone running across this thread later. This is a unique problem with (a kind of globally) consistent storage on small (single node equivalent) systems. If you have a large scale distributed system, you WANT writes to be well distributed across all nodes, or you’ll end up with problematic hot spots.

All new writes ending up on the same node/page/index is a good way to crush your system in a cascading-never-coming-back-up-until-you-drain-traffic-kind-of-way

Reply on news.ycombinator.com