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/