Обсуждение: How can fixed and variable width columns perform similarly?
Hi! I read the link below and am puzzled by or curious about something. http://www.postgresql.org/docs/8.1/interactive/datatype-character.html The Tip below is intriguing "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." How can a field that doesn't have a limit like "text" perform similarly to char varying(128), for example? At some point, we need to write data to disk. The more data that needs to be written, the longer the disk write will take, especially when it requires finding free sectors to write to. Another interesting quote from the same page is the following: "Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values. " If the long values are stored in a separate table, on a different part of the disk, doesn't this imply an extra disk seek? Won't it therefore take longer? Sid
"Siddharth Anand" <sid@etsy.com> writes:
> How can a field that doesn't have a limit like "text" perform similarly to
> char varying(128), for example? At some point, we need to write data to
> disk. The more data that needs to be written, the longer the disk write
> will take, especially when it requires finding free sectors to write to.
What's your point? If you're not going to put more than 128 characters
in the field, there's no difference in the amount of data involved.
regards, tom lane
I think the manual is implying that if you store a value like "Sid" in a field either of type varchar(128) or type text there is no performance difference. The manual is not saying that you get the same performance storing a 500k text field as when you store the value "Sid". Dave -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Siddharth Anand Sent: Friday, April 27, 2007 10:32 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] How can fixed and variable width columns perform similarly? Hi! I read the link below and am puzzled by or curious about something. http://www.postgresql.org/docs/8.1/interactive/datatype-character.html The Tip below is intriguing "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." How can a field that doesn't have a limit like "text" perform similarly to char varying(128), for example? At some point, we need to write data to disk. The more data that needs to be written, the longer the disk write will take, especially when it requires finding free sectors to write to. Another interesting quote from the same page is the following: "Long values are also stored in background tables so they do not interfere with rapid access to the shorter column values. " If the long values are stored in a separate table, on a different part of the disk, doesn't this imply an extra disk seek? Won't it therefore take longer? Sid ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Hi Tom, My question wasn't phrased clearly. Oracle exhibits a performance degradation for very large-sized fields (CLOB types that I equate to PostGres' text type) when compared with the performance of field types like varchar that handle a max character limit of a few thousand bytes in Oracle. It sounds like PostGres doesn't exhibit this same difference. I wanted to understand how this could be and whether there was a trade-off. Cheers! Sid > "Siddharth Anand" <sid@etsy.com> writes: >> How can a field that doesn't have a limit like "text" perform similarly >> to >> char varying(128), for example? At some point, we need to write data to >> disk. The more data that needs to be written, the longer the disk write >> will take, especially when it requires finding free sectors to write to. > > What's your point? If you're not going to put more than 128 characters > in the field, there's no difference in the amount of data involved. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
"Siddharth Anand" <sid@etsy.com> writes:
> My question wasn't phrased clearly. Oracle exhibits a performance
> degradation for very large-sized fields (CLOB types that I equate to
> PostGres' text type) when compared with the performance of field types
> like varchar that handle a max character limit of a few thousand bytes in
> Oracle.
> It sounds like PostGres doesn't exhibit this same difference. I wanted to
> understand how this could be and whether there was a trade-off.
Ah. Well, the answer is that we change behavior dynamically depending
on the size of the particular field value, instead of hard-wiring it to
the declared column type. It sounds like Oracle's CLOB might be doing
about the same thing as an out-of-line "toasted" field value in
Postgres. In PG, text and varchar behave identically except that
varchar(N) adds an insert-time check on the length of the field value
--- but this is just a constraint check and doesn't have any direct
influence on how the value is stored.
regards, tom lane