Bitcoin full node and query server
Apr 19, 2023
View in context in the libbitcoin Slack #general mirror.
evoskuil00:20
I've concluded that the central issue I'm dealing with in terms of confirmation query performance is the structure of the input table. Since identification of spender(s) from an output point requires a reverse index which is 1:1 with inputs, the index was attached to the input table. This saves about 6GB in redundant storage (and is super elegant). However as inherently the largest table in the store by far (at 322GB) this space savings is entirely overwhelmed by the paging cost that it introduces.
So I'm splitting out the spend index into a 30GB record table that indexes output point to spending tx. The tx is all that is required for the confirmation process. For another 10GB we could store the fk of the spending input as well, however there is no need to optimize this for validation/confirmation. The spender can otherwise be identified in constant time by walking the spending tx's inputs for the matching point, which in most cases is a walk of no more than two items, so prob not worth the extra space.
The input table then becomes a blob table with no direct search, just like output. I concluded that the confirmation perf problem was limited to this one query and not a consequence of the query complexity in general after reducing the complex query to the single table. Should have some numbers on this today.
💯 1
9 replies · jungly, evoskuil · last Apr 20, 2023
jungly18:10
So I'm splitting out the spend index into a 30GB record table that indexes output point to spending tx.
So the spend table is an index from outpoint point to spending transaction pk?
It stores entries for all spends then and will keep growing forever?
(edited)evoskuil02:51
Yes, just as it was as an index on input. By splitting it out we save on the relatively small amount of unused coinbase indexes, and the need to page the much larger table. But we cannot simply remove the index data from input, as otherwise there is no navigation from input to the output that the input spends. So this is a denormalization for high volume query perf that materially expands storage cost.
evoskuil02:54
Removing the searchability of the prevout point on input moves the linked lists and the header to another table, but duplicates all of the data { spent_output_tx_fk, spent_output_index, spending_tx_fk } - the search key parts and the data element.
evoskuil02:55
that's 11 bytes per (non-coinbase input) duplicated and coinbase input indexing removed. There are about 2.16 billion inputs, so presently 22.16gb.
(edited)evoskuil02:58
Previously I had missed that the index data would need to also remain with the input in order to identify its prevout.
jungly06:21
Right. Got it. Thanks for the explanation. In the non mmap/large ram world this 22gb will have to be on disk.