Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Beth Gatewood" <beth@vizxlabs.com>,
	"'Bruce Momjian'" <pgman@candle.pha.pa.us>
Subject: Re: indexing on char vs varchar
Date: Wed, 2 Oct 2002 10:55:22 -0700
Cc: <pgsql-sql@postgresql.org>
References: <000001c26a36$c1a48c80$0c00000a@bethvizx>
In-Reply-To: <000001c26a36$c1a48c80$0c00000a@bethvizx>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200210021055.22641.josh@agliodbs.com>

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=20
length of the stored string before the data itself, while CHAR does not=20
require this information because it is fixed-length.  This makes the CHAR=
=20
datatype marginally smaller, and thus faster, than the VARCHAR data type on=
=20
those databases.   This difference goes back to much older databases and=20
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=
=20
performance difference between CHAR and VARCHAR is not measurable unless yo=
u=20
are getting close to the 8K data page limit that MSSQL imposes.  YMMV.

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

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

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco