Re: Maximum number of columns in a table

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> On Sun, Apr 12, 2009 at 05:33:35AM +1000, Gerry Scales wrote:
>> I have a situation where I need to load tables from another database
>> which may theoretically contain up to 1000 columns per table and this
>> information will determine whether I need to rebuild PostgreSQL with
>> a larger blocksize. Splitting into multiple tables is not always an
>> easy option.

> ... Fixed size fields,
> such as integers are going to be fine for you and you should be able
> to fit more than a thousand integers into a row, but text values will
> depend on the size of the text you're inserting. I think that either
> short strings (i.e. less than 8 characters on average) or large bits of
> text, because they're getting TOASTed, should be OK.

In principle, every varchar column could get toasted into an 18-byte
(more in pre-8.3 versions) TOAST pointer, meaning you could get to
somewhere around 450 columns in 8K regardless of how wide they are.
In practice the performance would probably suck too much to be useful
--- you don't want to be toasting key columns, in particular.
So really the question is uselessly vague as posed. We'd need to know a
lot more about the columns' average widths and usage patterns before we
could say much about how well it's likely to work.

I rather suspect that most columns in a thousand-column table will be
null in any particular row, in which case it would likely work all
right. We used to have some issues with O(N^2) performance on lots of
columns, but I think those are largely gone in recent releases.

regards, tom lane