3 ms·

Harder to copy and paste IDs. Discourages denormalization since every reference is 128 bytes bits. Reduced throughout for inserts since the primary index pa

Harder to copy and paste IDs. Discourages denormalization since every reference is 128 ~~bytes~~ bits. Reduced throughout for inserts since the primary index pages won’t all be in cache if the keys are randomized. Lose ability to use id as a fast temporal ordering. Encourages uuids generated client-side. All in all not enough good reasons to make a lot of stuff slightly worse.

deleted 4y ago
[deleted]
CogitoCogito 4y ago
> Discourages denormalization since every reference is 128 bytes. This might just be a mistake, but a uuid is 128 bits = 16 bytes not 128 bytes. Of course yes there are still circumstances where the extra space (16 bytes vs 8 or 4 or something else) isn't worth it.
- srcreigh 4y ago
  Yeah I always mix these up. Thanks
drodgers 4y ago
> Reduced throughout for inserts since the primary index pages won’t all be in cache if the keys are randomized. Lose ability to use id as a fast temporal ordering. You can generate the first few bytes (we use the first 3) of the UUID from a timestamp - that gives you index locality. It's even better than an auto-inc because you can control exactly how much of your index will be used for hot inserts based on how many timestamp bits you use, so you can avoid lock contention around the single latest index page. > Encourages uuids generated client-side. It's really an API design choice about whether you allow this, but it can be useful in some circumstances (if you trust the client).
- jeltz 4y ago
  > so you can avoid lock contention around the single latest index page. That is usually the total opposite of what you want. There are some optimizations for inserting to the last page but primarily it is because you want to to sequential inserts. So if you want to avoid contention on the last page you should insert ordered by (connection ID, sequential ID or high resolution timestamp). That way every connection will do sequential inserts on its own page.
  - srcreigh 4y ago
    Nice this makes sense. Seems like storing connection_id in the table would make it difficult to look up data later? It seems like you could use (customer_id, seq_id) for similar effect.
