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 3A8943A434E for ; Wed, 24 Nov 2004 06:53:02 +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 39533-05 for ; Wed, 24 Nov 2004 06:52:56 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 6D81D3A4322 for ; Wed, 24 Nov 2004 06:52:56 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CWr1J-0001O5-00; Wed, 24 Nov 2004 01:52:53 -0500 To: Alexandre Leclerc Cc: pgsql-performance@postgresql.org Subject: Re: Data type to use for primary key References: <1dc7f0e30411221232603c4617@mail.gmail.com> <200411221654.56673.josh@agliodbs.com> <1dc7f0e30411230829bd1ed@mail.gmail.com> In-Reply-To: <1dc7f0e30411230829bd1ed@mail.gmail.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 24 Nov 2004 01:52:52 -0500 Message-ID: <87hdnfwxff.fsf@stark.xeocode.com> Lines: 37 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii 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/365 X-Sequence-Number: 9319 Alexandre Leclerc writes: > 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. Realize that space usage is really only part of the issue. If you ever have two records with the same natural key or a record whose natural key has changed you'll be in for a world of hurt if you use the natural key as the primary key in your database. Basically I never use natural keys except when they're arbitrarily chosen values defined by the application itself. Situations where I've used varchars instead of integer keys are things like: . Individual privileges grantable in a security system. (things like "VIEWUSER" "EDITUSER" privileges) . Reference tables for one letter codes used to indicate the type of object represented by the record. Actually I see one interesting exception to my policy in my current database schema. And I don't think I would do this one differently given the choice either. The primary key of the postal code table is the postal code. (postal codes are up here in the great white north like zip codes down there.) This could hurt if they ever reuse an old previously retired postal code, which isn't an entirely impossible case. As far as I know it hasn't happened yet though. And it's just so much more convenient having the postal code handy instead of having to join against another table to look it up. -- greg