Thread: Fixed width rows faster?

Fixed width rows faster?

From
Jeff
Date:
I'm guessing the answer to this is "no"

Is there any performance advantage to using a fixed width row (with PG)?

I've heard this theory a few times and I think it is based on older,
different databases and we have also some custom software here that
uses fixed width rows to be able to hit row N in O(1), but that isn't
what I'd call a real database.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Re: Fixed width rows faster?

From
Bill Moran
Date:
Jeff wrote:
> I'm guessing the answer to this is "no"
>
> Is there any performance advantage to using a fixed width row (with PG)?

No.  The user docs state that the performance is equal for char, varchar
and text.

> I've heard this theory a few times and I think it is based on older,
> different databases

MySQL used to have this issue (I don't know if it still does or not) to
the point that the docs once claimed that an index on a varchar was barely
as fast as a char with no index at all.

> and we have also some custom software here that uses
> fixed width rows to be able to hit row N in O(1), but that isn't what
> I'd call a real database.

Isn't needed in modern versions of Postgres, but I don't know (historically)
if it ever was or not.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com


Re: Fixed width rows faster?

From
Dennis Bjorklund
Date:
On Fri, 5 Mar 2004, Jeff wrote:

> Is there any performance advantage to using a fixed width row (with PG)?

As far as I know there is only a small win when you want to extract some
field from a tuple and with variable width fields you have to walk to the
correct field. But this is a small performance problem unless you have
very many variable size columns in the table.

> different databases and we have also some custom software here that
> uses fixed width rows to be able to hit row N in O(1)

This can not happen in pg since there is no row N. Every transaction can
have a different view of the table, some rows are visible and some others
are not. To find row N you have to walk from the start and inspect every
tuple to see if it's visible to this transaction or not.

--
/Dennis Björklund


Re: Fixed width rows faster?

From
Josh Berkus
Date:
Jeff, Bill:

> No.  The user docs state that the performance is equal for char, varchar
> and text.

Actually, CHAR is slightly *slower* than VARCHAR or TEXT for SELECTs in many
applications.   This is becuase of the field padding, and the frequent
necessity of casting CHAR::TEXT and back.

For INSERT and UPDATE, TEXT is the fastest becuase it's not checking a length
constraint (takes time) or padding the field out to the required CHAR length
(even more time).

Frankly, the only reason to use anything other than TEXT is compatibility with
other databases and applications.


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: Fixed width rows faster?

From
Josh Berkus
Date:
Jeff:

> As far as I know there is only a small win when you want to extract some
> field from a tuple and with variable width fields you have to walk to the
> correct field. But this is a small performance problem unless you have
> very many variable size columns in the table.

BTW, Dennis here is not talking about CHAR; CHAR is handled as a
variable-length field in Postgres.    INTEGER is a fixed-width field.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: Fixed width rows faster?

From
Mike Nolan
Date:
> Frankly, the only reason to use anything other than TEXT is compatibility with
> other databases and applications.

You don't consider a requirement that a field be no longer than a
certain length a reason not to use TEXT?
--
Mike Nolan

Re: Fixed width rows faster?

From
Rod Taylor
Date:
On Fri, 2004-03-05 at 18:43, Mike Nolan wrote:
> > Frankly, the only reason to use anything other than TEXT is compatibility with
> > other databases and applications.
>
> You don't consider a requirement that a field be no longer than a
> certain length a reason not to use TEXT?

Actually, I don't. Good reason to have a check constraint on it though
(hint, check constraints can be changed while column types cannot be, at
this moment).


Re: Fixed width rows faster?

From
Christopher Kings-Lynne
Date:
>>You don't consider a requirement that a field be no longer than a
>>certain length a reason not to use TEXT?

Can't you just create a TEXT(255) field same as you can just create
VARCHAR  (with no length) field?  I think they're basically synonyms for
each other these days.

Chris

Re: Fixed width rows faster?

From
Mike Nolan
Date:
> >>You don't consider a requirement that a field be no longer than a
> >>certain length a reason not to use TEXT?
>
> Can't you just create a TEXT(255) field same as you can just create
> VARCHAR  (with no length) field?  I think they're basically synonyms for
> each other these days.

I'll defer to the SQL standard gurus on this, as well as to the internals
guys, but I suspect there is a difference between the standard itself
and implementor details, such as how char, varchar, varchar2 and text
are implemented.  As long as things work as specified, I don't think
the standard cares much about what's happening behind the curtain.
--
Mike Nolan

Re: Fixed width rows faster?

From
Tom Lane
Date:
Mike Nolan <nolan@gw.tssi.com> writes:
>> Frankly, the only reason to use anything other than TEXT is
>> compatibility with other databases and applications.

> You don't consider a requirement that a field be no longer than a
> certain length a reason not to use TEXT?

If you have an actual business-logic requirement to restrict a field to
no more than N characters, then by all means use varchar(N); that's
what it's for.  But I agree with what I think Josh meant: there is very
seldom any non-broken reason to have a hard upper limit on string
lengths.  If you think you need varchar(N) you should stop and ask
why exactly.  If you cannot give a specific, coherent reason why the
particular value of N that you're using is the One True Length for the
field, then you really need to think twice.

            regards, tom lane

Re: Fixed width rows faster?

From
Tom Lane
Date:
Mike Nolan <nolan@gw.tssi.com> writes:
>> Can't you just create a TEXT(255) field same as you can just create
>> VARCHAR  (with no length) field?  I think they're basically synonyms for
>> each other these days.

> I'll defer to the SQL standard gurus on this, as well as to the internals
> guys, but I suspect there is a difference between the standard itself
> and implementor details, such as how char, varchar, varchar2 and text
> are implemented.  As long as things work as specified, I don't think
> the standard cares much about what's happening behind the curtain.

TEXT is not a standard datatype at all; that is, you will not find it
in the standard, even though quite a few DBMSes have a datatype that
they call by that name.

Postgres' interpretation of TEXT is that there is no length-limitation
option.  I don't know what other DBMSes do with their versions of TEXT.

            regards, tom lane

Re: Fixed width rows faster?

From
Christopher Kings-Lynne
Date:
> If you have an actual business-logic requirement to restrict a field to
> no more than N characters, then by all means use varchar(N); that's
> what it's for.  But I agree with what I think Josh meant: there is very
> seldom any non-broken reason to have a hard upper limit on string
> lengths.  If you think you need varchar(N) you should stop and ask
> why exactly.  If you cannot give a specific, coherent reason why the
> particular value of N that you're using is the One True Length for the
> field, then you really need to think twice.

One nice reason to have like VARCHAR(4096) or whatever is that if there
is a bug in your website and you forget to length check some user input,
it stops them from screwing you by uploading megs and megs of data into
a 'firstname' field, say.

Chris


Re: Fixed width rows faster?

From
"Eric Jain"
Date:
> Frankly, the only reason to use anything other than TEXT is
> compatibility with other databases and applications.

The main reason why I am still using VARCHAR rather than TEXT in many
places is to ensure that the column can be indexed. Postgres, it seems,
refuses to insert a string that is longer than some value into an
indexed column, and I'll rather have such errors flagged while inserting
a row rather than while rebuilding an index after having inserted lots
of rows.


Re: Fixed width rows faster?

From
Andrew Sullivan
Date:
On Sat, Mar 06, 2004 at 02:17:35PM +0100, Eric Jain wrote:
> places is to ensure that the column can be indexed. Postgres, it seems,
> refuses to insert a string that is longer than some value into an
> indexed column, and I'll rather have such errors flagged while inserting

Care to provide some details of this?  It sure sounds like a bug to
me, if it's true.  I've never run into anything like this, though.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
                --Brad Holland

Re: Fixed width rows faster?

From
Tom Lane
Date:
"Eric Jain" <Eric.Jain@isb-sib.ch> writes:
> The main reason why I am still using VARCHAR rather than TEXT in many
> places is to ensure that the column can be indexed. Postgres, it seems,
> refuses to insert a string that is longer than some value into an
> indexed column, and I'll rather have such errors flagged while inserting
> a row rather than while rebuilding an index after having inserted lots
> of rows.

This is bogus reasoning.  The limit on index entry length will not
change when you rebuild the index.

            regards, tom lane

Re: Fixed width rows faster?

From
Mike Nolan
Date:
> Actually, I don't. Good reason to have a check constraint on it though
> (hint, check constraints can be changed while column types cannot be, at
> this moment).

Is there a way to copy a table INCLUDING the check constraints?  If not,
then that information is lost, unlike varchar(n).
--
Mike Nolan


Re: Fixed width rows faster?

From
Rod Taylor
Date:
On Sat, 2004-03-06 at 20:16, Mike Nolan wrote:
> > Actually, I don't. Good reason to have a check constraint on it though
> > (hint, check constraints can be changed while column types cannot be, at
> > this moment).
>
> Is there a way to copy a table INCLUDING the check constraints?  If not,
> then that information is lost, unlike varchar(n).

No, not constraints.


Re: Fixed width rows faster?

From
Neil Conway
Date:
Mike Nolan wrote:
> Is there a way to copy a table INCLUDING the check constraints?  If not,
> then that information is lost, unlike varchar(n).

"pg_dump -t" should work fine, unless I'm misunderstanding you.

-Neil

Re: Fixed width rows faster?

From
Mike Nolan
Date:
> Mike Nolan wrote:
> > Is there a way to copy a table INCLUDING the check constraints?  If not,
> > then that information is lost, unlike varchar(n).
>
> "pg_dump -t" should work fine, unless I'm misunderstanding you.

I was specifically referring to doing it in SQL.

The COPY command goes from table to file or file to table, the
CREATE TABLE ... SELECT loses the check constraints.

Is there no SQL command that allows me to clone a table, including check
constraints?

Something like COPY TABLE xxx TO TABLE yyy WITH CHECK CONSTRAINTS.
--
Mike Nolan

Re: Fixed width rows faster?

From
Rod Taylor
Date:
On Sat, 2004-03-06 at 21:26, Mike Nolan wrote:
> > Mike Nolan wrote:
> > > Is there a way to copy a table INCLUDING the check constraints?  If not,
> > > then that information is lost, unlike varchar(n).
> >
> > "pg_dump -t" should work fine, unless I'm misunderstanding you.
>
> I was specifically referring to doing it in SQL.
>
> The COPY command goes from table to file or file to table, the
> CREATE TABLE ... SELECT loses the check constraints.
>
> Is there no SQL command that allows me to clone a table, including check
> constraints?

There is not in the spec or in PostgreSQL. Although, this may be a
relevant extension to the LIKE structure inheritance in 200N spec
(partly implemented 7.4).


Re: Fixed width rows faster?

From
Dennis Bjorklund
Date:
On Sat, 6 Mar 2004, Andrew Sullivan wrote:

> > places is to ensure that the column can be indexed. Postgres, it seems,
> > refuses to insert a string that is longer than some value into an
> > indexed column, and I'll rather have such errors flagged while inserting
>
> Care to provide some details of this?  It sure sounds like a bug to
> me, if it's true.  I've never run into anything like this, though.

There is a limit of the size of values that can be indexed. I think it's
8k or something (a block I assume). Something for someone with an itch to
fix in the future.

The error however comes when you try to insert the value. Doing a reindex
will not change the length of the value and will always work.

--
/Dennis Björklund


Re: Fixed width rows faster?

From
Andrew Sullivan
Date:
On Sun, Mar 07, 2004 at 02:42:41PM +0100, Dennis Bjorklund wrote:

> The error however comes when you try to insert the value. Doing a reindex
> will not change the length of the value and will always work.

I didn't do a good job in my quoting, but this is what I meant.  It'd
surely be a bug if you could get a value into an indexed field, but
couldn't later rebuild that index.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun

Re: Fixed width rows faster?

From
"Eric Jain"
Date:
> This is bogus reasoning.  The limit on index entry length will not
> change when you rebuild the index.

What I meant by 'rebuilding' was not issuing a REINDEX command, but
creating a new index after having dropped the index and inserted
whatever records. Building indexes can be slow, and I'd rather not have
the operation fail after several hours because record #98556761 is
deemed to be too long for indexing...

While we are busy complaining, it's a pity Postgres doesn't allow us to
disable and later recreate all indexes on a table using a single command
;-)