Best way to keep read-only table in memory

Will you always be querying by both keys? If so that makes it easier. Also Cachex is quite easy to integrate. You could probably get a lot of benefit with very little code using Cachex.fetch/4:

Cachex.fetch(:my_cache, {key1, key2}, fn {key1, key2} ->
  result = DB.lookup(key1, key2)
  {:commit, result}
end)

Sorry, I should have provided some examples. No, I’d never need to query with both keys together. It’d be either one or the other one. I might need to add a 3rd key in the future though.

Is CacheX easier to use than Mnesia? I don’t need any of its advanced features but cleaner code is always welcome. I was just reading about Amnesia as well

If it’s just in memory (:ets) then querying for one after the other should be fine. And I would say that Cachex is easier to use than Mnesia and suits your use-case better (since you already have a primary database).

Your comment about CacheX intrigues me, but with the little reading that I’ve done, it’s not obvious how I will maintain two or three keys for the same record. While with something like Mnesia or Amnesia (which seems easier to use), I’d just declare as many indexes as I need. Am I missing something?

If it’s just about the keys, You can define ets table of different types, which one did You use?

From Erlang Term Storage (ETS) · Elixir School

• set — This is the default table type. One value per key. Keys are unique.
• ordered_set — Similar to set but ordered by Erlang/Elixir term. It is important to note that key comparison is different within ordered_set . Keys need not match so long as they compare equally. 1 and 1.0 are considered equal.
• bag — Many objects per key but only one instance of each object per key.
• duplicate_bag — Many objects per key, with duplicates allowed.

I don’t follow. The different table types allows me to have multiple records on the same key, but that’s not my issue. Let me provide an example:

key1, key2, key3, value1, value2, …, valueN

Use cases:
Given key1, find the one record that matches it

Given key2, find the one record that matches it (this could be the same record as the previous case)

Given key3, find all the records that matches it

I’m using duplicate_bag, but this is just a trick. I know the records are not duplicate, and I don’t want the system wasting time checking for that

Can you make this a bit more concrete? What does match mean? equality? pattern matching? are there multiple keys for the same value?

What does match mean?
It means equality. It’s basically the equivalent of the SQL query:

Select * from table where key = value

are there multiple keys for the same value?
Yes, that’s what I meant before. There are two ways to access each unique row on the table. That’s why I have to support two different queries based on those two different keys

How many values are we talking here (order of magnitude)? How does your current implementation work? Structs don’t store data in a way that is accessible from different processes.

One option is to have two tables, one which stores k1 → v1 and then some other table which stores, k1 → k1, k2 → k1 mappings. You do a lookup in table 2 first to get the canonical key, and then a lookup in table 1 to get the real value.

EDIT: Actually your second table could just store k2 → k1, and if you do a lookup in the second table and there’s no values then you know you already have the canonical key.

How many values are we talking here (order of magnitude)?

We’re talking about 15K records

How does your current implementation work? Structs don’t store data in a way that is accessible from different processes.

The list of structs is in a GenServer. I did that precisely to allow concurrent access

One option is to have two tables, one which stores k1 -> v1 and then some other table which stores, k1 -> k1, k2 -> k1 mappings. You do a lookup in table 2 first to get the canonical key, and then a lookup in table 1 to get the real value.

Yes, that’s what I clumsily tried to explain before:
"Obviously, I can use a bit of brute force, and have a 2nd ETS table with the 2nd key as key, get the 1st key, and then query the 1st table. Kind of an reverse index table. "

And, my question still is if I should use something like Amnesia instead. I think that will allow me to more easily support queries on other columns that are not unique keys but that group data never the less.

axelson thinks CacheX is a better option for me, but I don’t see it. I still need to support queries on different columns.