search

runonbitcoin / run-db

An indexer that crawls the blockchain and calculates RUN states
MIT License
24 stars 22 forks source link

Hex strings in database should be stored as BLOBs #4

Closed brentongunning closed 3 years ago

brentongunning commented 3 years ago

sqlite3_analyzer reports that the tx table is ~59% of the database right now, and the hex column in that table is the bulk of it. We should convert that to the BLOB data type. The HEX() function of sqlite can be used to convert back to hex quickly in queries so changing this table should require minimal run-db changes. I'd also like to version the database so that existing DBs can be upgraded upon restart. After hex, other savings could be the txid, deps, spends, and location columns, but changing these will custom queries more difficult. Converting just the hex column will shrink the database by ~30% if using the default trustlist.

brentongunning commented 3 years ago

Fixed in https://github.com/runonbitcoin/run-db/commit/5bad5d3067f3b02d111c979ab5db2437807f772c

Result: Size reduction from 2.8G to 1.9G, ~33%.

Note that the VACUUM command during the upgrade takes ~5 minutes to complete for the size above on a regular laptop.

brentongunning commented 3 years ago

If you are querying hex strings from the tx table, you just need to change:

SELECT hex FROM tx WHERE ...

to

SELECT HEX(bytes) AS hex FROM tx WHERE ...