Spatial Window Frame Specification Gotchas

The frame clause of a spatial window function decides both the answer and whether the query fits in memory, and the two most expensive mistakes are invisible at write time: relying on the SQL default frame (RANGE UNBOUNDED PRECEDING) over geometry, and letting an unbounded ROWS frame materialize an entire partition of GEOMETRY values. This walkthrough, part of window functions for geospatial, pins down the ROWS vs RANGE vs GROUPS semantics as they apply to trajectory deltas and running spatial metrics, and turns a frame that silently OOMs on one dominant partition into one that streams.

Root-Cause Analysis of a Frame That OOMs or Lies #

A misframed spatial window fails in one of two directions: it returns a subtly wrong running metric, or it exhausts memory buffering geometry. Both trace back to a small set of causes, and each has a distinct fix.

• The implicit default frame. When you write SUM(...) OVER (ORDER BY ts) with no explicit frame, DuckDB applies the SQL-standard default of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Under RANGE, “current row” includes every peer — every row with the same ts value. On GPS feeds where several fixes share a timestamp, a running total silently sums all tied rows into each of their outputs instead of accumulating them one at a time. The result looks plausible and is wrong.
• Unbounded ROWS over geometry. A frame of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (or any frame that spans the whole partition) forces the window operator to hold every row of the partition — including its full WKB payloads — resident at once. On a partition of millions of dense geometries this is an immediate out-of-memory (OOM).
• RANGE with an offset on the wrong column type. An offset RANGE frame such as RANGE BETWEEN INTERVAL '30 seconds' PRECEDING AND CURRENT ROW requires exactly one ORDER BY expression of a numeric or temporal type, because the engine computes the boundary by value arithmetic. Order by a GEOMETRY, or by two columns, and the frame is rejected or degenerates.
• Single dominant partition. A partition key where one value holds most of the rows (one vehicle reporting 90% of a fleet’s fixes) makes that partition’s buffer exceed the budget no matter how high memory_limit is set — a partition cannot be split across the limit. This is a cardinality problem masquerading as a memory problem.

The diagnostic split is simple: a wrong number points at frame semantics (RANGE peers, default frame); an OOM points at frame extent or partition cardinality.

Deterministic Configuration #

A window executes strictly after a sort, so the whole pattern is memory-bound and acutely sensitive to what the frame forces the operator to buffer. Set the session up before diagnosing frames.

INSTALL spatial; LOAD spatial;

-- Physical cores only: windowing is memory-bound, and extra threads add
-- contention on the geometry buffer without shortening the sort.
SET threads = 8;

-- Headroom for the largest partition's frame buffer. A frame that spans a whole
-- partition holds every row's geometry here at once — under-committing is
-- cheaper than an OS OOM-kill mid-window.
SET memory_limit = '16GB';

-- Spilled GEOMETRY re-serializes to and from disk, so keep spill on fast local
-- NVMe; a network mount turns a frame spill into a stall.
SET temp_directory = '/mnt/nvme/duckdb_spill';

-- Window output rarely needs source order; releasing it lets the sort
-- parallelize across partitions.
SET preserve_insertion_order = false;

Preconditions worth confirming before trusting any running spatial metric:

• Every window with a running aggregate names its frame explicitly — never rely on the default.
• The ORDER BY key for an offset RANGE frame is a single numeric or temporal column.
• No partition key is so skewed that one partition’s geometry exceeds memory_limit.
• LAG/LEAD operate on raw coordinates where possible, so the frame buffers 8-byte doubles rather than variable-length WKB — the lever the parent window functions for geospatial reference identifies as the largest on sort throughput.

The Three Frame Types Over Geometry #

ROWS counts physical positions, RANGE counts value distance from the current row, and GROUPS counts distinct ordering values. The difference only bites when the ordering column has ties — which spatial-temporal feeds routinely do.

Read across the tie at ts = 10: ROWS stops at the physical current row; the default RANGE frame pulls in the second row sharing ts = 10 as a peer, so a running SUM(ST_Length(...)) double-counts across the tie; GROUPS counts the boundary in distinct ts values. For strict positional running metrics over trajectories, ROWS is almost always what you mean.

Optimized Execution Pattern #

Take a cumulative-distance query that OOMs because it leans on the default frame and buffers geometry:

-- ANTI-PATTERN: default frame + LAG over assembled GEOMETRY. The implicit
-- RANGE frame double-counts tied timestamps, and buffering ST_Point in the
-- window forces the frame to hold WKB for the whole partition → OOM on a
-- dominant vehicle_id.
SELECT
  vehicle_id, ts,
  SUM(ST_Distance(ST_Point(lon, lat), LAG(ST_Point(lon, lat)) OVER w)) OVER w AS cumulative_m
