Обсуждение: The speed problem of Varchar vs. Char
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
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 > 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/ >
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. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
> > 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