Big price lists, update or delete and re-record?

Question

PostgreSQL

Good afternoon, we encountered such a problem as to be with the constant updating of large price lists. Unfortunately, there is no experience in such an implementation, there are only assumptions. The bottom line is that there is a price (s) ~ 5 million and it needs to be updated quickly (every 2 hours).
RabbitMQ, PostgreSQL, PHP are currently used.
The mechanism works on the "insert + update" principle, but it is very time consuming.
If you change the implementation to:
1) update status old goods
2) insert new
3) delete old (at night)
How will such a mechanism behave, what do you say ?, I would like to hear advice from someone who has already encountered this.

Answer 1

The bottom line is that there is a price (s) ~ 5 million and it needs to be updated quickly (every 2 hours).

It's NOT much and NOT often.
False selection, it's the same operation. MVCC update = delete + insert
But what's important is not to do an update that you don't really need. Postgresql does not parse data looking for empty updates, update table set id = id; you will honestly double the table, although nothing at the logical level will change.
Profile what you really spend time on - pg_stat_statements will answer this question well from the database side. Finger to the sky - you work in autocommit and naturally 5 million write operations for any honest ACID base - this is an order of slow IO (even for SSD)

Answer 2

Put the price formation logic (the part that changes it often) into the base, in the form of coefficients, so that it is enough to change the general ones and those that are stored for the goods are constant.
When displaying a price list, form it anew each time, calculating from the coefficients taken from the database.
A great example is storing prices in the base in the currency of the site where you redirect your purchases (I don’t believe that all 5 million is your local production, of course you are a reseller), then it’s enough to calculate the exchange rate of the used currencies to your base one and multiply it with output.
Most likely there will be more coefficients, if there are many sites, each can make its own changes to the price. Also, if those sites also give out the calculated price, you will have to double-check all the prices on your site so that you do not suddenly receive individual products at a strange price.

Answer 3

Keep the price in the radish and there will be no problem.