Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2PJ9Ew47005 for ; Sun, 25 Mar 2001 14:09:14 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from linux.scd.net ([209.150.198.180]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2ML1nw19903 for ; Thu, 22 Mar 2001 16:01:49 -0500 (EST) (envelope-from peter@schoenster.com) Received: from home ([209.150.198.179]) by linux.scd.net (8.9.3/8.9.3) with ESMTP id MAA24278 for ; Thu, 22 Mar 2001 12:53:35 -0700 From: "Peter J. Schoenster" Organization: Schoenster To: pgsql-sql@postgresql.org Date: Thu, 22 Mar 2001 14:45:57 -0700 Subject: Re: CHAR or VARCHAR Reply-to: peter@schoenster.com Message-ID: <3ABA1025.26143.F5F9331@localhost> References: <01032208051900.07158@math.unl.edu.ar> In-reply-to: <25819.985273537@sss.pgh.pa.us> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200103/491 X-Sequence-Number: 2162 On 22 Mar 2001, at 10:05, Tom Lane wrote: > There is *no* performance advantage of CHAR(n) over VARCHAR(n). > If anything, there is a performance lossage due to extra disk I/O > (because all those padding blanks take space, and time to read). > > My advice is to use CHAR(n) when that semantically describes your data > (ie, truly fixed-width data, like US postal codes), or VARCHAR(n) when > that semantically describes your data (ie, variable-width with a hard > upper bound), or TEXT when that semantically describes your data (ie, > variable width with no specific upper bound). Worrying about > performance differences is a waste of time, because there aren't any. I wonder if this question of char/varchar is postgresql specific or rdbms in general. In any case I did some looking around the web and in the end I think the best advice is what Tom says above. Here are some pertinent things I found after entering (Index Performance Char vs Varchar) in google. http://www.swynk.com/friends/mcgehee/developers_tuning_tutorial. asp > If the text data in a column varies greatly in length, use a VARCHAR > data type instead of a CHAR data type. Although the VARCHAR data type > has slightly more overhead than the CHAR data type, the amount of > space saved by using VARCHAR over CHAR on variable length columns can > reduce I/O, improving overall SQL Server performance. Forgot URL: > Several other people at the session who were familiar with the > performance effects of using char vs. varchar confirmed my advice. One > person said his team was charged with deploying an application that > used SQL Server. After deploying the application, the team found that > it performed terribly. Upon inspecting the database, team members > discovered that all the fields were varchar. They changed the fields > to char, and the application now performs fine. Forgot URL: > Here's the advice from IBM in from the DB2 Admin guide SC96-9003) > Copyright IBM Corp. 1982, 1999 " Choosing CHAR or VARCHAR: VARCHAR > saves DASD space, but costs a 2-byte > overhead for each value and the additional processing required for > varying-length records. Thus, CHAR is preferable to VARCHAR, > unless the space saved by the use of VARCHAR is significant. The > savings are not significant if the maximum length is small or the > lengths of the values do not have a significant variation. In > general, do not define a column as VARCHAR(n) unless n is at least > 18. (Consider, also, using data compression if your main > concern is DASD savings. See "Compressing data in a table space or > partition" in topic 2.6.2 for more information.) > > If you use VARCHAR, do not specify a maximum length that is greater > than necessary. Although VARCHAR saves space in a table space, it > does not save space in an index, because index records are padded with > blanks to the maximum length. Note particularly the restrictions on > columns of strings longer than 255 bytes; for example, they cannot be > indexed. These restrictions are listed in Chapter 3 of DB2 SQL > Reference." > > David Seibert > Compuware Corporation File-AID product planner > Dave_Seibert@Compuware.com > > --------------------------- "Reality is that which, when you stop believing in it, doesn't go away". -- Philip K. Dick