marvin8/cang

Watch

Fork 0

DB performance: WAL mode, indexes, and query optimization #127

New issue

Closed

opened 2026-07-25 01:08:17 +00:00 by coding-agent-marvin8 · 1 comment

coding-agent-marvin8 commented

2026-07-25 01:08:17 +00:00

(Migrated from codeberg.org)

Cang's SQLite database currently has a single index (on path for dedup) and runs in default journal mode. With ~30k snapshots and growing, several operations feel sluggish:

Login/calendar page hang — likely caused by periodic scan writes blocking reads (no WAL mode)
Scrubber at 5× speed can't keep up — each image request runs a full-table-scan query to find the snapshot path
list_motion_snapshots_for_range — no index on motion or recorded_at

The fix involves four changes: enable WAL mode, add two covering indexes, rewrite queries to use range scans (so the indexes are actually used), and serve scrubber images by snapshot ID (PK lookup) instead of re-running the filtered query on every frame.

Cang's SQLite database currently has a single index (on `path` for dedup) and runs in default journal mode. With ~30k snapshots and growing, several operations feel sluggish: 1. **Login/calendar page hang** — likely caused by periodic scan writes blocking reads (no WAL mode) 2. **Scrubber at 5× speed can't keep up** — each image request runs a full-table-scan query to find the snapshot path 3. **`list_motion_snapshots_for_range`** — no index on `motion` or `recorded_at` The fix involves four changes: enable WAL mode, add two covering indexes, rewrite queries to use range scans (so the indexes are actually used), and serve scrubber images by snapshot ID (PK lookup) instead of re-running the filtered query on every frame.

coding-agent-marvin8 commented

2026-07-25 01:08:32 +00:00

(Migrated from codeberg.org)

Plan

1. Enable WAL mode

In open_db(), add PRAGMA journal_mode=WAL and PRAGMA synchronous=NORMAL. This allows concurrent reads during writes, eliminating the login hang when a periodic scan is in progress.

2. Schema V4 — two new indexes

Create a V4 migration that adds:

idx_snapshots_camera_recorded on (camera, recorded_at) — covers list_snapshots_for_day and latest_snapshot_per_camera
idx_snapshots_motion_recorded on (motion, recorded_at) — covers list_motion_snapshots_for_range

3. Rewrite queries for index-friendly range scans

The current queries use DATE(recorded_at) = ? which prevents index usage. Rewrite to range scans using ISO 8601 lexicographic ordering:

list_snapshots_for_day: recorded_at >= ? AND recorded_at < ? (day boundaries)
list_motion_snapshots_for_range: recorded_at >= ? AND recorded_at < ? (with computed end-date + 1)
Time-of-day filters within list_snapshots_for_day become full-datetime comparisons

4. Serve scrubber images by snapshot ID

Currently snapshot_image() re-runs list_snapshots_for_day() on every frame to find the nth snapshot's path. Instead:

Add snapshot id values to the snapshots_json payload in the template
Add a new route GET /snapshots/{id}/img that does a PK lookup
Update the JavaScript to use the ID-based URL

Files touched

src/cang/db.py — WAL pragma, V4 migration, query rewrites, new snapshot_by_id function
src/cang/web/routes/snapshots.py — new by-ID image route, pass IDs in JSON
src/cang/web/templates/scrubber.html — JS changes to use ID-based URLs
tests/test_db.py — tests for new indexes, query rewrites, and by-ID lookup
tests/test_db_queries.py — tests for updated motion range query
tests/web/test_snapshot_scrubber.py — tests for by-ID image serving

Branch

perf/issue-127-db-indexes-wal

Commits

:zap: Enable WAL mode, add performance indexes, rewrite queries for range scans
:zap: Serve scrubber images by snapshot ID via PK lookup

PR

fj --host https://codeberg.org pr create \
  --base main --head perf/issue-127-db-indexes-wal \
  -r MinimalNVR/cang \
  --body "Closes #127"

## Plan ### 1. Enable WAL mode In `open_db()`, add `PRAGMA journal_mode=WAL` and `PRAGMA synchronous=NORMAL`. This allows concurrent reads during writes, eliminating the login hang when a periodic scan is in progress. ### 2. Schema V4 — two new indexes Create a V4 migration that adds: - `idx_snapshots_camera_recorded` on `(camera, recorded_at)` — covers `list_snapshots_for_day` and `latest_snapshot_per_camera` - `idx_snapshots_motion_recorded` on `(motion, recorded_at)` — covers `list_motion_snapshots_for_range` ### 3. Rewrite queries for index-friendly range scans The current queries use `DATE(recorded_at) = ?` which prevents index usage. Rewrite to range scans using ISO 8601 lexicographic ordering: - `list_snapshots_for_day`: `recorded_at >= ? AND recorded_at < ?` (day boundaries) - `list_motion_snapshots_for_range`: `recorded_at >= ? AND recorded_at < ?` (with computed end-date + 1) - Time-of-day filters within `list_snapshots_for_day` become full-datetime comparisons ### 4. Serve scrubber images by snapshot ID Currently `snapshot_image()` re-runs `list_snapshots_for_day()` on every frame to find the nth snapshot's path. Instead: - Add snapshot `id` values to the `snapshots_json` payload in the template - Add a new route `GET /snapshots/{id}/img` that does a PK lookup - Update the JavaScript to use the ID-based URL ### Files touched - `src/cang/db.py` — WAL pragma, V4 migration, query rewrites, new `snapshot_by_id` function - `src/cang/web/routes/snapshots.py` — new by-ID image route, pass IDs in JSON - `src/cang/web/templates/scrubber.html` — JS changes to use ID-based URLs - `tests/test_db.py` — tests for new indexes, query rewrites, and by-ID lookup - `tests/test_db_queries.py` — tests for updated motion range query - `tests/web/test_snapshot_scrubber.py` — tests for by-ID image serving ### Branch `perf/issue-127-db-indexes-wal` ### Commits 1. `:zap: Enable WAL mode, add performance indexes, rewrite queries for range scans` 2. `:zap: Serve scrubber images by snapshot ID via PK lookup` ### PR ``` fj --host https://codeberg.org pr create \ --base main --head perf/issue-127-db-indexes-wal \ -r MinimalNVR/cang \ --body "Closes #127" ```

No labels

No milestone

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

marvin8/cang#127

No description provided.

Rows
Columns