It might be a good idea to mark the headers SQL table with a flag for this (to indicate that it's an orphaned block) so that it can be added as another filter to the SQL queries themselves (should be the most performant way to achieve this filtering of results)

See max_heap_table_size in mariadb my.cnf file. Generally I set this to 10GB

It should ideally function as a cache where lower fee txs get evicted and the higher fee txs remain...
But it may not need to be that sophisticated for the first iteration.

The number 1 priority is just to prevent OOM'ing.
Secondary objectives would be to match the node's eviction criteria.

This would allow ConduitRaw to still function normally but rely on the node to store the raw block data (e.g. a miner we could collocate with).

It should be rather simple to implement. Would basically just redirect http requests to the node instead and make ConduitRaw prune unwanted raw block data (but only after ConduitIndex has synchronised it).

• Get mempool txs from the node directly (p2p) not via conduit_raw
• Batch mempool txs for bulk flushing to MySQL
• Test conduit_raw on HDD -> For full archive of mainnet I think it makes a lot of sense but should segregate raw block data
  from LMDB and store raw blocks as flat files outside of LMDB. (Result: Performs very well)
• Add back the post-IBD mode event to conduit_index if the unnecessary mempool lookups show any slowdown for IBD
  (may be unnecessary now with fully in-memory mempool lookup table)
• Upgrade db tables from height -> block num (with a separate table for block hash lookups)
• Tune rocksdb for write-heavy loads
• Script for printing out blockchain metrics such as cumulative blockchain size and total tx count etc. for translating logging timestamp progress to meaningful metrics about what to expect under ever increasing loads.
• ShortHashing - essential for cutting footprint of index on NVMe storage and in-memory caches / network IO.
• Return full tx hashes and pushdata hashes in the API
• Test coverage of internal aiohttp API (json)
• TSC merkle proof endpoint
• Run tests in Azure pipeline
• Reorg handling
• Db repair on startup needs to do index-only lookups for the deletes so that it scales.
• Add reorg functional testing
• Pylint checks passing
• Mypy checks passing
• Tx offset data - move to flat files

Top priority post-MVP launch

• Add db integrity check for conduit_raw (the same as is already done and working well for conduit_index).
• Swagger documentation page (use Redoc https://github.com/Redocly/redoc)
• Nginx proxy to add https://

Not required for MVP launch:

• Simplify p2p network client
• Dynamically adjust size of main batch size as the moving average block size increases (otherwise python dictionary caches etc. will get overly large if storing metadata for numerous large blocks at a time)
• Make all configuration via a single top-level .env file

We need a HeaderSV-like "Peer manager" & "Chain tip tracker". It could be named something like: ConduitConnect

Its most important function would be to act as a pre-fetcher for raw blocks in parallel on behalf of ConduitRaw.

ConduitRaw would send it a request for blocks from e.g. height 1000 - 1500.

ConduitConnect would now divide up the work into contiguous sections of blocks.
If there were 10 peers there would be 10 x 50 header chunks. E.g. height 1050 - 1100, 1150 - 1200 etc.
There would be 10 open p2p socket connections - one for each node
There would be a max network buffer of 128MB. Therefore max memory usage of 10 x 128MB = 1280MB for streaming raw blocks.

A block < 128MB in size is considered a SMALL_BLOCK type. And so ConduitConnect would respond to ConduitRaw with this raw block immediately (keeping this small block in memory / network buffers the whole time)

A block > 128MB in size is considered a LARGE_BLOCK type. And so ConduitConnect would stream the raw block first to disc to avoid OOM'ing. The filename would == the block hash in hex and would be written to a kind of "staging area" (the file can be simply renamed and moved later to the proper location 😃 )

• If LARGE_BLOCK type then ConduitRaw would receive a different response. It would be given the location of the raw block (already written to disc) and to process it (the merkle tree info and tx offsets etc.) it would read it from disc - once again... this processing should be streams-processing-based and should not need to load the whole block into memory at once. At the end, it would NOT re-write it to disc. It would just move the file / rename it - should be instantaneous.

To continue returning full hashes in the response there are a few tasks that need completed:

• Change tx table from height -> block num
• Use the block num + tx position in the block for either the output or input tx_hashX to fetch the full tx_hash from LMDB (using the merkle tree db with an array of ordered tx_hashes for each block).
• This endpoint could be provided either via the socket server or via an aiohttp server. I think I lean towards socket server for now to keep everything under "one roof" - however, the main bottleneck will be random seeks on HDD for the tx hashes.
• The pushdata hash is already given in the request so can be matched up again - no need to re-parse the rawtx for it.
• Internally ConduitRaw would keep a small LRU cache for hashX -> fullHash given the likelihood of recent batches overlapping on the same txs.