Hacker News Reader

Comment by naranha | Hacker News Reader

uuidv7 will be supported in PostgreSQL 17, at which point generation should be as fast as uuidv4, or if you implement it in pgsql now it will be as fast as the proposed ULID algorithm.

Insert performance could be even better, iirc for BTree Indexes monotonic increasing values are better than random ones, but feel free to correct me on that ;)

View on HN

Commenting on this a bit late, but in case anyone reads this later too:

UUIDv7 support unfortunately didn't make it to Postgres 17, since the RFC wasn't completely finalized yet by the time of feature freeze (April 8), see discussion on pgsql-hackers:

https://www.postgresql.org/message-id/flat/ZhzFQxU0t0xk9mA_%...

So I guess we'll unfortunately have to rely on extensions or client-side generation for the time being, until Postgres 18.

In that case I don't understand why the author didn't go for uuidv7? It seems like existing tooling (both in database and outside) deals with it better, it seems like there are no downsides unless you expect your identifiers to be generated past 4147 but don't care if they are generated past 10889 (I'd love to hear that use-case, seems like it must be interesting).

As with databases it always depends, for maximum insert performance you'd actually often go with random uuids so you dont get a hot page.

Hot page?

Using a monotonically increasing PK would cause pages in the index to be allocated and filled sequentially, increasing throughput.

Using random UUIDs would lead to page-splitting and partially-filled pages everywhere, negatively impacting performance and size-on-disk.

Not always, this article describes the problem: https://learn.microsoft.com/en-us/troubleshoot/sql/database-...

Exactly, with one big insert its better to have a sequential value, for many small ones its often better to not.

As with all databases, measure before you cut.

The way to take advantage of the bandwidth of multiple storage devices is to distribute concurrent writes across them, rather than forcing everything to commit sequentially using contended locks or rollbacks.

The DB (or any application) should not have any need to know what devices are underneath its mount point. If you’re striping across disks, that’s a device (or filesystem, for ZFS) level implementation.

Reply on news.ycombinator.com