Re: Maximum number of columns in a table

On Sun, Apr 12, 2009 at 05:33:35AM +1000, Gerry Scales wrote:
> The FAQs state "Maximum number of columns in a table? 250-1600
> depending on column types".
>
> Is there a location which gives instructions for calculating whether a
> number of columns of different types can be accommodated?
>
> 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.

I think the only definitive advice I can give would be to try it and
see how it works. That said the pg_column_size function may be of
use and I'd have a look at how variable length fields (i.e. CHAR(n),
VARCHAR(n), TEXT, NUMERIC) and TOAST are handled. 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. Also note that
NULL values get compressed into a bitmap at the beginning of the tuple
and so don't take up much space.

Hope that helps!

--
Sam http://samason.me.uk/