10 ms·

> Unit test mode (random result sorting) Ran into this one recently, and worked around it by executing a CLUSTER command after inserting data. By CLUSTERing on

> Unit test mode (random result sorting) Ran into this one recently, and worked around it by executing a CLUSTER command after inserting data. By CLUSTERing on a column that contains random data (which your test could inject) you force Postgres to randomise the row order on disk, and thus randomise the return order. In my case our primary key is a random UUID (I know, it’s a terrible thing, it’s not my choice), which is perfect for the task as CLUSTER requires an index. You might be able to pull the same trick by creating an index in a transaction, CLUSTERing then rolling back the transaction, but I suspect that you can’t call CLUSTER in a transaction. Not as nice as proper test mode, but gets the job done, and avoids the need to wrap queries deep inside your application, with all the brittleness and peril that comes from using wizard level code reflection that’s normally required for such tricks.

hinkley 4y ago
The first and maybe the only time I saw UUID PKs was in a multi-tenant solution. If your customers are potentially competitors to each other, you absolutely do not want people to be able to walk IDs sniffing for other stuff. I wasn't the biggest fan of this solution, but I really didn't have a better suggestion. On that same project we ran afoul of Javascript's 10^53 floating point limit with IDs, but I think that was a separate issue.
- ccll 4y ago
  How about solutions like https://hashids.org/ https://hashids.org/ which let you keep auto increment intger PKs and present the IDs to the users in a obfuscated string form?
  - nszceta 4y ago
    It sounds like GP encountered UUID Version 4 keys. UUID version 7 features a time-ordered value field derived from the widely implemented and well known Unix Epoch timestamp source, the number of milliseconds seconds since midnight 1 Jan 1970 UTC, leap seconds excluded. As well as improved entropy characteristics over versions 1 or 6. If your use case requires greater granularity than UUID vesion 7 can provide, you might consider UUID version 8. UUID version 8 doesn't provide as good entropy characteristics as UUID version 7, but it utilizes timestamp with nanosecond level of precision.
    - yrro 4y ago
      Waiting patiently for these new UUID versions to be supported by uuid(1) or Python...
    - ttfkam 4y ago
      > Both UUIDv8 and UUIDv4 only specify that the version and variant bits are in their correct location. The difference is that UUIDv4 specifies that the remaining 122 bits be pseudo-randomly generated. UUIDv8 suggests that the generated value still be time-based however the implementation details of how that generation happens are up to the implementor. This means we have 48 bits of custom implementation, four bits for the version (1000), 12 more bits of custom implementation, two bits for the variant, and finally 62 bits as the implementation sees fit. This leaves a lot open to the implementor but it has enough rules around it that it can coexist in the existing UUID environment. https://blog.devgenius.io/analyzing-new-unique-identifier-formats-uuidv6-uuidv7-and-uuidv8-d6cc5cd7391a https://blog.devgenius.io/analyzing-new-unique-identifier-fo... In other words, one CAN implement a UUIDv8 with nanosecond precision, but that is not a requirement of UUIDv8. 4 is random. 8 is a custom grab bag.
- winrid 4y ago
  You can keep the incremental PK for internal stuff, and use an external_id for APIs. Benefit - you can change it if needed.
- avianlyric 4y ago
  Honestly I think random UUIDs, or just UUIDs in general, make for terrible PKs and generally shouldn’t be used. To solve the multi-tenant issue, I personally prefer either flakeID/hashID that have an ordered component and a random component to make walking IDs hard; Aggressively name spacing your data so a customer ID plus an object ID is always needed as pair to look something up, so trying to walk object IDs can only every result in someone accidentally looking up objects that already belong to them; edge layers that remap and filter internal identifiers via hashing etc so externally all ID are opaque and random; strong and careful access control, that ensures you can only ever lookup ID that belong to you, with careful consideration for side channel timing attacks. Relying on random UUIDs for customer privacy would raise red flags for me. If being able to walk ID is enough to break your security model, then I kinda wonder if you actually have a security model.
  - hinkley 4y ago
    It's security in depth in this case, as I recall. There are subtleties in how you confirm or deny the existence of a record and for their particular solution to the problem you would get a 403 vs a 404 for a record you did not have access to. Knowing how much activity a competitor is adding into a project management system isn't a lot of data, but it's more than zero.
    - avianlyric 4y ago
      There’s a simple solution to that. Always return 403, it’s what you should be doing regardless of your underlying data model. You should always be determine right to access a record before attempting to look it up. If you can’t determine ACLs without the lookup, then your permissions layer should always assume the requester doesn’t have permission to view a non-existent record and return a 403. Doing anything else is dangerous, and randomising PKs is a sticky plaster over a badly designed access control system.
- codingdave 4y ago
  If knowing/guessing an ID bypasses security between tenants, I don't think the ID itself is the security hole.
