Re: Design strategy for table with many attributes

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Design strategy for table with many attributes
Date: Thu, 4 Jul 2024 16:50:23 -0400
Message-ID: <CANzqJaB49Rp0buF3DwPjzivSbTM+3p+COvr9n6Athk+v35nFiQ@mail.gmail.com> (raw)
In-Reply-To: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>

On Thu, Jul 4, 2024 at 3:38 PM Lok P <[email protected]> wrote:

> 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
>

Nothing out of the ordinary.


> and should be stored in one table as one single row.
>

Says who?


> 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?
>

You need database normalization.  It's a big topic.  Here's a good simple
explanation:
https://popsql.com/blog/normalization-in-sql

view thread (5+ 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]
  Subject: Re: Design strategy for table with many attributes
  In-Reply-To: <CANzqJaB49Rp0buF3DwPjzivSbTM+3p+COvr9n6Athk+v35nFiQ@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