5

I have a full node, and have written some limited python programs with bitcoinrpc. I want to start doing slightly more ambitious things now, e.g. visualisations across the entire blockchain (possibly analysing all transactions) and need to slice and dice the data in ways that make me instinctively want to have it queryable in a db (e.g. bigquery). Then again there's already over 800m transactions and so the performance of querying a table of transactions might not be any better than querying via bitcoinrpc.

I suppose my question is whether anyone who's made similar decisions in the past has any guiding principles for thinking about when it makes sense to dump large parts of the blockchain into your own db instead of query it directly via your node? E.g. I'm assuming the bitcoin api services (e.g. blockcypher) aren't just running a bunch of full nodes and querying them directly when new requests come in but instead have structured the data in a way that makes that much more efficient? A related question I have is whether Bitcoin was even designed with maximally-efficient-blockchain-querying in mind?

Improve this question
3
  • 1
    Have you considered using a blockexplorer backend like Esplora? They may have done a lot of the heavy lifting for what you want to do already.
    – Murch ♦
    Feb 13 at 15:57
  • thanks for the suggestion @Murch - not familiar with Esplora but I'm guessing it must be one of those API models where I'm going to end up rate limited or paying for the privilege. Also part of me wants to do this for the fun and learning opportunity!
    – d3wannabe
    Feb 13 at 16:28
  • 2
    Okay, just figured that I’d point it out as an option. Esplora is open source software that you can run on your own computer: github.com/Blockstream/esplora
    – Murch ♦
    Feb 13 at 16:32

1 Answer 1

Reset to default
5

Google BigQuery already maintains a public dataset for Bitcoin, with tables optimized for querying transactions and blocks.

You can find the dataset here: https://console.cloud.google.com/marketplace/product/bitcoin/crypto-bitcoin

and I've also written a guide on connecting to this dataset with Python/R here: https://github.com/josibake/bitcoin-data-analysis/

If you want to use your local copy of the blockchain, there are block explorers you can run alongside your node, such as Esplora.

I suspect, however, that a block explorer will be more cumbersome if you try to do analytical queries which span multiple blocks and that a SQL database is better suited.

As to whether or not Bitcoin was designed for maximally efficient querying, I'd argue no. Bitcoin's data structures are designed to be as small as possible on disk, which often makes querying more difficult.

Disclaimer: I am the author of one of the linked resources.

Improve this answer
1
  • 1
    this is great thanks so much - had no idea BigQuery already maintained a public dataset!
    – d3wannabe
    Feb 13 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.