Analyzing the Bitcoin Cash Blockchain
Josh Green
January 5th, 2019 at 06:57:49 PM

Making Well-Informed Decisions Requires Information

Richard Winters was a commander of Easy Company, 2/506th PIR--famous for leadership, his memoir, and the HBO Mini Series, Band of Brothers. In his memoir, Richard stresses the importance of reflection in almost every chapter I recall reading.

Take a moment of self-reflection. Look at yourself in the mirror every night and ask yourself if you did your best. True satisfaction comes from getting the job done. The key to a successful leader is to earn respect—not because of rank or position, but because you are a leader of character.

Dick Winters, Beyond Band of Brothers: The War Memoirs of Major Dick Winters

I personally find that reflection is one of the most important tasks I do--as a leader, as a developer, and as a person. It's the only way I can improve myself: to take a moment, take a breath, and remember the things I'd done and the choices I made, and try to understand if I could have done things better--so that next time, I can learn from my choices.

Building software also requires reflection. Being bold and making new decisions is only productive if you're lucky, or if you take the moment to reflect on those decisions.

The other important component of reflection is having the information to reflect on--which is often why reflection is most useful after some time has passed. It's important to see how things turned out before you begin to make judgements. Having information to reflect upon is an important component of learning--and critical thinking, of course.

Reflecting on the Design Decisions of Bitcoin

A lot of 20-20 hindsight has been gleaned from the growth and success of Bitcoin... but also a lot of its warts have come to the surface. But a beautiful thing about software is that we can learn from our mistakes and improve what we've built upon. When we reflect upon the decisions made within the Bitcoin protocol, some become obviously brilliant and some are glaringly misguided. ...and some are still highly debated. Ultimately, we need information to make better decisions. Unfortunately, sometime information is hard to come by.

The ironic component of reflecting on Bitcoin is that the whole premise is built on transparency. Every tiny transaction to ever exist on the Bitcoin network is written to a public immutable ledger. So at face value, it seems kind of asinine that simple usage statistics aren't plastered everywhere in order to make better decisions about future directions of the protocol. For instance, which opcodes are the most used within the Bitcoin network? I feel that if we're to consider adding, removing, or changing opcodes, we should at least know which ones are currently the most used. I, as a developer, can tell you that the opcodes used within a P2PKH (the "standard" transaction format) are going to grossly overshadow any of the other opcodes, but I couldn't tell you by how much--at least not without speculating. Google search for "bitcoin opcode usage statistics"... I was surprised to find nothing relevant shows up.

So how can we be making the best decisions for the Bitcoin protocol if we can't even reflect upon the current state of the protocol? I think we can certainly try, but we're not being very good leaders of the protocol if we're not doing better than that.

So Let's Glean More of Bitcoin

Bitcoin Verde is a full node we've been working on for over a year that does more than just focus on efficiency--it indexes the whole chain in a relational database, allowing queries to be ran against it. One of the questions I was asking myself a year ago was focused around the various usages and distributions of the various Bitcoin Address types (a P2PKH, P2PK, P2SH, etc). It was hard to find this information. The reason was because this information isn't easily available within the reference client of Bitcoin. The reference client is built for efficiency--and it's quite good at that--but it was not built for reflection and analysis. However, after a lot of hard work and Bitcoin Verde's first beta release, we can start to ask questions about the blockchain's ledger--and get real, factual, independently verifiable results.

Yesterday, David Klakurka, the project lead of Coin Dance, did an AMA on reddit. Coin.dance is a website and team of people who spend a lot of dedicated time and effort in informing and keeping people informed of the state of Bitcoin and its derivatives. One of the questions asked was Which tools/features would you like to see in BCH clients (or any other coin you cover) to make gathering data more smooth?. Part of David's response was "doing lower-level analysis of the blockchain". In the example he uses to explain what he means, he talks about determining which UTXOs are inherently "unspendable" due to a rise in transaction fees (these UTXOs can be spent by combining them with larger UTXOs, but it's impractical to do so because their value is worth less than the fees required to spend them). I wanted to use Bitcoin Verde to start to answer that question.

