Re: About Maximum number of columns - Mailing list pgsql-general

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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073