FROM gps_events
WINDOW w AS (PARTITION BY vehicle_id ORDER BY ts);   -- no explicit frame

-- OPTIMIZED: explicit ROWS frame (positional, tie-safe) and LAG over raw
-- doubles so the frame buffers 8-byte coordinates, not variable-length WKB.
WITH stepped AS (
  SELECT
    vehicle_id, ts, lon, lat,
    ST_Distance(
      ST_Point(lon, lat),
      ST_Point(LAG(lon) OVER w, LAG(lat) OVER w)
    ) AS step_m
  FROM gps_events
  WINDOW w AS (PARTITION BY vehicle_id ORDER BY ts, event_id)  -- break ties
)
SELECT
  vehicle_id, ts,
  step_m,
  SUM(step_m) OVER (
    PARTITION BY vehicle_id ORDER BY ts, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- positional running total
  ) AS cumulative_m
FROM stepped;

Three behavioral changes fix both failure modes at once. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is positional, so it accumulates one row at a time regardless of tied timestamps. Adding event_id as a tie-breaker in ORDER BY makes the ordering total, removing peer ambiguity even before the frame is considered. And computing step_m on raw lon/lat doubles means the window buffer holds scalars — the geometry is never dragged through the frame. Note that ROWS UNBOUNDED PRECEDING … CURRENT ROW is streaming: the operator keeps a running accumulator, not the whole partition, so it does not OOM the way an all-following frame would.

Diagnostic Queries & Plan Validation #

Confirm the frame streams rather than materializes with EXPLAIN ANALYZE, read bottom-up.

EXPLAIN ANALYZE
SELECT vehicle_id, SUM(step_m) OVER (
  PARTITION BY vehicle_id ORDER BY ts, event_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM stepped;

• Spill node under the WINDOW. A SPILL or temporary-file node means a partition’s frame buffer exceeded memory_limit. If the frame is already positional-streaming, the cause is partition cardinality, not the frame extent — sub-partition the key.
• ORDER share of runtime. The ORDER feeding the window should stay well under half of total time. If it dominates, the sort is materializing more than the frame needs; verify you are carrying raw coordinates through the sort, not GEOMETRY.
• Estimated vs actual rows on the scan. Drift beyond ~30% means the sort buffer was sized against stale statistics, which usually precedes an unexpected spill.

A one-liner monitor for a dominant partition, the usual OOM trigger:

-- Flag skew: any partition holding a large share of rows will blow the frame
-- buffer regardless of memory_limit, because a partition can't be split.
SELECT vehicle_id, COUNT(*) AS rows,
       COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS partition_share
FROM gps_events
GROUP BY vehicle_id
ORDER BY rows DESC
LIMIT 10;

Geometry Validation & Fallback Routing #

LAG/LEAD over coordinates is robust to invalid geometry because it never assembles a polygon, but any window that computes ST_Distance or ST_Length on reconstructed geometry should still guard against nulls and degenerate points introduced by dropped fixes:

-- Guard the delta so a missing predecessor (partition start, or a null coord)
-- yields 0, not a null that poisons the running SUM.
SELECT
  vehicle_id, ts,
  COALESCE(
    ST_Distance(ST_Point(lon, lat), ST_Point(LAG(lon) OVER w, LAG(lat) OVER w)),
    0
  ) AS step_m
FROM gps_events
WHERE lon IS NOT NULL AND lat IS NOT NULL
WINDOW w AS (PARTITION BY vehicle_id ORDER BY ts, event_id);

When a single partition is still too large for its frame to fit — the irreducible skew case — fall back to sub-partitioning so no frame buffer spans the whole key, then fold the sub-partition results:

-- Sub-partition a dominant key by hour so no single frame buffer exceeds
-- memory; the running total resets per hour, then compose downstream.
SELECT
  vehicle_id, ts,
  SUM(step_m) OVER (
    PARTITION BY vehicle_id, date_trunc('hour', ts) ORDER BY ts, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS hourly_cumulative_m
FROM stepped;

If the real need is density-based grouping rather than a running metric, the grid-cell and distance-based approaches in ST_ClusterDBSCAN spatial grouping avoid frame buffering entirely; and where a window can be replaced by binning first, the vectorized aggregations reference shows how to aggregate into a small keyed set before any window runs.

Related #

See also

• Window functions for geospatial — the scan-sort-window execution model and partition-cardinality guardrails this frame guide builds on.
• ST_ClusterDBSCAN for spatial grouping — density grouping that sidesteps frame buffering.
• Vectorized aggregations — bin into a keyed set before windowing to shrink what each frame holds.

Up: Window Functions for Geospatial · Modern Spatial SQL Query Patterns