SQLite: no journal size limit, no `PRAGMA optimize` and no way to compact the file #1128

New issue

Closed

opened 2026-10-31 03:04:13 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-31 03:04:13 +00:00

Owner

What exists today

src/postulo/config/sqlite.py:51-55 gives every file-backed SQLite connection transaction_mode: IMMEDIATE, PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; and a 20 s timeout. Nothing else: a grep of src/postulo for VACUUM, wal_checkpoint, journal_size_limit and PRAGMA optimize finds nothing, and the tracker has no issue on them.

WAL size. SQLite checkpoints the WAL automatically at 1,000 pages but never truncates the -wal file unless journal_size_limit is set; after a large write (an archive import, a restore, a big purge such as D1's first run) it stays at its high-water size until the last connection closes, which with three gunicorn workers, the scheduler and the worker rarely happens.
Planner statistics. SQLite's documentation recommends running PRAGMA optimize periodically on long-lived applications (or at connection open with 0x10002 since 3.46). Postulo never runs it, so sqlite_stat1 is whatever a migration left, which for new indexes is nothing.
Free pages. Rows deleted by the scheduler's reaping and by the proposed cache purges (D1, B5) leave free pages inside the file; without VACUUM or auto_vacuum the file never shrinks, and the backup copies it at full size.

The image ships SQLite 3.50.4 through python:3.14-slim-bookworm (docker/Dockerfile:137; the dev .venv reports 3.50.4 too), so every pragma below is available. PostgreSQL instances are unaffected.

What would change

Add PRAGMA journal_size_limit=67108864; (64 MB) to FILE_OPTIONS["init_command"] in config/sqlite.py. Per connection, free.
Run PRAGMA optimize; from the scheduler pass at most once a day, and from the hourly request-time tidy (core/slow.py:tidy_up_if_due) when there is no scheduler; it is a no-op when nothing changed. SQLite only, skipped on PostgreSQL (connection.vendor).
A manage.py compact_database that runs VACUUM (SQLite) after saying how much space it would give back (PRAGMA freelist_count × page_size), documented on the wiki's maintenance page. Not automatic: VACUUM takes the write lock for its whole run and needs free disk equal to the database.

No library; three SQL statements in the places that already own the database.

What it saves or protects

small: the -wal file shrinks back after a burst instead of staying at its largest size, the query planner keeps current statistics, and an operator can give back the space that deletions (expired cache rows, task results, errands) leave inside the file

Accessibility

none

Decisions

Automatic VACUUM? Recommended: no; a command the operator runs, with the free-page figure on the staff overview so they know when it is worth it.
Split? Recommended: one issue; the three parts are small and touch the same module.

Found by the efficiency and reliability audit of 2026-10-30 (finding D8; index postulo/postulo#1108). Checked against main at daee30b15 (2026-10-30).

## What exists today `src/postulo/config/sqlite.py:51-55` gives every file-backed SQLite connection `transaction_mode: IMMEDIATE`, `PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;` and a 20 s timeout. Nothing else: a grep of `src/postulo` for `VACUUM`, `wal_checkpoint`, `journal_size_limit` and `PRAGMA optimize` finds nothing, and the tracker has no issue on them. - **WAL size.** SQLite checkpoints the WAL automatically at 1,000 pages but never truncates the `-wal` file unless `journal_size_limit` is set; after a large write (an archive import, a restore, a big purge such as D1's first run) it stays at its high-water size until the last connection closes, which with three gunicorn workers, the scheduler and the worker rarely happens. - **Planner statistics.** SQLite's documentation recommends running `PRAGMA optimize` periodically on long-lived applications (or at connection open with `0x10002` since 3.46). Postulo never runs it, so `sqlite_stat1` is whatever a migration left, which for new indexes is nothing. - **Free pages.** Rows deleted by the scheduler's reaping and by the proposed cache purges (D1, B5) leave free pages inside the file; without `VACUUM` or `auto_vacuum` the file never shrinks, and the backup copies it at full size. The image ships SQLite 3.50.4 through `python:3.14-slim-bookworm` (`docker/Dockerfile:137`; the dev `.venv` reports 3.50.4 too), so every pragma below is available. PostgreSQL instances are unaffected. ## What would change 1. Add `PRAGMA journal_size_limit=67108864;` (64 MB) to `FILE_OPTIONS["init_command"]` in `config/sqlite.py`. Per connection, free. 2. Run `PRAGMA optimize;` from the scheduler pass at most once a day, and from the hourly request-time tidy (`core/slow.py:tidy_up_if_due`) when there is no scheduler; it is a no-op when nothing changed. SQLite only, skipped on PostgreSQL (`connection.vendor`). 3. A `manage.py compact_database` that runs `VACUUM` (SQLite) after saying how much space it would give back (`PRAGMA freelist_count` × `page_size`), documented on the wiki's maintenance page. Not automatic: `VACUUM` takes the write lock for its whole run and needs free disk equal to the database. No library; three SQL statements in the places that already own the database. ## What it saves or protects small: the `-wal` file shrinks back after a burst instead of staying at its largest size, the query planner keeps current statistics, and an operator can give back the space that deletions (expired cache rows, task results, errands) leave inside the file ## Accessibility none ## Decisions - **Automatic VACUUM?** Recommended: no; a command the operator runs, with the free-page figure on the staff overview so they know when it is worth it. - **Split?** Recommended: one issue; the three parts are small and touch the same module. --- Found by the efficiency and reliability audit of 2026-10-30 (finding D8; index postulo/postulo#1108). Checked against `main` at `daee30b15` (2026-10-30).

tiagoagueda added this to the 0.6.0 milestone

2026-10-31 03:04:13 +00:00

tiagoagueda added the

performance

enhancement

labels

2026-10-31 03:04:13 +00:00

tiagoagueda referenced this issue

2026-10-31 03:04:40 +00:00

Efficiency and reliability audit of 2026-10-30: index #1108

tiagoagueda referenced this issue from a pull request that will close it,

2026-11-11 13:40:10 +00:00