Mar 26, 2023

At design time I was worried about is_confirmable due to the complexity of the relational query. Starting with a block height, the block fk is obtained, then all tx fks, then for each tx, all input fks, then for each input, a search for all spenders of the prevout of that input (by tx-hash/index hash table search key), then each spender is navigated to its tx, then its block fk, then the strong_bk table is searched for block confirmation. There are 2.16 billion inputs. So completing this query in 45 minutes across the entire blockchain is pretty amazing. And I'm glad I didn't try to optimize it with additional tables, since it doesn't appear limiting.