Re: Design table with many columns

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.

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?

Note-  This system is planned to have a postgres database as a "source of truth" or OLTP store. And then data moves to snowflakes for the olap store.

Regards

Lok

Lok, look at JSON columns. Oracle 19c can use JSON columns, which are a good method of bundling multiple columns together:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html

Of course, you can wait for Oracle 23ai which will probably be released by the 3rd quarter of the year 2525. Oracle 23ai will support up to 4096 columns, but we are still several years from seeing it installed in production. Oracle had layoffs last year and they've lost some programmers. Hopefully, the quality of the release will not be impacted. Always remember that the first production release of any new Oracle version is essentially beta quality, until at least 4 quarterly patches are released.