[schobi]

The paper shows a through analysis of write amplification and slowdown/wear with large databases (800GB) on a single machine. Databases are MySQL and postgres. As already commended, this can lead to an optimized storage table format for greater performance. Nice!

I would expect that a similar analysis can be done for sqlite, maybe with a different dataset, single write thread..

[lia323]

Thanks! I have not tested SQLite myself, but it would definitely be worthwhile to evaluate as well. SQLite would likely suffer from write amplification in a similar way as MySQL or PostgreSQL, since it is also a page-based DBMS with in-place updates, regardless of the single-writer design.

The degree of the resulting write amplification depends on several factors, including the fill factor, write skewness, and the write rate relative to the SSD characteristics. We discuss this in more detail in Section 10.2, “When should the DBMS care about WAF?” in the extended arXiv version.

There is also this paper on SQLite/mobile storage and zoned devices that may be relevant in this context: https://www.usenix.org/system/files/atc24-hwang.pdf