Thread: Fixed width rows faster?
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/
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
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
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
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
> 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
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).
>>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
> >>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
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
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
> 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
> 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.
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
"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
> 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
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.
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
> 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
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).
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
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
> 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 ;-)