stickfigure 4y ago
What's wrong with UUIDs? Back in the 90s Microsoft strongly encouraged it. You lose a little performance and a little storage (128 vs 64 bits), but gain the ability to merge databases (think corporate acquisitions) with less pain. At least one distribute datastore (Google Cloud Datastore) works better with random keys rather than incremental keys, it tends to produce fewer tablet splits. At least, it used to, the technology may have changed since then. Honestly it seems like the arguments for/against UUIDs as keys are pretty mild on both sides. Why would it be "terrible" to go one way or the other?
- srcreigh 4y ago
  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).

hinkley 4y ago

The first and maybe the only time I saw UUID PKs was in a multi-tenant solution. If your customers are potentially competitors to each other, you absolutely do not want people to be able to walk IDs sniffing for other stuff. I wasn't the biggest fan of this solution, but I really didn't have a better suggestion. On that same project we ran afoul of Javascript's 10^53 floating point limit with IDs, but I think that was a separate issue.

ccll 4y ago
How about solutions like https://hashids.org/ https://hashids.org/ which let you keep auto increment intger PKs and present the IDs to the users in a obfuscated string form?
- nszceta 4y ago
  It sounds like GP encountered UUID Version 4 keys. UUID version 7 features a time-ordered value field derived from the widely implemented and well known Unix Epoch timestamp source, the number of milliseconds seconds since midnight 1 Jan 1970 UTC, leap seconds excluded. As well as improved entropy characteristics over versions 1 or 6. If your use case requires greater granularity than UUID vesion 7 can provide, you might consider UUID version 8. UUID version 8 doesn't provide as good entropy characteristics as UUID version 7, but it utilizes timestamp with nanosecond level of precision.
  - yrro 4y ago
    Waiting patiently for these new UUID versions to be supported by uuid(1) or Python...
  - ttfkam 4y ago
    > Both UUIDv8 and UUIDv4 only specify that the version and variant bits are in their correct location. The difference is that UUIDv4 specifies that the remaining 122 bits be pseudo-randomly generated. UUIDv8 suggests that the generated value still be time-based however the implementation details of how that generation happens are up to the implementor. This means we have 48 bits of custom implementation, four bits for the version (1000), 12 more bits of custom implementation, two bits for the variant, and finally 62 bits as the implementation sees fit. This leaves a lot open to the implementor but it has enough rules around it that it can coexist in the existing UUID environment. https://blog.devgenius.io/analyzing-new-unique-identifier-formats-uuidv6-uuidv7-and-uuidv8-d6cc5cd7391a https://blog.devgenius.io/analyzing-new-unique-identifier-fo... In other words, one CAN implement a UUIDv8 with nanosecond precision, but that is not a requirement of UUIDv8. 4 is random. 8 is a custom grab bag.
winrid 4y ago
You can keep the incremental PK for internal stuff, and use an external_id for APIs. Benefit - you can change it if needed.
avianlyric 4y ago
Honestly I think random UUIDs, or just UUIDs in general, make for terrible PKs and generally shouldn’t be used. To solve the multi-tenant issue, I personally prefer either flakeID/hashID that have an ordered component and a random component to make walking IDs hard; Aggressively name spacing your data so a customer ID plus an object ID is always needed as pair to look something up, so trying to walk object IDs can only every result in someone accidentally looking up objects that already belong to them; edge layers that remap and filter internal identifiers via hashing etc so externally all ID are opaque and random; strong and careful access control, that ensures you can only ever lookup ID that belong to you, with careful consideration for side channel timing attacks. Relying on random UUIDs for customer privacy would raise red flags for me. If being able to walk ID is enough to break your security model, then I kinda wonder if you actually have a security model.
- hinkley 4y ago
  It's security in depth in this case, as I recall. There are subtleties in how you confirm or deny the existence of a record and for their particular solution to the problem you would get a 403 vs a 404 for a record you did not have access to. Knowing how much activity a competitor is adding into a project management system isn't a lot of data, but it's more than zero.
  - avianlyric 4y ago
    There’s a simple solution to that. Always return 403, it’s what you should be doing regardless of your underlying data model. You should always be determine right to access a record before attempting to look it up. If you can’t determine ACLs without the lookup, then your permissions layer should always assume the requester doesn’t have permission to view a non-existent record and return a 403. Doing anything else is dangerous, and randomising PKs is a sticky plaster over a badly designed access control system.
codingdave 4y ago
If knowing/guessing an ID bypasses security between tenants, I don't think the ID itself is the security hole.

stickfigure 4y ago

What's wrong with UUIDs? Back in the 90s Microsoft strongly encouraged it. You lose a little performance and a little storage (128 vs 64 bits), but gain the ability to merge databases (think corporate acquisitions) with less pain. At least one distribute datastore (Google Cloud Datastore) works better with random keys rather than incremental keys, it tends to produce fewer tablet splits. At least, it used to, the technology may have changed since then. Honestly it seems like the arguments for/against UUIDs as keys are pretty mild on both sides. Why would it be "terrible" to go one way or the other?

srcreigh 4y ago
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).