From: Josh Berkus <josh@agliodbs.com>
To: Beth Gatewood <beth@vizxlabs.com>
To: 'Bruce Momjian' <pgman@candle.pha.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: indexing on char vs varchar
Date: Wed, 2 Oct 2002 10:55:22 -0700
Message-ID: <200210021055.22641.josh@agliodbs.com> (raw)
In-Reply-To: <000001c26a36$c1a48c80$0c00000a@bethvizx>
References: <000001c26a36$c1a48c80$0c00000a@bethvizx>
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 Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, beth@vizxlabs.com, pgman@candle.pha.pa.us
Subject: Re: indexing on char vs varchar
In-Reply-To: <200210021055.22641.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox