The following sections might be updated with supplementary metadata relevant to reviewers and maintainers.

Code Coverage & Benchmarks

For details see: https://corecheck.dev/bitcoin/bitcoin/pulls/27432.

Reviews

See the guideline for information on the review process.

If your review is incorrectly listed, please react with 👎 to this comment and the bot will ignore it on the next update.

Conflicts

No conflicts as of last run.

This also closes #21670 ;-)

Pinging users who worked on or reviewed / expressed interest in the previous attempt to solve this issue (PR #24952):
@dunxen @0xB10C @jamesob @prusnak @willcl-ark @w0xlt @jonatack @brunoerg @laanwj @fanquake

the resulting file has roughly 2x the size of the compact-serialized UTXO set (this is mostly due to encoding txids and scriptpubkeys as hex-strings rather than bytes)

What is the rationale for encoding as text rather than bytes? SQLite can store byte values as BLOBs.

the resulting file has roughly 2x the size of the compact-serialized UTXO set (this is mostly due to encoding txids and scriptpubkeys as hex-strings rather than bytes)

What is the rationale for encoding as text rather than bytes? SQLite can store byte values as BLOBs.

Fair question. There was already some discussion in #24952 about whether to store txids/scriptPubKeys as TEXT or BLOB, see #24952 (review), #24952 (comment) and #24952 (comment). The two main points were:

• conversion from this database to json/csv etc should be as simple as possible, and ideally "select * from utxos" should already lead to human-readable output. Converting would then be as trivial as a short one-liner sqlite3 -json utxo.db "SELECT * FROM utxos" > utxo.json, without even having to specify the columns
• more annoying: if using BLOB for the txid, we would have to decide if we store the txid in little or big endian byte order. Big endian would be more natural, as that's how we internally store the txid and also serialize it on the wire, but we show everything in little endian byte order in Bitcoin Core, so a simple "select hex(txid) from utxos" would just show the txid in the wrong order, and something like "reverse(...)" doesn't exist in SQLite (though some hacky workarounds have been proposed: rpc: Add sqlite format option for dumptxoutset #24952 (comment) and rpc: Add sqlite format option for dumptxoutset #24952 (comment)). There remains the possibility to just store the txid in little endian order, but that's the opposite of what we do in leveldb or in the serialization of outpoints, so it could lead to huge confusion for users if not clearly documented. For TEXT the order is clear, it's just stored what is shown everywhere in Bitcoin Core and all wallets, block explorers etc.

Considering the scriptPubKey column individually, there is no good reason to use TEXT rather than BLOB, but I went for TEXT mostly for consistency reasons, to not mix TEXT and BLOB in different columns when it's both binary data.

That said, I'm also very open also for using BLOB instead, it's just a matter of trade-offs.

Approach ACK. Seems like a fine idea to me.

What is the rationale for encoding as text rather than bytes? SQLite can store byte values as BLOBs.

It's a python conversion script: can't you just add a command-line option for the resulting db to have hex txids or big/little endian blobs if there's user demand for it? Hex encoding seems a fine default to me, for what it's worth.

If people end up wanting lots of different options (convert scriptPubKeys to addresses? some way to update the db to a new state, rather than just create a new one?) maybe it would make sense for this script to have its own repo even; but while it stays simple/small, seems fine for contrib.

Concept ACK, will test soon

Concept ACK

Could mark as draft while CI is red?

Sorry for the extra-late reply, missed this message and the CI fail. Rebased on master and resolved the silent merge conflict (caused by the module move test_framework.muhash -> test_framework.crypto.muhash in #28374). Also fixed the Windows CI issue by closing the sqlite connections properly with explicit con.close() calls (see e.g. #28204). CI is green now.

What is the rationale for encoding as text rather than bytes? SQLite can store byte values as BLOBs.

It's a python conversion script: can't you just add a command-line option for the resulting db to have hex txids or big/little endian blobs if there's user demand for it? Hex encoding seems a fine default to me, for what it's worth.

Good idea, planning to tackle this as a follow-up.

post-merge tACK 4080b66

FTR, it's possible to import the data from SQLite into DuckDB, to use its optimized query execution engine (for faster queries):

ATTACH 'utxo.sqlite' AS utxo_sqlite (TYPE SQLITE, READONLY);
ATTACH 'utxo.duckdb' AS utxo_duckdb;
COPY FROM DATABASE utxo_sqlite TO utxo_duckdb;
USE utxo_duckdb;

$ du -h utxo.*
24G	utxo.sqlite
13G	utxo.duckdb

In [1]: import duckdb

In [2]: c = duckdb.connect()

In [3]: c.sql("ATTACH 'utxo.duckdb' AS utxo_duckdb")

In [4]: c.sql("USE utxo_duckdb")

In [5]: %time c.sql("SELECT count(*) FROM utxos").show()
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│    177884546 │
└──────────────┘

CPU times: user 2.16 ms, sys: 5.78 ms, total: 7.95 ms
Wall time: 7.1 ms

In [6]: %time c.sql("SELECT count(*) FROM utxos WHERE value < 1000").show()
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│     85656982 │
└──────────────┘

CPU times: user 926 ms, sys: 765 ms, total: 1.69 s
Wall time: 161 ms

In [7]: %time c.sql("SELECT count(*), CAST(height/50000 AS INTEGER) as i FROM utxos WHERE value < 1000 GROUP BY i ORDER BY i").show()
┌──────────────┬───────┐
│ count_star() │   i   │
│    int64     │ int32 │
├──────────────┼───────┤
│          501 │     2 │
│       364135 │     3 │
│       371358 │     4 │
│       883151 │     5 │
│       105033 │     6 │
│       264542 │     7 │
│        77163 │     8 │
│       256262 │     9 │
│       541590 │    10 │
│      1578882 │    11 │
│      3756328 │    12 │
│      1441847 │    13 │
│       712565 │    14 │
│       510563 │    15 │
│     34044107 │    16 │
│     34755149 │    17 │
│      5993806 │    18 │
├──────────────┴───────┤
│ 17 rows    2 columns │
└──────────────────────┘

CPU times: user 3.08 s, sys: 676 ms, total: 3.75 s
Wall time: 320 ms

Great to see that this was merged!