What DB do you use at your end?

[PremiumCodeX]

Thank you for suggestion! I too read very positive opinions on NEXUS, seems worthy to try out next.

@CIYAM, I find your description a bit difficult to imagine. Maybe because I have studied for my finals the whole day and my brain is tired   Have you got a model or - even better a - prototype of such a DB to see?

[Rick Storm]

But what you don't have in the log is the content of the record XXX which therefore cannot be restored unless you actually restore an older backup (this is the meaning of D in ACID transaction which is what all serious RDBMS do - i.e. once deleted always deleted).
...
and it should be clear that they could not do this simply because the information has not been preserved in the transaction log

This doesn't sound right. Afaik all major databases store the deleted record in the transaction log, e.g. SQL Server:

https://sqlfascination.com/2010/02/03/how-do-you-decode-a-simple-entry-in-the-transaction-log-part-1/

[CIYAM]

That certainly is a surprise to me (thanks for the link and I deleted my own post to avoid any further confusion). Thinking about it more I guess it makes sense that in order to do a "rollback" it would need to be able to restore deleted data (as the size of a transaction could be huge it has to write all the recovery information and keep that at least until the commit has been completed and all final tx data has been flushed to disk).

But can you force rollbacks to occur after a successful commit (and especially after a checkpoint)?

Certainly I'm not familiar with any standard SQL commands for doing such a thing (and I wouldn't count nested transactions if supported as really being a solution) so I still don't see how you could use a traditional RDMBS to achieve a blockchain "re-org" (which is the main point that I was perhaps poorly trying to make).

Assuming that most RDBMS engines do work the same way regarding logging then perhaps it would at least be potentially possible to add some method of tagging the DB state at a certain "block height" and therefore later be able to "rewind" the DB back to the state it was at that specified tag (although I don't think that there is any such standard method).

[PremiumCodeX]

I am glad because I have not closed this discussion yet lol. Thank you Rick Storm for enlightening me about this matter! I think, you could do this "tagging" with triggers. You could define the block height as a custom condition in a trigger and in the trigger set a savepoint where data is stored too. I too am not sure about how you could "tell the DBMS" to do that so I would be thankful if someone attached some example in any SQL.

[uanode]

We are using PostgreSQL for these tasks whose performance and capabilities are endless.

[btc_enigma]

mysql is slow in terms of read performance

Why not use leveldb as used by core. It has very good read speeds. Also you don't require concurrent writes. Only need to write a block that comes in every 10 minutes. Most of the times are reads. I doubt any other RDBMS can match the performance of leveldb

[tardi]

I would insert the outputs from bitcoin into a mysql database and then query it.

[TransaDox]

That certainly is a surprise to me (thanks for the link and I deleted my own post to avoid any further confusion). Thinking about it more I guess it makes sense that in order to do a "rollback" it would need to be able to restore deleted data (as the size of a transaction could be huge it has to write all the recovery information and keep that at least until the commit has been completed and all final tx data has been flushed to disk).

But can you force rollbacks to occur after a successful commit (and especially after a checkpoint)?

Certainly I'm not familiar with any standard SQL commands for doing such a thing (and I wouldn't count nested transactions if supported as really being a solution) so I still don't see how you could use a traditional RDMBS to achieve a blockchain "re-org" (which is the main point that I was perhaps poorly trying to make).

Assuming that most RDBMS engines do work the same way regarding logging then perhaps it would at least be potentially possible to add some method of tagging the DB state at a certain "block height" and therefore later be able to "rewind" the DB back to the state it was at that specified tag (although I don't think that there is any such standard method).

Rollback is a bit of a red herring for an immutable block chain. Just store everything, good or bad, and adjust the query.

SQLite also has the ability to treat multiple, separate database files as a single database at run-time (max 64). One could create separate DB files for working DB and historical but I don't even see the need for that.

BTW. SQLite can insert 1,000,000 records in under 10 secs and wipes the floor with My/MSSQL and Postgres in terms of performance. What it doesn't have is authentication so the others are a better choice for web servers.

[fikihafana]

I would use mongodb to maximize performance and scalability

[piotr_n]

You guys have no idea what you are talking about.

At this moment UTXO database holds over 45 millions records, from over 16 millions transactions.

What you need is an instant access to any of these records - otherwise verifying of blocks and transactions will be too slow.

No SQL database (or mongodb) can give you such a performance.
These kind of DBs are meant for complex queries, but they would have killed your bitcoin node had you tried to use them for handling the UTXO set.

[PremiumCodeX]

You guys have no idea what you are talking about.

At this moment UTXO database holds over 45 millions records, from over 16 millions transactions.

What you need is an instant access to any of these records - otherwise verifying of blocks and transactions will be too slow.

No SQL database (or mongodb) can give you such a performance.
These kind of DBs are meant for complex queries, but they would have killed your bitcoin node had you tried to use them for handling the UTXO set.

Are you suggesting using a No-SQL DB? I have not used a NoSQL DB yet. Is it any better than SQL DBs when handling BTC transaction data? It could be a good idea since BTC is often looked as an experiment, why not to use a new technology with it.

[piotr_n]

It is not about the language/API of the queries, but about the speed of accessing the records.

In SQL database just parsing the SQL query takes too much time.
But modern NoSQL databases (like MongoDB) are also not good here, because they are designed for complex queries and fancy data structures.
This all comes at a cost of performance and memory usage.

What you need for bitcoin is a simple [binary_key] -> [binary_record] database.
LevelDB is the most common one I know that can be used for that.
But IMHO even LevelDB is far away from optimal for bitcoin's UTXO set.

I'd say that if you want your bitcoin node to perform really well, you ought to make your own DB engine.
Depending how much RAM you can spare, I'd lean towards keeping all the data inside the memory.
Write data to the disk only to make the memory changes persistent and read from the disk only when loading the DB data while booting your node.
This requires more than 2 GB of RAM for the current UTXO-set, but gives the optimal performance.

That's for UTXO database - which is the tricky one here.
As for the blocks, you might just as well use a pure file system - no additional database engine is really necessary here.

[clickerz]

I would use mongodb to maximize performance and scalability

MongoDB is quite good and in fact it is gaining more followers also. It is also now incorporated in MEAN Stack development and it is becoming popular. I like MongoDb for its scalability. So far my also with performance is common database being used especially on web development.

[TransaDox]

mysql is slow in terms of read performance

Why not use leveldb as used by core. It has very good read speeds. Also you don't require concurrent writes. Only need to write a block that comes in every 10 minutes. Most of the times are reads. I doubt any other RDBMS can match the performance of leveldb

Because LevelDB is not ACID compliant and prone (read, renowned for) corrupting the DB. This is why we are being told to back up the chain, make copies etc. Bitcoin should be using an ACID compliant DB and the only one that gets close to the performance of a Key/Value DB for sequential access is SQLite. Plus, one has other niceties like live backup and in memory attached DBs.