There’s a bit of a debate going on right now on Chia Network’s github over a SQLite setting on how transactions are handled in the Chia database. In the recent Chia Blockchain 1.2.8 update a mempool bug wasn’t the only major change, the following setting was changed to allow for a more performant database while syncronizing.
Changes in full node to execute sqlite pragmas only once, at the level where the database is opened, and changed pragma synchronous=FULL to OFF to improve disk I/O performance. Also removed redundant database pragmas in wallet.Release 1.2.8 · Chia-Network/chia-blockchain · GitHub
I’m not a DBA, but I have played one in meetings so I’m going to take a stab at having an opinion on this, along with a bit of a personal twist here. My feelings on this are that sacrificing reliability for performance should only be done if the performance is causing an issue. I am not sure as to the reason this was changed, I did not have blockchain database performance issues with my farming setup, and I am not sure how common that was. But I don’t think it matters. My opinion is that today this change creates an interesting debate, but tomorrow when the database is huge reliability will be significantly more critical.
Lets take a look ahead for a moment. The database is growing fast. It will continue to grow fast, and as the system gets busier it will get bigger even faster than it is today. In 6 months the DB will be over 50GB. By 2025 I expect it will be around 400 or 500GB. When the database is this large, and downloading it and verifying it begins to creep up towards a week’s effort it will become extra crucial to keep that database reliable. This site will probably have a guide on how to backup your DB properly. We cannot have it going corrupt or inconsistent after a funny reboot.
I already had to restore a database because of this change. While testing FlexFarmer 2.0 I suffered a system crash, possibly related to a bug there, and my farm restarted. I was not running the Chia Blockchain software at that time, hence FlexFarmer, but because HDDCoin had updated so quickly my HDDCoin installation was using the new Chia codebase and my database corrupted when my farmer crashed. Took a day to rebuild the DB.
I don’t know how much performance they got back by turning off pragma synchronization, and I don’t know how many issues were being caused by slow database performance, but making changes that materially reduce system reliability will almost never be worth the performance gains unless you are also seeing unreliability or instability caused by those performance issues. This feels like a poorly considered change and I hope they revert it back. There are better ways to gain DB performance.