Skip to content

driver-sql: after reclaimSpace() on a WAL-mode SQLite file the -wal sidecar grows to about the space just freed (91.9 MB for a 105 MB freelist) and stays there until the last connection closes #20426

Description

@objectstack-fleet

Filing gate: ① a product defect with a named landing site and a reach:. Finding class (a). reach: is a named producer: LifecycleService.sweep() (the ADR-0057 Reaper) calls SqlDriver.reclaimSpace() on the default file-backed SQLite datasource, which runs in WAL mode, after every sweep that deleted rows, and then lists the datasource as reclaimed.

Filed by the domain:engine execution seat 1 (session_01N8TPEsoJxPsdSdNKGnNGEN, os-warren) from the #20106 dev's out_of_scope_findings (os-dev-report 5868445205 on #20106, PR #20425). The readings are the dev's. ⛔ Filed bare: routing and grading belong to triage. ⛔ Not a claim.

What happens

With PR #20425, reclaimSpace() returns the whole freelist. The dev's measurement at 25,754 freed pages through SqlDriver:

  • a second connection reads freelist 0 and 4 pages;
  • the database file goes 105,631,744 → 16,384 bytes;
  • but the -wal sidecar goes 4,577,352 → 91,855,432 bytes and holds that size until the last connection closes.

So on disk, while the process runs, the pair goes from 110.2 MB to 91.9 MB, not to about 16 KB. The vacuum's page moves land in the WAL, and nothing checkpoints and truncates it. The sweep reports the space as returned.

Options measured by the dev on raw better-sqlite3 (25,600 rows, one run, shared box; database file + -wal while open):

variant file + -wal time notes
exec alone (PR #20425) 12,288 + 90,112,672 267 ms
+ wal_checkpoint(TRUNCATE) 12,288 + 0 309 ms can wait on other processes' readers up to the busy timeout
chunked incremental_vacuum(1000) + wal_checkpoint(PASSIVE) 12,288 + 210,152 71 ms never waits

Where

SqlDriver.reclaimSpace's better-sqlite3 arm in packages/drivers/driver-sql/src/sql-driver.ts, as PR #20425 leaves it. A chunk loop must stop on no progress, because an auto_vacuum=NONE file never shrinks its freelist. The full reading is in PR #20425's Acceptance notes.

Dedupe

search_issues "sqlite wal sidecar grows after reclaimSpace incremental_vacuum wal_checkpoint lifecycle sweep disk" in objectstack-ai/objectstack, open and closed: 1 hit, #20106 (the one-page reclaim this finding came out of, which PR #20425 fixes). None is this.

Dedupe words: reclaimSpace wal sidecar · incremental_vacuum wal_checkpoint · sqlite -wal size after reclaimSpace

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

area:devpathThe road — create, dev, verify, publish/install, connect an agent, iteratebugSomething isn't workingdomain:enginepriority:p2Medium: important, M3

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions