Thread: Maximum row size

Hi,

What is the maximum row size for PostgreSQL?

http://www.postgresql.org/about/ says 1.6 TB

http://www.postgresql.org/docs/faqs.FAQ.html says 400 GB.

Both may be true in fact, depending on block size. But which one is the
accurate answer? I'm confused.

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

Devrim GÜNDÜZ <devrim@CommandPrompt.com> writes:

> What is the maximum row size for PostgreSQL?
>
> http://www.postgresql.org/about/ says 1.6 TB

I think this comes from the maximum of 1600 columns times the maximum of 1GB
per (toasted) datum.

However 1600 toasted datums won't fit on an 8k page. Each toast pointer is 17
bytes so only 480 toast pointers will fit on a 8k page. Which may be the
rationale for this?

> http://www.postgresql.org/docs/faqs.FAQ.html says 400 GB.

Before packed varlenas We used to be able to fit only 408 minus a few for the
page header. Perhaps it was 400 then, or perhaps they were just rounding down.

So with packed varlenas we can raise the second number to 480GB. But it's kind
of pie-in-the-sky since it depends on every column being toasted (and not
compressed). It's much more normal to have some columns be quite large and
others more normal sized or different data types.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

Gregory Stark <stark@enterprisedb.com> writes:
> ... it's kind
> of pie-in-the-sky since it depends on every column being toasted (and not
> compressed). It's much more normal to have some columns be quite large and
> others more normal sized or different data types.

Yeah.  I think those numbers should simply be removed from the
discussion entirely, because they are bogus.  There is no real-world
situation where someone is going to put 1Gb into each and every field of
a row.  The limits that should be stated are 1Gb per column and 250-1600
columns per table --- let the readers draw their own conclusions from
those.

As Greg notes, the 400-column figure came from presuming that every
column gets toasted out-of-line; but that's not totally realistic
either.  Furthermore, we have some untoastable types that are wider
than a toast pointer.  The worst case seems to be 32 bytes for box,
line, and lseg (I ignore "name" which is stated not to be intended
for general-purpose use).  If you made a table of only "box" columns
you could get at most a bit over 250 of them on a standard-size page.
So I think that's what we should quote as the lower bound.
        regards, tom lane