Hacker News Reader

Comment by Svip | Hacker News Reader

As far as I can gather from this post and looking at the data type documentation, MySQL does not have a specific UUID type, but Postgres does.[0] I'll assume that Postgres has some internal optimisations to UUID that MySQL thus lacks.

Addendum: I also realise this is anecdotal, but someone on Stackoverflow mentions a significant speed up from changing `text` to `uuid` in Postgres.[1] But this also fits with what I've been told on #postgresql on libera.chat. That being said, integers would still outperform uuid.

[0] https://www.postgresql.org/docs/14/datatype-uuid.html [1] https://stackoverflow.com/questions/29880083/postgresql-uuid...

View on HN

The UUID type index in Postgres is optimized: https://brandur.org/sortsupport

The Postgres UUID type works just like BINARY(16) covered in the article.

A lot of the problems listed in the post are physical issues with larger data types that are somewhat random - eg the size, how clustered indexes work, and you will have the same problems with them in SQL Server.

If you order the data based on the uuid and your uuid is randomly distributed, then you will almost always be writing the data in the middle of your table, physically. You can cut the impact somewhat by using spare tables (leaving lots of empty space) but eventually you'll be re-writing the data.

SQL Server has a sequential uuid type which avoids exactly this problem.

If that's the underlying issue, sequentiallity... Just use uuid uuid 6 or 7. They are time based and approximately sortable (unlike uuid1).

https://datatracker.ietf.org/doc/html/draft-peabody-dispatch...

Disclaimer, I have no data to back up this solves the performance problems described. It's just likely to solve that "writing in the middle of the table" part.

Why use a sequential UUID over integers as an id and then associating them with a random UUID?

Integers are a superior way to record sequential data.

Because you don't want your record IDs to be perfectly sequential data.

You only care about them being sequential enough that your database engine will write them down quickly and efficiently (for engines like InnoDB or MSSQL which have such behaviour). But as a developer you typically want them to be random so that you can generate them in a decentralised manner, prevent guessing or accidental joins, safely merge records from multiple tables, etc.

Sequential UUIDs usually preserve enough randomness for the latter (as long as you don't do stuff like generating a trillion IDs exactly at midnight), while providing enough sequentiality for the former.

But now the ID has a random concatenation of data in it, like the MAC address of the node that inserted it into the database and a timestamp.

It'd be much more orderly for each node to have a sequential ID, the ID of the node that created it, a timestamp when it was created and a real UUID v4 for if someone wants to prevent collisions. Same data, but the primary key on an individual node is a lot smaller (half the size) and the metadata is available for use if someone wants it. There is natural room to build up namespaces and such. It is better to keep these observations separate instead of munging them all into the record ID.

I 100% agree that metadata like timestamps and machine IDs, if you care about them, should have their own columns.

I only disagree with:

> It'd be much more orderly for each node to have a sequential ID

I've outlined some reasons above why a random ID has advantages over a sequential one.

I run Postgres in prod so I can use purely random UUIDv4, but for those running mysql or mssql, UUIDv6 and the like are a useful compromise between sequentiality and randomness. The fact that they happen to use timestamps as the source of sequentiality is only an implementation detail.

> SQL Server has a sequential uuid type which avoids exactly this problem.

you refer to the uuid generated by sql server?

Yes. When generating you can use NewSequentialId() which generates a sequential guid to avoid needing to reorganise indexes.

Probably just https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...

Reply on news.ycombinator.com