Hacker News Reader

Comment by RobertRoberts | Hacker News Reader

I am not a db expert, which is why I like read these kinds of comment threads. But what about this?

https://en.wikipedia.org/wiki/Universally_unique_identifier#...

"The random nature of standard version 3, 4, and 5 UUIDs and the ordering of the fields within standard version 1 and 2 UUIDs may create problems with database locality or performance when UUIDs are used as primary keys. For example, in 2002 Jimmy Nilsson reported a significant improvement in performance with Microsoft SQL Server when the version 4 UUIDs being used as keys were modified to include a non-random suffix based on system time. This so-called "COMB" (combined time-GUID) approach made the UUIDs non-standard and significantly more likely to be duplicated, as Nilsson acknowledged, but Nilsson only required uniqueness within the application."

View on HN

This article mentions this question. Database locality is a complex scaling question that may not be a problem depending on how read/write heavy your operations are. On the one hand, randomly distributed UUIDs may be a bad idea on a single SQL server trying to balance a single B-Tree index (and thrashing that index with a lot of incoming data); on the other hand it can be somewhat ideal for partition sharding across multiple servers. As with any trade-offs in database design, your mileage will vary with your application needs and resource availability.

Also, there are other options for time-ordered GUID/UUID alternatives. ULID is the one I've been heavily using in projects lately: https://github.com/ulid

(ULID uses a timestamp prefix and random suffix for reasonable time-ordered database locality; an interesting compromise between V1 and V4 UUIDs, though not directly compatible with either. The L stands for "lexicographic" in that its also meant to be sortable in string indices as well, which can be important for database locality in many document/NoSQL databases.)

I'm also not a DB expert, but I definitely think locality is a concern with UUIDs (at least in Postgresql, which stores records in sorted order).

But, storage locality has always struck me as a use-case-specific optimization, in the sense that it's hard to take locality into account without knowing what traffic patterns the table is expected to experience. So, although UUIDs provide locality for exactly no types of queries, that may not be a problem for you.

For instance, you could use an autoincrementing sequence, but that only provides good locality for queries over sequential records. The COMB method would provide good locality for queries over time periods. But if you aren't performing those types of queries, you might be better off with a different type of PK.

We had an application specific reason we needed to use them instead of numerics and used this method which helps somewhat: https://www.percona.com/blog/2014/12/19/store-uuid-optimized...

Reply on news.ycombinator.com