It usually starts with a screenshot. Someone in operations sends the live map with a van sitting in the middle of a field, or off the coast entirely, and asks whether the tracker is broken. It isn’t. The tracker is doing exactly what its firmware says it should. Nobody decided what a bad point looks like before the data was written down, so now there are eleven million rows and no way to tell the real ones from the noise.
By the time it reaches you it looks like a query problem. The trip report takes ninety seconds. The mileage total disagrees with the odometer. A geofence alert fires for a vehicle that never moved. Those are symptoms. The cause sits upstream, in the shape of the table and in the decision, made by default, to store every record the device sent as though it were true.
This post covers how I’d build a fleet GPS data pipeline that stays queryable as it grows: what the dirty data looks like at the source, how to split storage so cleaning stays reversible, how to keep the raw table from drowning in its own volume, and why nearly every question your users ask is a question about trips rather than points.
The four ways GPS data goes ugly before you query it
Organise your defences by failure family, not by field. Each of these produces rows that are individually well-formed and collectively wrong.
The device sends its last known position and calls it new
This is the one that bites hardest, because it is invisible. When a unit loses its fix, plenty of firmware does not send a null. Teltonika’s FMB documentation is explicit: when a record has no valid coordinates because there was no fix at acquisition time, the longitude, latitude and altitude fields carry the last valid fix, while angle, satellites and speed are set to zero. The row looks normal. It has a timestamp, it has coordinates, it parses.
That is worse than a gap. A van parked underground for six hours emits hundreds of records claiming the same coordinates, and your “last seen” logic reports it sitting at the entrance the whole time. Then it drives out, gets a fix elsewhere, and the trip builder draws a straight line through everything in between.
The signal you want is the satellite count and the dilution of precision, not the coordinates. Store both. Teltonika exposes a GPS Accuracy setting expressed as a minimum HDOP, PDOP or VDOP before the device refreshes its GPS data, plus a minimum satellite count, so the hardware can be told to be pickier before it transmits. Configure that, and still keep the fields, because you will want to re-derive the threshold later without re-collecting the fleet.
Null Island, and the reboot that gets you there
Zero latitude and zero longitude is a real place off the coast of Ghana, and it is where a surprising share of any fleet appears to live. The same Teltonika documentation notes that after a reboot the device sends zero coordinates. A naive pipeline stores that as a point, and every map, bounding box and distance calculation now carries an outlier several thousand kilometres from the fleet. Trivially filterable; it survives into production because nobody looks until the map is built, and by then the filter has to be retrofitted into six queries.
Records that arrive days late, in the wrong order
Telematics units buffer. A vehicle drives through a tunnel or a rural dead zone, keeps recording, and dumps the backlog on reconnect. Devices in this class also let you choose whether records are saved after a position fix, after a time sync, or always, which means you can receive records whose device clock was never synchronised at all.
So the timestamp on the record and the moment you received it are two different facts, and you need both. Storing one is the most common design mistake I see in fleet ingest. Keep only the device timestamp and you cannot answer “what did the dashboard show at 4pm yesterday”, which is what every dispute becomes. Keep only the receive time and your trips are wrong.
Parked-vehicle drift
A stationary receiver with a marginal sky view reports a slow random walk of a few metres at a time, all night, in a depot. Sum the distance between consecutive points and you have invented a three-kilometre trip. Multiply by a fleet and the mileage report is one nobody trusts.
Drift is not caught by the same rule as teleporting. A jump filter catches physically impossible movement. Drift is physically possible, it just isn’t real. You need ignition state, reported speed or an accelerometer movement flag to tell “not moving” from “moving very slowly in traffic”.
Split the fleet GPS data pipeline into three tables
The instinct is one positions table with cleaning applied on write. Resist it. Once you discard a record you cannot revisit the decision, and you will change your mind about the thresholds at least twice.
- Raw. Everything the device sent, as sent, plus a receive timestamp. Append-only, never cleaned in place.
- Clean. Points that passed your quality rules, with a geometry column. Derived from raw and fully rebuildable from it.
- Trips. One row per journey, with the whole path as a single geometry. This is what nearly every user-facing query reads.
Raw is what makes the rest safe. When a mileage figure is disputed eight months from now, you can re-run the cleaning under the old rules and the new ones and show the difference. Two timestamps, quality fields as first-class columns, and anything device-specific in JSONB so new hardware doesn’t force a migration:
CREATE TABLE gps_raw (
device_id text NOT NULL,
recorded_at timestamptz NOT NULL, -- the device's clock
received_at timestamptz NOT NULL DEFAULT now(),
lon double precision,
lat double precision,
speed_kph double precision,
heading double precision,
satellites smallint,
hdop double precision,
ignition boolean,
payload jsonb NOT NULL DEFAULT '{}'::jsonb
);
No primary key on device and time, deliberately. Devices resend. A unique constraint here turns a harmless duplicate into an ingest failure at three in the morning. Deduplicate on the way into the clean table, where a conflict is a no-op rather than an incident.
Making the raw table survive its own growth
A few hundred vehicles reporting every few seconds produces a table that ordinary PostgreSQL handles for a while and then doesn’t, usually right after someone adds a date filter to a dashboard. Time partitioning is the fix, either through native declarative partitioning or the TimescaleDB extension.
SELECT create_hypertable('gps_raw', 'recorded_at',
chunk_time_interval => INTERVAL '1 day');
Pick the chunk interval from your query pattern, not your ingest rate. If dashboards ask for “today” and “this week”, daily chunks let the planner exclude almost everything. If reporting is monthly, daily chunks leave a lot of small chunks to plan across.
Compression is where the storage argument is won, and the segment key is what people get wrong. Segmenting by vehicle groups each vehicle’s rows together inside a compressed batch, which is what keeps “one vehicle, one day” fast afterwards:
ALTER TABLE gps_raw SET (
timescaledb.compress,
timescaledb.compress_orderby = 'recorded_at',
timescaledb.compress_segmentby = 'device_id'
);
The licensing detail that catches you at runtime
TimescaleDB ships in two editions. The Apache 2.0 build gives you hypertables. Compression, continuous aggregates and retention policies live in the Community edition under the Timescale License, which is source-available rather than OSI-open-source. The vendor states the practical boundary plainly: as long as you are not offering TimescaleDB itself as a hosted database service, you can run the Community features free and self-hosted in production.
The trap is that this fails at runtime, not at install. If your distribution or provider ships the Apache-licensed module, CREATE EXTENSION succeeds, the hypertable works, and then the ALTER TABLE above returns an error saying the functionality is not supported under the current licence. Zabbix users have filed bugs about exactly this. Check the edition before you design around compression, especially on managed PostgreSQL where you don’t choose the build.
If that blocks you, native partitioning plus a scheduled job rolling old partitions into archive storage is a fine answer. More code, less magic, no licence question. The related fork is whether to self-host at all: a telemetry database is steady, disk-heavy and predictable, which is where a large VPS from Hetzner, Contabo or InterServer compares well against a managed hourly bill, and also where you inherit backups, upgrades and the extension build. My VPS versus managed AWS calculator prices your own admin hours against the managed bill and returns a break-even rather than an opinion.
Rollups, and the late records that quietly miss them
Continuous aggregates pre-compute the hourly numbers so dashboards stop scanning points:
CREATE MATERIALIZED VIEW vehicle_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
time_bucket(INTERVAL '1 hour', recorded_at) AS bucket,
count(*) AS fixes,
max(speed_kph) AS max_speed_kph
FROM gps_clean
GROUP BY device_id, bucket;
Now connect that to buffering. The refresh policy has a window, and anything older than its start is never revisited:
SELECT add_continuous_aggregate_policy('vehicle_hourly',
start_offset => INTERVAL '7 days',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');
A device out of coverage for ten days reconnects, replays its backlog, the rows land in the clean table, and the hourly rollup for those ten days stays exactly as wrong as it was. Raw is right, the dashboard is wrong, nothing errors. Set the start offset wider than your worst realistic backlog, and graph the gap between recorded_at and received_at per device model so you know what that worst case actually is. If Grafana already points at this database, it costs you one panel.
Cleaning: two filters that do most of the work
Promotion from raw to clean is where the quality rules live. Make it an idempotent batch over a time window so any period can be re-run without duplicating anything.
INSERT INTO gps_clean (device_id, recorded_at, geom, speed_kph)
SELECT device_id,
recorded_at,
ST_SetSRID(ST_MakePoint(lon, lat), 4326),
speed_kph
FROM gps_raw
WHERE recorded_at >= $1
AND recorded_at < $2
AND lat IS NOT NULL
AND lon IS NOT NULL
AND NOT (lat = 0 AND lon = 0)
AND satellites >= 4
AND hdop <= 5
ON CONFLICT (device_id, recorded_at) DO NOTHING;
Treat those numbers as starting points to tune against your own hardware, not constants. Four satellites is the minimum for a three-dimensional fix; the dilution threshold depends on your receivers and your operating environment. Write the chosen values somewhere a human will find them, because in a year someone will ask why a specific point was dropped.
The second filter is the jump test, which needs the previous row. Cast to geography so distance comes back in metres rather than degrees:
WITH stepped AS (
SELECT device_id, recorded_at, geom,
LAG(geom) OVER w AS prev_geom,
LAG(recorded_at) OVER w AS prev_at
FROM gps_clean
WHERE device_id = $1
AND recorded_at >= $2
WINDOW w AS (PARTITION BY device_id ORDER BY recorded_at)
)
SELECT device_id,
recorded_at,
ST_Distance(geom::geography, prev_geom::geography) AS metres,
EXTRACT(epoch FROM recorded_at - prev_at) AS seconds
FROM stepped
WHERE prev_geom IS NOT NULL
AND ST_Distance(geom::geography, prev_geom::geography)
/ NULLIF(EXTRACT(epoch FROM recorded_at - prev_at), 0) > 60;
Sixty metres per second is a little over 200 km/h. No delivery van does that, so anything above it is a bad fix, a clock problem, or two vehicles sharing a device identifier. Flag them rather than deleting them; the pattern tells you which of the three you have.
Trips are the abstraction that makes queries fast
Users don’t ask point questions. They ask trip questions: how far did this vehicle go, when did it enter the site, how long was it stopped, which vehicles passed through this area. Answering those from a point table means aggregating thousands of rows per answer, every time.
PostGIS has a native representation for this. A trajectory is a LINESTRING whose M dimension carries time, with M increasing from each vertex to the next, and ST_IsValidTrajectory checks exactly that. Once your paths are valid trajectories the spatio-temporal functions come free: ST_ClosestPointOfApproach returns the measure at which two moving objects were nearest, ST_DistanceCPA returns how near, and ST_CPAWithin gives the boolean. That turns “were these two vehicles ever within fifty metres of each other” into one function call instead of a self-join across a point table.
CREATE TABLE trips (
trip_id bigserial PRIMARY KEY,
device_id text NOT NULL,
started_at timestamptz NOT NULL,
ended_at timestamptz NOT NULL,
path geometry(LineStringM, 4326) NOT NULL,
distance_m double precision NOT NULL,
point_count integer NOT NULL
);
CREATE INDEX trips_path_gix ON trips USING GIST (path);
CREATE INDEX trips_device_time_idx ON trips (device_id, started_at DESC);
Building the path is an aggregate over the clean points, with epoch seconds as the M value:
SELECT ST_MakeLine(
ST_MakePointM(ST_X(geom), ST_Y(geom),
EXTRACT(epoch FROM recorded_at))
ORDER BY recorded_at
) AS path
FROM gps_clean
WHERE device_id = $1
AND recorded_at >= $2
AND recorded_at < $3;
Segmenting points into trips is a business decision dressed as a technical one. Ignition on to ignition off is cleanest where the wiring supports it; otherwise speed below a floor for longer than a gap threshold ends the trip. Whichever you pick, store the rule as a column on the trip, because someone will change it and you’ll need to know which rows were built under which version.
For rendering, keep a simplified two-dimensional copy. ST_SimplifyPreserveTopology runs Douglas-Peucker without collapsing the line into nonsense, and a route drawn at city zoom doesn’t need a vertex every two seconds. Simplify for display only, never for the geometry you measure distance from.
Map matching: when to snap, and when to skip it
Snapping traces to the road network fixes a cosmetic problem, paths wandering across the pavement, and a real one, distance summed from raw points overstating the journey. Valhalla’s Meili implements the well-known hidden Markov approach to matching noisy, sparse traces. Its API exposes two actions: trace_route returns the shape snapped to the road network with directions, and trace_attributes returns per-segment attribution along the matched path. Requests should be POSTed, because an encoded trace gets large. OSRM’s match service solves the same problem with a similar model.
curl -s -X POST https://valhalla.internal.example/trace_attributes
-H 'Content-Type: application/json'
-d '{
"shape": [
{"lat": 52.0907, "lon": 5.1214},
{"lat": 52.0912, "lon": 5.1229}
],
"costing": "auto",
"shape_match": "map_snap"
}'
Two things decide whether it’s worth running. First, trace density: Valhalla’s own guidance is to keep points roughly between one per second and one per ten seconds, and warns the match degrades as that spacing widens. If your devices report every two minutes to save data, matching will confidently snap you to plausible roads you never used, which is worse than an obviously wobbly line because it looks correct.
Second, what the number is for. Billing, driver disputes and compliance want the matched path. An internal ops map does not. Matching is either a per-trip network call or a self-hosted routing service with a tile build to maintain, and it’s the most operationally expensive piece in this design. If no money rides on the number, skip it and say so.
Self-hosting, Valhalla and OSRM both run fine in containers against an OpenStreetMap extract, on a separate box from the one serving queries because the tile build is memory-hungry. Worth pasting that stack into my Docker Compose production checker first, since routing containers are exactly the kind of thing that quietly exposes a port or writes tiles to a volume that dies on restart.
Geofences that actually use the index
Geofence queries are where a working platform turns slow, and the cause is nearly always a function that can’t use the spatial index. ST_Intersects, ST_Within and ST_DWithin are index-assisted. ST_Distance is not. Writing WHERE ST_Distance(a, b) < 500 gets the right answer by computing distance for every candidate row.
-- Polygon fences: which trips crossed a depot this week
SELECT g.name, t.trip_id, t.device_id
FROM trips t
JOIN geofences g ON ST_Intersects(t.path, g.area)
WHERE t.started_at >= now() - INTERVAL '7 days';
-- Radius fences: within 500 m of a point, using the index
SELECT t.trip_id
FROM trips t
WHERE ST_DWithin(t.path::geography, $1::geography, 500);
Running fences against trips rather than points is the other half of the win. A hundred fences against ten million points is a hundred million comparisons. A hundred fences against fifty thousand trip geometries, each with a bounding box the index rejects in one step, is a different query entirely.
The catch is latency. Trips only exist once they end, so live entry and exit alerts still run against incoming points. Keep that path separate and deliberately small: active fences only, evaluated as records arrive. Don’t make one query serve both the live alert and the monthly report.
Troubleshooting a fleet data platform that has gone wrong
Mileage disagrees with the odometer. Compare summed point-to-point distance against the matched-path distance for the same trip. Consistently higher means drift or noise, so tighten the quality filter and check whether stationary periods are included. Lower means missing points, with gaps bridged by straight lines.
A dashboard number is wrong but the raw data is right. Suspect the rollup window first. Query the raw table and the continuous aggregate for the same bucket and compare. A difference means late data outside the refresh offset.
A vehicle sits still for hours, then teleports. Check satellites and HDOP across the stationary block. Zero satellites throughout means repeated stale fixes, not a parked vehicle.
Queries slowed suddenly with no traffic change. Check whether the planner is excluding chunks. A predicate wrapped in a function on the time column, or a parameter type that doesn’t match, prevents partition pruning and silently turns a one-day scan into a full-table scan. Run EXPLAIN and count the chunks in the plan.
Two vehicles occupy the same position. Almost always a device moved between vehicles without the assignment history being updated. Model the device-to-vehicle link as a table with validity intervals, not a column on the vehicle.
One failure worth rehearsing before it happens is the ingest endpoint going down. Devices buffer, so a short outage survives and a long one becomes a thundering replay on recovery. If you’ve never asked what breaks when the database node dies mid-shift, my cloud architecture assessment scores where the single points of failure sit.
Common mistakes
- Cleaning on ingest and keeping nothing else. The rules will change and you’ll have no way to apply them retroactively.
- Storing latitude and longitude as plain numeric columns. Fine until the first spatial query, then you’re building indexes on a table you can’t afford to lock.
- Treating the device timestamp as authoritative. Unsynchronised clocks and buffered replays make it a claim, not a fact.
- Using
ST_Distancein a WHERE clause instead ofST_DWithin. Same answer, no index. - Designing around compression or continuous aggregates without checking which TimescaleDB edition is installed.
- Map matching a sparse trace and trusting the output. Confident wrong answers are harder to catch than obvious ones.
- Putting live alerting and historical reporting through the same queries.
Best practices
- Keep raw append-only and every downstream table rebuildable from it. Test that rebuild before you need it.
- Store both timestamps on every record, and alert on the lag between them per device model.
- Keep the quality fields after filtering on them. Satellites and HDOP are how you re-derive a threshold later.
- Match the chunk interval to how people query, and revisit it when the reporting cadence changes.
- Answer user questions from trips. Reach for points only when you genuinely need sub-trip resolution.
- Record the segmentation rule and quality thresholds as data, not as code comments.
- Retire raw data on a written retention schedule rather than when the disk fills.
Frequently asked questions
Do I need TimescaleDB, or is plain PostgreSQL enough?
Plain PostgreSQL with native partitioning and PostGIS carries a small fleet a long way, with no licensing question attached. TimescaleDB earns its place when you want compression and continuous aggregates without maintaining that machinery yourself. It saves you code, not capability.
Should I use PostGIS geometry or geography for GPS points?
Store as geometry in SRID 4326 and cast to geography for distance and radius work. Geometry has broader function coverage and cheaper operations; geography gives metres on a sphere instead of degrees. Casting at the point of use is the pragmatic middle.
How long should I keep raw GPS points?
Long enough to cover the dispute window your business actually faces, which is driven by employment law, insurance or customer contracts rather than by engineering. Decide it with whoever owns that risk, write it down, enforce it with a retention policy. Trips are far smaller and usually worth keeping much longer.
What about ClickHouse or InfluxDB instead?
Both are genuinely strong at high-volume ingest and time-range scans, and for a purely time-series workload I’d take either seriously. The reason I reach for PostgreSQL here is that fleet data isn’t purely time-series: it joins constantly to vehicles, drivers, contracts and sites, and it needs spatial predicates in the same query as the time filter. One database beats two and a reconciliation job. If your spatial needs are trivial and your volume is enormous, that flips.
Is MobilityDB worth adding?
MobilityDB extends PostgreSQL and PostGIS with proper moving-object types, tgeompoint and tgeogpoint, that interpolate between observations so you can query a position at any instant. It’s an OSGeo project and the modelling is better than hand-rolled trajectories. The cost is another extension to install and upgrade, including shared_preload_libraries and locking configuration on the server. I’d add it when trajectory analysis is the product, not when it’s a reporting feature.
How do I stop parked vehicles generating fake mileage?
Don’t rely on distance alone, because drift is physically plausible. Use ignition state or a movement flag to mark stationary periods, exclude those points from distance summation, and keep them in raw so the stop duration stays auditable.
Where does a fleet GPS data pipeline break first at scale?
The dashboard queries, and nearly always for the same reason: something in the predicate stopped the planner pruning partitions. Before adding hardware, run EXPLAIN and confirm how many chunks the query touches. Adding vehicles rarely breaks a partitioned table. Bad predicates always do.
The one thing worth remembering
A fleet GPS data pipeline stays queryable because of two decisions made early, not because of the database you picked. Keep the raw record untouched so every cleaning rule stays reversible, and answer questions from trips rather than points so the expensive aggregation happens once instead of on every page load.
Everything else is tuning. Chunk intervals, compression settings and index choices all matter, and all of them are recoverable. Throwing away the record of what the device actually sent is the only mistake here you can’t undo later.
Where I can help
Most fleet data work arrives as a number somebody disputes, and the fix lives three layers upstream of the report. Things I’d take on here:
- Reviewing an existing positions schema and working out what it takes to make it partition-friendly without a full rewrite.
- Designing the raw, clean and trip split, including the idempotent promotion job so any window can be safely re-run.
- Tracking down why a rollup and the raw data disagree, which is usually late records falling outside a refresh window.
- Building quality filters against your actual hardware, so thresholds fit your receivers rather than someone else’s blog post.
- Getting geofence and trip queries onto the spatial index, with the EXPLAIN output to show what changed.
- Standing up self-hosted map matching, and deciding honestly if you need it at all.
If any of that is live for you, send the concrete thing: a table definition, the EXPLAIN output from the slow query, or a day of records from one device. That’s usually enough to say something useful straight away.