- esailija 4y ago
  You lose security/privacy benefits. People in this thread pretend that there is one id type that is obviously better. - UUID (all types) scale, sequential ids don't scale - UUID (fully random) is completely secure and private. You cannot infer anything from it. As soon as you add anything to it ( like timestamp or counter), outsiders can infer things like how fast you are generating new objects - UUID (fully random) cannot be abused by developers, which might skip creating timestamp columns and read it from the id instead - UUIDs are slower, but as you see y's not for no benefits
  - ttfkam 4y ago
    Not that much slower than bigint as long as you replace the top one or two bytes in the UUID with a rotating per-minute counter. You keep 106 bits of entropy (from v4's 122 bits) while largely eliminating page faults. Walkability is eliminated while not leaking too much temporal info. https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ https://www.2ndquadrant.com/en/blog/sequential-uuid-generato...
  - srcreigh 4y ago
    https://youtu.be/b2F-DItXtZs https://youtu.be/b2F-DItXtZs
jeltz 4y ago
Also more btree index bloat and slower inserts due to random insert order.
- avinassh 4y ago
  How would one measure the B Tree bloat in Postgres?
  - srcreigh 4y ago
    B-trees are self balancing so I don't think there will be bloat. But if you insert towards the end of an index a lot, the right-side of the index will more likely be in cache which makes it a lot faster. If you inserted randomly then you need to do lots of traversals in different parts of the B-Tree.
avinassh 4y ago
> Encourages uuids generated client-side. Postgres can auto generate UUIDs too and can be used for pk
- selcuka 4y ago
  > Postgres can auto generate UUIDs too and can be used for pk True, but the practice makes it easier to shift to the dark side and insert the child records first, as you can already know the pk of the parent record in advance.
  - Tostino 4y ago
    How can you do that when you have fkeys in your database design like a good engineer, right? Right?
    - selcuka 4y ago
      > How can you do that when you have fkeys in your database design like a good engineer, right? Right? I know you are trolling, and it is ok if done sensibly, but it is not simply purism that makes it a bad idea. Because you have to trust the client it opens the way to discovery attacks (try inserting a record with a specific PK -> it will fail if the record exists -> now you know that it exists even if you don't have access to that record). You may also not have access to all client implementations (think of public APIs) so some client libraries might not implement proper (i.e. strongly random) UUID generation.
danielheath 4y ago
> Encourages uuids generated client-side. Is there an actual problem with this? I don't mean "it feels impure" - what's the failure case here that's severe enough to discard UUIDs as PKs? The other reasons all boil down to 'database performance tuning', which is reasonable if you have any prospect of needing it, but most datasets are _tiny_.
- srcreigh 4y ago
  The other reasons are more important imo, and they aren’t really all tuning more ergonomics and simplicity. There’s a lot more edge cases when you use client-side generated IDs. You have to check if the ID is actually new to avoid security issues is the main one. Let’s just upsert into the DB and return the results and the uuid is new and randomly generated so it’s fine! So simple! Except now Alice can send someone else’s uuid and read their data. X100 endpoints all need to handle this correctly. It’s a huge risk. If you do offline first ID generation using global uuids, the possibility of conflicts (due to bugs, patched client code, etc) that’s a whole rabbit hole of edge cases and problems as well. It can be done, Bret Taylor used that architecture for quip, but it’s needlessly tricky which is the whole story for UUIDs imo - annoying and slightly worse for many normal apps. If you’re building a complex distributed system and want to deal with all the trickiness, go ahead. I’d recommend you use a B64 custom identifier instead of UUID so that it’s copy paste able :-)
- stickfigure 4y ago
  >> Encourages uuids generated client-side. >Is there an actual problem with this? The nature of your application and its security and idempotence needs will determine who should (ideally) generate ids for any specific context. The format is irrelevant. Generate ids on the client when it makes sense, generate them on the server when it makes sense. Server-side is more common but if you choose poorly you'll make bad software either way.

Harder to copy and paste IDs. Discourages denormalization since every reference is 128 ~~bytes~~ bits. Reduced throughout for inserts since the primary index pa

deleted 4y ago

[deleted]

CogitoCogito 4y ago

> Discourages denormalization since every reference is 128 bytes. This might just be a mistake, but a uuid is 128 bits = 16 bytes not 128 bytes. Of course yes there are still circumstances where the extra space (16 bytes vs 8 or 4 or something else) isn't worth it.

srcreigh 4y ago
Yeah I always mix these up. Thanks

drodgers 4y ago

> Reduced throughout for inserts since the primary index pages won’t all be in cache if the keys are randomized. Lose ability to use id as a fast temporal ordering. You can generate the first few bytes (we use the first 3) of the UUID from a timestamp - that gives you index locality. It's even better than an auto-inc because you can control exactly how much of your index will be used for hot inserts based on how many timestamp bits you use, so you can avoid lock contention around the single latest index page. > Encourages uuids generated client-side. It's really an API design choice about whether you allow this, but it can be useful in some circumstances (if you trust the client).

jeltz 4y ago
> so you can avoid lock contention around the single latest index page. That is usually the total opposite of what you want. There are some optimizations for inserting to the last page but primarily it is because you want to to sequential inserts. So if you want to avoid contention on the last page you should insert ordered by (connection ID, sequential ID or high resolution timestamp). That way every connection will do sequential inserts on its own page.
- srcreigh 4y ago
  Nice this makes sense. Seems like storing connection_id in the table would make it difficult to look up data later? It seems like you could use (customer_id, seq_id) for similar effect.
esailija 4y ago
You lose security/privacy benefits. People in this thread pretend that there is one id type that is obviously better. - UUID (all types) scale, sequential ids don't scale - UUID (fully random) is completely secure and private. You cannot infer anything from it. As soon as you add anything to it ( like timestamp or counter), outsiders can infer things like how fast you are generating new objects - UUID (fully random) cannot be abused by developers, which might skip creating timestamp columns and read it from the id instead - UUIDs are slower, but as you see y's not for no benefits
- ttfkam 4y ago
  Not that much slower than bigint as long as you replace the top one or two bytes in the UUID with a rotating per-minute counter. You keep 106 bits of entropy (from v4's 122 bits) while largely eliminating page faults. Walkability is eliminated while not leaking too much temporal info. https://www.2ndquadrant.com/en/blog/sequential-uuid-generators/ https://www.2ndquadrant.com/en/blog/sequential-uuid-generato...
- srcreigh 4y ago
  https://youtu.be/b2F-DItXtZs https://youtu.be/b2F-DItXtZs

jeltz 4y ago

Also more btree index bloat and slower inserts due to random insert order.

avinassh 4y ago
How would one measure the B Tree bloat in Postgres?
- srcreigh 4y ago
  B-trees are self balancing so I don't think there will be bloat. But if you insert towards the end of an index a lot, the right-side of the index will more likely be in cache which makes it a lot faster. If you inserted randomly then you need to do lots of traversals in different parts of the B-Tree.

avinassh 4y ago

> Encourages uuids generated client-side. Postgres can auto generate UUIDs too and can be used for pk

selcuka 4y ago
> Postgres can auto generate UUIDs too and can be used for pk True, but the practice makes it easier to shift to the dark side and insert the child records first, as you can already know the pk of the parent record in advance.
- Tostino 4y ago
  How can you do that when you have fkeys in your database design like a good engineer, right? Right?
  - selcuka 4y ago
    > How can you do that when you have fkeys in your database design like a good engineer, right? Right? I know you are trolling, and it is ok if done sensibly, but it is not simply purism that makes it a bad idea. Because you have to trust the client it opens the way to discovery attacks (try inserting a record with a specific PK -> it will fail if the record exists -> now you know that it exists even if you don't have access to that record). You may also not have access to all client implementations (think of public APIs) so some client libraries might not implement proper (i.e. strongly random) UUID generation.

danielheath 4y ago

> Encourages uuids generated client-side. Is there an actual problem with this? I don't mean "it feels impure" - what's the failure case here that's severe enough to discard UUIDs as PKs? The other reasons all boil down to 'database performance tuning', which is reasonable if you have any prospect of needing it, but most datasets are _tiny_.

srcreigh 4y ago
The other reasons are more important imo, and they aren’t really all tuning more ergonomics and simplicity. There’s a lot more edge cases when you use client-side generated IDs. You have to check if the ID is actually new to avoid security issues is the main one. Let’s just upsert into the DB and return the results and the uuid is new and randomly generated so it’s fine! So simple! Except now Alice can send someone else’s uuid and read their data. X100 endpoints all need to handle this correctly. It’s a huge risk. If you do offline first ID generation using global uuids, the possibility of conflicts (due to bugs, patched client code, etc) that’s a whole rabbit hole of edge cases and problems as well. It can be done, Bret Taylor used that architecture for quip, but it’s needlessly tricky which is the whole story for UUIDs imo - annoying and slightly worse for many normal apps. If you’re building a complex distributed system and want to deal with all the trickiness, go ahead. I’d recommend you use a B64 custom identifier instead of UUID so that it’s copy paste able :-)
stickfigure 4y ago
>> Encourages uuids generated client-side. >Is there an actual problem with this? The nature of your application and its security and idempotence needs will determine who should (ideally) generate ids for any specific context. The format is irrelevant. Generate ids on the client when it makes sense, generate them on the server when it makes sense. Server-side is more common but if you choose poorly you'll make bad software either way.