ankitects / ankitects/anki

Schema-11 downgrade leaves SQLite freelist waste in .apkg exports

Open
#5,490 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
30.5k
Forks
3.2k
Avg merge
2d 8h
Merged PRs (30d)
60

Description

The schema-11 downgrade (`schema11_downgrade.sql`) drops tables and indexes, which leaves pages on the SQLite freelist without shrinking the file.

This affects `.apkg` exports in two places:
- **Legacy exports:** the main `collection.anki21` is downgraded to V11 before being copied, so it carries freelist pages.
- **Both legacy and non-legacy exports:** every package includes a dummy `collection.anki2` that is built at V18, vacuumed, then downgraded to V11 — the downgrade re-creates freelist pages after the vacuum.

Non-legacy main DBs (`collection.anki21b`) are unaffected because closing at V18 is a no-op.

**Real-world example:** the [UK Geography | Regions, Counties, and Cities](https://ankiweb.net/shared/info/80961363) shared deck (~200 notes), exported from the same build with and without a workaround:

| Export | Before | After manual VACUUM | Saving |
|---|---|---|---|
| Legacy (compatibility) | 751 KB | 706 KB | ~46 KB (~6%) |
| Non-legacy (zstd) | 269 KB | 240 KB | ~29 KB (~11%) |

Freelist pages observed in the exported packages:
- Legacy: `collection.anki21` had 37 free pages; dummy `collection.anki2` had 56.
- Non-legacy: `collection.anki21b` had 0 free pages; dummy `collection.anki2` had 56.

Proposed fix: run `VACUUM` after the downgrade transaction commits in `downgrade_to_schema_11`, so both the exported legacy DB and the dummy collection are compact.

Contributor guide

Open the contributing guide

Research direction

Start with schema11_downgrade.sql and the downgrade_to_schema_11 entry point, then trace the export paths for collection.anki21 and the dummy collection.anki2. Verify the transaction boundary and run an export using the affected paths. Done means the downgraded databases are compact and no longer contain the reported freelist pages.

Written by the indexing model from the issue text.

Assessment

Tech stack
rust, sqlite
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
76/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.