Window frames: ROWS vs RANGE vs GROUPS

The frame is the slice that actually gets aggregated

PARTITION BY decides which rows share a window; ORDER BY orders them; the frame decides which subset of that ordered partition a function aggregates for each row. The frame is a moving window-within-the-window. For a running total at row 5, the frame is “rows 1 through 5”; at row 6 it slides to “rows 1 through 6.” Frame syntax has three parts: a mode (ROWS / RANGE / GROUPS) and two bounds.

The bounds you’ll use constantly:

• UNBOUNDED PRECEDING — the first row of the partition.
• CURRENT ROW — where the calculation currently sits.
• n PRECEDING / n FOLLOWING — n positions back or forward.
• UNBOUNDED FOLLOWING — the last row of the partition.

So ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the classic running total; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a 3-row trailing window (a moving average); ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is the whole partition. Together the mode and bounds give you precise control over which rows feed the calculation — without spelling them out explicitly, you inherit the default, which may not be what you want.

RANGE vs ROWS: the difference is ties

ROWS counts physical rows: 1 PRECEDING means literally the one row before this one. RANGE works on values of the ORDER BY expression: CURRENT ROW in RANGE mode does not mean “this one row” — it means “all rows whose ORDER BY value equals this row’s value,” i.e. all peers. If three sales share sale_date = '2026-01-10', those three rows are peers, and under RANGE they enter and leave the frame together.

For a running SUM ordered by sale_date, the consequence is exact and damaging: on a tied date, every one of the tied rows gets the same running total — the total including all peers — instead of accumulating one at a time. The first tied row already shows the full day’s contribution; the others repeat it. If your report expects each row to add its own amount in sequence, the tied rows are wrong.

-- Default frame == RANGE; tied sale_date rows share one running value (overcounts early rows)
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_default
FROM sales;

-- ROWS frame: each physical row adds exactly its own amount, in order
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM sales;

The default frame, and the production rule

When you add ORDER BY inside OVER and specify no frame, Postgres uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is the SQL-standard default — and it is RANGE, not ROWS. On data with no ties in the ORDER BY column the two are identical, which is exactly why bugs sneak through: it works on a clean dev dataset and breaks the day production has two rows with the same timestamp or date.

The production rule is blunt: for any running aggregate, write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly. It makes the frame physical (one row at a time), it is also generally faster (Postgres needn’t scan ahead to find all peers), and it removes the silent tie behavior. Use RANGE only when you genuinely want peers grouped — for example a value-based frame like RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW, which ROWS cannot express. GROUPS is the third mode: its bounds count peer groups rather than rows or values — GROUPS 1 PRECEDING reaches back one whole tie-group — a rare but precise tool when you want “the previous distinct date’s rows, however many.”