I'm not much into the details but I'm wondering why is it not always writing to "<database>-wal" once it is full, it is renamed to "<database>-wal-0" and it starts writing again into "<database>-wal", once it is full again it is renamed to "<database>-wal-1" ?
This is to handle the case where transactions keep getting written to the wal file while it is being "checkpointed", preventing it from ever being deleted. You only need to alternate between two files, one checkpointed, one written to, for this.
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 and number of files less predictable would make that mode not worth it for many use cases
You can't use a filesystem snapshotting mechanism... Even when not using wal mode, you can't just cp the .db file somewhere (in some circumstances, yes).
Instead, sqlite provides an online backup api specifically for creating backups. This also takes wal mode into account.
Even the mechanism predating WALv1 (rollback journal) uses two files. I don't think there is any way of using SQLite crash-proof with just a single file.
Besides, even if the database is single-file it's still necessary to use filesystem snapshotting for live backup, or it's likely to get an inconsistent copy.
With smallish databases, just pipe the .dump command to gzip. No need to snapshot an entire filesystem just to back up a few hundred megabytes (what I would consider "smallish"). Backup and restore speeds aren't a significant concern at those sizes, anyway, and you get human-readable backups that can be inspected with standard Unix tools.
We would back up double-digit GB MySQL databases by piping mysqldump into gzip as well. Like you I’m sure there’s a size limit where that doesn’t work. I never found out if it was CPU or memory constrained though.
Where I’m at, the DBAs shifted from pipes to temporary files. They said at the double-digit TB size, it would occasionally fail otherwise. This was with Postgres / barman / pigz, FWIW.
Never saw it myself, so I have no idea what the cause was.
I've used mysqldump|gzip when migrating a ~1TB database to an incompatible version of MySQL/MariaDB. It's slow but very reliable. I think the single-threaded pipe is the bottleneck. The process never took up more than one full CPU core.
Presumably because you don't want to keep a full directory of WALs up to infinity. This uses WAL_size*2 on the disk and keeps it from growing beyond that.
