Re: Composite types or composite keys?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Tony Theodore <tony(dot)theodore(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Composite types or composite keys?
Date: 2013-11-15 16:01:31
Message-ID: CAHyXU0zqKcm5eFLxwmx9k17WpkxME33fR8PMJoegW_2QMcDONg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2013-11-15 16:21:35 Re: Push predicate down in view containing window function
Previous Message Janek Sendrowski 2013-11-15 15:35:09 Re: Install pg_trgm from source