text vs varchar(n)

On 17.02.02 21:52 -0800(+0000), Richard Emberson wrote:

Which is faster read and write, a table with text or varchar types:

...

Why would one every use varchar(n) instead of text (aside from non-sql
standard)?

Varchar(n) and text have the same performance. The difference is that
varchars are silently cut if they are longer than the limit specified.
In most cases you want to stick with text.

- Einar Karttunen

Le Lundi 18 F�vrier 2002 06:52, Richard Emberson a �crit :

If ones sets up the db to be UNICODE, does that also apply to the 'text'
type?

Yes, encoding is set at database creation (CREATE DATABASE foo WITH ENCODING
'Unicode') for all database. This applies to all tables and views. Unicode is
handled as fast as Latin1 with no real impact on performances.

When using a Unicode PostgreSQL database, you also need a Unicode backend
like Apache/PHP or Java. If your backend is Latin1, like VB6, PostgreSQL will
not be able to recode from Unicode to Latin1 transparently.

Please also note that some server-side functions do not support unicode yet.

Cheers,
Jean-Michel POURE

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.

Robert

"Robert Treat" <robertt@auctionsolutions.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

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