X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E753D9FB316 for ; Sun, 30 Jul 2006 17:29:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 39373-06 for ; Sun, 30 Jul 2006 17:28:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 5DE759FB2C5 for ; Sun, 30 Jul 2006 17:28:55 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so302665nfc for ; Sun, 30 Jul 2006 13:28:54 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=dkI+wzUSuPx3z5AXaNV5dp6un/fjy7HXezPpECWnnvjf1BLroe02SQNyr2Vr4QVSAYOk4WHlcBzKZg4EWiLmP2aF0I8Iki6bBlNVqI16p95ZmH0UQz8dhkKPlVVxUCX2DK2YEGVRYFcErKeCVijfKe1QDgVwyoHv+jz3XH0oCiE= Received: by 10.78.132.12 with SMTP id f12mr341678hud; Sun, 30 Jul 2006 13:28:54 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Sun, 30 Jul 2006 13:28:54 -0700 (PDT) Message-ID: Date: Sun, 30 Jul 2006 15:28:54 -0500 From: "Aaron Bono" To: "Manlio Perillo" Subject: Re: primary keys as TEXT Cc: "Michael Glaesemann" , pgsql-sql@postgresql.org In-Reply-To: <44CA6E29.4000201@libero.it> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_34216_14457788.1154291334627" References: <44C9CCC8.8020304@libero.it> <6ACD839C-D68A-4BD1-804F-B9DEACFC2ED1@seespotcode.net> <44CA6E29.4000201@libero.it> X-Google-Sender-Auth: 6232bfff5ac58118 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.36 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200607/367 X-Sequence-Number: 25941 ------=_Part_34216_14457788.1154291334627 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/28/06, Manlio Perillo wrote: > > Michael Glaesemann ha scritto: > > > > On Jul 28, 2006, at 17:37 , Manlio Perillo wrote: > > > >> There can be performancs problems in having primary keys of type TEXT? > >> What about having a primary key of 3 columns (all of type TEXT)? > > > > What defines a problem in terms of performance is heavily dependent on > > your particular needs and requirements. What are your requirements? What > > profiling have you done to see where your performance bottlenecks may > be? > > > > I still don't have done profiling. > > Simply in the first version of my schema I used serial keys but the > result is ugly and it force me to do a lot of joins. Ugly? Not sure what you mean by that. I do understand the problem with so many joins. I use views so that the joins are only delt with once (in the database) and then all my applications run off the views. That way, the applications use very simple queries. The views also allow me to change the table structure (column names, more table normalization, etc.) without having to make changes to the application. I am even getting ready to start using updatable views so my applications never touch the tables directly - it ads another layer of abstraction between the tables and the application. But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more. ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_34216_14457788.1154291334627 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/28/06, Manlio Perillo <manlio_perillo@libero.it> wrote:
Michael Glaesemann ha scritto:
>
> On Jul 28, 2006, at 17:37 , Manlio Perillo wrote:
>
>> There can be performancs problems in having primary keys of type TEXT?
>> What about having a primary key of 3 columns (all of type TEXT)?
>
> What defines a problem in terms of performance is heavily dependent on
> your particular needs and requirements. What are your requirements? What
> profiling have you done to see where your performance bottlenecks may be?
>

I still don't have done profiling.

Simply in the first version of my schema I used serial keys but the
result is ugly and it force me to do a lot of joins.

 
Ugly?  Not sure what you mean by that.

I do understand the problem with so many joins.  I use views so that the joins are only delt with once (in the database) and then all my applications run off the views.  That way, the applications use very simple queries.  The views also allow me to change the table structure (column names, more table normalization, etc.) without having to make changes to the application.

I am even getting ready to start using updatable views so my applications never touch the tables directly - it ads another layer of abstraction between the tables and the application.  But that may be more complicated than you are ready for - I have yet to decide if it will be worth the effort but I won't know until I experiment with it more.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_34216_14457788.1154291334627--