A 15-minute FPV flight is 9,000 GPS points. My ingest path ran 40 million distance calculations to keep one running total up to date.

Last time, I wrote about SearchWork, where normalizing eleven job-board APIs turned out to be harder than any of the LLM work. This one is about a different project and a different lesson: the difference between a slow query and a slow algorithm, and why adding indexes fixes only one of them.

The project is FLYON - a web platform for drone and FPV owners. It takes live telemetry from a ground station, draws the flight on a map, warns you when you enter a no-fly zone, and scores the flight afterwards. Node/Express, PostgreSQL with PostGIS, WebSockets for the live view, Next.js on the front.

The part that broke is the part I thought was trivial.

9,000 points

The ground bridge is a small Python process that sits between the radio link and the API. Here is the entire shape of it:

while True:
    packet = read_telemetry()
    requests.post(f"{API}/telemetry", json=packet, headers=auth)
    time.sleep(0.1)   # send every 100ms

100 ms. That's 10 Hz, which is a completely ordinary rate for flight telemetry - DJI's own SDK will happily give you 5–10 Hz, and Betaflight's blackbox logs are far denser than that.

A 15-minute flight at 10 Hz is 9,000 points. That number matters, because nothing in the system was designed around it. I had tested with a simulator that sent one point per second. 900 points. Everything was fine at 900 points.

What one telemetry point actually costs

Here is the ingest handler, trimmed to its structure:

export async function ingestTelemetry(droneId, userId, input) {
  const validated = validateTelemetryInput(input);
  const position = createPoint(validated.latitude, validated.longitude);

  // ... find or create the flight session ...

  await query(
    `INSERT INTO telemetry (flight_id, drone_id, timestamp, position, ...)
     VALUES ($1, $2, $3, ST_GeomFromText($4, 4326), ...)`,
    [...]
  );

  broadcastTelemetry(flight.id, { ... });

  updateFlightStats(flight.id).catch(...);          // fire and forget
  checkDangerZones(flight.id, lat, lon, alt, userId).catch(...);
  checkBatteryAndRTH(...).catch(...);
}

One insert, one WebSocket broadcast, three background jobs. That reads fine. The .catch() calls even look responsible — a failing analytics job shouldn't take down telemetry ingestion.

The problem is updateFlightStats. Here it is, in full, with the noise removed:

export async function updateFlightStats(flightId: string): Promise<void> {
  // 1. aggregate over every point in the flight
  const statsResult = await query(
    `SELECT COUNT(*) as point_count,
            MIN(timestamp), MAX(timestamp),
            MIN(altitude_meters), MAX(altitude_meters),
            MAX(speed_mps), MIN(battery_percent)
     FROM telemetry WHERE flight_id = $1`, [flightId]);

  // 2. total distance: window over every point in the flight
  const distanceResult = await query(
    `WITH ordered_telemetry AS (
       SELECT position, timestamp,
              LAG(position) OVER (ORDER BY timestamp) as prev_position
       FROM telemetry WHERE flight_id = $1 ORDER BY timestamp
     )
     SELECT COALESCE(SUM(
       CASE WHEN prev_position IS NOT NULL
            THEN ST_Distance(position::geography, prev_position::geography)
            ELSE 0 END), 0) as total_distance
     FROM ordered_telemetry`, [flightId]);

  // 3. + 4. first and last position, two more queries
  // 5. UPDATE flights SET ...
}

Read that again with "this runs ten times a second" in mind.

Every incoming point recomputes the entire flight from scratch. The max altitude of a 15-minute flight is recalculated 9,000 times. The total distance - a sum over every consecutive pair of points, each pair requiring a geodesic distance on the WGS84 spheroid - is recalculated 9,000 times, over a table that grows by one row each time.

Point 1 sums 0 distances. Point 2 sums 1. Point 9,000 sums 8,999.

Σ(n=1..9000) n = 40,504,500

Forty and a half million ST_Distance calls to maintain one running total that needed 9,000.

Measuring it instead of guessing

I don't trust my own reasoning about performance, so I ported the ingest path, verbatim, same queries, same schema, same PostGIS indexes - into a benchmark and ran a full 9,000-point flight against a real PostgreSQL 16 + PostGIS 3.4 instance. Then I ran the same flight again with the aggregates carried forward incrementally, changing nothing else.

Per-point latency, averaged over each 500-point window:

