News for hackers. Stuff that matters.
Re:ULIDs and Primary Keys by naranha on Friday April 12, 2024 @05:19PM (#40017785)
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 ;)

Re:ULIDs and Primary Keys by SahAssar on Friday April 12, 2024 @07:58PM (#40018912)
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).
Re:ULIDs and Primary Keys by hobs on Friday April 12, 2024 @05:27PM (#40017862)
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.
Re:ULIDs and Primary Keys by HumanOstrich on Friday April 12, 2024 @05:48PM (#40018040)
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.

Re:ULIDs and Primary Keys by rileymat2 on Friday April 12, 2024 @06:08PM (#40018182)
Not always, this article describes the problem: https://learn.microsoft.com/en-us/troubleshoot/sql/database-...
Re:ULIDs and Primary Keys by hobs on Friday April 12, 2024 @07:26PM (#40018673)
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.

Re:ULIDs and Primary Keys by erik_seaberg on Friday April 12, 2024 @06:14PM (#40018221)
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.
Re:ULIDs and Primary Keys by sgarland on Saturday April 13, 2024 @07:59AM (#40022551)
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.
Re:ULIDs and Primary Keys by lfittl on Monday April 15, 2024 @04:43PM (#40045311)
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.