undefined | Better HN

0 pointssaagarjha5mo ago0 comments

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

0 comments

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.

wongarsu5mo ago

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

morshu90015mo ago

Yeah, that's what I do

bramhaag5mo ago

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.

coolspot5mo ago

“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.

morshu90015mo ago

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

tracker15mo ago

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.

1 more reply

clintonb5mo ago

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).

markstos5mo ago

Why?

j / k navigate · click thread line to collapse

0 comments

morshu90015mo ago

wongarsu5mo ago

morshu90015mo ago

Yeah, that's what I do

bramhaag5mo ago

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.

coolspot5mo ago

“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.

morshu90015mo ago

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

tracker15mo ago

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.

1 more reply

clintonb5mo ago

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).

markstos5mo ago

Why?

j / k navigate · click thread line to collapse