The speed problem of Varchar vs. Char

Since VARCHAR length is run length encoded
and CHAR is fixed length
If your string will always be about the same size set as CHAR(MaximumLength)
otherwise use VARCHAR

M
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: "Crystal" <support@conceptpatterns.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, July 08, 2007 7:15 PM
Subject: [GENERAL] The speed problem of Varchar vs. Char

On 7/8/07, Crystal <support@conceptpatterns.com> wrote:

Hi All,

Our company need to save contact details into the PostgreSQL database. I
just begin to learn it, so I got many questions. I am not sure which
data type I should choose for website address, varchar or char. The
website address may be very long, and we also don't want to lose the
speed. Thus, the question is: if we have a large contact database, how
much slowdown or speed up will be expected if we choose variable length
rather than fixed length? Thanks forward.

Best Wishes,
Crystal

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

See http://www.postgresql.org/docs/8.2/interactive/datatype-character.html
(or whatever the corresponding page is for the version you're using).
Specifically, the following:

Tip: There are no performance differences between these three types,
apart from the increased storage size when using the blank-padded
type. While character(n) has performance advantages in some other
database systems, it has no such advantages in PostgreSQL. In most
situations text or character varying should be used instead.

- Josh

Crystal <support@conceptpatterns.com> writes:

Our company need to save contact details into the PostgreSQL database. I
just begin to learn it, so I got many questions. I am not sure which
data type I should choose for website address, varchar or char.

Use varchar. Or text, if you don't have a specific upper limit in mind.

The website address may be very long, and we also don't want to lose the
speed. Thus, the question is: if we have a large contact database, how
much slowdown or speed up will be expected if we choose variable length
rather than fixed length? Thanks forward.

Once upon a time, in the days of 80-column punch cards and no
variable-length character encodings, there were databases that could
handle fixed-width character fields a bit faster than variable-width.
That doesn't apply to Postgres. There is no, none, nada performance
advantage to char(n), and you should never use it unless your
application data clearly demands a specific field width.

regards, tom lane

Josh Tolley wrote:

On 7/8/07, Crystal <support@conceptpatterns.com> wrote:

Hi All,

Our company need to save contact details into the PostgreSQL database. I
just begin to learn it, so I got many questions. I am not sure which
data type I should choose for website address, varchar or char. The
website address may be very long, and we also don't want to lose the
speed. Thus, the question is: if we have a large contact database, how
much slowdown or speed up will be expected if we choose variable length
rather than fixed length? Thanks forward.

Best Wishes,
Crystal

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

See http://www.postgresql.org/docs/8.2/interactive/datatype-character.html
(or whatever the corresponding page is for the version you're using).
Specifically, the following:

Tip: There are no performance differences between these three types,
apart from the increased storage size when using the blank-padded
type. While character(n) has performance advantages in some other
database systems, it has no such advantages in PostgreSQL. In most
situations text or character varying should be used instead.

- Josh

-- Thanks a lot. Then we will use text to represent this.

Once upon a time, in the days of 80-column punch cards and no
variable-length character encodings, there were databases that could
handle fixed-width character fields a bit faster than variable-width.
That doesn't apply to Postgres. There is no, none, nada performance
advantage to char(n), and you should never use it unless your
application data clearly demands a specific field width.

I still often use char(n) a lot, mainly for documentation purposes. If a
column will only ever hold exactly a fixed length string (status codes
and stuff), it looks better in the datamodel to use char(n) IMHO.

Tim