Blog


25 Feb 2016

Blockbin is taking a break


Blockbin will be closing for a period of time at UTC March 1, 2016.

Additional columns and indexes were added over time to the original SQL design. The size of the database is now almost 100GB, resulting in very high server (memory) costs. Such costs, ongoing maintenance and limited interest in the project has caused a re-evaluation of the overall design and business model.

Compute facilities will no longer be available past the above date. Access to saved snippets will be available for a limited period after this date. Please locally save any snippets you may have.

Apologies for any inconvenience. We hope to welcome you back in future with a possible relaunch.


Regards,
Blockbin Support


08 Feb 2016

Blocks are full, right?


Blockbin has recently added some additional columns and indexes to the SQL schema. Your Python code now has access to even more data from the Bitcoin blockchain. This may offer some additional insights into the operations and growth of the network. Let us take a look at block size.

So, are blocks full? Not quite. They are, however, very busy.


cur.execute('SELECT block_id, size '
            'FROM block '
            'WHERE block_id > 395000')


686KB average block size over 2200+ blocks.

We can construct a more complex query here. Note - older coins are typically prioritized and can influence the overall required fee, even qualifying for a 'free' transaction occasionally. There are also a number of other conditions that affect transaction fees. Firstly, let us determine how many transactions have no fees at all. Instead of charting overall block sizes, we will sum all transaction sizes within each block that contain no fees.


cur.execute('SELECT block_id, sum(size) '
            'FROM tx '
            'WHERE block_id > 395000 AND '
            'fee = 0 '
            'GROUP BY block_id')


An average of 7KB per block contain transactions with no fees (approximately 4 transactions per block). Not a lot. These will either be old coins moving, transactions without fees, or the coinbase transaction.

For simplicity and argument's sake, let us conclude that all transactions should pay a fee. We will now sum all transaction sizes within each block, filtering out transactions that do not include a minimum fee per kilobyte (10,000 satoshis per 1,000 bytes).


cur.execute('SELECT block_id, sum(size) '
            'FROM tx '
            'WHERE block_id > 395000 AND '
            'fee / size >= 10 '
            'GROUP BY block_id')


An average of 632KB per block. Note a huge difference compared to the first chart. This is good, however, as it means participants are paying fees! The 50KB+ difference will include the 7KB mentioned earlier, plus any transactions that do not meet the minimum fee per kilobyte specified.

The current 'fee' of 10,000 satoshis (approximately 4c USD) per transaction (or KB) becomes very expensive for those looking to spam the network. Should spam, however, determine the cost of a transaction? Let us go further with this..

The 'halvening' is close approaching. Sometime in July of this year (block height 420000), the block reward will drop from 25 BTC to 12.5 BTC. Miners operate in the physical world and are subject to real-world costs. This drop has probably already been priced-in to some extent by the major miners and their projected earnings, but it could still wreak havoc with other operators. Could fees soon become more interesting to miners?

Out of curiosity, what if the average fee increased? Four cents for a fast, worldwide transaction seems quite cheap after all! What if miners, for the most part, determined fees based on their physical world costs (e.g., market forces)? How many transactions currently pay a minimum 10c (26,000 satoshis) per transaction (or kilobyte) for example?


cur.execute('SELECT block_id, sum(size) '
            'FROM tx '
            'WHERE block_id > 395000 AND '
            'fee / size >= 26 '
            'GROUP BY block_id')


An average of 437KB per block is paying at least 10c per transaction (or kilobyte). How about 20c (52,000 satoshis) per transaction (or kilobyte)?


cur.execute('SELECT block_id, sum(size) '
            'FROM tx '
            'WHERE block_id > 395000 AND '
            'fee / size >= 52 '
            'GROUP BY block_id')


An average of 100KB per block is paying at least 20c per transaction (or kilobyte). We could go on.. but that would not be very meaningful or constructive. These are arbitrary numbers.

Note - Approximately 5% of blocks are 'empty' (contain no transactions, other than the coinbase). These are ignored in the above figures.

Out of curiosity, which miners are already dropping low-fee transactions? How about we first take a look at average weekly block size, by miners?


miners = json.loads('{"BitFury":{"name":"Bitfury"},"AntPool":{"name":"AntPool"}.. Eligius":{"name":"Eligius"}}')
cur.execute('SELECT coinbase, size '
            'FROM block '
            'WHERE block_id > ?', (x,))
..
        if miner.encode('utf-8') in str(row[0].decode("hex")):
..


Let us again filter out some low-fee transactions and chart the average weekly block size. This query will be a little more difficult. We will first create a temporary view for our transactions, joining the 'tx' and 'block' tables.


cur.execute('CREATE TEMPORARY VIEW view_tx AS '
            'SELECT '
            '    tx.size as size, '
            '    tx.fee as fee, '
            '    tx.block_id as block_id, '
            '    block.coinbase as coinbase '
            'FROM tx '
            '    INNER JOIN block ON (tx.block_id = block.block_id)')

We will now filter out transactions that do not include a minimum fee per kilobyte (10,000 satoshis per 1,000 bytes).


miners = json.loads('{"BitFury":{"name":"Bitfury"},"AntPool":{"name":"AntPool"}.. Eligius":{"name":"Eligius"}}')
cur.execute('SELECT coinbase, sum(size) '
            'FROM view_tx '
            'WHERE block_id >? AND '
            'fee / size >= 10 '
            'GROUP BY block_id', (x,))
..
        if miner.encode('utf-8') in str(row[0].decode("hex")):
..


There seems to be a difference with Bitfury between those two charts. Let us count up all low-fee transactions (there are not many) over a 10-week period and and see who is mining them.


miners = json.loads('{"BitFury":{"name":"Bitfury"},"AntPool":{"name":"AntPool"}.. Eligius":{"name":"Eligius"}}')
cur.execute('SELECT coinbase, count(tx_id) ' # get coinbases from blocks, tx count
            'FROM view_tx '
            'WHERE block_id > ? AND '
            'fee / size < 10 '
            'GROUP BY block_id', (x,))
..
        if miner.encode('utf-8') in str(row[0].decode("hex")):
..


Blockbin was built with exploration and curiosity in mind. Originally developed to satisfy a private curiosity with Bitcoin trends, Blockbin is now encouraging others to play with this wonderful experiment known as Bitcoin. Blockbin permits you to query the Bitcoin blockchain in-memory (SQLite) via Python.