Real-world window patterns: gaps, islands, sessions

Sessionization: LAG a timestamp, flag gaps, cumulative-SUM the flags

Sessionization assigns a session number to each event so that events within an inactivity gap belong to the same session. The recipe is three windowed steps over (PARTITION BY user_id ORDER BY ts):

1. LAG(ts) gives the previous event’s timestamp for each row.
2. A boundary flag is 1 when ts - prev_ts > interval '30 min' (or the first event), else 0.
3. A cumulative SUM of the flag turns those 1s into a monotonically increasing session number — every flag bumps the counter, every non-flag keeps it.

SELECT user_id, ts,
       SUM(is_new_session) OVER (
         PARTITION BY user_id ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM (
  SELECT user_id, ts,
         CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
                   > interval '30 minutes'
               OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
              THEN 1 ELSE 0 END AS is_new_session
  FROM events
) flagged;

Gaps-and-islands: the row_number difference trick

“Islands” are runs of consecutive values; “gaps” are the holes between them. The classic problem: given login dates, find each streak of consecutive days. The trick is that for a run of consecutive integers (or dates), the value minus its row number is constant within a run, because both increase by 1 in lockstep. When the sequence skips, the difference changes — a new island.

SELECT user_id, MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end, COUNT(*) AS streak_len
FROM (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM daily_logins
) t
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

grp is the anchor: all rows in one consecutive streak share the same login_date - row_number value, so GROUP BY grp collapses each streak into one summary row. This is the canonical gaps-and-islands solution — ROW_NUMBER plus arithmetic plus a GROUP BY on the derived key. Dedup (lesson 4) and cumulative distributions (lesson 5) are smaller members of the same family: every one is “use a window to derive a key, then aggregate or filter on it.”

Performance: each distinct window ORDER BY costs a sort

Window functions are not free. Each distinct window definition — specifically each unique PARTITION BY ... ORDER BY ... — forces Postgres to materialize the rows in that order, which means a Sort node feeding a WindowAgg node in the plan. Stack three windows with three different orderings and you get three sorts; on a large table that is the dominant cost.

The optimization is to make windows share their ordering. Postgres reuses a single sort for all window functions that have the same PARTITION BY/ORDER BY, so writing SUM(...) OVER w, ROW_NUMBER() OVER w, LAG(...) OVER w against one named WINDOW w AS (PARTITION BY user_id ORDER BY ts) lets them ride one sort instead of three. When you genuinely need different orderings, order the window clauses so that compatible sorts are adjacent — the planner can sometimes pipeline a sort into the next if it is a prefix. Always confirm with EXPLAIN ANALYZE: count the Sort and WindowAgg nodes and watch for Sort Method: external merge Disk: which means the sort spilled past work_mem and is hitting disk — the moment a window query goes from milliseconds to seconds.