Offchain cache/view of on-chain storage data

Question

I am implementing a pallet, that stores in on-chain storage (aka StorageNMap's) some user provided data. Pallet is performing offchain computations by iterating on-chain storage from offchain-worker and creates transactions back on chain, but algorithm is quite unefficient, due structure of on-chain data is not optimized to iterate in a way that offchain algorithm needs.

I am looking for some solution, to keep offchain cache of on-chain storage (like "view" on current on-chain data) without need to store stale/historical data. In other words, offchain worker needs to reorgainze structure of on-chain data for faster computations, but this structure is not beneficial from on-chain perspective. We can assume that amount of data in on-chain storage is huge, so recreating the cache from scratch in each block is expensive. Instead, it would be great to have an option to compute the cache and maintain it after each block for example by iterating events.

I have analyzed few options there:

1. There is an option to create fork-aware local storage via StorageValueRef API:

   let s_info = sp_runtime::offchain::storage::StorageValueRef::local(
       b"my-pallet::cache_for_offchain_worker",
   );
   

   If I assume that this cache will be able to maintain forks, I can try to lock this storage from offchain-worker each time offchain_worker function is called, iterate through events in a block and insert data to the storage, release mutex and perform offchain computations bases on a cached data. But I am affraid of a cases, that offchain_worker function may be triggered multiple times (here mutex and keeping last block update number may help) and not triggered at all (according to the documentation it is possible as well). In this cases, my cache may be not up-to-date.

2. To achieve deterministic writes to an offchain storage the next option is of course offchain indexing. But this method does not allow to modify data from on-chain code, instead it allows to create new or remove existing keys. Here in theory I can try to perform deterministic writes from on_finalize function and store in offchain storage events from this block:

      sp_io::offchain_index::set(b"block_numer_here", b"data_to_be_added_to_cache_for_this_block");
   

   This should in theory solve an issue from case 1) with nondeterministic writes, so offchain_worker call for block N may first check if cache exists, if not then iterate entire on-chain set, create cache and for N+1 blocks relay on data from offchain_index set. But this idea is in contradiction with a fact, that this cache does not need historical data, so I should probably implement some pruning window (like 10 blocks) and remove obsolete keys from offchain_index from on_initialize function. Also, offchain_worker still needs to perform "modification" of the cache with new data for N+1 scenario, due indexing is not able to do this.

3. Third option I see is to reorganize the on-chain storage in a way that it will be optimized for offchain computations, but less optimized for everything else. Here the data may be stored in some sorted manner via BTreeMap's, so offchain worker will be able to perform queries faster and iterate data in expeced order. Then storing offchain data will be obsolete (offchain worker will be relaying only on on-chain storage). Reorganization of the on-chain structure will increase complexity of other operations, but it may be one of the trade-offs if maintaining of the cache will be more complex/hard to implement.

Do you have some thoughts how such cache can be achieved?