Thanks for the suggestion to split up the primary key into components. But even going down this way, packing the components into one superstructure (composite type) would be beneficial as the same scheme is used across multiple tables. And we are back at the original problem.
Feladó: Thomas Kellerer <spam_eater@gmx.net>
Elküldve: 2019. október 24., csütörtök 8:19
Címzett: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Tárgy: Re: Composite type storage overhead
> 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 such a 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 bitshifting or similar operations.
Thomas