Could not they shard the inventory table by shop_id? As I understand, the order includes only items from one store, so there is no need to keep all the stores in a single table.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
They never said they don’t shard it, however this doesn’t solve the problem they were facing. Even if they have a single store (therefore a single shard), the burst demand may be high for the item in that shop, which creates contention for “remaining item quantity” resource. Their solution spreads this contention across several rows.
> Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.
This requires a row per item unit, doesn’t it? If you have 50k units you’ll have to track status of every item, meaning 50k rows. They also mention this as a rationale to use at most 1k rows, and treat it as a buffer.
I now thought that "updating a row" might be more expensive than simply deleting because UPDATE is implemented as "mark row deleted" + "insert new version of a row" in a table which support multiple versions of a row (MVCC). So maybe using DELETE is actually faster - it just marks a row as "deleted in transaction X". Unless I forgot something.
That's how Postgres' (and perhaps others) MVCC works, yes. MySQL / InnoDB, however, updates tuples in-place [0], and uses the undo log to recreate older versions as needed.
Also, I wonder why they could not have a row status (available/reserved) and UPDATE it instead of deleting the rows.