How many UTXOs are "Unspendable"

Determining Historical Average Fees

I set out last night to try to determine how much work would need to be done to begin answering that question. The first component needed to answer that question is to determine the average fee rate for a particular point in time. Unfortunately, Bitcoin Verde doesn't index this value directly--but it could. But for now, we can just derive it. Deriving it won't be fast--the Bitcoin database is huge: there are over 28,780,265,472 transactions--so we won't be able to throw this on the Block Explorer without indexing these values first. ...but for now, let's figure out how we'd go about that.

To properly determine the fees, we'd technically need to calculate the average fee per byte--since this is a little more complicated, I decided to start by just calculating average fees per block. We can later leverage that transactions are on average about 250 bytes, so calculating the average fee per block and dividing that by 250 can estimate average fee per byte. When we display this in the Block Explorer later, we can calculate these values more accurately and won't have to take estimates, but for now I think this is an okay compromise. A fee is provided in a Transaction by totally outputs that are less than the total of the Transaction's inputs. So, by calculating the total inputs for a transaction and then subtracting the total outputs, we can calculate a transactions fee. The SQL to do that for a particular transaction looks something like this:

SELECT
    txns.id, txns.hash, inputs.total_inputs, outputs.total_outputs, (inputs.total_inputs - outputs.total_outputs) AS total_fee
FROM
    transactions AS txns
    INNER JOIN (
        SELECT
            transaction_inputs.transaction_id, SUM(transaction_outputs.amount) AS total_inputs
        FROM
            transaction_inputs
            INNER JOIN transaction_outputs
                ON transaction_outputs.id = transaction_inputs.previous_transaction_output_id
        WHERE
            transaction_inputs.transaction_id = 268599964
        GROUP BY transaction_inputs.transaction_id
    ) AS inputs
        ON txns.id = inputs.transaction_id
    INNER JOIN (
        SELECT
            transaction_outputs.transaction_id, SUM(transaction_outputs.amount) AS total_outputs
        FROM
            transaction_outputs
        WHERE
            transaction_outputs.transaction_id = 268599964
        GROUP BY transaction_outputs.transaction_id
    ) AS outputs
        ON txns.id = outputs.transaction_id
    INNER JOIN
        block_transactions
    ON block_transactions.transaction_id = txns.id
WHERE
    txns.id = 268599964
;

I arbitrarily chose Transaction 41CCF5DAB71E22781953BF330FFF0C5BD4B6546DCB541C640A96121289F9F2C1 to search for. The result of this query returned:

+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
| id        | hash                                                             | total_inputs | total_outputs | total_fee |
+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
| 268599964 | 41CCF5DAB71E22781953BF330FFF0C5BD4B6546DCB541C640A96121289F9F2C1 |       316945 |        316702 |       243 |
+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
1 row in set (0.00 sec)

So Tx 41CCF5DAB71E22781953BF330FFF0C5BD4B6546DCB541C640A96121289F9F2C1 spent 243 satoshis in fees, which is a little less than 1 satoshi per byte.

Next, let's find the all of the transaction fees for this block. We can modify the above query to find this.

SELECT
    block_transactions.block_id, txns.id, txns.hash, inputs.total_inputs, outputs.total_outputs, (inputs.total_inputs - outputs.total_outputs) AS total_fee
FROM
    transactions AS txns
    INNER JOIN (
        SELECT
            transaction_inputs.transaction_id, SUM(transaction_outputs.amount) AS total_inputs
        FROM
            transaction_inputs
            INNER JOIN transaction_outputs
                ON transaction_outputs.id = transaction_inputs.previous_transaction_output_id
        WHERE
            transaction_inputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
        GROUP BY transaction_inputs.transaction_id
    ) AS inputs
        ON txns.id = inputs.transaction_id
    INNER JOIN (
        SELECT
            transaction_outputs.transaction_id, SUM(transaction_outputs.amount) AS total_outputs
        FROM
            transaction_outputs
        WHERE
            transaction_outputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
        GROUP BY transaction_outputs.transaction_id
    ) AS outputs
        ON txns.id = outputs.transaction_id
    INNER JOIN
        block_transactions
    ON block_transactions.transaction_id = txns.id
