Re: Maximum number of columns in a table

Thanks Sam, that's excellent advice.
If a developer doesn't jump in with a definitive formula, that is the path I
will take.

Regards
Gerry

----- Original Message -----
From: "Sam Mason" <sam@samason.me.uk>
To: <pgsql-general@postgresql.org>
Sent: Sunday, April 12, 2009 7:23 AM
Subject: Re: [GENERAL] 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/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general