In this video and the next video, we're gonna talk about the single most important setting for making sure that your SQLite database is performant and that is the journal mode. So I'm gonna tell you a little bit about what journal mode is and then we'll look at it. And then in a future video, we're gonna compare the two journal modes and you'll see the very stark difference. So looking at this database here, we don't have much in here. I think we just have a key value store, which is basically just a table we're gonna use for testing.

But the interesting thing is if we look at pragma journal mode, we see that it is currently in journal mode delete. Now, delete is a version of the rollback journal which I'll describe here in a second but this means we are not in write ahead log mode. So those are the two 2 different versions. You've got W.A.L which is right ahead log, W.A.L. You've got W.A.L mode and rollback mode.

And rollback mode has a few settings that you can tweak and you can set you can set it to delete, for example. So what is the actual journal? Well, the journal is the way that SQLite ensures that you can have atomic commits and rollbacks. We don't want stuff being half written and we don't wanna read half written stuff. Right?

So in rollback mode what happens is you'll remember I told you that a SQLite database is a single file but within that file are many equally sized blocks of data called pages. So inside the one file are many pages of data. And that is what the database sees when it looks at the file is it sees a bunch of pages. Some contain data, some contain btrees, some contain just regular locking information, lots of different stuff in these pages. Now, when somebody goes to right to the database in rollback mode, what happens is whichever page is about to be written to, first that page is copied out and set aside.

So a second file is created. It's usually a dash journal. So you'll see it another file on your file system named dash journal. That file is set aside or that page is copied out and set aside and then the writing actually starts happening within the database file itself. So the original page is copied out, new data is written into that page of the database overriding the original data.

Now when that transaction is committed, when everything's written, the transaction is committed, at that point the journal becomes irrelevant, right? Because now the database contains the source of truth. We don't need the original data that was copied out. And so this is where rollback mode can do a few different things. It can delete the journal altogether.

It can truncate the file. It can fill the file up with, zeros. It can just 0 fill the whole file. So there are a couple different modes, each having their advantages and disadvantages. Mostly irrelevant because we want to be using the other mode which is right ahead log.

Now, rollback is the default mode. So we are gonna have to change our databases, to use right ahead logging. If you are using Terso, and their their fork of SQLite which is LibSQL, then it supports right ahead logging only. You'll find that most modern offerings probably only support or highly encourage right ahead log, but the default is rollback mode. So as a recap, in rollback mode, a page is copied out with the original data.

The the database is modified itself and then the journal is thrown away. If a rollback needs to happen, that page that was set aside into the journal is copied back into the database and everything is back to how it was. This is why when in rollback mode somebody is writing to the database, all the readers must stop because the actual file is being modified. Let's talk about right ahead logging in the next video.