Why fleet telematics databases fall over at a thousand vehicles

There is a specific failure we get called about often enough that it has a shape. A fleet platform works beautifully in the pilot. It works at a hundred vehicles. Somewhere between four hundred and a thousand, the live map starts taking eleven seconds to load, historical reports time out, and the database CPU sits at ninety percent with no obvious query to blame.

The instinct is to add an index, then a bigger instance, then a read replica. All three help briefly. None of them address the actual problem, which is that the system is storing time-series data in a table designed for transactional access, and querying it in a way that guarantees full scans.

The arithmetic nobody does upfront

Start with the volume, because it is rarely stated plainly during design. A thousand vehicles reporting position every ten seconds produces 8.64 million rows per day. Over a year that is roughly 3.1 billion rows in a single table. Each row carries a timestamp, coordinates, speed, heading, ignition state, and usually a dozen vendor-specific fields.

That table is now several hundred gigabytes. Every index on it is tens of gigabytes. The working set no longer fits in memory, so every query that touches historical data goes to disk. And crucially, the same table is serving both the live map — which wants the single most recent row per vehicle, right now — and the reporting layer, which wants millions of rows aggregated.

The live view and the historical view are different workloads with opposite access patterns. Putting them in one table means one of them is always being punished.

Three things that make it worse

The latest-position query

Almost every implementation asks for the newest row per vehicle with a correlated subquery or a window function over the whole positions table. On a billion-row table this is catastrophic, and it runs every few seconds because the map polls. The fix is boring and effective: maintain a separate current_position table with exactly one row per vehicle, updated on write. The live map never touches the history table again.

Buffered pings from dead zones

A vehicle leaves coverage for ninety minutes and returns with five hundred and forty buffered pings, which arrive in one burst, frequently out of order. Systems that assume ordered arrival compute speed between consecutive rows and produce phantom violations — a truck apparently doing 400km/h because two pings arrived transposed. Order by device timestamp, never by arrival time, and reconstruct trips as a separate pass rather than incrementally.

Unbounded historical queries

A report screen with no upper bound on date range is an outage waiting for a curious user. Someone will select 'all time' on the whole fleet. Bound it in the API, not the UI, because the UI is not the only caller.

The architecture that holds

The shape that works is not exotic, and it is substantially cheaper to run than the oversized instance it replaces.

• Ingestion through a streaming layer — Kafka, Kinesis or Redis Streams — so a burst of buffered pings queues rather than hammering the database directly, and so protocol adapters for mixed hardware normalise into one internal event shape.
• Time-series storage for history. TimescaleDB is the least disruptive option if you are already on Postgres: hypertables partition by time automatically, compression routinely reaches ten to twenty times on telemetry, and retention policies drop old chunks instantly instead of running a DELETE across billions of rows.
• A small, hot current_position table serving the live map, with a spatial index via PostGIS for geofence and proximity queries.
• Continuous aggregates for the metrics reports actually ask for — daily distance, idle time, harsh events per driver — computed incrementally rather than scanned on demand.
• Server-side clustering for the map, so a thousand markers become forty at low zoom. The browser is often the second bottleneck once the database stops being the first.

What to measure before you rebuild anything

Do not start with the architecture. Start with three numbers: the actual ping rate per device, the peak concurrent burst after a coverage gap, and the p95 latency of the live-map query under real load. We have seen teams plan a full re-platform when the entire problem was a missing composite index and a polling interval set to one second by a developer who has since left.

If the numbers do justify the rebuild, do it incrementally. Dual-write to the new store, run both in parallel until the reports reconcile exactly, then cut the reads over. A fleet platform is operational infrastructure — nobody gets to have a migration weekend where the trucks stop.