Skip to content

ADR 017: Track points as one 5 s file per track in object storage, no original files

  • Status: Accepted
  • Date: 2026-10-26
  • Deciders: Owner (agreed in the session about a 502 on trip saves, 2026-10-26)
  • Technical Context: apps/server (TrackFile, Track#points, ingestion, track editor, replay); behaviour in data-model.md § Track file, ingestion.md, track-editor.md; browser decoder app/javascript/track_view/track_file.js

Context

Every GPS point was one row in track_points (11 columns, three of them decimal, two indexes): 865 MB of a 1742 MB production database for 3.5 million points of a few beta users, plus 693 MB of original FIT/GPX files in object storage. A 1 Hz charter week adds about 600 000 rows. Every recompute loaded the rows of a track into Ruby (ActiveRecord objects, Time, BigDecimal): a trim or sail/engine edit of a 400 000-point track took 60 s in production, and slow trip saves ran into Thruster's 30 s limit (502).

Imports come at any rate (1 Hz Garmin, "smart recording", 20 s), the watch app always records every 5 s (track-sync.md). 1 Hz adds GPS jitter, not sailing information.

Decision

  1. Every source is normalized to the 5 s grid on ingest (FIT, GPX, API, SLTB): each point goes to the nearest multiple of 5 s; per slot the point nearest to it stays, with the mean SOG of the slot. Gaps stay gaps. From then on every calculation, the editor and the replay only see these points.
  2. One file per track in object storage (Active Storage tracks.points_file, bucket sailever-files), not rows: columnar, delta-encoded, gzip, about 6 bytes per point (format in data-model.md § Track file). Postgres keeps only trip, track, day, bridge, event and map line data.
  3. The browser reads the same file. The track editor fetches it as stored and decodes it with typed arrays (track_view/track_file.js); no JSON per point.
  4. Edits rewrite the file: a sail/engine edit and every SLTB segment read the file, change it and store a new one (the track row is locked meanwhile). Trims stay timestamps on the track; the replay and the public page only ever get the kept part, cut on the server.
  5. No original files. Uploaded FIT/GPX files are decoded and then deleted; the files of tracks imported before are deleted after the move (originals:purge in deploy:after). The watch's SLTB segments stay in Postgres as before (they are small and the sync needs them while a track is received).
  6. The track files are backed up every night with the database (TrackFilesBackup: one age-encrypted tar in sailever-backups, kept 30 days), because the files bucket keeps no versions and the points are no longer in the database dump.
  7. Moved in two deploys: first the files are written (new uploads) and backfilled from the rows (track_files:backfill in deploy:after) while the rows stay; after checking production, track_points is dropped and the original files are deleted.
  8. Considered and rejected: slimmer rows (float columns, fewer indexes: half the size, still a row per point); chunked rows with arrays per 10 minutes (fewer rows, but SQL over points goes anyway); keeping 1 Hz in the file (3× the size and work for GPS noise); keeping the originals (690 MB, personal data without use once the points are normalized).

Consequences

  • Production: about 1.6 GB less in database and files; backups shrink by the same share. A 1 Hz week is about 0.8 MB instead of 100 MB of rows.
  • Measured on a copy of production: trip days of a 466 000-point trip 17 s → 0.2 s; recompute of a 400 000-point track 18 s → 0.8 s; trip day totals unchanged (distances identical, hours +0.1 %).
  • Computed distances of 1 Hz imports drop by about 0.3 % (jitter); device distances are unchanged. Max speed is the 30 s mean, unchanged in meaning.
  • A track can't be rebuilt from an original at a finer resolution; a change to the derivation runs on the 5 s points.
  • No SQL over points any more (no row_number() thinning, no per-point queries): every reader decodes the file. Reads are cached per process by blob key.
  • Deleting a track or an account deletes its file with its Active Storage attachment (UnattachedBlobCleanupJob catches files of rows deleted by a cascade).