Processing speed is too low for the number of sweatcoin events

[telezhnaya]

Starting from September 13th, we can process only 1 block per minute. We need to speed up the solution at least 120 times so that we can catch the latest block at some point of time.

Ideas:

• We need to have all the infra pieces at the same region to avoid network delays (@ecp88 I need your help here)
• We need to parallelise the balances collection at the events collecting stage
• We need to parallelise the balances collection at the checking stage
• We can skip check stage for now and make it later (I tried that, it's not enough)
• We can ban sweatcoin for now and collect the other events, while thinking how to increase the processing speed

[telezhnaya]

We may init absolute value lazily

draft of sql query to update values later, for future me:

with t as (select event_index, affected_account_id, contract_account_id, delta_amount, absolute_amount from coin_events 
where affected_account_id = 'mitchellcm.near'
order by event_index)
select event_index, delta_amount, 
sum(delta_amount) OVER (PARTITION BY affected_account_id, contract_account_id ORDER BY event_index) - 1000 AS cum_amt,
absolute_amount from t;

[telezhnaya]

There's no need to review the PR above, but we need to think carefully how should we fill absolute_amount. The speed of each block processing should be at least 2 blocks per second on the heaviest possible load. The main bottlenecks:

1. write speed to the DB
2. number of RPC queries per second

I see several options of filling absolute_amount with the resulting value

At first, we insert the data with NULL absolute_amount, the other process does UPDATE on each line to fill in the value

Pros:

• We can fill the values lazily only for active accounts
• It's easy to start both processes after they die for any reason
• We can take many balance changes at once for each contract + account_id, do only 2 RPC calls (at the beginning and at the end of the period) and fill all the absolute_amount values between these points -> less number of RPC calls
• We can do batch UPDATE, we can batch even with different users and contracts if we want
• We can use at least partial data for showing it to the final user (Enhanced API), the lag can be tiny

Cons:

• Our data is not append-only anymore
• UPDATE query is heavy and it will slow down all the processes in the DB
• VACUUM will be required much more often because updating the line means deleting + inserting. That will give us tons of deleted queries that should be vacuumed
• It's not that easy to use partially filled data at Enhanced API (with new solution, we don't have the list of inconsistent contracts, and we have to make RPC queries on that side to make the data complete)

To sum up, I don't believe in this solution.

At first, we insert the data with NULL absolute_amount to the temp table, the other process does SELECT + INSERT on each line to fill in the (other) resulting table

Similar approach, but Pros:

• Our data is not append-only, but it's at least insert-only, that's good
• no updates, no additional vacuums

Cons:

• We can't easily find the moment when we need to start after the second process died. We can fill in the data for the specific user at block X, but we haven't finished processing block X - 10.
• 2x number of inserts

We keep absolute_amount NOT NULL, collect the events, save them somewhere (files? using event streaming?), the other process takes these files/events and perform inserts to the DB

Pros:

• Our data is insert-only
• Our data in the DB is complete, much easier to use it in Enhanced API
• We can do less RPC queries (take many balance changes at once for each contract + account_id)

Cons:

• the lag (user transferred coins -> we serve it in Enhanced API) potentially could be big (1 minute and more)
• we need to think carefully how to design this solution. If we use files, who will drop the used files? How can we guarantee that we haven't lost any lines? If we use event streaming, which one to choose?

I think this solution has the potential, but we need to discuss it.

[telezhnaya]

@frol suggested just to drop absolute_value column Pros:

• no column - no problem

Cons:

• additional RPC requests in Enhanced API
• we don't know which contracts produce inconsistent events

[telezhnaya]

We dropped absolute_value column