Re: About Maximum number of columns

Scott Marlowe wrote:
> On Thu, 2005-12-22 at 10:10, Jim C. Nasby wrote:
> > On Thu, Dec 22, 2005 at 07:22:12PM +0800, zhaoxin wrote:
> > > Hi All.
> > >
> > > I have a question about the Maximum number of columns in a table ?
> > >
> > > In FAQ for PostgreSQL,I can find this description :
> > > Maximum number of columns in a table?
> > > 250-1600 depending on column types
> > > But , I want to know what type is 1600 limit , and what type is 250
> > > limit . it is important for me , thanks .
> >
> > I'm pretty sure I've read the reason for the limit somewhere in the
> > source code, but I can't remember where. It's probably somewhere in
> > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/. I know
> > that the exact upper limit isn't actually 1600 fields, it's more like
> > 1643 or something.
> >
> > But, as others have said, just try creating your table and see what
> > happens. If it fails, you might be able to get it to work by increasing
> > the block size.
> >
> > And as others have said, this is almost certainly a horrible schema that
> > needs to be fixed, badly. Luckily, thanks to views and rules, you could
> > probably fix it without actually changing any of the client code.
>
> The limit has to do with the fact that all the "header" info for each
> column must fit in a single block (8K default).
>
> I seem to recall someone stating that increasing block size to 16k or
> 32k could increase this number by about 2x or 4x. Not sure if it'll
> work, but it might be worth the effort if you're stuck keeping some
> legacy app happy long enough to replace it with a well designed system.

Yes, that is correct. Increasing the block size can increase the
maximum number of columns. Certain columns like int4 are 4 bytes,
while text/varchar/char can be placed in toast tables so only the
pointer has to fix in the table, and I think the header is 8 bytes.

However, the fixed limit is 1600. Here is a comment from the code:

/*----------
* MaxHeapAttributeNumber limits the number of (user) columns in a table.
* This should be somewhat less than MaxTupleAttributeNumber. It must be
* at least one less, else we will fail to do UPDATEs on a maximal-width
* table (because UPDATE has to form working tuples that include CTID).
* In practice we want some additional daylight so that we can gracefully
* support operations that add hidden "resjunk" columns, for example
* SELECT * FROM wide_table ORDER BY foo, bar, baz.
* In any case, depending on column data types you will likely be running
* into the disk-block-based limit on overall tuple size if you have more
* than a thousand or so columns. TOAST won't help.
*----------
*/
#define MaxHeapAttributeNumber 1600 /* 8 * 200 */

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073