4 ms·

Actually don't use UUIDs. We use something very similar to what Instagram does with Postgres : http://instagram-engineering.tumblr.com/post/10853187575/shardin

by eksith 14y ago

Actually don't use UUIDs. We use something very similar to what Instagram does with Postgres : http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram http://instagram-engineering.tumblr.com/post/10853187575/sha... This allows far greater flexibility with regard to sharding and scale. Bigint with a function generated nextval will sustain up to 9223372036854775807. If you need more than that, your next option is Numeric, but I don't think we'll exhaust bigint any time soon.

jacques_chester 14y ago
You still need to correctly merge the records for aggregate reporting. If you use UUIDs, this is easy. If you used SERIAL, you now need a scheme to intelligently merge records. Not hard when everything is in a single table. A bit tougher when you have a wholly or partly normalised schema, because now you need to come up with correct renumbering schemes for every single table which has another table holding a foreign key into it. I can tell you from personal experience that this is not fun. v1 UUIDs are "guaranteed" to be unique, modulo people fiddling with your MAC addresses. v4 UUIDs are random keys and the odds of collision are so infinitesimal that it's worthless to fuss about it. v4s also have the nice property that they can double as nonces, but the sucky problem that they are not that great for indexes. Then there's stuff like replaying logs reliably on multiple machines, blah blah blah. Every instance of 186360 is indistinguishable from every other instance of 186360. So you will need additional logic to keep them straight. But take this uuid: 69c8e822-83b0-11e2-a6a5-001f5bfffe14 The odds that this has been created anywhere else, at any point, is as good as zero. You're more likely to have an integer key collision due to cosmic radiation flipping some bits. So no special merging / field-source tracking logic is required.
- jokull 14y ago
  I’m not sure I understand. The way Instagram do it, they ensure uniqueness across shards so reporting should not have to merge anything.
  - malkia 14y ago
    I guess the point is, that you don't need to "ensure uniqueness across..."
- eksith 14y ago
  I can't be too sure of the specifics, but our db admins decided against UUIDs after a few days debating and lots of coffee. We aren't using serial, but instead using functions to generate our own nextvals with the server IDs, epoch etc..., kinda the same way as Instagram (though, obviously our databases are no where near the same size). It came down to how much logic we were comfortable implementing in the schema from the start to avoid continuing complexity in the applications themselves.

by eksith 14y ago

jacques_chester 14y ago

You still need to correctly merge the records for aggregate reporting. If you use UUIDs, this is easy. If you used SERIAL, you now need a scheme to intelligently merge records. Not hard when everything is in a single table. A bit tougher when you have a wholly or partly normalised schema, because now you need to come up with correct renumbering schemes for every single table which has another table holding a foreign key into it. I can tell you from personal experience that this is not fun. v1 UUIDs are "guaranteed" to be unique, modulo people fiddling with your MAC addresses. v4 UUIDs are random keys and the odds of collision are so infinitesimal that it's worthless to fuss about it. v4s also have the nice property that they can double as nonces, but the sucky problem that they are not that great for indexes. Then there's stuff like replaying logs reliably on multiple machines, blah blah blah. Every instance of 186360 is indistinguishable from every other instance of 186360. So you will need additional logic to keep them straight. But take this uuid: 69c8e822-83b0-11e2-a6a5-001f5bfffe14 The odds that this has been created anywhere else, at any point, is as good as zero. You're more likely to have an integer key collision due to cosmic radiation flipping some bits. So no special merging / field-source tracking logic is required.

jokull 14y ago
I’m not sure I understand. The way Instagram do it, they ensure uniqueness across shards so reporting should not have to merge anything.
- malkia 14y ago
  I guess the point is, that you don't need to "ensure uniqueness across..."
eksith 14y ago
I can't be too sure of the specifics, but our db admins decided against UUIDs after a few days debating and lots of coffee. We aren't using serial, but instead using functions to generate our own nextvals with the server IDs, epoch etc..., kinda the same way as Instagram (though, obviously our databases are no where near the same size). It came down to how much logic we were comfortable implementing in the schema from the start to avoid continuing complexity in the applications themselves.