WHERE
    txns.id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016);

The results:

+----------+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
| block_id | id        | hash                                                             | total_inputs | total_outputs | total_fee |
+----------+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
|   564016 | 268599964 | 41CCF5DAB71E22781953BF330FFF0C5BD4B6546DCB541C640A96121289F9F2C1 |       316945 |        316702 |       243 |
|   564016 | 268599965 | B5AB78ED5552FE10F5E9526499AC20F224415CACB53E4D784505AEFC05CE61D1 |     34000000 |      33998900 |      1100 |
|   564016 | 268599967 | D0F8527991F0ED75CEEAE68E63C18D03E11F1386C35C4DA7BF3A66C9633300C4 |    133372350 |     133370254 |      2096 |
|   564016 | 268599968 | 84DF3D7A51889C7FDCD778C648B5DE3C08EC72AA99982C9EFB89C28F479E5755 |    575191857 |     575189256 |      2601 |
|   564016 | 268599969 | F88EEE6C2967D45A916748FEB0F5EADE6F244AB35325624D02C80FCD4F193AC0 |      7999771 |       7999542 |       229 |
|   564016 | 268599970 | A499F6BC4F6C4E51B22EBBD1739160C4ADBE937D02E60DB2D1830EC9F5F1D526 |      5600460 |       5600070 |       390 |
|   564016 | 268599971 | EEB193D594FC0253523457EE35121C6D64E72162DC59A0CB57F85217A01C7FA5 |     28654920 |      28654218 |       702 |
|   564016 | 268599972 | 7625B94041CB725850AC5F458C1E536133CED5FB12DF0C9D88FCD9B9350BCA1C |    119414050 |     119411954 |      2096 |
|   564016 | 268599973 | BE7FFEB57E246F79DA242759A3D0BB6302B60A3C27C0114FFA97114B6344B1FD |    928031681 |     928027161 |      4520 |
|   564016 | 268599974 | 296E48162A15C663B3B2D81E9C47EDC8045D809C23ADAC382892E8550ED4E5D2 |    374642146 |     374641917 |       229 |
|   564016 | 268599975 | 273B894D135EE3BD009C6CF80663D1DC265CFCBC39EAB2ECB29235F835C7F1DB |      2116360 |       2114490 |      1870 |
|   564016 | 268599976 | 9FA02F398E4A822460C397725EA9ABCC83A2C0CCDC92EC8680B6CC39F02A94CD |   6499993220 |    6499986440 |      6780 |
|   564016 | 268599977 | 696AE93F182220E4EB2E14803E5E6564592F131C696A6A5B84E60BC9826E735F |    206903259 |     206902771 |       488 |
|   564016 | 268599978 | D2CE8C4E6BFD735C4AAEA011DE8FE37B33D154AF0FF717584F3B98B0A04F3207 |    167374446 |     167372350 |      2096 |
|   564016 | 268599979 | A52B503A547E96EEEC0FF14B6D4444A01754D6178C454FB19607575A74247F13 |      3668272 |       3667893 |       379 |
|   564016 | 268599980 | 6C572D47F5C38F2D7E10B58CD43D5A69903046F2A6BA75C6E6C827F8022F9030 |    101132746 |     101130650 |      2096 |
|   564016 | 268599981 | 72DA8B5F1B7D2FAF983345154F7D12B50CF8074E832C3A7FCAC1D306DA230219 |    585585565 |     585580565 |      5000 |
|   564016 | 268599982 | 0863DC868EA0800BB3A972E2B5A78041114C32FC190D486766B16F997FFF24B3 |    159334950 |     159333900 |      1050 |
|   564016 | 268599983 | B6FEC3D1EEE703496DA32012F778AD1EBE4D0AAB8E3F0DD468BC29C0C7A2134B |      1242439 |       1242196 |       243 |
|   564016 | 268599984 | 151C0797909042B0CCF4168DEC48B80569C11EFE0E9136E9ECFE3131B0A46FE3 |    586667758 |     586662758 |      5000 |
|   564016 | 268599985 | FE5D419476865D7621DC8A072BDC7D15A71540C7E8B9C6F30A08FC5EC12E25E8 |    828027161 |     828023321 |      3840 |
|   564016 | 268599986 | D6D9B008C6361CD27E68A21283B9C5B4D85B375146337A425ADFEF359CE399AD |    116396600 |     116396373 |       227 |
|   564016 | 268599987 | 2B8A921E8CF185E76EB73F74A7AEEE1983CF258A83124DA92996CE20F392ADC6 |     12325392 |      12325013 |       379 |
|   564016 | 268599988 | BB74BFBF9EF7844937E2EE2346B1191B6A2B6EB6CE5CED259EA2F367F5883E85 |    797796689 |     797795925 |       764 |
|   564016 | 268599989 | C8D796BD6FFB8262E4CC8D721ACE604BC2E6B2F82AD2A0B9DB5C8E8748F7E39C |      6219925 |       6219682 |       243 |
|   564016 | 268599990 | FB3C10AD9DB762AC5B28BCB03B3CDAB5B059B81FDD1D5B1FE68D8FC914B933CA |     93699647 |      93698629 |      1018 |
|   564016 | 268599991 | 17C2C6729F61CDD9FCDD3E831010578C71F72ADA3F15CE3929166D11F7E4394C |     44568654 |      44565264 |      3390 |
|   564016 | 268599992 | 4DE2359D6433EA92BAE108444E3EF08BA62F7D8B654A351FE0583C6337D56017 |      8953347 |       8953118 |       229 |
|   564016 | 268599993 | 5A15879FDDC0717F863425763259C2DA962AC8AF0754FDFDAA75BFEDD000B9C1 |       581193 |        580192 |      1001 |
|   564016 | 268599994 | 0A39C07C27E90024F9D8D3F75B9C7F0C3598E8313168A90171F4C38F9B52D350 |       193873 |        189921 |      3952 |
|   564016 | 268599995 | 65054F28BA9CEDA27902308B82E9205F2DE029DCDC3E84594B16DC825231FC96 |      1081220 |       1080683 |       537 |
|   564016 | 268599996 | 1B23CA1AC1000E7EF8C5D3DEE9D07B8EB9B73D5A181F7673BCC26DDFA5939EE2 |      5521923 |       5521549 |       374 |
|   564016 | 268599997 | 1FBE4E03F41E0D44B41A41383044E092F361C11A996174093E80236972B2E119 |        75170 |         70750 |      4420 |
|   564016 | 268599998 | B34286192A5ABA92343BA0CC60F06A79B4EABC73C8C66A5B21BE1B7057DA4AD6 |        10194 |         10000 |       194 |
|   564016 | 268599999 | 9B9F6E9CA57F04F978E10195A509E3A65597B72034297A20DF52203C78980E60 |    674681059 |     674680573 |       486 |
|   564016 | 268600000 | 86C87F2C199D9EACE72AD9A1C6276E584E5017FD87D61898ED70129A9BE9CE00 |    570816568 |     570815824 |       744 |
|   564016 | 268600001 | 151869F2B3ABF8D7B23837CD52771AC952EE2D4EEC88C28A00D95070898B7AE5 |    586707738 |     586706974 |       764 |
|   564016 | 268600002 | A6B532FAA1A605DAE190151E71DC77B05BDA8F88B2494B851330490DE1C0AC94 |         9754 |          9508 |       246 |
|   564016 | 268600003 | F138303540F49390E76C11464B2027026A3967A868DFBDC6992595DB5FD83B1A |    137799500 |     137798400 |      1100 |
|   564016 | 268600004 | C1D1930BFF5DDDB2EBFEFA9DC940000A7076A8957FFCC42196A86994E399D49F |    881744549 |     881744323 |       226 |
|   564016 | 268600005 | 79EDDBB6BEAEE4EAD7EDB5884ADEB25C40D0366FD202E4ED4633E84062E3D362 |    199500000 |     199498900 |      1100 |
|   564016 | 268600006 | A9D9BA7CAB358E44BCAFA79CBEE23288C754E48ED1E99AB5FA73236F71E5A5DD |    118000000 |     117999757 |       243 |
|   564016 | 268600007 | B5923CE534908EBB1A06862E07834D6EFD01BBC428133EA29861E814A14F33A1 |      1254209 |       1253819 |       390 |
|   564016 | 268600008 | E48B110E13555149534BAA09D0DDF8AF323F3CD7D88C713F043C6E5DBED1FA7F |   1250041061 |    1250039936 |      1125 |
|   564016 | 268600009 | 110C827D0875CD7987DDD05B770923CBECF2DEFAE9E1189AF6F1468A3C0BEE69 |       300758 |        300515 |       243 |
|   564016 | 268600010 | 78CF01EEB4B02B6D16AF55C9B5C93E295D88ED2F0A1422379E0500A502584C83 |      4969756 |       4969513 |       243 |
|   564016 | 268600011 | C371161C9CB306B4B29B236A74E2731D8858CB33AD6326E38AD4D4A9F98E87D4 |      3004709 |       3002839 |      1870 |
|   564016 | 268600012 | E6D9187F7923F4271FA8E7C53C70BD8EFFC299D802B22E379ACE3428E5722ED9 |     55000000 |      54998900 |      1100 |
|   564016 | 268600013 | 025393AE130E92C2BA9ED2EA00A2704E59AD7D2A9218A475B9DF21F4482D04D2 |       330875 |        330485 |       390 |
|   564016 | 268600014 | A9470F512113F080679C6936D3169C62BFE772EFA4116FA88484C896EF572549 |     83370254 |      83368158 |      2096 |
|   564016 | 268600015 | 7F784F3488E21387CA8F6B0DFF4EDE17BFB762DC59267619A01960EADA07F243 |       311948 |        311705 |       243 |
|   564016 | 268600016 | 8A3AC28CB2DC220170114213B1510456E9FD5FA42ED2BD00EC5F15CBC84FDB37 |        50000 |         49612 |       388 |
|   564016 | 268600017 | C5D75793291E74DAA5675BD333925B39EC44556347407145ECC72237023FBC94 |     30100700 |      30099600 |      1100 |
|   564016 | 268600018 | 8AD78103FB6276FAB4E2ED6AAA7DB546AC9B315E892C1A80179F5C3A1F05B7E7 |    126416146 |     126414050 |      2096 |
|   564016 | 268600019 | 95B35DF0429864A63D46A829DBAC1E511007110F66A8072A718B3A0CF1711582 |    102080190 |     102072174 |      8016 |
|   564016 | 268600020 | 9C3C4A85834C635B3E66B680403A24A1DD0CE847DC577576EBA60EFD89793421 |        50000 |         49612 |       388 |
|   564016 | 268600021 | 68313CDAE76D6158D004564032FDE98F9F987EF5D5503B54A2C4651BD4A513E1 |     60000000 |      59998900 |      1100 |
|   564016 | 268600022 | F7D2C71A10D7AEC914475C17309DDEE396CDD85A1F49E23A7FDC272282B2DCCD |    715492617 |     715487617 |      5000 |
+----------+-----------+------------------------------------------------------------------+--------------+---------------+-----------+
58 rows in set (0.00 sec)

