pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alexandre Leclerc <alexandre.leclerc@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Data type to use for primary key
Date: Tue, 23 Nov 2004 11:29:45 -0500
Message-ID: <1dc7f0e30411230829bd1ed@mail.gmail.com> (raw)
In-Reply-To: <200411221654.56673.josh@agliodbs.com>
References: <1dc7f0e30411221232603c4617@mail.gmail.com>
	<200411221654.56673.josh@agliodbs.com>

On Mon, 22 Nov 2004 16:54:56 -0800, Josh Berkus <josh@agliodbs.com> wrote:
> Alexandre,
> 
> > What is the common approach? Should I use directly the product_code as
> > my ID, or use a sequantial number for speed? (I did the same for the
> > company_id, this is a 'serial' and not the shor name of the customer.
> > I just don't know what is usually done.
> 
> Don't use SERIAL just because it's there.    Ideally, you *want* to use the
> product_code if you can.   It's your natural key and a natural key is always
> superior to a surrogate key all other things being equal.
> 
> Unfortunately, all other things are NOT equal.    Here's the reasons why you'd
> use a surrogate key (i.e. SERIAL):
> 
> 1) because the product code is a large text string  (i.e. > 10bytes) and you
> will have many millions of records, so having it as an FK in other tables
> will add significantly to the footprint of the database;

Thanks for those tips. I'll print and keep them. So in my case, the
product_code being varchar(24) is:
4 bytes + string size (so possibly up to 24) = possible 28 bytes. I
did the good thing using a serial. For my shorter keys (4 bytes + up
to 6 char) I will use the natural key.

This is interesting, because this is what I did right now.

The "transparent surrogate keying" proposal that is discussed bellow
in the thread is a very good idea. It would be nice to see that. It
would be easier for the DB admin and the coder; the moment this is not
slowing the system. : )

Best regards.

-- 
Alexandre Leclerc



view thread (14+ messages)  latest in thread

Message-ID: <1dc7f0e30411230829bd1ed@mail.gmail.com>
Permalink:  ../1dc7f0e30411230829bd1ed@mail.gmail.com/
Also on:    postgresql.org/message-id/1dc7f0e30411230829bd1ed@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: alexandre.leclerc@gmail.com
  Subject: Re: Data type to use for primary key
  In-Reply-To: <1dc7f0e30411230829bd1ed@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox