@jononomo Your requirement that the ETS tables be perfectly synchronized is the problem - this is a non-trivial problem to solve generally, and is one that every distributed database has to deal with in some way (e.g. CAP theorem and related tradeoffs on guarantees).

Mnesia solves this problem, to some degree, though you have to do extra work to make it resilient to network partitions. Another approach is to have a process which synchronizes data between nodes, using CRDTs to handle conflict resolution, e.g. Phoenix.Tracker. There are some libraries which build on this capability, such as dispatch. You may find that the latter works better for your use case.

ETS is extremely useful for a broad array of cases, but for your specific need here, it is not the appropriate tool.

ETS is single-machine.

Mnesia is multi-machine.

Mnesia wraps ETS and DETS to add a distributed transaction layer, it running as in-memory mode is exactly a distributed ETS.

afaik mnesia carries a write overhead, not really a read overhead.

what is the payload size, and what is the spike RPS, are the nodes to be geo distributed etc. how do you intent to load balance, what is max accepted latency etc.

fyi in my benchmarks a 5$/month scaleway (really low powered) server can do around 800 RPS, (~70 million hits per day) - and this is going through the phoenix browser pipeline and html rendering etc - and with the db call cached in ets.

I assume a single server can handle your load.. so fire up some servers and loadtest it.