Stream-stream joins

Why is every stream-stream join an interval join, and why are its errors absences?

To join two unbounded streams, you must keep records from each side until you can prove that no future match can arrive. That proof requires a bound. So:

SELECT o.order_id, c.courier_id
FROM orders o JOIN courier_pings c
  ON  c.order_id = o.order_id
  AND c.ts BETWEEN o.ts - INTERVAL '2' MINUTE AND o.ts + INTERVAL '10' MINUTE;

Both sides are buffered

Either side can arrive first, so both must be buffered. The buffer is the rate × time regime from state size (the 240 GB example). When a record arrives, it probes the other side's buffer and emits every match immediately. So one input record produces a variable number of outputs, spread over time. Join output size is therefore unpredictable, and a join downstream of another join is dangerous (the variability multiplies).

The failure is silent

A record that arrives after the other side's buffer has been evicted produces no row and no error. Who pays: the consumer of the joined output, who sees a slightly low count. This is Ana's 2% shortfall: some courier pings arrive more than the allowed skew after the order, and their partners were already evicted.

Outer joins cost much more

A left outer join must emit a row for an order even when no courier matched, padded with nulls. You can emit that null-padded row only once you can prove no match will ever arrive. That needs a timer per unmatched record, firing at the interval boundary. So there is one timer per unmatched row, on top of the buffered row itself. This is why outer interval joins are substantially more expensive than inner ones, not marginally.