Thread: problem about maximum row size ?
Hi ALL ,
I have a question about maximum row size .
In pgsql FAQ, I can find this description :
...
Maximum size for a row? 1.6TB
...
does it mean 1600 text column in a table and 1G every column ?
but, when I test this case by psqlodbc, I got some error :
1.create table testMaxRowSize(c1 text, c2 text, ......c1599 text, c1600
text)
2.insert into testMaxRowSize values('1','2',......'1599','1600')
3.error occurred:
--ERROR: row is too big: size 12832, maximum size 8136
does it mean maximum row size 8136 ?
and int4 is 4 bytes , int8 is 8 bytes , and char ,varchar , text is 20
bytes , ......?
if it is true , then how can I reach the max maximum size 1.6T in FAQ????
I want to test this case , can you help me ??
Regards, Zhao.
zhaoxin <zhaox@necas.nec.com.cn> writes:
> I have a question about maximum row size .
> In pgsql FAQ, I can find this description :
> ...
> Maximum size for a row? 1.6TB
> ...
> does it mean 1600 text column in a table and 1G every column ?
> but, when I test this case by psqlodbc, I got some error :
This was posted in a fairly randomly chosen list, but I think it does
expose an error in the FAQ: you can't fit 1600 TOAST pointers into one
row and so the 1.6TB figure is overoptimistic.
Since TOAST pointers are 20 bytes, a reasonable number for the maximum
number of large toasted fields is about 400, which would make the
correct entry for this question 400Gb. This could be improved by using
a non-default block size, so this should be listed as one of the limits
affected by block size.
regards, tom lane
Tom Lane wrote: > zhaoxin <zhaox@necas.nec.com.cn> writes: > > I have a question about maximum row size . > > In pgsql FAQ, I can find this description : > > ... > > Maximum size for a row? 1.6TB > > ... > > > does it mean 1600 text column in a table and 1G every column ? > > > but, when I test this case by psqlodbc, I got some error : > > This was posted in a fairly randomly chosen list, but I think it does > expose an error in the FAQ: you can't fit 1600 TOAST pointers into one > row and so the 1.6TB figure is overoptimistic. > > Since TOAST pointers are 20 bytes, a reasonable number for the maximum > number of large toasted fields is about 400, which would make the > correct entry for this question 400Gb. This could be improved by using > a non-default block size, so this should be listed as one of the limits > affected by block size. FAQ updated with new number, and mention that increasing block size quadruples it. -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. +
> -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Bruce Momjian > Sent: 03 April 2006 04:41 > To: Tom Lane > Cc: zhaoxin; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] problem about maximum row size ? > > FAQ updated with new number, and mention that increasing > block size quadruples it. I've updated the limitations page on the website, though I didn't bother with the blocksize hack on there. Whilst we're on the subject, is 16TB for a table still correct given CE partitioning? Regards, Dave
Dave Page wrote: > > > > -----Original Message----- > > From: pgsql-odbc-owner@postgresql.org > > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Bruce Momjian > > Sent: 03 April 2006 04:41 > > To: Tom Lane > > Cc: zhaoxin; pgsql-odbc@postgresql.org > > Subject: Re: [ODBC] problem about maximum row size ? > > > > FAQ updated with new number, and mention that increasing > > block size quadruples it. > > I've updated the limitations page on the website, though I didn't bother > with the blocksize hack on there. > > Whilst we're on the subject, is 16TB for a table still correct given CE > partitioning? Uh, probably not, but do we want to require CE to increase that limit? -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. +
> -----Original Message----- > From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] > Sent: 03 April 2006 14:41 > To: Dave Page > Cc: Tom Lane; zhaoxin; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] problem about maximum row size ? > > Dave Page wrote: > > > > > > > -----Original Message----- > > > From: pgsql-odbc-owner@postgresql.org > > > [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of > Bruce Momjian > > > Sent: 03 April 2006 04:41 > > > To: Tom Lane > > > Cc: zhaoxin; pgsql-odbc@postgresql.org > > > Subject: Re: [ODBC] problem about maximum row size ? > > > > > > FAQ updated with new number, and mention that increasing > block size > > > quadruples it. > > > > I've updated the limitations page on the website, though I didn't > > bother with the blocksize hack on there. > > > > Whilst we're on the subject, is 16TB for a table still > correct given > > CE partitioning? > > Uh, probably not, but do we want to require CE to increase that limit? It's worth a mention don't you think? Something like: Maximum table size: 16TB (for a partitioned table, this is the maximum size of each partition). Regards, Dave
Dave Page wrote:
> > > > FAQ updated with new number, and mention that increasing
> > block size
> > > > quadruples it.
> > >
> > > I've updated the limitations page on the website, though I didn't
> > > bother with the blocksize hack on there.
> > >
> > > Whilst we're on the subject, is 16TB for a table still
> > correct given
> > > CE partitioning?
> >
> > Uh, probably not, but do we want to require CE to increase that limit?
>
> It's worth a mention don't you think? Something like:
>
> Maximum table size: 16TB (for a partitioned table, this is the maximum
> size of each partition).
OK, FAQ updated by adding second setence:
<P>The maximum table size, row size, and maximum number of columns
can be quadrupled by increasing the default block size to 32k. The
maximum table size can also be increased using table partitioning.</P>
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +