Coding Enthusiast (OP)
Legendary
Offline
Activity: 1040
Merit: 2785
Bitcoin and C♯ Enthusiast
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 05:42:41 AM |
|
I'm starting to look into key-value stores and I'm curious whether there has been any research and comparison between different options from a bitcoin usage point of view. I'm looking into MongoDB, Redis, PostGres, Cassandra and of course BerkeleyDB since bitcoin core uses it. There are a lot more options which make things even harder.
The information I've found so far are for purposes different than bitcoin, for instance PostGres is used by Reddit and they claim it is the fastest based on their benchmarks.
Hopefully a comparison into their performance (speed) and scalability but also I'm also interested in concurrency and whether it could be taken advantage of using these DBs. I know that Cassandra and Redis support concurrency but BerkeleyDB doesn't seem to.
|
|
|
|
achow101
Moderator
Legendary
Offline
Activity: 3486
Merit: 6824
Just writing some code
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 06:14:10 AM |
|
It depends on what the database is being used for. There isn't a single "Bitcoin use case". Are you talking about UTXO set storage? An index? A wallet? It all depends on what you are actually planning on using the database for.
For example, Bitcoin Core currently uses LevelDB for the UTXO set and all indexes (block index, transaction index, block filter index). Berkeley DB and SQLite (in 0.21 for descriptor wallets) are used for the wallet.
|
|
|
|
Coding Enthusiast (OP)
Legendary
Offline
Activity: 1040
Merit: 2785
Bitcoin and C♯ Enthusiast
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 06:41:20 AM |
|
There isn't a single "Bitcoin use case". Are you talking about UTXO set storage? An index?
I currently have both UTXO set and wallet in mind but lets start with UTXO. Index will be my future interest.
|
|
|
|
NotATether
Legendary
Offline
Activity: 1708
Merit: 7185
In memory of o_e_l_e_o
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 07:38:15 AM |
|
It's definitely really important that a portable database format that can be persisted on disk be used for the wallet file database, or else you couldn't move wallet files around and they'd (for the most part) be stuck on the system they were created in. So the options are one of BDB and sqlite3 which achow101 already mentioned.
Obviously any change in the database used for wallet.dats will be rejected since it will break compatibility with all tools that depend on the structure.
First off you wouldn't want to use a SQL database for any bitcoin data because you'd have to shoehorn the key/value data into relational format with tables, primary keys and all that special stuff. This leaves only NoSQL databases as a viable option, and wouldn't be end-user disruptive because nobody moves around the chainstate database.
Usability also has to be considered to e.g. we don't want to require people to create a Postgres database and user and password at first run for Bitcoin Core to use.
|
|
|
|
ABCbits
Legendary
Offline
Activity: 2982
Merit: 7830
Crypto Swap Exchange
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 12:27:00 PM |
|
Not research, but apparently Electrum uses JSON to store the wallet content. But obviously it's not efficient since it's written on pure Python and JSON doesn't scale well. There's discussion about JSON alternative for Electrum at https://github.com/spesmilo/electrum/issues/4823, you might find one or two interesting idea. For example, Bitcoin Core currently uses LevelDB for the UTXO set and all indexes (block index, transaction index, block filter index). Berkeley DB and SQLite (in 0.21 for descriptor wallets) are used for the wallet.
Are there any particular reason why Bitcoin Core uses Berkeley DB & LevelDB while both of them are key-level database? I imagine it's easier to maintain Bitcoin Core source code if only one of the is used.
|
|
|
|
Coding Enthusiast (OP)
Legendary
Offline
Activity: 1040
Merit: 2785
Bitcoin and C♯ Enthusiast
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 26, 2020, 03:35:54 PM |
|
But obviously it's not efficient since it's written on pure Python and JSON doesn't scale well.
The lack of efficiency is not about the language, it is because the entire file is read and written each time instead of partial read/writes which can't really scale. It woks well for small sizes which is why there is only a handful of complaints so far. But you are right JSON itself doesn't scale well.
|
|
|
|
zeuner
Member
![*](https://bitcointalk.org/Themes/custom1/images/star.gif)
Offline
Activity: 189
Merit: 16
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 28, 2020, 06:03:54 PM Merited by NotATether (1) |
|
It's definitely really important that a portable database format that can be persisted on disk be used for the wallet file database, or else you couldn't move wallet files around and they'd (for the most part) be stuck on the system they were created in.
Actually, I think requiring a wallet file that can be moved around in all cases might actually hinder proper research on database backend efficiency. Ideally, database engines would be pluggable in the code, and there would be a database migration tool to switch between engines. Then it suffices to have one engine around that has a fixed wallet file format. This would enable benchmarks like https://blog.lopp.net/2020-bitcoin-node-performance-tests/ to be ran on one engine, but with different database backends enabled. Being able to make informed decisions based on benchmarks will gain importance with the growth of the blockchain.
|
|
|
|
coinlatte
Newbie
Offline
Activity: 21
Merit: 16
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 28, 2020, 07:09:37 PM |
|
Obviously any change in the database used for wallet.dats will be rejected since it will break compatibility with all tools that depend on the structure. The only thing that users need is the "xprv" value if it is HD wallet or all WIF-encoded private keys if it is not. By having private keys it is possible to recover everything else. Am I missing something? Both things can be easily written on a piece of paper and expressed in text as needed, so they are as "standard" as they could be.
|
|
|
|
achow101
Moderator
Legendary
Offline
Activity: 3486
Merit: 6824
Just writing some code
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 28, 2020, 09:14:05 PM Merited by ABCbits (3), HCP (2) |
|
Are there any particular reason why Bitcoin Core uses Berkeley DB & LevelDB while both of them are key-level database? I imagine it's easier to maintain Bitcoin Core source code if only one of the is used.
Bitcoin Core used to only use Berkeley DB. It was replaced by LevelDB for all of the non-wallet things because LevelDB has better performance characteristics. Notably, BDB has this issue with database locks which caused an accidental hard fork in March 2013. At that time, BDB was simply unable to handle blocks over a certain size (the specific size is not quite defined and can vary from machine to machine) while LevelDB was able to just fine. BDB is still used in the wallet for backwards compatibility reasons. But this is changing with 0.21. The only thing that users need is the "xprv" value if it is HD wallet or all WIF-encoded private keys if it is not. By having private keys it is possible to recover everything else. Am I missing something? Both things can be easily written on a piece of paper and expressed in text as needed, so they are as "standard" as they could be.
Bitcoin Core doesn't store xprvs. There's also no way to import an xprv. It also doesn't store private keys encoded in WIF, and the way they are stored is not an importable format. The only way to get any of those things is to be able to understand the wallet.dat format, parse how things are actually stored, and then output them to the user in some other way. Actually, I think requiring a wallet file that can be moved around in all cases might actually hinder proper research on database backend efficiency.
It is an unfortunate fact of reality. People move wallet files to other machines. People need to make backups of their wallet. The easiest and least corruptable way to do that is to have a wallet that is contained in a single file. Ideally, database engines would be pluggable in the code, and there would be a database migration tool to switch between engines. Then it suffices to have one engine around that has a fixed wallet file format.
Frankly, a different database engine would not make a significant difference to the performance of the wallet. The database engine is barely used as a database engine, moreso as a data storage method rather than any actual database stuff. Everything that is stored on disk is loaded into memory and the wallet works entirely off of the in-memory data, not the data in the wallet file. The place where a using different database engines makes sense is for everything else - the UTXO set and all of the indices. However the work required to do that is immense and none of the active developers care enough about that since clearly LevelDB is good enough and there are tons of other things to be worked on.
|
|
|
|
RHavar
Legendary
Offline
Activity: 1463
Merit: 1886
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 29, 2020, 04:26:46 AM |
|
I'm going to throw postgres out there. I've used it for a lot of bitcoiny things, including a wallet that I never finished -- and I've always been extremely happy with postgres. Having a real database makes for an amazing developer experience, especially debugging. You don't need to create an API for everything, you just query the data. And a new access pattern often just means adding a new index. It has great support and documentation for concurrency and bona fide transactions with meaningful isolation levels. It easily scales to the size of bitcoin (e.g. storing all transactions, while indexing the utxo, etc) and in general I think is a lot more performant than KV databases. Maybe not for some micro benchmark, but postgres makes it feasible to actually make sure every action you're doing can run in sub-linear time. While the same is technically true for lower-level datastores, postgres actually makes it actually realistic. I think the only real downside to postgres is deploying/packaging it for end users. That's nightmare material; and most likely a show-stopper for most use cases. Other than that, it's pretty cool though ![Tongue](https://bitcointalk.org/Smileys/default/tongue.gif)
|
Check out gamblingsitefinder.com for a decent list/rankings of crypto casinos. Note: I have no affiliation or interest in it, and don't even agree with all the rankings ... but it's the only uncorrupted review site I'm aware of.
|
|
|
zeuner
Member
![*](https://bitcointalk.org/Themes/custom1/images/star.gif)
Offline
Activity: 189
Merit: 16
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 30, 2020, 12:14:28 AM |
|
I think the only real downside to postgres is deploying/packaging it for end users. That's nightmare material; and most likely a show-stopper for most use cases. Other than that, it's pretty cool though ![Tongue](https://bitcointalk.org/Smileys/default/tongue.gif) By avoiding PostGres-specific features and using a DBAL, SQLite can be used to have configuration suited for easy packaging, while keeping the possibility to use PostGres for performance-hungry tasks.
|
|
|
|
PrimeNumber7
Copper Member
Legendary
Offline
Activity: 1652
Merit: 1899
Amazon Prime Member #7
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
December 30, 2020, 02:47:53 AM |
|
First off you wouldn't want to use a SQL database for any bitcoin data because you'd have to shoehorn the key/value data into relational format with tables, primary keys and all that special stuff. This leaves only NoSQL databases as a viable option, and wouldn't be end-user disruptive because nobody moves around the chainstate database.
Why don't you like using relational tables and pkeys? Storing your data this way ensures that you are only accessing the exact data you need when making a query. The schema of a database storing the blockchain, and data required to use a wallet are not going to change. IMO the biggest advantage of NoSQL databases over SQL databases is the flexibility in storing documents/records, but I really don't think that is necessary for any bitcoin-related use case.
|
|
|
|
gmaxwell
Moderator
Legendary
Offline
Activity: 4242
Merit: 8647
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 02, 2021, 05:21:56 AM Last edit: January 02, 2021, 05:33:26 AM by gmaxwell |
|
[I'm ignoring the wallet sidebar, because it's offtopic for the question AFAIK, and the answer depends a LOT more about what you're doing.] I wonder if this happens in other fields. Are there forums of people that don't have a lot of direct experience with practical plumbing plus people that have only done plumbing as part of nuclear reactors, theorizing on if solid gold fittings are superior to hand carved wooden pipe fittings? ![Smiley](https://bitcointalk.org/Smileys/default/smiley.gif) From the perspective of a Bitcoin node usage of the UTXO: The UTXO set is logically a set. There is no important natural order of the elements. For a Bitcoin node the only operations is to insert an element (with replacement of duplicates), to lookup an item by key (create an output), or to delete an item by key (spend an output). For that query load, the logical data-structure is some kind of hash-table since those can give asymptotic O(1) performance. For Bitcoin's use this datastructure needs to be persisted on disk (both because it may be bigger than ram, also because people don't want to resync on restart) and be crash recoverable. Previously-- until 2017-- (and still in some less sophisticated software), it also needed to support transactional updates, but we eliminated that requirement in Bitcoin core by using the blockchain itself as a write ahead log: Bitcoind keeps a record that tracks of as what height were all utxo changes were last completely flushed to the database, and at startup if that block isn't the node's most recent block it just goes and reapplies all the changes since that block, which works because insert/delete is idempotent-- there is no harm in deleting an entry already deleted or inserting an entry that is already there. Now, because all a node needs is a persistent hash table in some sense you could use practically any kind of database to support it, or even just a bare file system. But validating the chain requires approximately three billion operations (currently) with a current database of sixty-eight million (tiny) entries. As a result, most things people discuss aren't within two orders of magnitude of acceptable performance, like -- if it communicates over a network socket with a round-trip (thus context-switch) per operation that alone probably blows your entire performance budget. LevelDB is among the fastest of the very simple in-process key-value stores, many things which claim to be faster are actually not-- at least not against the bitcoin like workload. (there are many leveldb clones which meet this description, but the only way to know for sure is to try something out) Because the usage is so simple, the particular choice is not very important so long as it is extremely fast. Among all extremely fast choices the performance doesn't tend to differ that much because (1) their performance is driven by stuff like memory access speed, which is the same among them, and (2) the majority of the load is actually removed from the database in any case: There is an in memory buffer (called the dbcache) that prevents most of the utxo entries from ever touching leveldb: It turns out that most utxo created are spent quickly, so that a when they are they can be settled in a simple non-persistant hash table without being written to the database. There are plenty of ways that what Bitcoind does could be improved-- for example, changing dbcache to be an open hash-table to avoid memory allocations and improve locality would probably be a big win, as would creating an incremental flushing facility to keep flushing constantly running in the background. But no amount of "just swap out leveldb with FooDb" is likely to make an improvement. For most values of Foo I've seen suggested it's so much slower that it kills performance. You're welcome to try-- the software abstracts it so that it's pretty easy to drop something else in, in the past people have and give up after their sqllite or whatever hasn't synced the first couple years of blocks in a *week* of runtime. For any highly tuned system, speculating about the performance without getting in and understanding what's already there is unlikely to yield big improvements. Personally I think modular buzzword thinking is a cancer of the IT industry: "My car won't start, any idea whats wrong?" "Have you tried MongoDB? I heard it has WebScale!". Now if you want to talk about some block explorer or chainanalysis like usage then the needs are entirely different. But the way to go about answering questions there isn't to pick some DBMS off a shelf, it's to first think about what data it will need to handle and what queries will need to be performed against it, with what kind of performance... and then that will inform the kinds of systems which could be considered.
|
|
|
|
NotATether
Legendary
Offline
Activity: 1708
Merit: 7185
In memory of o_e_l_e_o
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 02, 2021, 10:09:50 AM |
|
First off you wouldn't want to use a SQL database for any bitcoin data because you'd have to shoehorn the key/value data into relational format with tables, primary keys and all that special stuff. This leaves only NoSQL databases as a viable option, and wouldn't be end-user disruptive because nobody moves around the chainstate database.
Why don't you like using relational tables and pkeys? Storing your data this way ensures that you are only accessing the exact data you need when making a query. The schema of a database storing the blockchain, and data required to use a wallet are not going to change. IMO the biggest advantage of NoSQL databases over SQL databases is the flexibility in storing documents/records, but I really don't think that is necessary for any bitcoin-related use case. Here's the thing though. The current implementation of CDBBatch and CDBWrapper (which you have to adapt after you replace the database) only understand key and value data, there is no concept of primary keys or tables or any other SQL-specific stuff in it. And since Key and Value types are exposed in an API, it is impossible to drop-in an SQL database in it without changing the rest of the codebase that uses the database wrapper.
|
|
|
|
gmaxwell
Moderator
Legendary
Offline
Activity: 4242
Merit: 8647
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 02, 2021, 11:12:51 AM |
|
Here's the thing though. The current implementation of CDBBatch and CDBWrapper (which you have to adapt after you replace the database) only understand key and value data, there is no concept of primary keys or tables or any other SQL-specific stuff in it. And since Key and Value types are exposed in an API, it is impossible to drop-in an SQL database in it without changing the rest of the codebase that uses the database wrapper. The only thing the software uses the "database" for is as a disk backed hashtable. Thus the only interface the software needs or even remotely makes sense to use is a key,value interface. There wouldn't be any "primary keys or tables or any other SQL-specific stuff in it" because the software has no use for anything like that, and the database wrapper can use whatever is suitable for the backend. You can backend that interface with anything you like, and other people have substituted other things into it (including sql databases). If you google around you can find some of these failed experiments. (Failed because the performance was extremely poor)
|
|
|
|
RHavar
Legendary
Offline
Activity: 1463
Merit: 1886
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 02, 2021, 07:40:47 PM Last edit: January 03, 2021, 01:21:47 AM by RHavar |
|
[I'm ignoring the wallet sidebar, because it's offtopic for the question AFAIK, and the answer depends a LOT more about what you're doing.]
I think the question is so underspecified its hard to tell what they're asking exactly. I think you're answering it from a performance-point of view. While I've only done stuff on top of bitcoin, where I run into problems that bitcoin-core isn't designed-for or flexible enough for my use cases. Probably the most classic example (but far from only) would be not able to lookup transactions that are from/to a particular address. So when ever I've done my stuff, I've tried to optimize for flexibility/developer experience -- but never had to even consider performance. For my usage, even if it took an additional 10 seconds (which it doesn't) to process a block, it would be no big deal at all. From the perspective of a Bitcoin node usage of the UTXO:
The UTXO set is logically a set. There is no important natural order of the elements. For a Bitcoin node the only operations is to insert an element (with replacement of duplicates), to lookup an item by key (create an output), or to delete an item by key (spend an output).
I'm not disagreeing with you, but it almost feels like you've reasoned backwards about it. If you model the "blockchain" in a very natural way (i.e. blocks have transactions and parent blocks). (transactions have inputs and outputs) with the right indexes and what not, you can do all the operations you need in an efficient (i.e. sublinear) time. i.e. most operations you talk about is logically "does this output exist in this chain" & then "and has it not been spent in this chain". While the UTXO feels very "unnatural" in comparison, as you're basically manually maintaining a index for a very specific question, and that without some sort of "structural sharing set" -- your utxo is not going to be efficiently support re-orgs. Of course it's the right choice for a high performance full-node. But if you were building something that needed more flexibility (e.g. wallet node, block explorer) or trying to build something as correct as possible with the least amount of code it might not even make sense keeping your own materalized utxo set. --- A bit of a tangent, but bitcoin-core's real problem is it doesn't by come with a jump started utxo set. It's kind of insane that everyone is supposed to build it themselves when if each release came with a new utxo set hash hardcoded in, it'd be easy for dozens of people to verify it and then people would actually be able to use/recommend bitcoin-core instead of electrum or w/e
|
Check out gamblingsitefinder.com for a decent list/rankings of crypto casinos. Note: I have no affiliation or interest in it, and don't even agree with all the rankings ... but it's the only uncorrupted review site I'm aware of.
|
|
|
gmaxwell
Moderator
Legendary
Offline
Activity: 4242
Merit: 8647
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 02, 2021, 11:37:29 PM |
|
While the UTXO feels very "unnatural" in comparison, as you're basically manually maintaining a index for a very specific question, and that without some sort of "structural sharing set" -- your utxo is not going to be efficiently support re-orgs.
Reorgs more than a couple blocks are unobservable rare, so I don't think it matters if they're linear time in depth. For short reorgs of a couple blocks, one can just keep a separate dbcache per block in memory, and only merge/flush them once buried. Doing this is extremely fast. Somewhere I had a patch to do that for reorgs of up to depth 2, but esp post compact blocks reorgs are so rare that no one seemed very interested. It's also the case that reloading the evicted mempool txn ends up taking more time in reorg than anything else (though at least that gets deferred until after the reorg is completed). Going further to generally support reorgs as a native operation has a lot of overhead. A bit of a tangent, but bitcoin-core's real problem is it doesn't by come with a jump started utxo set. It's kind of insane that everyone is supposed to build it themselves when if each release came with a new utxo set hash hardcoded in, it'd be easy for dozens of people to verify it and then people would actually be able to use/recommend bitcoin-core instead of electrum or w/e
After years of trying to get cause progress on support for an assume-valid style utxo set, and finding it always sidebarred by complex commitment structure discussions (which aren't necessary for it, and I think make the wrong bandwidth/storage tradeoff), I've given up trying. But I agree with you.
|
|
|
|
Coding Enthusiast (OP)
Legendary
Offline
Activity: 1040
Merit: 2785
Bitcoin and C♯ Enthusiast
|
![](https://bitcointalk.org/Themes/custom1/images/post/xx.gif) |
January 03, 2021, 06:51:44 AM |
|
I think the question is so underspecified its hard to tell what they're asking exactly.
I tried keeping the question short and precise without adding any complications. I'm also not solving a problem so there is nothing specific I could include, I simply have been learning everything there is about Bitcoin for the past couple of years and at this point only a handful of subjects are remaining (P2P network protocol, blockchain as in storage and handling reorgs, second layer protocol(s) and finally optimization). I got some good insights here that gave me some directions. I'll probably start with a primitive way of handling UTXOs and indexes for now but abstract it away like everything else for future flexibility.
|
|
|
|
|