SQLite Forum

incremental_vacuum and WAL size
I think it's because SQLite needs to spill its page cache midway through the big incr-vacuum operation.

As it does the incremental vacuum, SQLite is marking each free page removed from the end of the file as dirty. If it flushes these out at the end of the transaction, when it knows the db image is about to be truncated, then it is smart enough not to write to the wal file any pages that will be truncated away anyway. But, if the cache fills up mid-transaction, then SQLite just starts writing dirty pages to the wal file. Including those that would be discarded at commit time when the database image is being truncated.

So the big incr-vacuum, because it overflows the page-cache, ends up writing lots of pages to the wal file that the series of small incr-vacuum operations do not.

You could confirm by adding something like the following to the big incr-vacuum case:

        PRAGMA cache_size = 30000;

With the larger cache size no extra pages should be written to the wal file.

There's an optimization opportunity here of course. I think it may cases, these pages should not be being marked as dirty in the first place.