You are storing the track and you cannot draw it
Every accepted ping goes in. One row each, no thinning, four columns, no indexes — not even on the foreign key — in a codebase that indexes another table nine ways. And the product it was collected for draws three markers and no line.
On this page
- The asymmetry inside one codebase
- Why nobody noticed: the reader is exported and never called
- The client draws three markers, not a track
- How much does the missing index cost?
- The advice I was about to give you here was wrong
- What should I actually do with mine?
- The index, and the algorithm, concretely
- FAQ
- The four things worth taking away
TL;DR: The location history table is the fastest-growing object in a tracking system and the last one anybody optimises, because for the first year nothing reads it. Ours takes one row per ping with no thinning, has four columns, declares no indexes at all — not even on the foreign key it is always filtered by — and sits in a codebase that indexes the orders table nine ways including a spatial one. The index was never missed because the query that would have needed it is exported and called from nowhere.
There is a particular kind of technical debt that accrues without ever causing a symptom, and this is the cleanest example of it I have.
We collect a position every time a driver's device reports one and the order is in an active state — the endpoint rejects a report outside that window, and rejects anyone who is not the assigned driver. Everything that passes those two gates becomes a row: its own id, the order id, the point, and the recorded-at timestamp — four columns, and that is the entire table. Nothing is coalesced, nothing is downsampled, nothing is dropped.
The write itself is careful — the same transaction as the live position, for the reason the previous piece goes into.
And then nothing reads it.
The asymmetry inside one codebase
The same repository that leaves this table bare declares nine indexes on the orders table, one of them a spatial GIST index. So this is not a team that does not know about indexes, or a codebase where nobody thought about query plans. It is the same people, the same week, making a completely different decision about two tables — and the difference between the tables is not size or importance. It is that one of them was being queried during development and the other was not.
The foreign key gets no index either, which surprises people who assume a constraint implies one. It does not: PostgreSQL's documentation states plainly that it is often a good idea to index the referencing columns of a foreign key, precisely because the database does not do it for you. A constraint is about integrity; an index is about access. The ORM adds the first and says nothing about the second.
Why nobody noticed: the reader is exported and never called
Here is the detail that makes this a story rather than a scolding.
A history query does exist. It selects position and recorded-at for one order, newest first, capped at fifty rows. It is written, it is exported from the service module, and it is called from nowhere — no route, no controller, no scheduled job. So the missing index was never a slow query, because there was never a query. The table has been append-only in the strictest sense: written on every ping, read by nothing, growing at exactly the rate the fleet reports.
That is why this class of debt is invisible. There is no P95 to watch climb, no timeout in a log, no dashboard going red. The first person to feel it will be whoever finally writes the feature the data was collected for, and they will experience it as "the history endpoint is slow", which is a symptom three years downstream of the decision.
The client draws three markers, not a track
The storage half has a mirror on the client, and together they close the loop.
There is no drawn track. The route view renders three independent markers — origin, destination, and one current position — with no line between them. The component holds a single point in state, and every inbound position update overwrites it. Nothing is appended, so no polyline could be rendered even if someone added the component tomorrow, because the client keeps no history in memory either.
So the full shape is: capture everything, store everything, index nothing, read nothing, display three dots. Every individual decision along that path is defensible. The composition is a table that grows forever to serve a feature that does not exist.
How much does the missing index cost?
I cannot answer that for the system this article is about — nobody took the measurement, and a projection dressed up as a benchmark is the exact failure I am warning about. But the general question is cheap to answer on a table shaped like that one, so I did.
A throwaway Postgres, a synthetic history table with the same four columns, and the query the missing
reader would have run: the newest fifty fixes for one order. Warm cache, thirty executions against
thirty different orders, median reported. order_id is written in contiguous blocks, which is what an
append-only ping table looks like on disk and which hands the unindexed arm perfect locality — so
the gap below is a floor, not a ceiling.
| Rows | No index | (order_id) |
(order_id, recorded_at) |
Table |
|---|---|---|---|---|
| 100 k | 2.4 ms | 0.045 ms | 0.041 ms | 6.5 MB |
| 1 M | 15.0 ms | 0.130 ms | 0.073 ms | 65 MB |
| 10 M | 115 ms | 0.050 ms | 0.036 ms | 651 MB |
The shape is the finding, not the milliseconds. Unindexed cost is a function of the table; indexed
cost is not. Every row added makes the query slower for every order, including orders written years
earlier, because there is no access path and the only plan available is to read the whole table — the
planner says Seq Scan at all three sizes. The indexed arms do not move at all, because they never
look at more rows than the one order has.
So the answer to "when does it start to hurt" is not a row count. It is the moment somebody writes the feature, and by then the query is already as slow as the table is large.
The advice I was about to give you here was wrong
I was going to tell you the composite (order_id, recorded_at) beats the plain foreign-key index and
makes it redundant. At two hundred fixes per order the two are level — within the run-to-run noise of
this harness — because sorting two hundred rows to take fifty is free, so the composite saves nothing
it can be credited for. Vary that number instead of the table size and the picture separates:
| Fixes per order | (order_id) |
(order_id, recorded_at) |
|---|---|---|
| 200 | 0.050 ms | 0.036 ms |
| 2 000 | 0.309 ms | 0.047 ms |
| 20 000 | 2.83 ms | 0.090 ms |
The composite's advantage is not a property of the index. It is a property of how many rows one entity has, and it is worth little until that number reaches the thousands. A fleet whose orders close within a day and ping once a minute never gets there; one that tracks a vehicle rather than a delivery gets there in a fortnight.
Which is a better rule than the one I started with: the table decides whether you need an index; the entity decides which one.
- no index
- composite (order_id, recorded_at)
What should I actually do with mine?
There is a fork here that every tracking system reaches, and the thing to understand is that not choosing is itself a choice — the expensive one, made by default.
Full fidelity. Keep every fix. You need this if the track is ever evidence: a delivery dispute, an incident reconstruction, an insurance claim, anything where "we downsampled it" is an unacceptable answer. It is also the only option that lets you change your mind later, because you cannot recover detail you discarded. The cost is unbounded growth and a table whose access patterns you must actually design.
Downsampled. Thin on write or on a schedule — by distance, by time, or by keeping the points that change the shape of the path and dropping the ones that do not. This is what you want if the track is for display, because a map cannot show more resolution than its pixels anyway. The cost is that the discarded detail is gone, so this is a decision to make deliberately and not one to discover you made.
Both, tiered. Full fidelity in a hot window, downsampled beyond it, and the raw tier expiring on a schedule. More machinery, and usually the right answer once the system matters.
| Keeps | Costs | Pick it when | |
|---|---|---|---|
| Full fidelity | every fix, forever reinterpretable | unbounded growth, and you must design the access path | the track is ever evidence |
| Downsampled | the shape, not the detail | the discarded points are gone | the track is for display |
| Tiered | both, at different ages | two paths and a scheduled job | the system already matters |
| Not deciding | everything | all of full fidelity's costs, none of its benefits | never — this is the default you fall into |
Whichever you pick, decide the retention at the same time, because an append-only table with no expiry is not a design, it is a deferral.
Retention is two decisions, and the first is not technical. How long is a track evidence? That is a dispute window, a claim period, or a contractual term, and somebody in the business knows it — a delivery disagreement surfaces within days, an insurance claim within months, a contractual audit possibly within years. Pick the longest one that actually applies and you have your horizon; guess and you will either keep everything forever or delete the row somebody needed.
The second is the mechanism, and a nightly delete is the wrong one at scale:
-- the reflex: a long transaction, and the table stays the same size
DELETE FROM location_logs WHERE recorded_at < now() - interval '90 days';
-- the one that returns the space, in constant time
DROP TABLE location_logs_2026_05;The delete takes a long transaction, leaves dead tuples for autovacuum to chase, and does not shrink the table — the space is reused, not returned. Partitioning by time turns expiry into a metadata operation instead: constant time, no bloat, and the filesystem gets the space back the moment the partition drops. Like the index, this is far cheaper to set up before the table is large than to retrofit afterwards.
ST_Simplify's tolerance is in the geometry's units, not metres
10 means ten degrees, roughly eleven hundred kilometres, and it will reduce a national track to its endpoints. And the reflex fix, EPSG:3857, has the same bug one order of magnitude smaller: Web Mercator’s units are called metres and stretch by 1/cos(latitude), so a tolerance of 25 is 25 m at the equator and 15 m at 52°N. Use a local UTM zone or your national grid.The index, and the algorithm, concretely
Two things I would rather have been handed than reasoned out, so here they are.
The index is composite and ordered — (order_id, recorded_at) — once you know the entity carries
thousands of rows, which is the qualification the measurement above forced onto this paragraph. It
serves the entity filter, the time range and the sort from one structure, and it also serves the plain
entity lookup. You do not need DESC on the time column, which is the reflex — a btree scans
backwards, so an ascending index already serves "most recent first"; explicit DESC only earns its
keep in a multicolumn sort of mixed direction. This is the index a foreign-key constraint gestures at
and does not create.
But it is a trade, not a free win, and the storage side is where it bites. At ten million rows the
plain (order_id) index is 65 MB and the composite is 301 MB — four and a half times larger for two
columns instead of one. The cause is btree deduplication: order_id repeats two hundred times per
value, and Postgres 13 and later collapse those into one entry with a posting list. Add a
near-unique second column and there is nothing left to collapse.
Two mechanisms, and the run splits them. Building the plain (order_id) index with
deduplicate_items = off gives 215 MB, so of the 236 MB gap deduplication accounts for 150 MB —
about two thirds. The remaining 86 MB is the second column itself, and it lands within a rounding
error of 8 bytes × 10,000,000 rows. The same run measured the table's own primary key, a single
undedupable bigint, at 214 MB: an index on a near-unique column at this row count costs what
arithmetic says it should, and deduplication is the only reason the single-column index is cheap.
And "adding it later costs a maintenance window" was an overstatement, which the same run caught.
The composite builds on a populated ten-million-row table in about 1.8 seconds. What it actually
costs is not time but the SHARE lock CREATE INDEX holds against writes for the duration — which is
why CONCURRENTLY exists, and why the honest advice is "cheap, but take the lock into account", not
"schedule an outage".
Shape-preserving thinning has a name. If you downsample for display, the naive approaches — every
Nth point, or one per fixed interval — both destroy exactly the parts of a track that carry
information, because a vehicle at a standstill emits the same points repeatedly while a vehicle
turning a corner emits the points that define the path. What you want is line simplification: the
Ramer–Douglas–Peucker algorithm keeps the points whose removal would move the rendered line by
more than a tolerance you choose, and discards the rest. PostGIS exposes it as
ST_Simplify.
Two things about it that cost me time, and neither is in the one-line version:
The tolerance is in the units of the geometry's coordinate system, not metres. If your points are
stored as lat/lng — which is what most trackers do — then a tolerance of 10 means ten degrees,
roughly eleven hundred kilometres, and it will collapse a national track to its endpoints. Transform
to a metric projection first, then pick the tolerance against the zoom level you actually render at.
And the obvious metric projection has the same bug, one order of magnitude smaller. The reflex is
ST_Transform(geom, 3857), because Web Mercator's units are called metres and every web map is in it.
They are not ground metres. Mercator stretches by 1/cos(latitude), so a tolerance of 25 is 25 m at
the equator, 15 m at 52°N, and 10 m at the Arctic Circle — the same tolerance thins a Norwegian
track more than twice as hard as a Kenyan one, and nothing anywhere says so. Use a locally
appropriate projected CRS instead: a UTM zone, or your national grid.
And with one point per row you have to build the line before you can simplify it. ST_Simplify
takes a geometry; a single Point simplifies to itself. So the shape is: order the rows, ST_MakeLine
them, simplify the result — transforming to metres on the way in and back on the way out:
-- 32631 is UTM 31N: metres that are metres, for tracks around 3°E
ST_Transform(ST_Simplify(ST_Transform(line, 32631), 25), 4326)Pick the zone your fleet actually drives in, and if it drives across several, that is its own decision — one zone for the whole fleet with a known error, or per-track selection.
That gives you a display artefact — deciding which rows to delete from the table is a separate problem, and a harder one, because the algorithm returns a geometry rather than the identifiers of the points it kept.
FAQ
Is one row per ping actually wrong? No, and that is worth being clear about — full fidelity is a legitimate and often correct choice. What is wrong is arriving at it by not deciding, because then you get its costs without its benefits: the growth of a full-fidelity table, without the retention policy, the indexing, or the downstream feature that justified keeping everything. The failure is the absence of the decision, not the option.
When does the missing index actually start to hurt? When the first real reader appears, which is usually a feature request rather than an incident. Until then the table is written and never scanned, so the plan that would be slow is never produced. This is why the debt is invisible: it is not degrading anything today, and it will present as a new feature being unexpectedly hard rather than as an existing thing breaking. There is no row count to watch for, which is the uncomfortable part — the cost is already fully accrued at whatever size the table happens to be on the day someone queries it, and on our harness that was 115 ms at ten million rows against 0.04 ms with the index.
Should the client keep position history in memory? Only as much as it draws. The useful rule is that the client should hold exactly the window it renders and refetch the rest, because a client-side buffer that grows for the length of a session is a second copy of the same unbounded-growth problem with a worse eviction story. If you want the full track, that is a query.
How do we tell whether our own history table has a reader? Search for the query, then search for its callers, and treat "exported" as no evidence at all. A function that is exported and uncalled looks identical to a live one in every listing, in every autocomplete, and in most code review. The call graph is the only thing that answers it, and it is worth checking before you plan a retention policy around a read pattern nobody actually performs.
The four things worth taking away
- The fastest-growing table gets optimised last, because nothing reads it yet — so the missing index is never a slow query, and the debt is invisible until someone builds the feature it was collected for.
- A foreign key is not an index. The constraint is about integrity; the access path is yours to
declare, and for a history table it is
(entity, time). - Not choosing between full fidelity and downsampled is choosing full fidelity — with none of the retention, indexing or downstream feature that would have justified it.
- Decide retention as a horizon plus a mechanism, and partition by time so expiry is a
DROPrather than a delete that leaves the table exactly as large as it was.
The system this table belongs to, and the three other places it goes wrong before anyone notices, is written up on freight telemetry that survives a bad link.
If you are carrying a table like this and want to know what it will cost before it becomes urgent, that is a conversation we have often.
