On Wed, 23 Oct 2019 21:24:58 +0000, Laiszner Tamás <t.laiszner@xxxxxxxxxxx> wrote: Rob Sargent <robjsargent@xxxxxxxxx> wrote >> Why not use UUID type? > 1. > Although it does not enforce, but the UUID type kind of suggests a > specific interpretation of the data. Of course the documentation says > you are free to use any algorithm to generate the values, but there > are quite a few standard UUID types and we are not planning to use > any of them. The usual interpretation suggests an IP address and a timestamp, but there is nothing that /requires/ that interpretation. The value is just a large integer and you can treat it that way if you want. Postgresql doesn't check UUID data for any particular format. You can store arbitrary integer values into UUID columns - with the caveat that, as UUIDs, you can only compare them and can't (easily) do any arithmetic. > 2. > The serialization format is different than needed by the application > and, while once again this is not a hard technical barrier, that > might cause slight additional complexity and confusion. Not sure what is the issue here. Admittedly I don't know how pglib passes binary UUIDs[*] but ceratinly they can be sent as strings if necessary. [*] Off the top of my head, I would guess a struct or array of four 32-bit values, but truly I haven't looked. > 3. > The value is logically defined as a 128-bit integer, that is in itself > a compound value split into a few "bit groups". Extracting these > parts can be done by simple (and supposedly efficient) bitwise > operators when stored as integer, but becomes much more cumbersome > with UUID, I guess. The groupings are only for display / printing, to make it easier for humans to read. WRT mixing logic into the key (sharding, etc.), all UUIDs except type 4 can provide you with a reasonable static sharding key. And even type 4 works if you shard by time. Also, keep in mind that a UUID can be generated by a client or a key server and thus have no relationship to the DBMS server that stores it. Depending on how you choose to generate and use it, a UUID doesn't necessarily carry or reveal any exploitable information. YMMV, George