Cool! Let's take the average of those to find the Block's average.

SELECT
    SUM(block_txn_fees.total_fee) / COUNT(*)
FROM
    (
        SELECT
            block_transactions.block_id, txns.id, txns.hash, inputs.total_inputs, outputs.total_outputs, (inputs.total_inputs - outputs.total_outputs) AS total_fee
        FROM
            transactions AS txns
            INNER JOIN (
                SELECT
                    transaction_inputs.transaction_id, SUM(transaction_outputs.amount) AS total_inputs
                FROM
                    transaction_inputs
                    INNER JOIN transaction_outputs
                        ON transaction_outputs.id = transaction_inputs.previous_transaction_output_id
                WHERE
                    transaction_inputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
                GROUP BY transaction_inputs.transaction_id
            ) AS inputs
                ON txns.id = inputs.transaction_id
            INNER JOIN (
                SELECT
                    transaction_outputs.transaction_id, SUM(transaction_outputs.amount) AS total_outputs
                FROM
                    transaction_outputs
                WHERE
                    transaction_outputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
                GROUP BY transaction_outputs.transaction_id
            ) AS outputs
                ON txns.id = outputs.transaction_id
            INNER JOIN
                block_transactions
            ON block_transactions.transaction_id = txns.id
        WHERE
            txns.id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
    ) AS block_txn_fees;
