PERF: make Mail folder page SQL work proportional to its window (#663)

kayg commented

2026-10-02 13:19:07 +00:00

Owner

Context: SQLite architecture audit #663, DESIGN §58 rules 2 and 8; #549 shows why HDD query work matters. #685 owns response limits and signed cursor contracts. This issue owns the folder query's work and index shape; coordinate the two in one Mail cache change.

Source: c4a61e8cf0, crates/plugins/mail/src/cache/store.rs:1202–1242, list_messages. The same function remains in queued merge-round-7a 2f4482ded0 (lines shifted by Connected Account work). It joins one mail_folders row to every live mail_memberships row, selects the preferred duplicate UID, joins mail_messages, then orders by m.received_ms DESC,m.id DESC LIMIT 101. The message time index starts with owner_id; membership indexes do not carry the folder's message-time order.

Local reproducible evidence: audit-sqlite.py --plans, Python SQLite 3.53.3, trusted base migrations, 100k synthetic messages/memberships in one live Inbox. This is a query-work probe, not production/HDD latency. The Rust locked libsqlite3-sys 0.37.0 bundles SQLite 3.51.3; recheck plans there before implementing.

The first folder page returns 101 rows but does about 8,001,000 VM instructions (progress callbacks in 1,000-instruction units):
SEARCH f USING INDEX sqlite_autoindex_mail_folders_1 (id=?)
SEARCH mm USING INDEX mail_memberships_folder_message (folder_id=? AND generation=?)
SEARCH m USING INDEX sqlite_autoindex_mail_messages_1 (id=?)
USE TEMP B-TREE FOR ORDER BY

There is also a small per-message sort for the preferred duplicate UID; that is separate from the whole-folder sort. ANALYZE does not remove the outer sort (about 8,502,000 instructions on this fixture). The existing unified Inbox query, on the same corpus, streams mail_messages_page and returns 101 rows in about 10,000 instructions. Its per-message duplicate choice remains correct. This comparison is not an endpoint speedup ratio.

Reasoned impact: keyset syntax and LIMIT cap returned rows but this join plan must inspect the full folder for every first/deep page. On HDD it can require a message lookup per membership plus a sort. Received-time sort must stay; do not silently substitute UID order. Small folders among a much larger account also need coverage; forcing a message scan alone can be a poor fix for those folders.

Concrete fix: provide a canonical per-folder message-time projection/index, or prove another query plan that seeks in folder+generation+received_ms+stable message ID order and stops after the bounded page. Maintain it atomically with store_window, duplicate-UID flags, generation switches, moves and expunges. Keep the preferred UID and stable message identity semantics from #626. Reuse the existing Inbox query machinery where suitable; do not make a second paging implementation. Avoid COUNT or materialized sorting before the first row.

Tests: 100k-message folder first/deep pages; a small Archive beside a large Inbox; duplicate UIDs with differing Seen flags; identical received times; category filters; concurrent insert/delete; UIDVALIDITY rebuild/restart; two-User isolation. Assert returned identities and no skipped/duplicate rows. Capture EXPLAIN and use a work bound as rows grow, then extend the existing Mail bench with ≥5 samples, latency/CPU/RSS/burst on the perf VM under flock /root/perf.lock and HDD emulation.

Duplicate check: all issue titles through #800; #685 covers bounded responses, #757 covers per-run delta/expunge budgets, #626 covers duplicate UID identities. None identifies this folder-wide received-time sort. Non-blocking performance finding. No product change or test expectation change in the audit.

Context: SQLite architecture audit #663, DESIGN §58 rules 2 and 8; #549 shows why HDD query work matters. #685 owns response limits and signed cursor contracts. This issue owns the folder query's work and index shape; coordinate the two in one Mail cache change. Source: c4a61e8cf090170f35b1bed3350d9de20c83ecd5, crates/plugins/mail/src/cache/store.rs:1202–1242, list_messages. The same function remains in queued merge-round-7a 2f4482ded066d9c5d9c59130377907f7fd2916c9 (lines shifted by Connected Account work). It joins one mail_folders row to every live mail_memberships row, selects the preferred duplicate UID, joins mail_messages, then orders by m.received_ms DESC,m.id DESC LIMIT 101. The message time index starts with owner_id; membership indexes do not carry the folder's message-time order. Local reproducible evidence: audit-sqlite.py --plans, Python SQLite 3.53.3, trusted base migrations, 100k synthetic messages/memberships in one live Inbox. This is a query-work probe, not production/HDD latency. The Rust locked libsqlite3-sys 0.37.0 bundles SQLite 3.51.3; recheck plans there before implementing. The first folder page returns 101 rows but does about 8,001,000 VM instructions (progress callbacks in 1,000-instruction units): SEARCH f USING INDEX sqlite_autoindex_mail_folders_1 (id=?) SEARCH mm USING INDEX mail_memberships_folder_message (folder_id=? AND generation=?) SEARCH m USING INDEX sqlite_autoindex_mail_messages_1 (id=?) USE TEMP B-TREE FOR ORDER BY There is also a small per-message sort for the preferred duplicate UID; that is separate from the whole-folder sort. ANALYZE does not remove the outer sort (about 8,502,000 instructions on this fixture). The existing unified Inbox query, on the same corpus, streams mail_messages_page and returns 101 rows in about 10,000 instructions. Its per-message duplicate choice remains correct. This comparison is not an endpoint speedup ratio. Reasoned impact: keyset syntax and LIMIT cap returned rows but this join plan must inspect the full folder for every first/deep page. On HDD it can require a message lookup per membership plus a sort. Received-time sort must stay; do not silently substitute UID order. Small folders among a much larger account also need coverage; forcing a message scan alone can be a poor fix for those folders. Concrete fix: provide a canonical per-folder message-time projection/index, or prove another query plan that seeks in folder+generation+received_ms+stable message ID order and stops after the bounded page. Maintain it atomically with store_window, duplicate-UID flags, generation switches, moves and expunges. Keep the preferred UID and stable message identity semantics from #626. Reuse the existing Inbox query machinery where suitable; do not make a second paging implementation. Avoid COUNT or materialized sorting before the first row. Tests: 100k-message folder first/deep pages; a small Archive beside a large Inbox; duplicate UIDs with differing Seen flags; identical received times; category filters; concurrent insert/delete; UIDVALIDITY rebuild/restart; two-User isolation. Assert returned identities and no skipped/duplicate rows. Capture EXPLAIN and use a work bound as rows grow, then extend the existing Mail bench with ≥5 samples, latency/CPU/RSS/burst on the perf VM under flock /root/perf.lock and HDD emulation. Duplicate check: all issue titles through #800; #685 covers bounded responses, #757 covers per-run delta/expunge budgets, #626 covers duplicate UID identities. None identifies this folder-wide received-time sort. Non-blocking performance finding. No product change or test expectation change in the audit.

kayg referenced this issue

2026-10-02 13:39:12 +00:00

PERF: instant everywhere — app-wide speed architecture rules, audit and shared primitives #663

kayg commented

2026-10-02 13:44:26 +00:00

Author

Owner

Starting #825 on branch job/mailsql-825, based on c4a61e8cf090170f35b1bed3350d9de20c83ecd5 (origin/dev). I am tracing the Mail folder query and its write paths against the audit probes, then I will add bounded-work coverage and measure the 200k-message HDD case before and after.

Starting #825 on branch `job/mailsql-825`, based on `c4a61e8cf090170f35b1bed3350d9de20c83ecd5` (`origin/dev`). I am tracing the Mail folder query and its write paths against the audit probes, then I will add bounded-work coverage and measure the 200k-message HDD case before and after.

kayg referenced this issue from a commit

2026-10-02 15:01:16 +00:00

bench(mail): add HDD folder page profile runner (#825)

kayg commented

2026-10-02 15:23:15 +00:00

Author

Owner

The audit evidence identifies the unbounded work: the existing folder page returns 101 rows from a 100,000-message fixture but executes about 8,001,000 SQLite VM instructions. Its plan builds a temporary B-tree to sort the joined messages by received time. This audit used Python SQLite 3.53.3; I am measuring the application path with SQLx's bundled SQLite 3.51.3 as part of this job.

The existing cursor already uses (received_ms, id), and DESIGN §45 keeps that order. I am testing a membership-backed keyset index while preserving the existing duplicate-UID rule and folder ordering.

The audit evidence identifies the unbounded work: the existing folder page returns 101 rows from a 100,000-message fixture but executes about 8,001,000 SQLite VM instructions. Its plan builds a temporary B-tree to sort the joined messages by received time. This audit used Python SQLite 3.53.3; I am measuring the application path with SQLx's bundled SQLite 3.51.3 as part of this job. The existing cursor already uses `(received_ms, id)`, and DESIGN §45 keeps that order. I am testing a membership-backed keyset index while preserving the existing duplicate-UID rule and folder ordering.

kayg referenced this issue from a commit

2026-10-02 16:15:17 +00:00

bench(mail): add cold legacy page baseline phase (#825)

kayg commented

2026-10-02 17:15:53 +00:00

Author

Owner

The SQLx 0.9 regression probe now confirms the pre-fix scaling problem on the application’s bundled SQLite: a 100-row first page from 5,000 messages executes about 371,000 VM instructions. The probe counts VM work with SQLite's progress handler. This is the expected red result; I am adding the covering key query and keeping detail reads bounded to those selected keys.

kayg commented

2026-10-02 17:43:06 +00:00

Author

Owner

The HDD profile now compares the pre-#825 list_messages SQL with the indexed path on the same 200,000-message / 202,000-membership fixture. I ran five cold samples per page case under /root/perf.lock with bench/hdd-emu.sh and recorded load average inside each lock.

Cold first page: legacy p50/p95 2,490/2,536 ms; indexed 10.15/10.46 ms.
Cold category page: legacy 1,565/1,969 ms; indexed 8.69/10.16 ms.
Cold deep page: legacy 1,838/2,292 ms; indexed 7.08/7.34 ms.
Warm indexed pages (25 samples per case): p95 8.05 ms first, 6.89 ms category, 6.78 ms deep.
Worst profile: 32 concurrent mixed pages, p50/p95 219/237 ms, process peak RSS 45.5 MB.

The cold legacy process averaged 42.19% CPU and 16.1 MB RSS. The cold indexed process averaged 5.75% CPU and 13.5 MB RSS. The retained legacy SQL is measured in the same binary and fixture; the new page columns and indexes are unused by that query. The profile is recorded in docs/perf/baseline.json.

The HDD profile now compares the pre-#825 `list_messages` SQL with the indexed path on the same 200,000-message / 202,000-membership fixture. I ran five cold samples per page case under `/root/perf.lock` with `bench/hdd-emu.sh` and recorded load average inside each lock. - Cold first page: legacy p50/p95 2,490/2,536 ms; indexed 10.15/10.46 ms. - Cold category page: legacy 1,565/1,969 ms; indexed 8.69/10.16 ms. - Cold deep page: legacy 1,838/2,292 ms; indexed 7.08/7.34 ms. - Warm indexed pages (25 samples per case): p95 8.05 ms first, 6.89 ms category, 6.78 ms deep. - Worst profile: 32 concurrent mixed pages, p50/p95 219/237 ms, process peak RSS 45.5 MB. The cold legacy process averaged 42.19% CPU and 16.1 MB RSS. The cold indexed process averaged 5.75% CPU and 13.5 MB RSS. The retained legacy SQL is measured in the same binary and fixture; the new page columns and indexes are unused by that query. The profile is recorded in `docs/perf/baseline.json`.

kayg referenced this issue from a commit

2026-10-02 17:45:04 +00:00

perf(mail): bound folder page SQL by its window (#825)

kayg referenced this issue from a commit

2026-10-02 17:45:04 +00:00

test(mail): match legacy folder page window in profile (#825)

kayg referenced this issue from a commit

2026-10-02 17:45:04 +00:00

perf(mail): record 200k folder page HDD profile (#825)

kayg referenced this issue from a commit

2026-10-02 18:29:16 +00:00

style(mail): collapse preferred UID remap guard (#825)

kayg commented

2026-10-02 18:34:53 +00:00

Author

Owner

Finished: mailsql-825

Implemented on branch job/mailsql-825. Final head: f6c62f291 (f6c62f291 full SHA below). The branch includes the required merge of the latest origin/job/mailperf; no push, deploy, or merge to dev was done.

Built

Migration 0010 caches received time, category, and the preferred duplicate UID on each membership. Partial covering indexes select one row per visible message in folder order.
list_messages now gets page keys with keyset paging, then loads details for those keys in the same read transaction. The sort remains (received_ms, message_id); duplicate UIDs keep the unread-first, then lowest-UID rule from DESIGN §45.
Sync windows, Seen changes, category corrections, metadata refreshes, and UID pruning keep the page projection current.
Added bounded-work and query-plan tests plus an ignored 200k-message profile fixture and bench/mail-folder-page.py.

Files

crates/plugins/mail/migrations/0010_folder_page_index.sql
crates/plugins/mail/src/cache/store.rs
bench/mail-folder-page.py
docs/perf/baseline.json
Merged from origin/job/mailperf, left untouched: apps/web/src/lib/mail/MailSidebar.svelte, apps/web/src/lib/mail/MailSidebar.svelte.test.ts, and apps/web/src/lib/mail/live.ts.

Performance

Measured on the locked perf VM with the HDD emulator, on a 200,000-message / 202,000-membership fixture. The baseline ran the pre-#825 SQL against the same migrated fixture; it ignored the new fields and indexes.

Phase and case	Before p50 / p95	Indexed p50 / p95
Cold first page	2490.243 / 2536.075 ms	10.155 / 10.458 ms
Cold category page	1564.624 / 1968.777 ms	8.687 / 10.162 ms
Cold deep page	1838.436 / 2291.710 ms	7.084 / 7.340 ms
Warm first page	—	5.830 / 8.049 ms
Warm category page	—	6.181 / 6.890 ms
Warm deep page	—	6.044 / 6.779 ms

Cold process CPU/RSS averaged 42.19% / 16,061,236 bytes before and 5.75% / 13,470,154 bytes after. The warm run averaged 107.19% CPU and 17,116,585 bytes RSS. A 32-request burst measured p50/p95/max 219.260 / 237.490 / 239.050 ms, 202.45% process CPU, 22,833,717 bytes mean RSS, and 45,453,312 bytes peak RSS. The profile is recorded in docs/perf/baseline.json.

Gates

cargo fmt --check: exit 0, no output.

cargo clippy -p calternal-plugin-mail --all-targets -- -D warnings (exit 0):

    Checking calternal-plugin-mail v0.0.1 (/home/kayg/Developer/calternal-wt/mailsql-825/crates/plugins/mail)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 7.57s

cargo test -p calternal-plugin-mail:

test result: ok. 49 passed; 0 failed; 4 ignored; 0 measured; 0 filtered out; finished in 15.48s

   Doc-tests calternal_plugin_mail

running 0 tests

test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s

bun run check: failed in the merged, untouched #771 file apps/web/src/lib/mail/MailSidebar.svelte.test.ts: svelte-check reported that source may be undefined at lines 98 and 100. I left that owner-owned change untouched.

bun run test:

 Test Files  154 passed (154)
      Tests  1074 passed (1074)
   Start at  20:24:12
   Duration  113.29s (transform 64%, import 14%, environment 12%, tests 8%, setup 2%)

The Rust gates passed immediately before the final commit, which only corrected DESIGN section references in comments. The web check/test were run after the latest mailperf merge.

UX gaps

Closed: no UI changes were needed for this SQL paging issue.
Left: none introduced by #825. The merged #771 type-check issue is noted above. Screenshots are not applicable to this backend-only change.

Decisions and known gaps

The design specifies received-time ordering and duplicate UID preference but not the index representation. I used a partial denormalized membership projection and a metadata trigger so page selection stays proportional to its window while sync and sender corrections remain visible. The HDD baseline reuses the migrated fixture and executes the preserved pre-#825 query.

Full head SHA: f6c62f291e88fb14319d16db1d92400e988aede3.

## Finished: mailsql-825 Implemented on branch `job/mailsql-825`. Final head: `f6c62f291` (`f6c62f291` full SHA below). The branch includes the required merge of the latest `origin/job/mailperf`; no push, deploy, or merge to `dev` was done. ### Built - Migration 0010 caches received time, category, and the preferred duplicate UID on each membership. Partial covering indexes select one row per visible message in folder order. - `list_messages` now gets page keys with keyset paging, then loads details for those keys in the same read transaction. The sort remains `(received_ms, message_id)`; duplicate UIDs keep the unread-first, then lowest-UID rule from DESIGN §45. - Sync windows, Seen changes, category corrections, metadata refreshes, and UID pruning keep the page projection current. - Added bounded-work and query-plan tests plus an ignored 200k-message profile fixture and `bench/mail-folder-page.py`. ### Files - `crates/plugins/mail/migrations/0010_folder_page_index.sql` - `crates/plugins/mail/src/cache/store.rs` - `bench/mail-folder-page.py` - `docs/perf/baseline.json` - Merged from `origin/job/mailperf`, left untouched: `apps/web/src/lib/mail/MailSidebar.svelte`, `apps/web/src/lib/mail/MailSidebar.svelte.test.ts`, and `apps/web/src/lib/mail/live.ts`. ### Performance Measured on the locked perf VM with the HDD emulator, on a 200,000-message / 202,000-membership fixture. The baseline ran the pre-#825 SQL against the same migrated fixture; it ignored the new fields and indexes. | Phase and case | Before p50 / p95 | Indexed p50 / p95 | | --- | ---: | ---: | | Cold first page | 2490.243 / 2536.075 ms | 10.155 / 10.458 ms | | Cold category page | 1564.624 / 1968.777 ms | 8.687 / 10.162 ms | | Cold deep page | 1838.436 / 2291.710 ms | 7.084 / 7.340 ms | | Warm first page | — | 5.830 / 8.049 ms | | Warm category page | — | 6.181 / 6.890 ms | | Warm deep page | — | 6.044 / 6.779 ms | Cold process CPU/RSS averaged 42.19% / 16,061,236 bytes before and 5.75% / 13,470,154 bytes after. The warm run averaged 107.19% CPU and 17,116,585 bytes RSS. A 32-request burst measured p50/p95/max 219.260 / 237.490 / 239.050 ms, 202.45% process CPU, 22,833,717 bytes mean RSS, and 45,453,312 bytes peak RSS. The profile is recorded in `docs/perf/baseline.json`. ### Gates `cargo fmt --check`: exit 0, no output. `cargo clippy -p calternal-plugin-mail --all-targets -- -D warnings` (exit 0): ``` Checking calternal-plugin-mail v0.0.1 (/home/kayg/Developer/calternal-wt/mailsql-825/crates/plugins/mail) Finished `dev` profile [unoptimized + debuginfo] target(s) in 7.57s ``` `cargo test -p calternal-plugin-mail`: ``` test result: ok. 49 passed; 0 failed; 4 ignored; 0 measured; 0 filtered out; finished in 15.48s Doc-tests calternal_plugin_mail running 0 tests test result: ok. 0 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.00s ``` `bun run check`: failed in the merged, untouched #771 file `apps/web/src/lib/mail/MailSidebar.svelte.test.ts`: svelte-check reported that `source` may be undefined at lines 98 and 100. I left that owner-owned change untouched. `bun run test`: ``` Test Files 154 passed (154) Tests 1074 passed (1074) Start at 20:24:12 Duration 113.29s (transform 64%, import 14%, environment 12%, tests 8%, setup 2%) ``` The Rust gates passed immediately before the final commit, which only corrected DESIGN section references in comments. The web check/test were run after the latest mailperf merge. ### UX gaps Closed: no UI changes were needed for this SQL paging issue. Left: none introduced by #825. The merged #771 type-check issue is noted above. Screenshots are not applicable to this backend-only change. ### Decisions and known gaps The design specifies received-time ordering and duplicate UID preference but not the index representation. I used a partial denormalized membership projection and a metadata trigger so page selection stays proportional to its window while sync and sender corrections remain visible. The HDD baseline reuses the migrated fixture and executes the preserved pre-#825 query. Full head SHA: `f6c62f291e88fb14319d16db1d92400e988aede3`.

kayg referenced this issue from a commit

2026-10-02 18:43:13 +00:00

docs(mail): correct folder paging design references (#825)

kayg referenced this issue

2026-10-02 20:13:08 +00:00

calternaldav: IMAP mail proxy, all Connected Accounts as one mail account (grill) #486

kayg referenced this issue from a commit

2026-10-02 21:02:21 +00:00

bench(mail): reuse the shared folder-page profile from #825

kayg referenced this issue from a commit

2026-10-02 21:38:40 +00:00

perf(mail): reuse #825 keyset folder pages and covering indexes