Bitcoin Forum

I'm building a bitcoin website, and have been using bitcoind for database requests which has been fairly slow. We'll only really be looking at history, and not managing any wallet based functions. I'm considering switching to either SQL or Mongo for longer-term use, does anyone have experience using either for bitcoin sites? What database structure do some of the larger sites like blockchain.info use?

Thanks for your help.

Wouldnt it make sense to keep all you transaction records in a database?  I use MySQL and then can reference TX ID's if I make qt calls.

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

People generally wind up creating custom databases, or at least highly custom setups of standard database software.

You are talking about indices containing many millions of records.  It isn't as easy as just telling your SQL db to index a column.  People often turn to tools like redis for such huge datasets.

Thanks Jeff. Why would you recommend redis over mongo?

Mongo works just fine, too.

Maybe take a peek at the Bits of Proof Enterprise Server (http://bitsofproof.com/).  Stores the blockchain in the JPA compliant database of your choice.

+1

You may run it with an SQL Configuration (http://bitsofproof.com:8082/display/BPD/Build+and+run+the+Server) to get the block chain into a fully normalized relational database or with LevelDB and use its API (http://bitsofproof.com:8082/display/BPD/Scan+Transactions) to retrieve blocks or transactions.

I build, host and support servers running the configuration of your choice if you like.

PHP Sites = MYSQL
I haven't touched Mongo as of yet.

Anything with MYSQL should be fine as a stable database for a bitcoin website.

PostgreSQL has no problem indexing the entire blockchain.

Example: select * from transaction_outputs where ARRAY['18ese9gmJ5zYePvLQiFoC5bVNzVicgDZWz'::character varying] && addresses;
Total runtime: 0.066 ms

The issue is the time to insert/update columns, in general indexed select queries are fast.

I have used Mongo for other projects and it's a bitch to maintain. It makes most sense if
a) You know how to maintain it
b) The R/W ratio is skewed to 90% reads or more
c) You have lots of RAM.
d) You don't care about transactions and can live with less than 100% data integrity

I have used mySQL for the same purpose of analysing and searching for info in block chain.
All are INNODB tables with baraccuda compression with key-block-size being 2kb
I am recording all the data available over the block chain and it is effectively taking twice the amount of data (17gb) it takes for normal bitcoind block chain(8.5gb).

Are you storing things as their hex representation or binary representation?

The nearly exact doubling in size makes me suspect hex.

May I offer an other play: LevelDB is a persistent key-value map. I use transaction and block hashes as keys and the value is protobuf serialization. Blocks are stored as serialized header + list of tx hashes + bloom filter. The persistent bloom filter is populated with all data elements of scripts and outpoints. I read the bloom filter set into memory to query. For blocks where filter is positive I retrieve and parse the transactions.

The result is 11.8 GB disk use (at block 241061) and it takes 3 seconds to scan the entire blockchain for addresses generated from a BIP32 public key and transactions spending them with 10 keys lookahead. (LevelDB is native, rest is Java).

Yes i am storing it as hex at this point of time.