Manual · Chapter 5 of 5

Future Optimizations

The map is designed to hold decades of neighborhood history. The current architecture is sound but some query patterns will need attention as the fact log grows. This page documents the known scaling cliffs and their fixes, so they can be applied when — not before — they matter.

Estimated load at 20 years

At 5–10 ranges per week with edits and tag memberships: roughly 5,000–10,000 range entities, 200k–500k fact rows, and 50–200 ranges visible at any given clock moment.

1. Full-table scan in range queries

Where: entitiesOfKind("range") loads every range ever created, then Go filters by visibility window.

Why it hurts: At 10k ranges, the pivot query reconstitutes ~150k attribute cells per map load. Only 50–200 survive the time filter.

Fix: Push the time filter into SQL. Index starts_at and ends_at attributes so the database can skip expired ranges before the pivot.

2. N+1 queries per visible range

Where: handleActiveRanges calls RangeViewerState per row, which fires 3–6 individual queries (LastTransition, ParticipantName for claimer and editor, factWithStamp for title edit).

Why it hurts: 200 visible ranges × 3–6 queries = 600–1,200 SQLite round-trips per map load.

Fix: Batch these into the pivot query or a single supplemental query that returns claimer/editor data for all visible ranges at once.

3. Tag membership full scan

Where: TagsByRange() loads every tag_membership entity, then filters in Go.

Why it hurts: Decades of tagging could produce thousands of membership entities, scanned to find ~50 active ones.

Fix: Filter by the visible range set in SQL, or pass range IDs into an IN clause.

4. Sprite HTTP caching

Where: Each visible range sprite is fetched individually via GET /ranges/{id}/pixels with no caching headers.

Why it hurts: 200 uncached HTTP requests per page load. Sprites rarely change but are re-fetched every time.

Fix: Add ETag or Last-Modified headers derived from the sprite's fact ID. Consider client-side persistence (Cache API or IndexedDB).

5. Verified dots full scan

Where: VerifiedDots() loads all participant entities, then filters to verified ones with coordinates. Each fires a LastTransition query.

Why it hurts: Thousands of participants over decades, scanned to find a handful of verified dots.

Fix: Filter on verified lifecycle state in SQL, not Go.

6. current_facts view degradation

Where: The current_facts view uses a correlated subquery — MAX(id) per (entity, attribute) pair.

Why it hurts: At 500k+ facts, the view scans significant index pages for every (entity, attribute) combination.

Fix: Maintain a materialized latest_facts table updated by the single-writer goroutine on each write. Cheap to maintain, big read-side payoff.

7. No viewport culling

Where: The map fetches all time-visible ranges regardless of viewport bounds.

Why it hurts: If the neighborhood grows or ranges span a wide area, the client loads ranges it can't see.

Fix: Pass viewport bounds to /ranges/active and add a bounding-box filter in SQL. No need for R-tree at neighborhood scale.

What already scales well

• Single-writer goroutine — no lock contention on writes.
• WAL journal mode — concurrent reads never block writes.
• Canvas rendering — 200 sprites with frustum culling is trivial for <canvas>.
• SSE invalidation — hub broadcast is cheap at neighborhood scale.
• Blob projection as length-only — pivot query never pulls sprite bytes.
• Append-only fact log — write path stays O(1) forever.

Priority

1. Push time filter into SQL (biggest win, unlocks everything else)
2. Batch per-range queries (eliminates N+1)
3. Filter tag memberships by visible range set
4. Add sprite ETag headers
5. Filter verified dots in SQL
6. Materialize current_facts (long-tail, only matters past 500k rows)
7. Viewport culling (nice-to-have at neighborhood scale)