I’m hoping for some advice in optimizing a Phoenix/Elixir application with respect to a couple different kinds of database tables. The regular CRUD stuff is simple: lookups based on an id that can get cached at the database layer or in Elixir (e.g. using Cachex) as needed. Those use-cases are under control. There are 2 other use cases that are a bit trickier.

First use case: small, commonly used tables like “countries” or “currencies”. Our application has dozens of small (ish) tables with maybe 100 rows each whose values do not change often. Is it possible in Elixir to load these tables into memory or cache when the application is started? We could save ourselves many database lookups and still maintain a modest memory footprint. Are there recommended approaches to this tactic? Has anyone seen a tutorial for this?

Second use case: large, but critical tables that do not change often. This one is trickier, and I may not be able to describe the exact need this fills without ending up in the weeds. But for every request into the application, we need to do a couple lookups to this table to “translate” certain values that relate to the route and do some “reverse lookups” of other values. Currently, in our legacy PHP application, these values are stored in Redis, and that works reasonably well. The one feature of Redis that makes this approach possible is its mget feature: with a single Redis call, we can retrieve multiple records. If this were happening in a SQL database, it might be analogous to a query like “SELECT value FROM table WHERE column IN (a, b, c, …)” where we specify ~50 values to lookup.

This 2nd use case is trickier because of the “mget” lookup – I’m not sure how we might cache that because the result returned depends on a fairly long set of conditions.

To clarify the difficulties, we have not been successful in any solution that attempted to “lazy load” results: the query simply takes a bit too long and the results are unique enough so that there isn’t much payoff afterwards because the results are rarely re-used. So in this case, we have needed to preload the results in Redis, and this has worked reasonably well.

If anyone has some advice / links / thoughts on the matter, I’m thankful for any guidance!