Chart Resync#
Overview#
Chart Resync is a BullMQ system-queue job (resyncCharts) that fires daily at midnight (0 0 * * *) to recalculate snapshot metrics for three charts: Drive, Notes, and Users. Its purpose is to correct drift that accumulates from CASCADE deletes and other database-level changes that the application layer cannot observe.
Execution Flow#
Entry point: ResyncChartsProcessorService.process()
The three charts are run sequentially (not with Promise.all) to avoid concurrent DB connections:
driveChart.resync() → notesChart.resync() → usersChart.resync()
Each resync() call delegates to Chart.tick(major=true), which calls the chart's tickMajor() abstract method and writes the result to both the hourly and daily log tables.
What each tickMajor does#
| Chart | tickMajor behavior |
|---|---|
| DriveChart | Returns {} — no DB query; drive metrics are maintained only through incremental updates. |
| NotesChart | Issues two COUNT queries on the note table: one for local notes (userHost IS NULL) and one for remote. Writes local.total and remote.total. |
| UsersChart | Issues two COUNT queries on the user table: one for local users (host IS NULL) and one for remote. Writes local.total and remote.total. |
Known Performance Issue: TypeORM Composite Index Column Order#
Problem: Chart tables for grouped charts define a composite unique index declared as ['date', 'group'].
The getLatestLog() helper fetches the most recent log for a group by filtering on group only and ordering by date DESC:
SELECT ... WHERE group = $1 ORDER BY date DESC LIMIT 1
For this query to use an index seek, the index must lead with group. However, TypeORM does not preserve composite index column order, so the index can end up created with the wrong leading column — forcing PostgreSQL into a full table scan for every getLatestLog() call.
Impact: On large instances with many groups, queries that should take ~0.2 ms reportedly take ~11 seconds per group. This degrades not only the midnight resync job but all incremental chart updates, because getLatestLog() is called inside claimCurrentLog() on every save() and tick() invocation.
Known fix: Reordering the index definition to lead with group (i.e., ['group', 'date']) drops the per-group query time from ~11 s to ~0.2 ms. A proposed patch is under investigation.
Statement Timeout Risk#
PostgreSQL connections are configured with a statement_timeout of 10 seconds. The COUNT(*) queries in NotesChart and UsersChart scan potentially millions of rows. On large instances — especially compounded by the index issue above — these scans can exceed the timeout and abort the resync job before all charts complete.
Related Files#
| File | Purpose |
|---|---|
ResyncChartsProcessorService.ts | Job processor; sequential execution of all three resync calls |
core/chart/core.ts | Base Chart class: resync(), tick(), getLatestLog(), claimCurrentLog(), index definitions |
chart/charts/notes.ts | NotesChart.tickMajor() — COUNT queries on the note table |
chart/charts/users.ts | UsersChart.tickMajor() — COUNT queries on the user table |
chart/charts/drive.ts | DriveChart.tickMajor() — no-op |
QueueService.ts | Cron schedule definition (0 0 * * *) |
postgres.ts | statement_timeout = 10 s connection config |
Open Issues#
- #17772 — TypeORM composite index column order bug causing full table scans in grouped chart queries.
- Per-user and per-instance chart resyncs are not yet implemented (marked
TODOin the processor).