Why gap‑filling matters

When you work with sensor data, financial ticks, or any metric that should appear at regular intervals, missing rows are the norm rather than the exception. A naïve GROUP BY on a timestamp column will simply drop the gaps, which makes downstream charts look like the signal vanished. I’ve spent too many evenings writing recursive CTEs or temporary tables just to synthesize a continuous series. There’s a cleaner way: LATERAL joins combined with generate_series.

The scenario

Imagine a table sensor_readings that stores a value every minute, but network hiccups cause occasional missing minutes. You need a result set that contains **every minute** in a given window, showing the last known value (or NULL when there is none). The window can be supplied by the UI – say, the last 24 hours.

The LATERAL trick

LATERAL lets a sub‑query reference columns from preceding tables in the same FROM clause. Pair it with generate_series to produce a row per minute, then left‑join the actual readings. Because the series is generated **once per query**, the planner can push the time‑range predicate down, keeping the scan tight.

-- 1️⃣ Define the window (could be parameters from the app)
WITH window_bounds AS (
    SELECT 
        (now() - interval '24 hours')::timestamptz AS start_ts,
        now()::timestamptz                         AS end_ts
),
-- 2️⃣ Generate a dense series of minute timestamps
series AS (
    SELECT generate_series(start_ts, end_ts, interval '1 minute') AS minute_ts
    FROM window_bounds
),
-- 3️⃣ Pull the latest reading *at or before* each minute
filled AS (
    SELECT 
        s.minute_ts,
        r.value,
        r.recorded_at
    FROM series s
    LEFT JOIN LATERAL (
        SELECT value, recorded_at
        FROM sensor_readings sr
        WHERE sr.recorded_at <= s.minute_ts
        ORDER BY sr.recorded_at DESC
        LIMIT 1
    ) r ON true
)
SELECT minute_ts, value
FROM filled
ORDER BY minute_ts;

The LATERAL sub‑query runs once per row of series, but because it only scans the index on recorded_at with a simple ORDER BY … LIMIT 1, the cost is negligible even for millions of minutes.

Why this beats the alternatives

  • Recursive CTE – forces a row‑by‑row evaluation and prevents parallelism.
  • Temporary table + UPDATE – adds round‑trips and lock contention.
  • Window functions (e.g., last_value with IGNORE NULLS) – require the base table to already contain a row for every minute, which defeats the purpose.

With LATERAL the planner sees a simple index scan per series element, and modern PostgreSQL versions can even turn the whole thing into a **merge join** when the series is large enough.

Performance tips

  1. Index the timestamp column – a B‑tree on recorded_at is mandatory.
  2. Limit the window – don’t generate a series for years unless you really need it.
  3. Consider BRIN indexes for massive append‑only tables; they’re tiny and still support the <= predicate.
  4. Use parallel query – PostgreSQL 14+ can parallelize the generate_series scan when max_parallel_workers_per_gather is set.

Gotcha: If the sensor can write multiple rows with the same timestamp, the ORDER BY recorded_at DESC LIMIT 1 will arbitrarily pick one. Add a tie‑breaker like id DESC if you need deterministic results.

Extending the pattern

The same skeleton works for any “last known value” problem: inventory levels, account balances, feature flags. Swap generate_series for a date‑dimension table if you prefer a pre‑materialized calendar. You can also aggregate inside the lateral sub‑query (e.g., MAX(value)) when you need the peak within the minute instead of the latest tick.

Wrapping up

LATERAL joins are one of those PostgreSQL features that feel like a secret weapon once you internalize them. They turn a procedural gap‑filling loop into a single declarative statement that the optimizer can reason about. Next time you catch yourself writing a script to “fill the holes”, give this pattern a shot – you’ll likely shave both code lines and execution time.