Databases with V

Did you know that if SQLite performs better, then the lifetime of your mobile increases? But how? Let's uncover that from the paper SQL Statement Logging for Making SQLite Truly Lite. Link - https://www.vldb.org/pvldb/vol11/p513-park.pdf This paper appeared in VLDB, 2017. First, let's understand the problem. The authors claim that by adopting a simple design, SQLite took a less complicated transaction design, which causes excessive write amplification. SQLite has two kinds of transaction modes: rollback and WAL journal. You may read here for detailed info: - How SQLite Scales Read Concurrency - https://fly.io/blog/sqlite-internals-wal/ - Atomic Commit In SQLite - https://www.sqlite.org/atomiccommit.html Rollback is the legacy mode; almost everyone uses WAL mode because it lets you have a writer and multiple readers simultaneously. In WAL mode, SQLite appends the changed pages to a WAL journal. Consider this query: update students set dept_id = 21 where dept_name = 'chemistry'; SQLite will fetch all the pages which contain rows matching this query, update the rows and write these pages to the log. The default page size is 4KB. A page may contain more than one row. To update a single column in a row, you are writing 4KB to disk. (you must also read 4KB first, but that's a problem for another day.) Imagine 20 rows match the above query, spread across 20 different pages. That simple update of a few bytes would result in 80KB writes. This is write amplification. According to a referenced paper, over two-thirds of smartphone writes are from SQLite! Due to the write amplification issue, the unnecessary writes can shorten the lifespan of flash storage!

Jokes aside, the CMU DB course is the best on database internals, and I highly recommend it. The 2023 edition is already half complete, with lecture videos uploaded on YouTube. The assignments include writing core db parts like Buffer Pool or Index in C++. They also have a primer on C++ if you are new to the language. Here is the course page - https://15445.courses.cs.cmu.edu/fall2023/schedule.html YouTube playlist - https://www.youtube.com/playlist?list=PLSE8ODhjZXjbj8BMuIrRcacnQh20hmY9g (The second video has distorted audio; you can check the alternative video from the previous semester. The content is mostly the same.)

As a first message, here is a reminder from Andy to take databases seriously or else you will end up in jail :p