X-Original-To: pgsql-admin@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D4998474E44 for ; Wed, 2 Apr 2003 17:24:20 -0500 (EST) Received: from proteus.affordablehost.com (unknown [216.46.203.177]) by postgresql.org (Postfix) with SMTP id 5D46247631F for ; Wed, 2 Apr 2003 17:24:18 -0500 (EST) Received: (qmail 2506 invoked from network); 2 Apr 2003 22:24:17 -0000 Received: from unknown (HELO e-greek.net) (66.92.74.181) by 216.46.203.177 with SMTP; 2 Apr 2003 22:24:17 -0000 Message-ID: <3E8B6316.6080505@e-greek.net> Date: Wed, 02 Apr 2003 17:24:22 -0500 From: Andrew Biagioni User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Juan Miguel Cc: pgsql-admin@postgresql.org Subject: Re: Speed & Memory Management References: <200304022313.17633.juanmime@ono.com> In-Reply-To: <200304022313.17633.juanmime@ono.com> Content-Type: multipart/alternative; boundary="------------020308050202020409010808" X-Spam-Status: No, hits=-33.8 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,HTML_10_20,IN_REP_TO,REFERENCES, REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/29 X-Sequence-Number: 8184 This is a multi-part message in MIME format. --------------020308050202020409010808 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Juan Miguel wrote: >>That would buy you absolutely nothing. char() is not faster than text. >>Instead you would make everything slower because the system would >>constantly have to pad and trim your values and it would bloat the storage >>with the extra spaces. >> >> > >First, sorry about my english. > >Well ... but ... reading database theory books, you can see that fixed size >records are "better" than variant size records. When the records are size >fixed, inserts, updates and deletes can recalculate easier the position of >the rest records in the pages. These books tell you that in general cases, >variant record size tables (records that have variant type colums, for >example) imply a lower performance in the system ... > >Why in postgresql these things don't matter ? > > Because unlike other DBs, PostgreSQL doesn't actually remove anything when you do an update or delete, it marks the old record as "dead" and (for an update) adds a new instance of the record at the end. That's one reason why you want to vacuum tables after some activity, to remove the dead records fro all updates/deletes. >Thanks. > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > > > > --------------020308050202020409010808 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

Juan Miguel wrote:
That would buy you absolutely nothing.  char() is not faster than text.
Instead you would make everything slower because the system would
constantly have to pad and trim your values and it would bloat the storage
with the extra spaces.
    

First, sorry about my english.

Well ... but ... reading database theory books, you can see that fixed size 
records are "better" than variant size records. When the records are size 
fixed,  inserts, updates and deletes can recalculate easier the position of 
the rest records in the pages. These books tell you that in general cases, 
variant record size tables (records that have variant type colums, for 
example) imply a lower performance in the system ...

Why in postgresql these things don't matter ?
  
Because unlike other DBs, PostgreSQL doesn't actually remove anything when you do an update or delete, it marks the old record as "dead" and (for an update) adds a new instance of the record at the end.  That's one reason why you want to vacuum tables after some activity, to remove the dead records fro all updates/deletes.

Thanks.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


  
--------------020308050202020409010808--