+------------------------------------------+
| SUM(block_txn_fees.total_fee) / COUNT(*) |
+------------------------------------------+
|                                1559.8793 |
+------------------------------------------+
1 row in set (0.01 sec)

So roughly 1560 satoshis per Transaction, which is roughly 6.5 satoshis per byte--about 2.5 cents. Next, we could query the whole database to find the number of Transaction Outputs that are less than the average Transaction fee, but I'd have to leave that query running overnight... so instead, let's find how many of the last million outputs are less than that. Let's also exclude outputs that are zero, since they're most like OP_RETURN outputs and aren't meant to be spent anyway.

SELECT
    COUNT(*) AS unspendable_outputs_count
FROM
    transaction_outputs
WHERE
    amount > 0
    AND amount < (
        SELECT
            SUM(block_txn_fees.total_fee) / COUNT(*)
        FROM
            (
                SELECT
                    block_transactions.block_id, txns.id, txns.hash, inputs.total_inputs, outputs.total_outputs, (inputs.total_inputs - outputs.total_outputs) AS total_fee
                FROM
                    transactions AS txns
                    INNER JOIN (
                        SELECT
                            transaction_inputs.transaction_id, SUM(transaction_outputs.amount) AS total_inputs
                        FROM
                            transaction_inputs
                            INNER JOIN transaction_outputs
                                ON transaction_outputs.id = transaction_inputs.previous_transaction_output_id
                        WHERE
                            transaction_inputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
                        GROUP BY transaction_inputs.transaction_id
                    ) AS inputs
                        ON txns.id = inputs.transaction_id
                    INNER JOIN (
                        SELECT
                            transaction_outputs.transaction_id, SUM(transaction_outputs.amount) AS total_outputs
                        FROM
                            transaction_outputs
                        WHERE
                            transaction_outputs.transaction_id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
                        GROUP BY transaction_outputs.transaction_id
                    ) AS outputs
                        ON txns.id = outputs.transaction_id
                    INNER JOIN
                        block_transactions
                    ON block_transactions.transaction_id = txns.id
                WHERE
                    txns.id IN (SELECT transaction_id FROM block_transactions WHERE block_transactions.block_id = 564016)
            ) AS block_txn_fees
        )
    AND transaction_outputs.id BETWEEN 720814733 AND 721814733
;

Result:

+---------------------------+
| unspendable_outputs_count |
+---------------------------+
|                     29422 |
+---------------------------+
1 row in set (0.11 sec)

So, of about 1,000,000 outputs, 29,422 of them are less than the average fee--normally, unspendable dust would be a function of a block's minimum fee, but here we've taken an average. Still an interesting observation, though! If we replaced the average with a minimum, then we'd more likely get a better representation of "unspendable" outputs.

More importantly, we can now start asking ourselves questions and using data to reflect on future decisions about the protocol.

Next Steps

Next, we can properly index/pre-calculate some of these values to speed up some of the queries so we can start making interactive charts on the Block Explorer. We can also spend a little bit more of extra computation time to make less estimates and keep the values as accurate as possible.

I look forward to seeing more use cases of Bitcoin Verde and using it to ensure we're leading the protocol's design the best we responsibly can. With accurate information, and real, verifiable facts.

Comments