Employee Booking: Using ETS for Concurrent Requests

What have you tried so far and was not performant enough?

Nothing tried so far. Right now the current implementation is every company have Genserver and Genserver have state like online employee list , company status based if have any idle employee and it work but i want good implementation. So that’s why i start researched on it and found these things which i mention above.I am thinking to go with ets with single process. i should start implementing this but i want solid foundational ground to cover normal load. Maybe i wrong, these are premature optimal solution i talked about.

Why is database not your go-to solution?

Are You talking about Mnesia or postgres.
But ets or redis have speed advantage.They are much faster for data access then database.Although i have database table for company, client, employee

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?

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)

You raise a valid point about the potential for over-optimization. While a 1ms response time isn’t a strict requirement, I do believe that minimizing latency is crucial in this scenario. The nature of the booking system demands a good user experience. Delays in finding available employees could lead to frustrated clients and missed opportunities.

While my concerns might not warrant microsecond-level optimizations, I’m keen on avoiding unnecessary database round-trips for each booking request. This is why I’m leaning towards in-memory solutions like ETS or Redis. They generally offer faster retrieval times than traditional databases, helping to keep latency low and responsiveness high.

While the proposed two-table ETS structure elegantly solves the issue of efficient employee lookup and data integrity, I’m still concerned about the potential bottleneck of using a single process to manage table manipulation.

I understand that a single process guarantees that a company won’t accidentally assign the same employee to multiple clients, as requests are handled sequentially. However, as the number of booking requests grows, this approach may become a performance bottleneck.

On the other hand, introducing multiple processes to handle the workload raises the risk of race conditions. There’s a small but real chance that two processes could simultaneously attempt to assign the same employee to different clients, leading to conflicts and inconsistencies.

I’m considering using a concurrency mechanism like ConCache for locking to mitigate the risk of race conditions in a multi-process environment. However, I’m still weighing the trade-offs between the simplicity of a single process and the scalability of a multi-process approach.

For the initial MVP, I plan to implement a single process solution to validate the overall design and functionality. However, I recognize the need for a more scalable solution in the long run to accommodate increasing traffic and ensure a smooth user experience.

I would greatly appreciate any insights or advice on the most suitable concurrency strategy for this scenario. Are there other locking mechanisms or architectural patterns I should consider? How can I best balance the need for data consistency with the desire for a high-performance, scalable system?

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.

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.