Re: indexing on char vs varchar

Beth,

> Sorry....I don't understand.  The length is at the front of what?

In some RDBMSs, the VARCHAR data type has a 2 or 4-byte indicator of the
length of the stored string before the data itself, while CHAR does not
require this information because it is fixed-length.  This makes the CHAR
datatype marginally smaller, and thus faster, than the VARCHAR data type on
those databases.   This difference goes back to much older databases and
computers, where every byte of a row counted in terms of performance.

In my experience, even though MS SQL Server still functions this way, the
performance difference between CHAR and VARCHAR is not measurable unless you
are getting close to the 8K data page limit that MSSQL imposes.  YMMV.

Postgres does not materially differentiate between CHAR, VARCHAR, and TEXT,
except that CHAR is padded by spaces and VARCHAR often has a length limit.
However, in terms of storage efficiency (and indexing efficiency), they are
identical.  In Postgres, the character count is included in all string data
types.

Thus, you should use the data type most appropriate to the data you are
storing, ignoring performance issues.  If the data is a fixed-length string
(such as a required zip code) use CHAR; if it's variable but limited, use
varchar; if it's a long description, use TEXT.

--
-Josh BerkusAglio Database SolutionsSan Francisco