What DB do you use at your end?

[PremiumCodeX]

I was researching the ways how people make queries in the blockchain and I got the result that many create custom DBs on their ends to support their ways. If you use an additional DB at your end (other than core's leveldb) to store blockchain data, what DB do you use?

[1714776511]

1714776511

[1714776511]

1714776511

[1714776511]

1714776511

[TransaDox]

SQLite

[CIYAM]

SQLite

Whilst it is a great small SQL DB it isn't suitable for multiple users due to its use of global locking (unless they have completely reworked that in recent years).

I would favour using something like MySQL if wanting to have something that will scale hugely and handle multiple concurrent users.

[MadGamer]

I was researching the ways how people make queries in the blockchain and I got the result that many create custom DBs on their ends to support their ways. If you use an additional DB at your end (other than core's leveldb) to store blockchain data, what DB do you use?

Both MSSQL Server (If you are using Windows to host it) and MySQL should be great choices . SQLite or Realm.io should be considered as an alternative for MSSQL or MySQL If you are building a mobile app.

[PremiumCodeX]

So, most people use MySQL here, but also recommend SQLite and Realm.io. I am missing Oracle, though. In my story, I was using Oracle for several months up to now and it has nice features like highly customizable locking configuration. Although I used and agree with the excellence of the listed other DBs, why am I so lonely with my Oracle as a favorit?

[MadGamer]

So, most people use MySQL here, but also recommend SQLite and Realm.io. I am missing Oracle, though. In my story, I was using Oracle for several months up to now and it has nice features like highly customizable locking configuration. Although I used and agree with the excellence of the listed other DBs, why am I so lonely with my Oracle as a favorit?

Oracle is considered as one of the most powerful RDBMS, don't under estimate it. It's mostly the companies that use Oracle and here why as for the differences you could check this article from Udemy or Oracle documentation

[CIYAM]

I have worked with many RDBMS engines (including DB/2 and Oracle) and they are all very powerful things.

There is really no reason to prefer one over the other in regards to what you are wanting to do.

The only thing that no current RDBMS handles well is actual "rollback" in terms of a "re-org" (normally a DB "rollback" is actually just restoring a backup and then "rolling forward" the newer txs from the log).

AFAIA no modern DB can do this (although I do have a design for exactly this).

[PremiumCodeX]

I have worked with many RDBMS engines (including DB/2 and Oracle) and they are all very powerful things.

There is really no reason to prefer one over the other in regards to what you are wanting to do.

The only thing that no current RDBMS handles well is actual "rollback" in terms of a "re-org" (normally a DB "rollback" is actually just restoring a backup and then "rolling forward" the newer txs from the log).

AFAIA no modern DB can do this (although I do have a design for exactly this).

With that argument you have raised my curiosity.

Would you mind, in the essence at least, elaborating your design for me?

How should a rollback happen in your view?

[CIYAM]

How should a rollback happen in your view?

A real "rollback" would require that the transaction log would have kept the full details of any deleted record (no major DB in the world does that AFAIA).

So if you want to "reverse history" it would be easy enough to recover "deleted records" using the transaction log.

Hope that makes sense.

[PremiumCodeX]

How should a rollback happen in your view?

A real "rollback" would require that the transaction log would have kept the full details of any deleted record (no major DB in the world does that AFAIA).

So if you want to "reverse history" it would be easy enough to recover "deleted records" using the transaction log.

Hope that makes sense.

Correct me if I misunderstood something, but would not that require a MUCH bigger size of HDD? In addition, the log writing and reading speed would significantly increase that would result in slower DBs, would not it? At large DBs, speed is essential because some queries could take up to 20 or more minutes even without the mentioned idea.

[doof]

How should a rollback happen in your view?

A real "rollback" would require that the transaction log would have kept the full details of any deleted record (no major DB in the world does that AFAIA).

So if you want to "reverse history" it would be easy enough to recover "deleted records" using the transaction log.

Hope that makes sense.

Correct me if I misunderstood something, but would not that require a MUCH bigger size of HDD? In addition, the log writing and reading speed would significantly increase that would result in slower DBs, would not it? At large DBs, speed is essential because some queries could take up to 20 or more minutes even without the mentioned idea.

Yes, I used MS SQL for a project www.blockchainsql.io.  450GB database.  If you optimise right, queries can be lightning fast.

[doof]

Eg:  total chain size

select sum(block.size) as totalsize from block

[CIYAM]

Correct me if I misunderstood something, but would not that require a MUCH bigger size of HDD?

As deletion (and in Bitcoin rollbacks) are infrequent operations it wouldn't practically be as significant an increase as you are imagining (but it would use more space for the translation log).

In addition, the log writing and reading speed would significantly increase that would result in slower DBs, would not it? At large DBs, speed is essential because some queries could take up to 20 or more minutes even without the mentioned idea.

Normal RDBMS "queries" do not involve the log at all so it would not have any speed effect upon read operations (it would only slow down "delete" operations).

[PremiumCodeX]

Yes, I used MS SQL for a project www.blockchainsql.io.  450GB database.  If you optimise right, queries can be lightning fast.

Cool. Is that your project or did you build a project that used the mentioned project as something like an API?

At a 450-GB DB I think queries could be optimized up to less than 1 minute speed with your home gaming PC.

Correct me if I misunderstood something, but would not that require a MUCH bigger size of HDD?

As deletion (and in Bitcoin rollbacks) are infrequent operations it wouldn't practically be as significant an increase as you are imagining (but it would use more space for the translation log).

In addition, the log writing and reading speed would significantly increase that would result in slower DBs, would not it? At large DBs, speed is essential because some queries could take up to 20 or more minutes even without the mentioned idea.

Normal RDBMS "queries" do not involve the log at all so it would not have any speed effect upon read operations (it would only slow down "delete" operations).

It depends on the use-case then because in some DBs there are frequent delete operations. But I agree that most DBs are not like that so the possibility is worth to take into consideration. The question is how much it would increase the necessary space and the issue of SRP because even though rollbacks are infrequent operations, when they need them they usually need them ASAP.

[CIYAM]

It depends on the use-case then because in some DBs there are frequent delete operations. But I agree that most DBs are not like that so the possibility is worth to take into consideration.

For blockchains (whose very purpose is to be a non-mutating structure) the "deletes" are only re-orgs (which although not that uncommon are generally not very large delete operations as most re-orgs are only one or two blocks deep) and depending upon how things are structured (such as if the structure of the chain itself is separated from the content of the blocks) would not need to involve much data.

The question is how much it would increase the necessary space and the issue of SRP because even though rollbacks are infrequent operations, when they need them they usually need them ASAP.

A "true rollback" (which as I said isn't being used by an major RBDMS that I am aware of) would actually be much faster than any other method (i.e. you either are going to have to issue update queries to act as though things are being unwound or restore an earlier backup which you'd then have to perform a partial log restore from).

Understand also that even if deletes involve appending the data to the log you would still be able to truncate the log for "checkpoints" (at which point any of that extra wasted space is recovered).

[BuySomeBitcoins]

The choice of the database depends on your needs, mostly I work on market places and cybersecurity [fraud] so I use MongoDB - Oracle

[TransaDox]

A "true rollback" (which as I said isn't being used by an major RBDMS that I am aware of) would actually be much faster than any other method (i.e. you either are going to have to issue update queries to act as though things are being unwound or restore an earlier backup which you'd then have to perform a partial log restore from).

Understand also that even if deletes involve appending the data to the log you would still be able to truncate the log for "checkpoints" (at which point any of that extra wasted space is recovered).

SQLite enables one to create "Savepoints" which are like GOTO statements for rollback.

[CIYAM]

SQLite enables one to create "Savepoints" which are like GOTO statements for rollback.

An interesting feature although from reading the documentation I think that is just for use with nested transactions.

The concept of a blockchain re-org though is not the same thing as you need to "rollback" transactions that have already been committed (something rather alien to how RDBMS transactions operate).

[piotr_n]

If you use an additional DB at your end (other than core's leveldb) to store blockchain data, what DB do you use?

I use my own solution, based on a hashmap (acting as the index) holding pointers to the records.

It makes accessing the db very fast, but needs much more RAM comparing to leveldb or berkeley.

Apart from the hashmap index and the records, there is of course also a code for the disk operations - to keep the files in sync with the memory.

It's simple and rather archaic, but has been proven to work great for UTXO-db purposes - I would not swap it for any other db engine I know.
Although I'm also quite interested in other solutions, which I don't know - there must be some more optimal ways of doing it.

https://github.com/piotrnar/gocoin/tree/master/lib/qdb

[borris123]

Nexus has created their own DB 10x faster than googles.

https://twitter.com/mirraxFTC/status/798217450739220481

https://bitcointalk.org/index.php?topic=657601.0