The incremental line is flat. The original line is a straight ramp - per-point cost rising linearly with points already stored, which is exactly what O(N) work per point looks like, and which makes the flight as a whole O(N²).

Note what this does to capacity. At 26 ms per point, one server thread can absorb about 38 points per second - under four concurrent drones at 10 Hz, and that number gets worse as everyone's flight gets longer. On the incremental path, 1.24 ms per point is roughly 800 points per second: around 80 concurrent drones, and it stays there whether the flight is two minutes old or forty.

A single flight never crashed. That's the nasty part. It degraded, smoothly and quietly, right up until the live map started lagging behind the drone - about twelve minutes in, which is deep enough into a battery that I assumed the problem was the radio link.

"But I had indexes"

I did. Migration 001 creates a composite index precisely for this access pattern:

CREATE INDEX idx_telemetry_flight_timestamp ON telemetry(flight_id, timestamp);

(flight_id, timestamp) is the right index. It can return exactly the rows for one flight, already in timestamp order, which is what the window function wants. So I expected the planner to use it and the sort to disappear.

Here is what actually happens at 9,000 points:

Aggregate  (actual time=... rows=1)
  ->  WindowAgg  (actual time=10.514..14.173 rows=9000)
        ->  Sort  (actual time=10.494..10.925 rows=9000)
              Sort Key: telemetry."timestamp"
              Sort Method: quicksort  Memory: 947kB
              ->  Bitmap Index Scan on idx_telemetry_flight_id (rows=9000)
Execution Time: 60.998 ms

The planner ignores the composite index, grabs rows through the single-column index and sorts them itself. My first instinct was stale statistics — the table is being written to constantly, and before ANALYZE the planner was estimating rows=1 for a scan that returns 20,915. So I ran ANALYZE. The estimate corrected itself. The plan didn't change.

So I forced it, with enable_bitmapscan = off and enable_sort = off

->  Index Scan using idx_telemetry_flight_timestamp on telemetry (rows=9000)
Execution Time: 62.804 ms

62.8 ms with the "right" index, 61.0 ms without it. The planner was correct and I was wrong.

Then I removed the distance calculation and ran the identical scan-and-window over the identical 9,000 rows:

Execution Time: 1.639 ms

There it is. Reading and ordering 9,000 rows costs 1.6 ms. Computing 9,000 spheroid distances over them costs 59 ms. Roughly 6.6 µs per ST_Distance on geography - which is not slow, it's the correct price for real geodesic math on an ellipsoid. It's just that I was paying it forty million times instead of nine thousand.

No index fixes that. An index changes how fast you find rows. It has nothing to say about how many times you choose to process them.

The fix is arithmetic, not SQL

You don't need a window function to maintain a running total. You need to remember the previous point:

// carried in memory for the lifetime of the session
interface FlightAccumulator {
  distance: number;
  maxAltitude: number;
  maxSpeed: number;
  minBattery: number;
  prev: { lat: number; lon: number } | null;
}

function accumulate(acc: FlightAccumulator, p: TelemetryPoint) {
  if (acc.prev) {
    // haversine — already in utils/postgis.ts, I just wasn't using it here
    acc.distance += calculateDistance(acc.prev.lat, acc.prev.lon, p.lat, p.lon);
  }
  acc.maxAltitude = Math.max(acc.maxAltitude, p.altitude);
  acc.maxSpeed    = Math.max(acc.maxSpeed, p.speed);
  acc.minBattery  = Math.min(acc.minBattery, p.battery);
  acc.prev        = { lat: p.lat, lon: p.lon };
}

Every one of those aggregates is incremental by nature. MAX over a growing set is just Math.max against the running value. Distance is a sum, and a sum only ever needs its last term. Five queries per point become one UPDATE, and 138.9 seconds of database work become 11.2.

The honest caveat: an in-memory accumulator is process state, and process state dies with the process. Two ways out, and pick deliberately: recompute the flight once from the table on session recovery - the expensive query is fine when it runs once per flight instead of once per point — or keep the accumulator in Redis, which FLYON already runs. The point isn't which. The point is that "recompute everything, every time" was never a design decision. It was the shape the code took when I wrote updateFlightStats to be called from the batch log-upload path and then called it from the live path too.

That's the actual bug. Not the SQL. A function that is correct at 1 call per flight, reused at 10 calls per second, with nothing in its name to warn me.

Three smaller things the same afternoon turned up

