Best approach for high-frequency stock updates in Mnesia

Questions / Help
, ,
1

Hi all,

I’m building an app using Erlang/Yaws with Mnesia. I have an item record that stores product info including stock quantity:

-record(item, {
id, % String
store_id, % String
name, % String
description, % String
category, % Atom
price, % Integer
mrp, % Integer
unit, % String
stock, % Integer
image, % String
is_available, % Boolean
created_at,
updated_at
}).

Most fields are static (written once), but stock gets decremented on every order and restored on cancellations.

Current approach: Simple dirty_read → update → dirty_write :slight_smile:

case db:dirty_read(item, ItemId) of
[Item] when Item#item.stock >= Qty →
UpdatedItem = Item#item{
stock = Item#item.stock - Qty,
updated_at = erlang:system_time(second)
},
db:dirty_write(UpdatedItem);
…
end

My questions:

  1. At what point does this approach become a bottleneck?

  2. I considered using ETS with write_concurrency for stock counters and syncing to Mnesia periodically. But I’m running two nodes and ETS is node-local, which complicates things. Is this premature optimization?

  3. Should I separate hot fields (stock, price, is_available) into a different table to reduce write contention on the main item record?

  4. Would a separate counter table with mnesia:dirty_update_counter work better?

-record(item_stock, {item_id, quantity}).
mnesia:dirty_update_counter({item_stock, ItemId}, -Qty)

  1. Any other patterns the community recommends for this use case?

Thanks!

Venkat