The central thing to think about when designing with ETS is “what operations does this need to support?”. There are specific things that ETS can do very efficiently, so you want to structure your code to align with those.

For instance:

ordered_set tables have an efficient next operation for a given key, even if the specific key is not present in the table.

So imagine an ETS table with the structure:

{{company_id, timestamp_ms}, employee_id}

where an employee “becoming available” inserts a tuple.

The scheduler can ask :ets.next(some_table, {company_id, 0}) - and get the tuple with a matching company_id and the lowest timestamp.

There are some corner-cases to watch out for:

• if there are NO tuples with that company_id, next will return one from the next company. The scheduler could either deal with this by inserting a sentinel “no more employees” value, or by checking the result.
• two employees that become available at exactly the same millisecond will cause one of their inserts to fail. Increasing the timestamp precision to microseconds would reduce this chance, or the scheduler could retry.
• since employee_id isn’t part of the key, an employee could have multiple tuples with different timestamps. This may be a feature, but if it isn’t see below.

The other thing is to use multiple ETS tables to handle different questions. For instance, the “employee can have multiple entries” case, you might have an auxiliary ordered_set table like :

{{company_id, employee_id}, timestamp_ms}

The scheduler would attempt to insert into this table first when recording an employee’s availability.

The above table can also answer useful-sounding questions like:

• “is this company’s employee available?” (:ets.member)
• “since when has this company’s employee been available?” (:ets.lookup_element)
• “what employees are available for this company?” (:ets.next etc)

Let me give you my 5 cents on the problem you are trying to solve: if you don’t have a hard requirement for response time, then as others mentioned above, use a database.

There are multiple reasons why the ETS solution is harder to maintain, some of them:

• all the data stored in ETS tables will vanish when you restart the server or redeploy (with exception if you are doing hot-code reloading, but that in itself a hard thing to get right);
• you can have potential problems as you mentioned with the inbox overflow if you don’t know exactly what you are doing;
• the complexity of having persistence will most likely outweigh the small performance gain you will get from storing the data in memory.

What I would personally do, is try implementing a prototype using ecto + sqlite, check response times when the throughput is to your expected maximum and decide whether that is a good or bad solution.

You can also later add a caching layer in ETS by using one of available caching libraries like cachex (this library also handles message overflows for you). Having the source of truth in the database will save you a lot of trouble and will offer you a structured way to store your data.

Yes, Postgres.

Your speed concerns seem overblown. Are you sure you’re not over-optimizing for a problem that you’ll never have? Do you have hard requirements to respond within 1ms or less?

Why is database not your go-to solution?

Look up how cachex does transactions in its source code. That could potentially solve both your performance and race condition worries at the same time.

But as @D4no0 said, I’d use cachex together with SQLite. It will live inside the OS process of your app so any extra latency will be extremely minimal.