Re: Composite type storage overhead - Mailing list pgsql-general

From George Neuner
Subject Re: Composite type storage overhead
Date
Msg-id hke7re16oeehbd9hml5nf4ng7huark6kkh@4ax.com
Whole thread Raw
In response to Re: Composite type storage overhead  (Laiszner Tamás <t.laiszner@outlook.com>)
List pgsql-general
On Wed, 23 Oct 2019 21:24:58 +0000, Laiszner Tamás
<t.laiszner@outlook.com> wrote:

 Rob Sargent <robjsargent@gmail.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




pgsql-general by date:

Previous
From: Yessica Brinkmann
Date:
Subject: Re: I think that my data is saved correctly, but when printing again,other data appears
Next
From: Rich Shepard
Date:
Subject: Updating data: confirmation and question