Skip site navigation (1) Skip section navigation (2)

Re: seeking advise on char vs text or varchar in search table

From: Jim Nasby <decibel(at)decibel(dot)org>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: chrisj <chrisj(dot)wood(at)sympatico(dot)ca>, pgsql-performance(at)postgresql(dot)org
Subject: Re: seeking advise on char vs text or varchar in search table
Date: 2007-04-25 17:30:41
Message-ID: F1267F58-2E7B-480B-9C6E-0669B2A0DBBF@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
On Apr 23, 2007, at 7:16 AM, Merlin Moncure wrote:
> On 4/20/07, chrisj <chrisj(dot)wood(at)sympatico(dot)ca> wrote:
>>
>> I have a table that contains a column for keywords that I expect  
>> to become
>> quite large and will be used for web searches.  I will either  
>> index the
>> column or come up with a simple hashing algorithm add the hash key  
>> to the
>> table and index that column.
>>
>> I am thinking the max length in the keyword column I need to  
>> support is 30,
>> but the average would be less than10
>>
>> Any suggestions on whether to use char(30), varchar(30) or text,  
>> would be
>> appreciated.  I am looking for the best performance option, not  
>> necessarily
>> the most economical on disk.
>
> Don't use char...it pads out the string to the length always.   It
> also has no real advantage over varchar in any practical situation.
> Think of varchar as text with a maximum length...its no faster or
> slower but the database will throw out entries based on length (which
> can be good or a bad thing)...in this case, text feels better.

AIUI, char, varchar and text all store their data in *exactly* the  
same way in the database; char only pads data on output, and in the  
actual tables it still contains the regular varlena header. The only  
reason I've ever used char in other databases is to save the overhead  
of the variable-length information, so I recommend to people to just  
steer clear of char in PostgreSQL.
--
Jim Nasby                                            jim(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



In response to

Responses

pgsql-performance by date

Next:From: Jim NasbyDate: 2007-04-25 17:40:09
Subject: Re: What`s wrong with JFS configuration?
Previous:From: Jim NasbyDate: 2007-04-25 17:26:07
Subject: Fragmentation of WAL files

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group