From what I know, the journal_size_limit PRAGMA still affects WAL mode, but it doesn't solve the issue of the WAL file potentially growing uncontrollably. Am I missing something?
Indeed on rereading my question I see it was not phrased correctly.
Yes, the `journal_size_limit` affects the maximum journal/wal file that remains on disk if larger than that — and by the way ensures that these files are not deleted once created.
Your setting it to ~25MiB while the default `wal_autocheckpoint` PRAGMA is set to 1000 pages (with the typical page size of 4KiB that means after ~4MiB the WAL file contents will get moved to the main database file if no other transaction is active) is what confused me. 25MiB seems very specific for a file size to keep in the occasion that the WAL file keeps growing beyond 4MiB. Perhaps you also meant to tinker with the `wal_autocheckpoint` PRAGMA but didn't?
>Journal size limit is measured in bytes and only applies to an empty journal file.
>The WAL file will grow without bounds until a checkpoint takes place that reaches the very end of the WAL file. Usually, a checkpoint is performed when a commit causes the WAL file to be longer than 1000 pages (PAGES, not bytes). There are conditions when running a checkpoint to completion is not possible, like disabled checkpointing, checkpoint starvation because of open read transactions and large write transactions.
tzot|1 year ago
Yes, the `journal_size_limit` affects the maximum journal/wal file that remains on disk if larger than that — and by the way ensures that these files are not deleted once created.
Your setting it to ~25MiB while the default `wal_autocheckpoint` PRAGMA is set to 1000 pages (with the typical page size of 4KiB that means after ~4MiB the WAL file contents will get moved to the main database file if no other transaction is active) is what confused me. 25MiB seems very specific for a file size to keep in the occasion that the WAL file keeps growing beyond 4MiB. Perhaps you also meant to tinker with the `wal_autocheckpoint` PRAGMA but didn't?
kgeist|1 year ago
>Journal size limit is measured in bytes and only applies to an empty journal file.
>The WAL file will grow without bounds until a checkpoint takes place that reaches the very end of the WAL file. Usually, a checkpoint is performed when a commit causes the WAL file to be longer than 1000 pages (PAGES, not bytes). There are conditions when running a checkpoint to completion is not possible, like disabled checkpointing, checkpoint starvation because of open read transactions and large write transactions.