MIME-Version: 1.0
From: Lok P <loknath.73@gmail.com>
Date: Fri, 5 Jul 2024 01:07:47 +0530
Message-ID: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
Subject: Design strategy for table with many attributes
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000095f181061c711547"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VbQ%3Do2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA%40mail.gmail.com>
Precedence: bulk

--00000000000095f181061c711547
Content-Type: text/plain; charset="UTF-8"

Hello,
In one of the applications we are getting transactions in messages/events
format and also in files and then they are getting parsed and stored into
the relational database. The number of attributes/columns each transaction
has is ~900+. Logically they are part of one single transaction and should
be stored in one table as one single row. There will be ~500million such
transactions each day coming into the system. And there will be approx ~10K
peak write TPS and 5K read TPS in target state. This system has a postgres
database as a "source of truth" or OLTP store. And then data moves to
snowflakes for the olap store.

Initially when the system was designed the number of attributes per
transaction was <100 but slowly the business wants to keep/persist other
attributes too in the current system and the number of columns keep growing.

However, as worked with some database systems , we get few suggestions from
DBA's to not have many columns in a single table. For example in oracle
they say not to go beyond ~255 columns as then row chaining and row
migration type of things are going to hunt us. Also we are afraid
concurrent DMLS on the table may cause this as a contention point. So I
wanted to understand , in such a situation what would be the best design
approach we should use irrespective of databases? Or say, what is the
maximum number of columns per table we should restrict? Should we break the
single transaction into multiple tables like one main table and other
addenda tables with the same primary key to join and fetch the results
wherever necessary?

Regards
Lok

--00000000000095f181061c711547
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<br>In one of the applications we are getting transa=
ctions in messages/events format and also in files and then they are gettin=
g parsed and stored into the relational database. The number of attributes/=
columns each transaction has is ~900+. Logically they are part of one singl=
e transaction and should be stored in one table as one single row. There wi=
ll be ~500million such transactions each day coming into the system. And th=
ere will be approx ~10K peak write TPS and 5K read TPS in target state. Thi=
s system has a postgres database as a &quot;source of truth&quot; or OLTP s=
tore. And then data moves to snowflakes for the olap store.<br><br>Initiall=
y when the system was designed the number of attributes per transaction was=
 &lt;100 but slowly the business wants to keep/persist other attributes too=
 in the current system and the number of columns keep growing.<br><br>Howev=
er, as worked with some database systems , we get few suggestions from DBA&=
#39;s to not have many columns in a single table. For example in oracle the=
y say not to go beyond ~255 columns as then row chaining and row migration =
type of things are going to hunt us. Also we are afraid concurrent DMLS on =
the table may cause this as a contention point. So I wanted to understand ,=
 in such a situation what would be the best design approach we should use i=
rrespective of databases? Or say, what is the maximum number of columns per=
 table we should restrict? Should we break the single transaction into mult=
iple tables like one main table and other addenda tables with the same prim=
ary key to join and fetch the results wherever necessary?<br><div><br></div=
><div>Regards</div><div>Lok</div></div>

--00000000000095f181061c711547--