On Thu, Jul 4, 2024 at 3:38 PM Lok P <loknath...@gmail.com> 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
