Обсуждение: Composite type storage overhead

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.
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.
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.
   

P {margin-top:0;margin-bottom:0;}

That's an absolutely reasonable suggestion. I am still in the exploration phase so while this solution is not completely ruled out, I have some concerns about it:

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.
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.
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.
   

---

Feladó: Rob Sargent <robjsargent@gmail.com>
Elküldve: 2019. október 23., szerda 22:58
Címzett: Laiszner Tamás <t.laiszner@outlook.com>
Másolatot kap: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Tárgy: Re: Composite type storage overhead

 

On Oct 23, 2019, at 1:32 PM, Laiszner Tamás <t.laiszner@outlook.com> wrote:

Hey there,

I am currently exploring the options to utilize 128-bit numeric primary keys. One of the options I am looking at is to store them as composites of two 64-bit integers.

I would highly appreciate any comments or additional information on this topic.

Best regards,

Tamas

Why not use UUID type?

Putting logic and meaning into primary keys?  To what end, I wonder.

> 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.

This is usually a bad idea. 

Putting logic into the primary key value and merging different types of information in a single column is typically not
sucha good idea. 
 
(And it violates first normal form to begin with) 

I would strongly recommend to revisit this idea, and e.g. think about a multi-column primary key instead. Where each of
these"groups" are stored in a separate column where the actual (business) value can be retrieved without any
bitshiftingor similar operations. 
 

Thomas

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