Performance & scale¶
fxquinox stores every entity as a row in one universal record table, with
entity-specific fields in a JSON data column. This page describes how that
scales and the knobs that keep large projects fast.
Indexes¶
Structural fields are real columns and carry indexes tuned to the queries the app actually issues:
| Index | Columns | Serves |
|---|---|---|
ix_record_type_active |
entity_type, active |
the ubiquitous "all live X" listing |
ix_record_type_project |
entity_type, project_id |
a project's shots / tasks / versions |
ix_record_type_status |
entity_type, status |
board columns and status filters |
Postgres applies these via Alembic (alembic upgrade head); SQLite dev builds
them directly. JSON data fields (e.g. step) are filtered with a coerced
column expression — fast enough at the scales below, with per-field expression
indexes available later if a specific field becomes hot.
Benchmark¶
scripts/bench_query.py seeds a configurable dataset and times the hot query
shapes. Run it to track regressions:
Representative results on 22,000 records (20k tasks across 4 projects), SQLite, best of 5, returning up to 1,000 rows:
| Query | Time |
|---|---|
| type + status (indexed) | ~7.6 ms |
| type + step (JSON field) | ~12.8 ms |
| project-scoped tasks (indexed) | ~7.7 ms |
link traversal task → shot.code |
~43 ms |
| paginated page 10 (offset 1000) | ~1.2 ms |
EXPLAIN QUERY PLAN confirms the hot path uses the composite index
(SEARCH record USING INDEX ix_record_type_status). Postgres with JSONB is
faster still on the JSON-field and traversal cases. A test
(tests/test_performance.py) pins index coverage and the limit cap so a
regression fails CI.
Result-set limits¶
Queries are capped at 1,000 rows per request (MAX_QUERY_LIMIT) so a single
call can't pull an unbounded set into memory. The web grid fetches exactly this
page size. Use offset for further pages.
Known limits (post-1.0 roadmap)
- Keyset/cursor pagination beyond the 1,000-row page is not yet wired; offset pagination is available in the meantime.
- Grid virtualization — the data grid renders the full fetched page in the DOM. At the 1,000-row cap this is fine; row virtualization is the planned next step for very large single-view result sets.