Re: Maximum number of columns in a table

On Sat, Apr 11, 2009 at 05:43:06PM -0400, Tom Lane wrote:
> 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.

Wow, never realised it was that much before.  Where is all that?

  varattrib_1b_e  = 3 bytes, but will overlap one byte with:
  varatt_external = 16 bytes

Is that right?

> In practice the performance would probably suck too much to be useful
> --- you don't want to be toasting key columns, in particular.

Good point; if you're referring to any reasonable number of these
columns in each query it's going to be somewhat grim.

> 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 guess it's things like lots of NULLs = good that the OP was trying to
find out.


Just out of interest; what would happen if you had:

  CREATE TABLE wider (
    col0001 TEXT,
    col0002 TEXT,
    -- [ .... ]
    col9998 TEXT,
    col9999 TEXT
  );

  CREATE TABLE store (
    pk1 INTEGER,
    pk2 TEXT,
      PRIMARY KEY (pk1,pk2),
    data wider
  );

Would the "data" tend to end up toasted, or would PG try and expand the
data inline and fail some of the time?  Also, if I ran the following
query:

  SELECT pk1, pk2, (data).col0001, (data).col0101 FROM store;

Would "data" get detoasted once per row, or per column referenced?

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