41–50 of 99 posts
> In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", where " " is of course the name of the database file. When data is written to the database, the writer begins by appending the new data to the first wal file. Once the first wal file has grown large enough, writers switch to appending data to the second wal file. At this point the first wal file can be checkpointed…
> In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", where " " is of course the name of the database file. When data is written to the database, the writer begins by appending the new data to the first wal file. Once the first wal file has grown large enough, writers switch to appending data to the second wal file. At this point the first wal file can be checkpointed…
> Checkpointers do not block writers, and writers do not block checkpointers. This means that, even following the checkpoint, the wal file cannot be overwritten or deleted, and so all subsequent transactions must also be appended to the wal file. The work of the checkpointer is not wasted - SQLite remembers which parts of the wal file have already been copied into the db file so that the next checkpoint does not have…
Earlier quoted context omitted.
I'm just speculating here, but in a normal database you would have different processes writing the wal files to the database or archives. You don't have that with sqlite, so I don't see an obvious advantage for this, except if they now spawn a process or thread to do this concurrently. Edit: so I read the doc (shame on me) and it has nothing to do with speed. Its purpose is to prevent a wal file from growing too larg…
I believe SQLite supports accessing a single database file from multiple processes or threads at once
tangential point: one thing that always bothered me about WAL is that it is supposed to exist to help maintain data integrity, recover from crash etc but that file itself is written (committed to disk reliably) in batches and not after every change to the database, apparently to gain performance. Doesn't that defeat the purpose? How haven't things broken down despite this? Not specific to sqlite but databases in gene…
Since SQLite is single writer I'm not sure if it does this. But this (batch yet block) is how I understood Postgres works.
Of course you can turn off the blocking too by setting postgres fsync configuration to an interval rather than synchronous.
Earlier quoted context omitted.
If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
If you're using it as a standalone file format you presumably shouldn't leave .sqlite files with associated wal files lying around in places where users are going to get confused by them, either by sticking to the rollback journal mode or by using some other method
> In wal2 mode, the system uses two wal files instead of one. The files are named " -wal" and " -wal2", where " " is of course the name of the database file. When data is written to the database, the writer begins by appending the new data to the first wal file. Once the first wal file has grown large enough, writers switch to appending data to the second wal file. At this point the first wal file can be checkpointed…
> Checkpointers do not block writers, and writers do not block checkpointers. This means that, even following the checkpoint, the wal file cannot be overwritten or deleted, and so all subsequent transactions must also be appended to the wal file. The work of the checkpointer is not wasted - SQLite remembers which parts of the wal file have already been copied into the db file so that the next checkpoint does not have…
Because with a single wal file you can't checkpoint it during a read since said file may change out from under you.
With two wal files, the one you are actively appending to can be treated like in wal1 mode but the one that isn't being appended to is immutable for the time being just like the main database.
This means you can treat the actual db file and the immutable wal file together as one immutable database file with some underlying abstraction. That abstraction then allows you to perform the checkpoint operation since the abstraction can keep all that immutable data accessible in some form or another while reworking the data structure of the db file.
Then once the checkpoint is complete, the abstraction can clear the now redundant immutable wal file, become transparent, and just present the underling single DB file.
And now once the wal file you are actively appending to reaches a sufficient size, you "lock" that one, rendering it immutable, and switch over to appending to the cleared wal file you were previously checkpointing. With this you can now checkpoint again without blocking reads or writes.
Earlier quoted context omitted.
I'm not much into the details but I'm wondering why is it not always writing to " -wal" once it is full, it is renamed to " -wal-0" and it starts writing again into " -wal", once it is full again it is renamed to " -wal-1" ?
when doing backups, managing file permissions, etc, it's really convenient to only have a fixed number of known files to worry about. Switching to WAL already makes handling Sqlite databases much less convenient, since you now have three files instead of one, and need a filesystem snapshotting mechanism to reliably back them up (so you don't have one state in the database and another in the wal). Making the filenames…
Earlier quoted context omitted.
If people just randomly delete files they don't fully understand on a production system maybe they should be bitten.
in the case of sqlite though, the technology is often used as a standalone file format. So it is very tempting to consider the ".sqlite" file to be the one containing all the data, and all the rest to be temporary files that don't matter much. IMHO this (having a variable number of files containing the data, depending on your configuration) is the only real design quirks of this technology.
I know reading the manual isn't very common and people are lazy, but getting burned can be a useful and necessary lesson.