Replacing stored procedures with Elixir processes?

There’s a discussion on caching to reduce DB load here (particularly for heavy reads that are frequently hit). It is probably a bad idea to roll your own caching system using raw processes - it gets hard quite quickly (e.g. handling timeouts, knowing when to invalidate etc).

I’ve given this some thought, and I actually think what I want to build is not a cache. I think it’s more akin to a buffer for the DB. As such, there’s no need to cache invalidation.

When a request comes in for a record, it’s loaded into an Agent, and the Agent will persist for X minutes since the most recent read/write request. All reads/writes for the associated record will be handled by the Agent while it’s alive, and writes to the DB will be generated by this Agent at a configured time interval (gives devops control of DB load). When the Agent reaches end of life, it writes the final state to the DB.

Is data loss acceptable?

This is exactly the issue. If your database is slow and inefficient, batching data in memory before inserts just adds another layer of failure. It isn’t clear how this would even help with the stored procedure issue, cause those will ultimately fire when inserted anyway.

If you have valuable data, and you can’t tolerate data loss, then you can’t afford to store that data in memory, it must be written to a disk somewhere. If reads are your issue and not writes then you can look into caching.

Writes are the issue. I would remove the stored procedures and do those operations in Elixir, so then the Agents simply write their state to the DB.

Let’s separate the Agent then from the question of “stored procedures and do those operations in Elixir”. What do the stored procedures do?

I think the core of the problem is this loop:

          BEGIN
            LOOP
              UPDATE table1
                SET col2 = col2 + val
                WHERE id = arg_id AND month = month_of;
                  IF found THEN
                    RETURN;
                  END IF;
                BEGIN
                  INSERT INTO table1 (id, month, col1, col2)
                    VALUES (arg_id, month_of, 0, val);
                RETURN;
                  EXCEPTION WHEN unique_violation THEN
                    -- do nothing, and loop to try again
                  END;
              END LOOP;
          END;

To persist the data to disk prior to DB update, maybe DETS could handle it? I’ve tested writing Erlang terms to disk in binary format via :erlang.term_to_binary(), it’s really simple, but I don’t know how fast.

Currently devops kills this SP when our service goes down, so I assume data loss is already happening.

These aren’t multi-node solutions. If you run more than 1 server, how do you deal with conflicts? Your stored procedure is based entirely around a unique constraint violation, you can’t possibly replicate that outside of your database because you don’t have a consistent view of your database from Elixir.

I do think that you could move these stored procedures to Elixir, but by “to elixir” I mean you could craft a database transaction that was perhaps more efficient.

I’m not sure that’s true. If a user submits a request, and then devops kills stuff, then the return to the user is that their request fails. The danger of using an Agent is that the user submits a request, your server says “ok, confirmed”, and then the agent dies and their data is actually gone, even though you said it was confirmed.

Before we can evaluate things further though, we need to understand better what the purpose of that loop is, because at the moment you can’t straight up reimplement it in Elixir at all.

It looks like the purpose of this code is to track an amount that we are summing. Our DB has things, each is globally unique, and each has this amount field. Then, events occur which add to this amount, and we want to update and report that amount accurately. All seems doable via topics, and I would think we could spawn a single node process for each globally unique thing (using DynamicSupervisor), so we don’t need to worry about conflicts. The number of events we’re talking about is ~100k - ~200k over ~2 hours, and this many events all relates to just one of our globally unique things. I would think this quite manageable.

The only constraint I see on this table:

ALTER TABLE ONLY public.table1
    ADD CONSTRAINT table1_pkey PRIMARY KEY (id);

And there’s also this index:

CREATE INDEX index_table1_id_month ON public.table1 USING btree (id, month);

Does this generate a unique_violation when inserts are attempted with an existing ID & month pair?

Ah! I finally get it. Which database are you on? This stored procedure is basically a home grown version of postgres’s INSERT ON CONFLICT so if you’re on postgres you may be able to refactor this to just use that and move on.

Beyond that though, I was referring to EXCEPTION WHEN unique_violation THEN. The whole thing is weird though because if the id is a primary key, indexing on (id, month) shouldn’t actually help because querying on just id should already be indexed. Where does arg_id come from in your stored procedure?