I always use

    UUID PRIMARY KEY DEFAULT uuid_generate_v1mc()

In Postgres. It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential). I do this always, even if the table has other int columns that look like friendly values that could be used as PKs. Some time in the future they will ruin your day.

If you want to use UUID for primary keys, the argument should not be the larger key space.

A 64 bit int is enough for a single server. If a 64 bit int PK is exhausted, the data used to store the keys alone would require 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds. Then you need several times that to store any kind of meaningful data

If you need to use UUIDs for other reasons such as a high degree of unique keys between servers that do not coordinate, then go for it, but not for the key size.

> 128 exabytes, or in perspective, a SAN with 6.4 million 20tb hdds

Nitpick: It's actually 7.38 million disks since "20 TB" for a disk means terabyte, not tebibyte.

True - not that it changes the soundness of the argument.

UUIDs can have severe performance implications. Even without massive randomness, a change in the MAC address used or non sequential numbers can slow down writes massively. As others have written, a simple 64bit integer should be enough for pretty much any case. Even Google won't exhaust those for their user database.

v1mc does not depend on the MAC address.

For others curious about this uuid type:

"This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications."

I guess that's not immune to problems either (i can imagine problems with both mac address and time).

This is not the right function. V1mc randomises the MAC. Also it's only one of the possibilities. There are many ways if generating uuids depending on your situation.

Isn't UUID discouraged for a PRIMARY KEY pertaining indexes?

What would the B+Tree look like for over 2 billions UUIDS where there is no order within? Also, the cache locality problem.

This is why GP wrote "It will give you UUIDs and the larger keyspace, without being excessively random (they're basically almost sequential)". The main point for not screwing up the clustered index is the "almost sequential" part.

That is for random (v4) UUIDs. v1 UUIDs are mostly sequential in time on a single machine so don't have this problem, altough they do have problems that others indicated already.

I could be wrong, but I thought UUIDs could cause some performance troubles for larger table when there is an index on it?

edit: This explains it a bit

> When you insert a new row with a random primary key value, InnoDB has to find the page where the row belongs, load it in the buffer pool if it is not already there, insert the row and then, eventually, flush the page back to disk. With purely random values and large tables, all b-tree leaf pages are susceptible to receive the new row, there are no hot pages. Rows inserted out of the primary key order cause page splits causing a low filling factor. For tables much larger than the buffer pool, an insert will very likely need to read a table page from disk. The page in the buffer pool where the new row has been inserted will then be dirty. The odds the page will receive a second row before it needs to be flushed to disk are very low. Most of the time, every insert will cause two IOPs – one read and one write. The first major impact is on the rate of IOPs and it is a major limiting factor for scalability. [1]

1: https://www.percona.com/blog/2019/11/22/uuids-are-popular-bu...

OP mentioned they are using one of the sequential UUID variants so this is not an issue.

I believe they still use the MAC address? If the master node changes, the number could suddenly start at a different block, causing massive performance impacts. If they're purely sequential you can as well use a 64 bit int.

> If the master node changes, the number could suddenly start at a different block, causing massive performance impacts.

What massive performance impact? You'd have one extra IOP, total, as it loaded the page for the new mac address, and then it would carry on as normal. It would be no different from when a sequential ID crosses a page boundary.