Re: Composite types or composite keys?

On Fri, Nov 15, 2013 at 2:01 AM, Tony Theodore <tony(dot)theodore(at)gmail(dot)com> wrote:
> Hi,
>
> I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this:
>
> create table products (
> source_system text,
> product_id text,
> description text,
> ...
> primary key (source_system, product_id)
> );
> create table inventory (
> source_system text,
> product_id text,
> qty int,
> ...
> foreign key (source_system, product_id) references products
> );
>
>
> and it means having to add the “source_system" column to many queries. Would something like:
>
> create type product as (
> source_system text,
> product_id text
> );
> create table products (
> product product,
> description text,
> ...
> primary key(product)
> );
> create table inventory (
> product product,
> qty numeric,
> ...
> foreign key (product) references products
> );
>
> be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice.

Well, here are the downsides. Composite types:
*) are more than the sum of their parts performance-wise. So there is
a storage penalty in both the heap and the index
*) can't leverage indexes that are querying only part of the key
*) will defeat the implicit 'per column NOT NULL constraint' of the primary keys
*) are not very well supported in certain clients -- for example JAVA.
you can always deal with them as text, but that can be a headache.

...plus some other things I didn't think about. If you can deal with
those constraints, it might be interesting to try a limited
experiment. The big upside of composite types is that you can add
attributes on the fly without rebuilding the index. Test carefully.

merlin