Feb 15, 2025

There are two costly confirmation queries, is-spent and is-spendable, Last month I used a new prevout table to allow for precalculation of is-spendable in the validation phase, with completion on the confirmation phase. This reduced the amount of sequential computation in that query to the minimal strictly necessary. I hadn't been able to figure out a way to do this with the other query. On the big machine, during pure confirmation, there is now very little resource usage, due to query optimizations. So at least 50x more work could be done on this machine if is-spent could be parallelized except for the strictly necessary checks. Last night I figured out how to move the entire is-spent query into the validation phase, except for a final check which in a typical run will always be a no-op. This won't necessarily help a lot on the mini without paging optimizations, and I have a couple more in mind. But it finally gets us to the point where the implementation maps to the theoretical ordering constraints. As with the is-spendable optimization, this moves the costly part of the query to validation, where it can be parallel by block given all previous blocks are archived. The result of the precomputation is moved via the store to the confirmation phase in a block of fully-disposable data (can be purged during start, stop, and/or snapshot). In the is-spendable case the data required a 4 byte primary key for the parent tx of every spend. This is already read during validation, so it was free except for the temporary table. In the is-spent case we need to identify the same primary key of the parent tx of any conflicting spends. This is a value also obtained during validation, as is the hash of every input point. So precomputation saves these redundant reads of the "self" spend identity. The costly part is the search in the spent table for conflicts (spends for every point, other than self). Turns out this query doesn't require total order, since it's not possible for a spending tx, introduced during validation (i.e. from the tx pool), to become confirmed in the interim. Only the instance of any tx, already associated with the block header at this point, will get marked as confirmed in the final confirmation step (due to enumeration of txs via the block association). So the query can safely identify all spenders that might become confirmed in any previous block, and save the parent pk of those to the temporary table. This is typically going to be zero, and can be implemented with a prefix count value in order to differentiate from the is-spendable values in the table. The confirmation phase then just checks these txs for confirmed-ness, and if so the block is invalid for double spend. Though again, in a typical run there are no such conflicts, so is-spent essentially becomes a no-op in the strictly-ordered confirmation phase.