> By shifting business logic to stored procedures you avoid this.
Thanks, we considered stored procedures to bring the number of database queries down from 18 queries per payment to 1 query per payment. However, that would have provided only an order of magnitude improvement, and brought with it complexity of testing, compared to the state machine [1] we have in TigerBeetle.
At the same time, the biggest performance bottleneck is not only the number of roundtrips, but the lack of first-class batching in the interface per roundtrip. What we do in TigerBeetle instead, is we send 8192 transfers in a single network request. This brings the network/disk cost equation down from 1 query per payment, to 1/8192 query per payment. It's like group commit, on steroids.
[1] https://github.com/coilhq/tigerbeetle/blob/main/src/state_ma...
> That's also why SQLite is very fast, as it runs in your application's memory as a library. But then your data is tied to the same limitations as the machine the application is on.
SQLite is one of my favorite storage engines. However, SQLite does not solve our storage fault model. For example, misdirected reads/writes, lost reads/writes, bitrot in the middle of the committed log. SQLite was also not explicitly designed to be integrated with a global consensus protocol as per ”Protocol-Aware Recovery for Consensus-Based Storage” from UW-Madison. For example, there are optimizations around storage fault tolerance in the commit log that you can do, or around deterministic storage across replicas for faster distributed recovery, that you can't do with SQLite. Check out the paper [2] from UW-Madison for the details, which apply also to LevelDB and RocksDB. We wanted our engine also to be able to run in our deterministic simulator. For example, no random thread scheduling etc.
[2] https://www.usenix.org/conference/fast18/presentation/alagap...