Performance optimization and progress tracking at collection scale #48

New issue

Open

opened 2026-07-28 04:59:57 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-07-28 04:59:57 +00:00

Context

Every prior milestone has been built against a small development dataset. The real target
is a large family collection, and the access patterns that feel instant over a few hundred
photos behave differently over tens of thousands. This issue is the deliberate pass to
find out where.

Scope

Performance profiling and optimization across the read paths, plus progress tracking for
long-running operations.

Implementation notes

Measure before optimizing, against a realistically sized seeded dataset. Optimizing from
intuition on a dataset that fits in cache produces changes that help nothing.
Likely hot spots given the schema: the photo browser list query with filters and
pagination, near-duplicate candidate search, constraint recomputation across a large
album, and the audit log viewer.
Verify the existing indexes are actually used by the query planner; add what is missing.
Watch for N+1 queries through the repository layer, particularly where evidence and
decisions are loaded per photo in list views.
Frontend: virtualize long photo grids, and use appropriately sized thumbnails rather
than full-resolution images in list views â€” this is likely the single largest browser-side win.
Consider generating and caching thumbnails at ingest rather than resizing per request.
SQLite specifics: confirm WAL behaviour under the worker's concurrent writes, and check
whether any long-running job holds a write lock long enough to block review actions.
Progress tracking and cancellation for every long operation: ingest, constraint rebuild,
duplicate scan, export, and integrity check.
Record the measured baseline and the improvement in the issue, so this pass is evaluable
rather than assertive.

Done when

A realistically sized dataset can be seeded for benchmarking
Photo browse, review workspace, and admin views perform acceptably at that scale
N+1 query patterns are eliminated in list views
Long-running jobs report progress and can be cancelled
No background job blocks interactive review
Before/after measurements are recorded

References

docs/circa-spec.md section 13 (processing queue and performance)
backend/app/repositories/, frontend/src/pages/PhotoBrowser

## Context Every prior milestone has been built against a small development dataset. The real target is a large family collection, and the access patterns that feel instant over a few hundred photos behave differently over tens of thousands. This issue is the deliberate pass to find out where. ## Scope Performance profiling and optimization across the read paths, plus progress tracking for long-running operations. ## Implementation notes - Measure before optimizing, against a realistically sized seeded dataset. Optimizing from intuition on a dataset that fits in cache produces changes that help nothing. - Likely hot spots given the schema: the photo browser list query with filters and pagination, near-duplicate candidate search, constraint recomputation across a large album, and the audit log viewer. - Verify the existing indexes are actually used by the query planner; add what is missing. - Watch for N+1 queries through the repository layer, particularly where evidence and decisions are loaded per photo in list views. - Frontend: virtualize long photo grids, and use appropriately sized thumbnails rather than full-resolution images in list views â€” this is likely the single largest browser-side win. - Consider generating and caching thumbnails at ingest rather than resizing per request. - SQLite specifics: confirm WAL behaviour under the worker's concurrent writes, and check whether any long-running job holds a write lock long enough to block review actions. - Progress tracking and cancellation for every long operation: ingest, constraint rebuild, duplicate scan, export, and integrity check. - Record the measured baseline and the improvement in the issue, so this pass is evaluable rather than assertive. ## Done when - [ ] A realistically sized dataset can be seeded for benchmarking - [ ] Photo browse, review workspace, and admin views perform acceptably at that scale - [ ] N+1 query patterns are eliminated in list views - [ ] Long-running jobs report progress and can be cancelled - [ ] No background job blocks interactive review - [ ] Before/after measurements are recorded ## References - `docs/circa-spec.md` section 13 (processing queue and performance) - `backend/app/repositories/`, `frontend/src/pages/PhotoBrowser`

claude-bot added this to the v0.6.0 milestone

2026-07-28 04:59:57 +00:00

claude-bot added the

enhancement

frontend

backend

labels

2026-07-28 04:59:57 +00:00

claude-bot referenced this issue

2026-07-28 06:00:29 +00:00

Photo list query has no index for its sort shape (436x) #84

claude-bot referenced this issue

2026-07-28 06:00:30 +00:00

Ingest blocks the event loop and buffers whole uploads in RAM #86

claude-bot referenced this issue

2026-07-28 06:00:30 +00:00

SQLite configuration is not ready for a concurrent worker #87

claude-bot referenced this issue

2026-07-28 06:03:02 +00:00

Generate thumbnails and display derivatives at ingest #93

claude-bot referenced this issue

2026-07-28 06:03:03 +00:00

Photo browser cannot reach past the first 100 photos #94

claude-bot commented

2026-07-28 06:07:50 +00:00

Author

Amended by the audit of 2026-07-28.

Two things pulled out of this issue and scheduled earlier, because they block usability rather
than optimize it:

Thumbnails → #93 (v0.3.0). The grid currently renders a camera emoji placeholder, so
browsing means reading filenames. Also a display-correctness matter: image/tiff is accepted at
ingest and browsers cannot render TIFF in <img> at all.
The missing index → #84 (v0.2.0). This issue asked generically to "verify the existing
indexes are actually used." Measured answer: the browse query does a full scan plus temp B-tree
at 61 ms over 50k rows, versus 0.14 ms with (collection_id, created_at) — 436×.

Also split out: media HTTP caching (#85), the event-loop-blocking ingest handler (#86), SQLite
pragmas for the worker (#87), cursor tiebreak (#88), and gzip (#92).

Measured and found not a problem, so removable from this issue's scope: N+1 queries. The
projection pattern is genuinely exploited — the list and detail endpoints read only projection
columns and no relationship access occurs during serialization. The production bundle is also
healthy at 89.5 KB gzip, single chunk; code-splitting is unnecessary at this size.

**Amended by the audit of 2026-07-28.** Two things pulled out of this issue and scheduled earlier, because they block usability rather than optimize it: - **Thumbnails → #93** (v0.3.0). The grid currently renders a camera emoji placeholder, so browsing means reading filenames. Also a display-correctness matter: `image/tiff` is accepted at ingest and browsers cannot render TIFF in `<img>` at all. - **The missing index → #84** (v0.2.0). This issue asked generically to "verify the existing indexes are actually used." Measured answer: the browse query does a full scan plus temp B-tree at **61 ms** over 50k rows, versus **0.14 ms** with `(collection_id, created_at)` — 436×. Also split out: media HTTP caching (#85), the event-loop-blocking ingest handler (#86), SQLite pragmas for the worker (#87), cursor tiebreak (#88), and gzip (#92). Measured and found **not** a problem, so removable from this issue's scope: N+1 queries. The projection pattern is genuinely exploited — the list and detail endpoints read only projection columns and no relationship access occurs during serialization. The production bundle is also healthy at 89.5 KB gzip, single chunk; code-splitting is unnecessary at this size.