Once I was measuring instead of assuming, the assumptions started falling over in a row.

I cached the thing that was already fast. There is a whole dangerZoneCache.ts in the codebase - Redis with an in-memory fallback, 5-minute TTL, invalidation on write. I was proud of it. It is wired into dangerZoneService.getDangerZones(), the REST endpoint that serves the zone list when you open the map. Once per page load. The hot path - checkDangerZones, running ten times a second - queries danger_zones directly and never touches the cache.

And here's the joke: it wouldn't have mattered. Measured across the whole flight, the danger-zone check held flat at 0.4–0.55 ms per point from the first point to the last. ST_Contains against a GIST index on 25 polygons is genuinely fast, and it doesn't care how long you've been flying. The scary-looking spatial query was never the problem. The boring UPDATE was.

I had cached the fast path and left the quadratic one alone, because spatial queries feel expensive and SELECT MAX(...) feels cheap.

Log uploads break at exactly 5,957 points. The batch path builds one big parameterized INSERT:

for (const point of points) {
  placeholders.push(`($${paramIndex++}, $${paramIndex++}, ... )`);  // 11 params
  values.push(flight.id, droneId, timestamp, position, /* ... */);
}
await query(`INSERT INTO telemetry (...) VALUES ${placeholders.join(', ')}`, values);

Eleven parameters per point. PostgreSQL's extended query protocol encodes the parameter count in the Bind message as a 16-bit field, so the ceiling is 65,535 — and node-postgres uses the extended protocol for every parameterized query. 65,535 ÷ 11 = 5,957 points.

Tested against a real server:

5957 points (65527 params): OK
5958 points (65538 params): bind message has 2 parameter formats but 0 parameters
9000 points (99000 params): bind message has 33464 parameter formats but 0 parameters

The counter wraps around - 65,538 mod 65,536 = 2, and 99,000 mod 65,536 = 33,464 - so you don't get "too many parameters", you get an error message that confidently reports a number that has nothing to do with your query. Uploading a 15-minute log fails with a message that sends you looking in entirely the wrong place. Chunk the batch at a few thousand rows, or use COPY, which has no parameter limit at all and is faster besides.

A performance migration that did nothing. Migration 003 is named add_performance_indexes.sql. Seven of its twelve CREATE INDEX IF NOT EXISTS statements reuse index names that migration 001 already created. IF NOT EXISTS matches on the name, not the definition - so it finds the name, skips silently, and reports success. Two of them were meant to change the sort order:

-- 001
CREATE INDEX idx_flights_started_at ON flights(started_at);
-- 003, intended to replace it with DESC
CREATE INDEX IF NOT EXISTS idx_flights_started_at ON flights(started_at DESC);

The second one never ran. Not once, on any environment. I "optimized" the database and the database ignored me, politely, with a green checkmark in the migration log. If you want a changed index, drop it and recreate it, and give an index a name that describes its definition, so a collision is visible instead of silent.

What I'm taking to the next project

• "Fire and forget" hides cost, it doesn't remove it. Three .catch()ed background jobs per point kept the request fast and the database busy. Nothing in the API's response time told me anything was wrong.
• Test at the rate the producer actually sends. My simulator ran at 1 Hz because I wrote it before the bridge. 900 points passes every test. 9,000 points is the real workload, and it's the same code - just longer.
• Reused functions inherit the calling frequency of their worst caller. updateFlightStats was written for batch. Nothing stopped me calling it at 10 Hz, and nothing in its signature warned the next reader either.
• Measure before you optimize, then measure the thing you didn't suspect. Everything I had proactively optimized - the spatial index, the zone cache, the WebSocket broadcast loop - was already fine. The cost was in a function I'd never once thought about.
• An index is not a performance strategy. It changes the constant. It has no opinion about the exponent.

Still on the list: COPY for log ingest instead of chunked inserts, BRIN on telemetry(timestamp) instead of a UUIDv4 primary key on a pure append table, and downsampling old flights so a year of history doesn't mean a year of 10 Hz rows.

Code is at github.com/Bogdusik/FLYON, including the quadratic version - I haven't rewritten history, and the benchmark above is reproducible against the schema in backend/src/migrations/.

Numbers measured on PostgreSQL 16.15 with PostGIS 3.4 in a single container, absolute latencies depend on your hardware, the shape of the curve doesn't. If you spot something else wrong in there, the issues tab is open.