Should we favor multiple INSERTs or multiple UPDATEs I always have an impression that INSERT would be beneficial for performance (let's forget storage for now) rather than UPDATES, in all the DB that I know. Especially in Postgres, UPDATES involves transaction and may result in locks

Imagine we are building a shopping cart. For each item I have a table with these fields [row_id, product_id, quantity].

Should I model data for UPDATE (I update quantity column multiple times when user add/remove from cart)

[row_id = 1, product_id = A, quantity = 3]


or to model for multiple INSERTS (I insert a line for each time user clicks, and remove all when they remove from cart) like an event stream

[row_id = 1, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 2, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 3, product_id = A, quantity = 1] // user clicks add to cart
[row_id = 4, product_id = A, quantity = -1] // user clicks remove from cart
All changes to the database involve transactions and may result in locks.
In general, locks are a problem only if concurrent transactions try to access/modify the same row. But concurrent updates are unlikely to happen for a shopping cart.

Postgres does not actually update rows. Other transactions that started before the UPDATE transaction has committed must be able to see the old row, so it can be necessary for the old row version and the new row version to exist at the same time. Therefore, an UPDATE command just inserts a new row, and marks the old row as outdated.

I do not see any upside for doing one INSERT per click. (Except that your code has to choose to do either INSERT or UPDATE, or to use INSERT … ON CONFLICT DO UPDATE.)