X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 638713A3F95 for ; Tue, 23 Nov 2004 16:30:01 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12700-09 for ; Tue, 23 Nov 2004 16:29:46 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.199]) by svr1.postgresql.org (Postfix) with ESMTP id EA5083A3F59 for ; Tue, 23 Nov 2004 16:29:46 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 40so221736wri for ; Tue, 23 Nov 2004 08:29:45 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=nRyMLE3lXHkgOk+DhCzLLFYvmp/1t9LN9HbDtFRI9TW+IiOGyxT07+RL8RyRzASGCoz9qQox2BwRSyBTKlxPySWvMqJbo9vaXSbgSzm/9H5E9LY42wINnkKeYWdUBXjxy++bTKPuYwN5zJevNLjhrTjfjUnP+LxFPgQuBSl+JFM= Received: by 10.54.20.74 with SMTP id 74mr92119wrt; Tue, 23 Nov 2004 08:29:45 -0800 (PST) Received: by 10.54.20.6 with HTTP; Tue, 23 Nov 2004 08:29:45 -0800 (PST) Message-ID: <1dc7f0e30411230829bd1ed@mail.gmail.com> Date: Tue, 23 Nov 2004 11:29:45 -0500 From: Alexandre Leclerc Reply-To: Alexandre Leclerc To: pgsql-performance@postgresql.org Subject: Re: Data type to use for primary key In-Reply-To: <200411221654.56673.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <1dc7f0e30411221232603c4617@mail.gmail.com> <200411221654.56673.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/353 X-Sequence-Number: 9307 On Mon, 22 Nov 2004 16:54:56 -0800, Josh Berkus 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