On 19.02.02 10:30 -0500(+0000), Robert Treat wrote:
> I had always thought that the db would get *some* performance increase
> simply by knowing that x column in a table would never be longer than n
> characters, meaning it could allocate space ahead of time for those columns
> as needed. Is this correct or is there really no benefit to using
> varchar(n)?
>
> I want to clarify because one of my coworkers is considering switching a
> table he has that is all text fields to all varchar(255) and if there really
> is no benefit I'll tell him to save his time. Furthermore I'd actually start
> recomending to people to use text fields rather than varchar(n) if this is
> true.
>
Please read part 3.3 from users guide for version 7.2. There is no difference
in performance. Both of them need to calculate the length of the input to
decide what to do, i.e. to toast or not to toast. In output the size of the
string is already known. Note also that size and length of the string may
differ in more complex encodings.
There are only two scenarios that I am aware of in which varchar is better:
* you need to use some other db without text datatype (like mysql (it's text support is broken))
* you want to enforce the length constraint
ps. Note how the handling of too large strings has changed from 7.1 to 7.2
- Einar Karttunen