Re: Composite type storage overhead

On Wed, 23 Oct 2019 21:24:58 +0000, Laiszner Tamás
<t(dot)laiszner(at)outlook(dot)com> wrote:

Rob Sargent <robjsargent(at)gmail(dot)com> 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