Messing Around with Data Analytics

I've been diving into the raw data for the past few months after setting up a node and building a relational database just to play around with analytics. Honestly, my database is massive, like almost 4TB, a lot of which is basically just indexing. I really need to trim down the data to focus on more relevant tables for what I want to discover moving forward. If this isn't the best spot for this, my bad. Just wanted to share some of my findings and maybe spark a discussion or get some ideas on what else I could check out. I realize I probably should have a time series dataset too. I'm really interested in modeling the hodl waves. If any of the data I mention here is way off, that feedback would be appreciated too, lol. I might have some data quality issues despite my best efforts to keep things clean. I started looking into utxos, keeping track of all of them and marking them as spent when they get used. Trying to figure out what counts as dust or what might be lost over time. Since Bitcoin began, I've seen around 3.2 billion utxos, and out of those, 179,288,217 are still unspent. Of that unspent group, 86,898,633 have less than 1000 sats (0.00001 BTC or about a buck right now, pretty much at or lower than network fees). The total amount of that unspent 'dust' adds up to 426.3546 BTC. This year alone (2025, since block 877279), we've seen an additional 18.95 BTC added to that dust pile. If I set the threshold to 0.0001, the total for this year is 56.9 BTC. Gonna have to set up a cron job for every new block update since my database was last refreshed about 20 hours ago.

Bitcoin Core have 546 satoshi as dust limit, although it was 5460 satoshi long time ago. Although the exact way to calculate dust limit is more complex, see https://github.com/bitcoin/bitcoin/blob/v28.1/src/policy/policy.cpp#L26. Since you don't mention when those UTXO dust created, i would speculate most of them are created by Ordinals. See https://bitcoin.stackexchange.com/a/118262.

OP can verify this data by analysing the dust accumulation over the time and see if it's spikes exactly when the ordinals started spamming the network. The other contributors are some wallets which doesn't allow coin control lead to created unnecessary dust amount into the change address.

I'll need to do some more digging into what exactly Ordinals are. I'm still cleaning up the data. I discovered that about 140m of my utxos seem to be OP_RETURN and have an amount of 0.00000000 but are marked as unspent (or rather, were never spent after being created). I'm guessing these were just place markers for people to add comments to the chain? I ordered these by script type and 99.9% of them are of type nulldata. I'm trying to decide if I should extract these into a separate table to maintain the ingerity (in case in the future I want to distinguish them for some other purpose), or simply mark them as false for the is_spent column to get them out of the way when I do queries on spendable utxos. I suppose I could just exclude them by the nulldata association? Thinking out loud here... Good idea. Once I can run some more efficient queries, I'll look at clustering them by data/time.

Apart from the recent Ordinals Attack that contributed to a large number of small UTXOs, majority of the old ones are mainly because of Dusting Attack. This attack is performed as an attempt to deanonymize the addresses that are sent these dust amounts. The "hope" is that the receiver would use other UTXOs from other addresses previously not-linked to the dusted address when consolidating the coins, therefore linking them all together. Try analyzing scripts looking for non-standard/unusual output scripts, unusual signatures, and stuff like that. It could be interesting. Technically OP_RETURN outputs are never included in the UTXO set because they are not unspent (ie. not yet spent, may be spent in the future) transaction outputs. They are unspendable as in they cannot be spent ever or they are provably unspendable. OP_RETURN is just the standard way of adding a small arbitrary data to the bitcoin blockchain, which is why they call it "nulldata".

Forget to mention it earlier, but have you consider using existing statistic/analytic tool such as Statoshi[1-2]? You may get some inspiration of what you could analyze further. For additional reference, OP_RETURN used by OmniLayer and Runes protocol to create token. OmniLayer was mainly used for USDT which AFAIK no longer supported by Tether, while Runes is about NFT/token. [1] https://github.com/jlopp/statoshi [2] https://statoshi.info

That's right but it is worth mentioning that from the perspective of Bitcoin protocol and its smart contract language (scripts) what is followed by OP_RETURN is arbitrary data that won't be interpreted in any other way. Outside Bitcoin protocol, usually in centralized databases (like Tether) they can interpret that arbitrary data however they like with their own protocol such as using it as token transfers inside Omni Layer.

OP_RETURN, in Bitcoin protocol, prune transaction embedded with data out of the Utxo set and make them unspendable, which creates a way for the Opcode to be used for burning bicoin. Runes protocol, for instance, then uses the Opcode to hold just about 80bytes of fungible data token, that includes, name, minting, token ID, and symbol, to aid the transfers of Runestoken.

I did a print out of the combined total of all utxos where is_spent = false and I got back 20009210.72379106 ... which is close, but higher than the total supply in circulation (around 19.82m) I'm hoping the difference has something to do with a certain script type not strigger the is_spent variable I track... I'm also aware that the node maintains a current list of all utxos, so maybe my best bet is to do a comparison of utxo ID fields? Curious if anything stands out to anyone on this: Here is a print out of amounts summed by script_type... Not worth doing more dust accounting until I get this table synced with the current chain... I'll do a join with the tx_inputs table to check for situations where a spend has happened but possibly didn't get reflected in my utxos table. I'll have to mark down where this happened... hopefully don't be an ongoing issue forward, but I guess could be accounted for programatically as new data comes in... making progress...

The protocol doesn't make them unspendable by pruning them out of the UTXO set (or rather not including them in first place), they are unspendable because of how that OP code is defined. Not to mention that bitcoin core is only skipping certain scripts that contain OP_RETURN[1] not all of them and that behavior can be categorized as a preference by this client not as part of the protocol. The way protocol makes them unspendable is by defining how they are treated[2] which is: if the interpreter reaches OP_RETURN the script evaluation fails right away. Otherwise the following output script is also unspendable but bitcoin core will include it in the UTXO set: P.S. FWIW when I say "reaches them" I mean the following output script is spendable regardless of the OP_RETURN inside it: [1] https://github.com/bitcoin/bitcoin/blob/43e71f74988b2ad87e4bfc0e1b5c921ab86ec176/src/script/script.h#L571 [2] https://github.com/bitcoin/bitcoin/blob/43e71f74988b2ad87e4bfc0e1b5c921ab86ec176/src/script/interpreter.cpp#L666-L669 That is still arbitrary data as far as Bitcoin protocol is concerned.