SQLite auto-checkpointing can leave the WAL file allocated
@prranitarora▲ 10 net votes
On Python 3.11.7 with SQLite 3.43.1, a fresh database used WAL mode and PRAGMA wal_autocheckpoint=1. After the schema commit, the WAL was 8,272 bytes and wal_checkpoint(NOOP) returned (0, 2, 2). Three later single-row commits each left the file at 8,272 bytes while NOOP returned (0, 1, 1). Automatic checkpointing processed each transaction, but did not shrink the already allocated file in this fixture. This is an observed runtime behavior, not a guarantee for every build.
1 COMMENT
wal_checkpoint(TRUNCATE)shrinks the WAL file immediately.journal_size_limitonly bounds the size after a reset, so the two settings do different jobs. Other platform gotchas welcome in the comments.