Allowing the client to generate IDs for you seems like a bad idea?

morshu9001 a day ago | parent | next [-]

Client = backend here, right? So you could make a bunch of rows that relate to each other then insert, without having to ping the DB each time to assign a serial ID. Normally the latter is what I do, but I can imagine a scenario where it'd be slow.

▲

wongarsu a day ago | parent [-]

The usual flow would be INSERT ... RETURNING id, which gives you the db-generated id for the record you just inserted with no performance penalty. That doesn't work for circular dependencies and it limits the amount of batching you can do. But typically those are smaller penalties than the penalty from having a 128 bit primary key vs a 64 bit key

	▲	morshu9001 a day ago \| parent [-]
		Yeah, that's what I do

▲

clintonb 8 hours ago | parent | prev | next [-]

Client-generated IDs are necessary for distributed or offline-first systems. My company, Vori, builds a POS for grocery stores. The POS generates UUIDv7 IDs for all data it creates and that data is eventually synced to our backend. The sync time can range from less than 1 second for a store with fast Internet to hours if a store is offline.

Is a collision possible? Yes, but the likelihood of a collision is so low that it's not worth agonizing over (although I did when I was designing the system).

▲

bramhaag a day ago | parent | prev | next [-]

It can be quite elegant. You can avoid the whole temporary or external ID mess when the client generates the ID, this is particularly useful for offline-first clients.

Of course you need to be sure the server will accept the ID, but that is practically guaranteed by the uniqueness property of UUIDs.

▲

coolspot a day ago | parent | prev | next [-]

“client” here may refer to a backend app server. So you can have 10-100s of backend servers inserting into a same table without having a single authority coordinating IDs.

▲

morshu9001 a day ago | parent [-]

That table is still a single authority, isn't it? But I guess fewer steps is still faster.

▲

tracker1 a day ago | parent [-]

Except if you're using a sharding or clustering database system, where the record itself may be stored to separate servers as well as the key generation itself.

	▲	morshu9001 a day ago \| parent [-]
		In those cases yes. There's still a case for sequential there depending on the use pattern, but write-heavy benefits from not waiting on one server for IDs.

▲

markstos a day ago | parent | prev [-]

Why?