Working in dev technosys, on a fleet tracking platform and hit a wall that I suspect is a solved problem we’re solving badly.
Setup: vehicles report position + basic diagnostics over MQTT every 10 seconds. Around 400 vehicles in the pilot. Everything lands in Postgres, and the ops dashboard renders a live map of current vehicle positions.
The map is what’s breaking. Getting “latest position per vehicle” means a DISTINCT ON (vehicle_id) ... ORDER BY vehicle_id, recorded_at DESC against a table that’s now several hundred million rows. It was fine at 50 vehicles. At 400 it takes 3-4 seconds, and the dashboard polls every 15.
Obvious fix is separating current state from history — last-known position in Redis, full trail in a time-series store (looking at TimescaleDB since we’re already on Postgres). What I’m unsure about:
- Where does the write fan-out happen? Ingestion worker writes to both Redis and the time-series table in the same handler, or publish to a queue and let two consumers handle it independently? The second is cleaner but adds a failure mode where the map and the history disagree.
- Duplicate handling. Devices buffer during connectivity gaps and flush on reconnect, so we get replays — sometimes hours late, out of order. We dedupe on
(device_id, event_id)at insert, but a late-arriving event with an old timestamp shouldn’t overwrite a newer cached position. Currently we compare timestamps before the Redis write, which feels fragile under concurrency. - Geofence evaluation. We check zone containment on the ingestion path with PostGIS. GPS jitter at boundaries generates entry/exit pairs continuously — we’ve added a 3-consecutive-readings debounce, which mostly works but delays legitimate transitions.
Has anyone run this shape in production? Specifically curious whether the Redis-plus-time-series split is worth the operational complexity at a few hundred vehicles, or whether it’s premature and a materialised view refreshed on a short interval would carry us further than I think.