undefined | Better HN

0 pointskijin3mo ago0 comments

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.

0 comments

splix3mo ago

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.

esafak3mo ago

To spell it out: it improves performance by avoiding hot spots.

zelphirkalt3mo ago

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.

kijinOP3mo ago

Yeah, they had relatively simple requirements so BIGINT was a quick optimization. MariaDB can guarantee uniqueness of auto-incrementing integers across a cluster of several servers, but that's about the limit.

Had the requirements been different, UUIDv7 would have worked well, too, because fragmentation is the biggest problem here.

j / k navigate · click thread line to collapse