Hacker News new | past | comments | ask | show | jobs | submit
esafak | on: UUIDv7: Timestamps aren’t time-ordered and other oddities
That depends on your database. Serial keys can create hot spots in distributed databases because new writes are consistently directed to the same partition.
ndriscoll | parent | next
Yeah but for nearly everyone, they'd get better performance from not using a distributed database, and benefit from "hotspots" i.e. data locality.
JoshTriplett | parent | next
There's no fundamental reason for this. Much as with per-CPU memory allocation, shards could pre-reserve chunks of the next serial space, and then only coordinate when they need a new chunk.
NyxWulf | root | parent | next
Many schemes do exactly this, if all you are doing is single table inserts that works pretty well.

If you do anything that creates related records via foreign keys you start to run into lock contention. For example:

If your insert requires a parent record with a single child record, you have to get the next sequential id which involves a non-preemptable single threaded lock, increment it, and send it back to the connection which requested it. In most databases this would mean writing the parent record and getting the sequential id of the parent record back so you can set the foreign key reference on the child record. Stored procedures are one way to mitigate the network transit time, but they don't solve the single threaded lock acquisition time.

The reason most distributed systems use UUIDs or another flavor of distributed id is so you can generate the id for the parent record on the client, set the foreign key there as well, then send both records in as simple inserts. It's not that there aren't ways to work around it or mitigate it. Using a coordination free id, is just much easier to work with and scale in practice.

loading story #50216943
loading story #50213849
NyxWulf | parent
In my testing the primary bottleneck isn't about writing to the same partition. Lock coordination to increment the serial key, then send it back across a connection was the real culprit. Virtually all databases make updates into RAM and to a write ahead log, which is then spooled to disk in the background. No matter which pages are written or updated, the changes all go sequentially in order into the WAL. For me, getting rid of Serial Keys was the big hot spot in processing. YMMV.
loading story #50215087