I've completed some extensive work on optimizing SP indexing (GPU and CPU) and scanning (GPU and CPU). We index blocks out of order as they are validated, but are now also able to provide start thresholds and height/time-based cursoring. The initial implementation scanned the full table and filtered the results. Now scanning will not exceed one concurrency window 50k blocks by default) of excess scanning for a given request.
The work also included a chance to the way we track block state. This was originally envisioned as a multimap, which I later optimized into an arraymap. This allowed one state to be reflected and looked up directly from the block link without a search. But new states were appended to the table, and due to the simplified indexation, hid the previous states.
This worked fine with a linear state progression, but block states became more complicated with the addition of client filtering indexing and now silent payment indexing. These are independent states that race each other and validation, which itself must contend with milestone/validation bypass, to combine to form a next stage, such as validated or confirmed. And read/write of these values needs to be very fast (no search) because of how extensively it's used.
Over time we've evolved tables without heads (nomap), tables without bodies (headmap), arraymap/hashmap tables, and SoA variants of each. These remain simple and provide all sorts of powerful optimizations over the mmap. While working off the SP added complexity I realized that we didn't need a body for block state. The state has been reduced over time to a single byte. That can just be retained in the head bucket using a headmap, where the bucket is indexed by block link and the value is the flags. The flags can be read and written dynamically, because as a head the file is fully captured in a snapshot. So the flags can be merged into the head cell using a memory fence in our existing constructions.
So, smaller, faster, one less file, and much more flexible. And so the block state machine implementation has become much simpler.