Re: text vs varchar(n)

"Robert Treat" <robertt(at)auctionsolutions(dot)com> writes:
> 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)?

There is no benefit because there is no such thing as allocation ahead
of time. More, there is a loss of performance on insert/update because
you have to go through the length-constraint-checking code.

> 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.

As a rule of thumb: if there's not a clear application-defined limit for
a field length, you shouldn't make one up in order to use varchar(n).
Numbers like "255" are surely made up, not driven by application
logic...

regards, tom lane