MIME-Version: 1.0
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
 <CAKFQuwYpai5snt5mEgyXK4m6nR5T6sP-YTYH-GQ0Hs2rXS7uLA@mail.gmail.com> <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
In-Reply-To: <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Fri, 5 Jul 2024 17:27:20 +1200
Message-ID: <CAApHDvo8cyQOS=siD3mhYUhoUydUawQ8M=MHE5z_qz5pXTYN9g@mail.gmail.com>
Subject: Re: Design strategy for table with many attributes
To: Lok P <loknath.73@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvo8cyQOS%3DsiD3mhYUhoUydUawQ8M%3DMHE5z_qz5pXTYN9g%40mail.gmail.com>
Precedence: bulk

On Fri, 5 Jul 2024 at 17:07, Lok P <loknath.73@gmail.com> wrote:
> Also I understand the technical limitation of the max number of columns p=
er table is ~1600. But should you advise to restrict/stop us to some low nu=
mber long before reaching that limit , such that we will not face any anoma=
lies when we grow in future. And if we should maintain any specific order i=
n 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