undefined

Yes, this is standard. And the point is performance. The needs of user-facing ID's (short alphanumeric, randomly ordered) tend to be pretty orthogonal to the needs of internal ID's (raw bits, monotonically increasing, maybe timestamp data).

by mahboi11 hours ago|

parent|

[-]

Yep, you'll probably join like 20 of those for each public uuid lookup. There are potential reasons not to do it this way, but it's a good default.

by ndriscoll15 hours ago|

parent|

prev|

[-]

Higher density for indexes you use for joins. Also you can then use UUIDv4 to hide creation times if that's sensitive too.

by 14 hours ago|

parent|

[-]

deleted

by nextaccountic10 hours ago|

parent|

prev|

[-]

It makes more sense to have two columns yes, one autoincrement for the primary key, and another uuidv4. Because uuids are too large and makes indexes slower

by skrtskrt12 hours ago|

parent|

prev|

[-]

This was hard enough to enforce when it was just people writing code, and once one of those stupid incrementing integer IDs go in a public API or important codepath, it can be a real pain to rip out.

by esafak17 hours ago|

prev|

[-]

That depends on your database. Serial keys can create hot spots in distributed databases because new writes are consistently directed to the same partition.

by ndriscoll16 hours ago|

parent|

[-]

Yeah but for nearly everyone, they'd get better performance from not using a distributed database, and benefit from "hotspots" i.e. data locality.

by JoshTriplett17 hours ago|

parent|

prev|

[-]

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.

by NyxWulf15 hours ago|

parent|

[-]

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.

by JoshTriplett12 hours ago|

parent|

[-]

> If your insert requires a parent record with a single child record, you have to get the next sequential id

Why would you need the next sequential ID, rather than just a unique ID? You could have a serial column that uses per-shard preallocations like I was describing, and allocate the IDs for both records.

by baq17 hours ago|

parent|

prev|

[-]

It’s much easier if you can simply not coordinate. Bonus points if that doesn’t mean you need an atomic clock in your rack

by mahboi17 hours ago|

parent|

[-]

If you're designing a distributed DB, sure. If you're using something, you do whatever it wants. Spanner recommends random keys. Citus uses serial.

by NyxWulf15 hours ago|

parent|

prev|

[-]

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.

by ndriscoll15 hours ago|

parent|

[-]

It's been a while since I've touched db stuff, but pg still has to write the indexes before commit completes, no? So it's still having to go load and write a gazillion pages. And then unless your filesystem has atomic page writes like ZFS, it also writes the whole page to WAL to ensure that it doesn't do torn writes. Writes to the same page within a checkpoint don't need full page write, so benefit.