We came across three absolutely identical in structure tables in M2 DB:
Anybody has any idea why such redundancy has been introduced?
cataloginventory_stock_status is the one that is used for reading in the frontend.
In order to not interfere with the table reads, when the stock index is running it is writing in the
_idx table and when it's done, the values are moved to the main table.
The temporary table used is determined by this method
So in case it is
_idx table is used as temporary table. Otherwise the
_tmp table is used.
_idx table is used when you rebuild all the indexes, but I didn't find a case when the
_tmp table is used, or what's the difference is between them.
I will report back when/if I find our more.