How to flush Firefox storage SQLite database files to eliminate large SQLITE-WAL files?

Question

I've noticed that the WAL (Write Ahead Log) files (*.sqlite-wal) associated with the SQLite databases (*.sqlite) used by the Firefox web browser often get quite large. They literally can get over 150 times the size of the associated SQLite databases, and remain that size for months (years? forever?).

According to this StackOverflow answer, a possible solution may be to run the following SQLite pragma command on each affected storage database:

PRAGMA schema.wal_checkpoint(TRUNCATE);

In theory, one could use an SQLite tool external to Firefox to perform this action, but I've learned that performing actions within Firefox generally yields the best results. (For example, try to work with omni.jar outside of Firefox... it can be a PITA because Mozilla uses an atypical JAR structure.)

Within Firefox, is there a way to flush all its SQLite storage databases so their contents are written completely within the corresponding .sqlite files?

---

UPDATE:

I would like to mention that one of the primary goals in posting this question is to safely deal with the storage-sync-v2.sqlite-wal file that grows until it reaches 32MB in each Firefox profile. The corresponding database, storage-sync-v2.sqlite, also has a storage-sync-v2.shm file, but tends to remain rather small. To keep things focused on one topic per question, I wrote a related question to specifically address that goal.

Answer 1

This is actually really good question, which not many people ask.

The .sqlite-wal file grows to 32MB limit as this is probably the transaction journal limit which you (or your package creator) have defined during the compile of your firefox's sqlite.

The directive with which you can adjust the transaction journal limit during compile time is called SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT and it is defined in bytes. In your case it is 32MB.

You also can adjust the transaction journal limit later on in the sqlite with:

PRAGMA schema.journal_size_limit = N ; (again N is in bytes, negative number sets no limit)

What you have to understand this is a soft limit, not a hard one. If you have an active process that is writing into the journal; it will continue to write even when the specified limit is reached. It can easily reach 2GB even when you have 20MB limit defined.

This limit is for inactive transaction journal. I think the Firefox developers decided 32MB is the right balance between having a write-ahead-log and speed.

If you want to adjust the PRAGMA size after compilation you need to do it for each profile you have. If you plan on using many profiles/database you probably want to recompile it with the SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT set.

To quote valid sections from the man:

In WAL mode, the write-ahead log file is not truncated following a checkpoint. Instead, SQLite reuses the existing file for subsequent WAL entries since overwriting is faster than appending.

The journal_size_limit pragma may be used to limit the size of rollback-journal and WAL files left in the file-system after transactions or checkpoints. Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit.

The second form of the pragma listed above is used to set a new limit in bytes for the specified database. A negative number implies no limit. To always truncate rollback journals and WAL files to their minimum size, set the journal_size_limit to zero. Both the first and second forms of the pragma listed above return a single result row containing a single integer column - the value of the journal size limit in bytes. The default journal size limit is -1 (no limit). The SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT preprocessor macro can be used to change the default journal size limit at compile-time.

This pragma only operates on the single database specified prior to the pragma name (or on the "main" database if no database is specified.) There is no way to change the journal size limit on all attached databases using a single PRAGMA statement. The size limit must be set separately for each attached database.

An update:

I forgot to mention one important SQLite parameter wal_autocheckpoint=N;(where N is number of 32KiB pages in a 512KiB journal). (Which OP already mentioned when truncating the data)

You can configure it to use smaller autocheckpoint. Note that this affect performance of the SQLite and thus the browser. Too many checkpoints will slow down the browser.

To correctly configure auto-checkpoint follow the link.

What is also important to note that checkpoints initiated in such way are PASSIVE. Which means that SQLite should do as much as possible without blocking.

To quote the man for the PASSIVE mode:

SQLITE_CHECKPOINT_PASSIVE Checkpoint as many frames as possible without waiting for any database readers or writers to finish, then sync the database file if all frames in the log were checkpointed. The busy-handler callback is never invoked in the SQLITE_CHECKPOINT_PASSIVE mode. On the other hand, passive mode might leave the checkpoint unfinished if there are concurrent readers or writers.

Same as above the update note you can also set this option during compile time with SQLITE_DEFAULT_WAL_AUTOCHECKPOINT=<pages>.

From man:

This macro sets the default page count for the WAL automatic checkpointing feature. If unspecified, the default page count is 1000.

Answer 2

Quit the Firefox instance that uses the profile that contains the databases in question.

Launch Firefox from a temporary profile. Open the Browser Console and paste the following snippet. Edit the path string so that it points to the target profile. Press Enter

Repeat for every sqlite database.

Hopefully this will work like charm, but it wouldn't hurt to backup your profile before trying anything.

(()=>{
  let file = new FileUtils.File("/pathToTargetProfile/places.sqlite"); // edit this string
  let db = Services.storage.openDatabase(file);
  let stmt = db.createStatement("PRAGMA wal_checkpoint(TRUNCATE)");
  stmt.executeStep();
  console.log(stmt.row); //this might provide useful info
  stmt.finalize();
  db.close();
})();