[chjj]

Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node.

In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for something like 30,000 utxos out of a set of a couple million. What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over 30 seconds in SQlite.

Now, you can tell me I did the benchmarks wrong, and "oh, if you just did this it would be faster!", but 30+ seconds is slower to an absolutely insane level. Needless to say, I went the key-value store route, but I was still astounded at how slow sqlite was once it got a few million records in the database.

I actually like sqlite, but when you know you're going to be dealing with 70gb of data and over 10 million records, preemptive optimization is the key. If I were the author, I would consider switching to postgres if there are over 500k-1m records to be expected. That being said, if they're partial to sqlite, SQLightning (https://github.com/LMDB/sqlightning) looks pretty interesting (SQLite with an LMDB backend).

edit: To clarify, these weren't particularly scientific benchmarks. This was me timing a very specific query to get an idea of the level of data management I was up against. Don't take my word for it.

[panic]

What operations were you doing in your benchmark? What was SQLite actually doing (CPU?, disk I/O?) while taking 30 seconds to finish? This would be a lot more useful and less FUDdy with more detail.

[chjj]

I'll clarify here: these weren't very scientific benchmarks, as I alluded to. So, don't take my word for it. I didn't measure ops per second, I didn't take into account system load or cpu usage, etc. This is all anecdotal evidence. I'm not saying this an announcement that "you shouldn't use sqlite". I measured the time of one query.

It was a benchmark I ran just to personally give me a general idea of what I was up against in terms of data management. This is primarily why I didn't post them in the first place. This was several months ago. I'll try to find the code that ran them and put them up on github if I can, but I doubt it will be that useful without the actual data, which isn't easy to upload.

So yes, to clarify, don't take my word for it. This is just my experience.

[vonklaus]

I would be interested in a repo or post about your setup for the actual project; downloading, storing and using blockchain data. Sounds super interesting so if it(article, repo, site) isn't private & exists, would be keen. Cheers.

[coleifer]

Not the poster, but I did some benchmarks with embedded databases and Python if you're interested in completely useless, unscientific data:

http://charlesleifer.com/blog/completely-un-scientific-bench...

[chjj]

The project itself an alternative bitcoin full node, one of the few (along with btcd, bitcoinj, etc), and the only one that runs in the browser: http://bcoin.io/browser.html. I've posted it here before but no one seemed to be interested.

It's probably not good if you want to index anything more than addresses, but you could easily modify it to index other things if you wanted to. The actual blockchain database resides here: https://github.com/bcoin-org/bcoin/blob/coinviews5/lib/bcoin...

^ That's the current branch I'm working on which, as it happens, optimizes a lot of the storage of utxos. Before, it was storing them the messy bitcoinj way. Now it uses coin viewpoints similar to bitcoind and btcd.

[vonklaus]

this is super awesome, starred the repo. thanks!

[mappu]

I've found sqlite's performance on bulk insertions to be massively (100x) improved by wrapping the bulk insertion in a transaction.

fsync()ing a couple hundred thousand individual INSERTs isn't fast.

[coleifer]

Batching inserts, wrapping the batches in transactions, waiting til after to add indexes: best way to go.

[Sami_Lehtinen]

Journaling mode and sync modes etc, also make a big difference: http://www.sami-lehtinen.net/blog/sqlite3-performance-testin...

[lomnakkus]

Those optimizations also apply to PostgreSQL.

[Programmatic]

It would still allow you to use a simpler solution and not paint yourself in a corner if you optimized on sqlite; no preemptive optimization necessary wrt choice of dbms if you can perform the work on the simpler solution.

[lomnakkus]

Optimizing in SQLite is just as much of a sunk cost as optimizing in PostgreSQL if you switch away to another database.

[orf]

> What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over 30 seconds in SQlite.

I'm sorry to say but you're almost certainly doing something wrong then.

[chjj]

It's possible, but it was a very simple database layout. Pretty hard to screw up. Then again, I screw up simple things all the time. But whatever I screwed up would likely have been duplicated on the postgres side since the sqlite and postgres setups were nearly identical. Postgres performed fine compared to sqlite. It was a bit behind leveldb, but I figure the overhead of flushing the data to a socket has an added cost. Leveldb has the advantage of being in the same process.

If I remember right: Querying for leveldb was iteration from [20-byte-hash][lo-utxo-id] to [20-byte-hash][hi-utxo-id] and subsequent lookups on the keys containing the actual data. The sql table setup was `id` as a char primary key and `address` as a char with an index. The query was a simple `select data from utxos where address = ?`. The actual `data` blob was on average maybe 60 bytes in size.

Maybe there was something I could have done better there. I'm not sure. This was just my experience. This is all beside the point anyway. My point was whatever database was better, it was worth testing each one, and I don't consider it to be premature optimization.

[pilif]

`select * from utxos where address =?` should not take 300ms no matter the size or type of database (assuming there is an index on address).

[agnivade]

Not really. If your table does not fit into memory, there is a good chance that a disk seek is required. Now all bets are off.

[invaliduser]

By table you mean index, right ? What matters most is the size of the index, and most index data structures (see btrees) work so that very few disk seeks are needed, even if the index is large.

[orf]

Perhaps if you're using a really slow spinning disk and have no indexes, or the results are distributed evenly across the entire dataset (i.e lots of random, non sequential access).

It's just 300ms is really slow, even with a largeish dataset that doesn't fit into memory. Perhaps you hit a corner case in some way that destroyed performance in sqlite, but I'd be surprised if those results were representative.

[SixSigma]

Until there are millions of records grep will win

[ww520]

I'm curious to know how come Sqlite performed so bad on couple millions records.

Can you share what the table schema is? What the queries are? And what indices built for the Postgres and Sqlite tables?

[ams6110]

With relational databases, you absolutely can see subsecond to 30+ second ranges for the same query on the same data, if you don't have proper indexing or stats on your tables.

[voiper1]

Indeed! An index can be the difference between a 2-5minute full table scan and a subsecond query.

[Sami_Lehtinen]

Depends from your disk system and data set size ... It can also mean 2-5 hours or days versus subsecond query. Try having 6 TB database with integer column. Then select just one row from it. Either it's indexed or not. You don't need to explain that, if query ends up taking forever it wasn't indexed.