We have a gigantic table of products that have many prices that are updated by different microservices (say 20 different sources updating 80 columns on the same rows). We also have a website with say 40-100 concurrent users, and the pricing data is refreshed every 5 seconds and generally not cached as it is time sensitive.
Every single source of price is a new column and as you can imagine there are currently occasional deadlocks.
Would splitting them into multiple tables work well enough (e.g. split meta data and prices, or split meta data and prices that each microservice updates)? Would we get even worse performance with all the joins we have to do while reading?
Are there other options other than splitting tables? We only have indexes on the lookup fields (generally an ID and one other piece such as quantity for bundles). Or should we just suck it up and go into a larger instance?