> If you need to list things ordered by creation time very often, it’s useful if the primary index gives that behavior, removing the need for another index.

I thought the main reason UUIDv7 was created with a time component is to keep temporally related rows together in the index pages. Randomly distributed row ids seem OK until the entire index no longer fits in RAM.

It's not just index pages. Generally databases write records into pages, which are then grouped into extents. If the primary key gives an ordering you can create new pages and start filling them up in an orderly fashion. If it's random, you are doing random inserts into pages everywhere all the time, then you start running out of room in pages and have to start splitting them. UUIDv7 solves the most painful parts of this problem in general and is a much better option for a Primary Key than UUIDv1-4.

Yeah, this is 99% of the benefit for me. And it's not theoretical. My company saw performance issues using pure random IDs with MySQL way back in 2015; and consequently rolled our own UUIDv7 that we're still using to this day.

Yeah, same.

I suppose it's a problem if the spec actually says they're monotonically incrementing because it's clear that isn't guaranteed. But that was never a property I wanted or expected out of them anyway.

All of the other issues here sound like misunderstandings or misuses of ids, IMHO

As well as keeping rows of similar age roughly together, which may create access efficiencies but should not be relied upon, relatively ordered keys massively reduces page splits as the index grows (this can be particularly significant for clustered indexes as implemented by MS SQL and others) because you are always adding items to the end of the index. This can reduce the size of the index overall, potentially a big problem if working in a situation where your data+indexes do not fit comfortably in RAM (you don't want your precious RAM half full of half-empty index pages).

Yes you can shrink it down when things get bad, but if your index is large enough that you care then it is large enough that this reorganisation is going to be costly in time and may slow your system while it happens (or cause a complete lock if your DB doesn't support, or optionally supports and you don't select the option, this as an online operation.

you thought right. the author is just plain wrong here.

I thought it was for distributed DBs?