Does combining multiple tables into one table with more than 100 columns improve performance and overall design?

Question

I have 10 tables without any type of foreign key relations between them. All the tables have the same primary key. In this case, "stock_id" is the primary key. All table columns describe a metric of the stock_id. For eg: product_fees is a column to describe the total fees of stocks. Like this, we have various metrics in all the tables. I use all 10 tables using Django API for the frontend website to show all visualizations (20+ charts). I don't think this design is a good one.

My solution: As all attributes have stock_id I'm thinking of combining the tables into one table which will contain about 110 columns. There is one extra table that will have the prediction data with dates (1000+ rows for each stock) for all stocks. This prediction data will have a foreign key (1:M) to the main table with 110 columns. To improve readability, I am thinking of using 20 views to debug the data in the future instead of scanning through all the column names.

The most important thing is all the values of stocks in the 20 tables change every day and I usually do delete and insert them in each table instead of using updates especially for the stock prediction table with dates.

Is it a good idea to have more than 100 columns and use views for aggregating smaller sections of the table for debugging purposes?

Answer 1

A single "fat" table will be more efficient than joining 10 separate tables. While the disk IO will be the same in principle, a view will need to perform 9 joins - essentially 9 large sort/merge operations.

Materialized views (https://www.postgresql.org/docs/14/sql-creatematerializedview.html) makes it easy to separate the physical layout of the 'insert/update' and 'select' parts of the application.

create materialized view consolidated_stock_view as
select a.pk, a.measure_1, b.measure_2, c.measure_3 -- etc
  from a_table a
  join b_table b on a.pk = b.pk
  join c_table c on a.pk = c.pk 
  -- etc

When you are finished loading data into the 10 'fact' tables, refresh the materialized view

refresh materialized view consolidated_stock_view;