public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Lok P <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Design strategy for table with many attributes
Date: Fri, 5 Jul 2024 17:27:20 +1200
Message-ID: <CAApHDvo8cyQOS=siD3mhYUhoUydUawQ8M=MHE5z_qz5pXTYN9g@mail.gmail.com> (raw)
In-Reply-To: <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
	<CAKFQuwYpai5snt5mEgyXK4m6nR5T6sP-YTYH-GQ0Hs2rXS7uLA@mail.gmail.com>
	<CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>

On Fri, 5 Jul 2024 at 17:07, Lok P <[email protected]> wrote:
> Also I understand the technical limitation of the max number of columns per table is ~1600. But should you advise to restrict/stop us to some low number long before reaching that limit , such that we will not face any anomalies when we grow in future. And if we should maintain any specific order in the columns from start to end column in the specific table?

Something else you may wish to consider, depending on the column types
of your 900+ columns is the possibility that some INSERTs may fail due
to row length while others with shorter variable length values may be
ok.

Here's a quick example with psql:

select 'create table a (' || string_agg('a'||x||' text not null
default $$$$',',') || ')' from generate_series(1,1000)x;
\gexec
insert into a default values;
INSERT 0 1

again but with a larger DEFAULT to make the tuple larger.

select 'create table b (' || string_agg('a'||x||' text not null
default $$hello world$$',',') || ')' from generate_series(1,1000)x;
\gexec
insert into b default values;
ERROR:  row is too big: size 12024, maximum size 8160

There is a paragraph at the bottom of [1] with some warnings about
things relating to this.

The tuple length would be fixed for fixed-length types defined as NOT
NULL. So, if you have that, there should be no such surprises.

David

[1] https://www.postgresql.org/docs/current/limits.html

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Design strategy for table with many attributes
  In-Reply-To: <CAApHDvo8cyQOS=siD3mhYUhoUydUawQ8M=MHE5z_qz5pXTYN9g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox