Thread: How can fixed and variable width columns perform similarly?

From:
"Siddharth Anand"
Date:

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








From:
Tom Lane
Date:

"Siddharth Anand" <> 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

From:
"Dave Dutcher"
Date:

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: 
[mailto:] On Behalf Of Siddharth Anand
Sent: Friday, April 27, 2007 10:32 AM
To: 
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


From:
"Siddharth Anand"
Date:

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" <> 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
>



From:
Tom Lane
Date:

"Siddharth Anand" <> 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