Interesting Approaches to Write Concurrency and Write Speed of sqlite

[hpvd]

Just read this interesting stuff on a new approach to write concurrency of sqlite, which:

• Except for single process servers, the new concurrent writes backend delivers higher throughput for almost all concurrency level, by up to 4X under heavy loads
• P90 latencies are reduced for ALL process and client counts, by up to 93%.
• For all multi process servers, P99 latencies are reduced across the board, by up to 95%.
• All this with high durability writes as compared to WAL operations that operate with normal synchronous level

https://oldmoe.blog/2024/07/08/the-write-stuff-concurrent-write-transactions-in-sqlite/

=> Maybe one can learn something for this project...

[hpvd]

just another imho interesting write speed inside on https://blog.julik.nl/2025/01/maximum-speed-sqlite-inserts

takeaways:

• there is lots of room for improvement (-> x7) coming from a naive or solely transaction optimize write approach
• on disk (SSD) can be nearly as fast as in memory

Results for inserting 500.000 rows

Disk DBs:

Naive - 474.50496300001396 seconds
With transaction - 2.3882359999988694 seconds
With transaction and prepared statement - 0.7706829999806359 seconds
With pragmas, transaction and prepared statement - 0.7386469999910332 seconds
With multirow inserts, pragmas, transaction and prepared statement - 0.3565039999957662 seconds

Memory DBs:

Naive - 2.3065050000150222 seconds
With transaction - 1.8898840000038035 seconds
With transaction and prepared statement - 0.6456299999845214 seconds
With pragmas, transaction and prepared statement - 0.634888000000501 seconds
With multirow inserts, pragmas, transaction and prepared statement - 0.31626500000129454 seconds

-> would be highly desirable, if one day

• turso can benefit of this optimisations (if use case needs it / all optimizations are suitable for it)
• there is a test case for this, documenting progress and preventing write speed downgrades/regressions for new versions
• great built in feature like CDC (available now, see https://turso.tech/blog/introducing-change-data-capture-in-turso-sqlite-rewrite) could keep up with this speed without being overwhelmed and make sync duration as fast as an eye-blink :-)

[hpvd]

current stunning state of Turso's write speed optimizations:
early technology preview of MVCC implementation in Turso
https://turso.tech/blog/beyond-the-single-writer-limitation-with-tursos-concurrent-writes

open topics in the field of MVCC:
https://github.com/tursodatabase/turso/issues?q=is%3Aissue%20state%3Aopen%20mvcc