A window partition determines which rows belong together; a window frame determines which of those rows feed a frame-sensitive calculation for the current row. Confusing these boundaries can turn a running total into a repeated subtotal or make last_value return the current value when the report expected the final value in the partition.

Defaults depend on ordering

In PostgreSQL, the default frame is RANGE from the partition’s beginning through the current row’s last peer. Peers have equal values for every expression in the window ORDER BY. With an order, a default running sum therefore includes all rows tied with the current row, including peers that appear later in the displayed output.

Without a window ORDER BY, all rows are peers and the default frame covers the whole partition. Thus sum(amount) OVER (PARTITION BY account) repeats an account total, while adding an order changes the calculation into an ordered cumulative result. Neither “the default is always the whole group” nor “the default adds exactly one row” describes both cases correctly.

The window order is separate from the final result order. An outer ORDER BY can arrange rows for display without changing the frame. Conversely, adding a unique identifier inside a RANGE window can split a peer group, altering the calculation rather than merely making the output prettier. Treat that change as a semantic decision.

Rows, value ranges, and peer groups

ROWS counts positions in the window ordering. A frame from two preceding rows through the current row contains at most three rows, fewer near the partition start. It does not mean three days or three minutes. If multiple events share a timestamp, include a suitable unique tie-breaker when their positional order must be deterministic.

RANGE uses ordering values and peer boundaries. With an offset, PostgreSQL requires one ordering expression and a compatible offset type; a timestamp order can use an interval. A value-based time range includes events by time difference, so its row count can grow during busy periods and shrink during quiet ones. GROUPS instead counts peer groups, allowing a frame to move by distinct ordering groups while retaining all their tied rows.

First_value, last_value, nth_value, and aggregates used as windows depend on the frame. Ranking and lag or lead have different semantics based on partition order; shrinking a frame does not make lag forget the preceding partition row. Check the function’s definition before applying one generic explanation to all window functions.

When it breaks

Last_value with an ordered default frame usually means the end of the current frame, not the end of the entire partition. With unique ordering, that is the current row. To repeat the partition’s final value, specify an ending boundary of UNBOUNDED FOLLOWING and provide an order that defines what “final” means. A chronological maximum and the value of the last chronological row are different questions.

Running totals can also surprise when tied dates receive the same cumulative number. This is expected peer behavior under the default RANGE frame. If the business wants each individual event to advance the total, use an explicit ROWS frame and deterministic event ordering. Do not eliminate genuine ties from a date-level report just to obtain a more familiar-looking curve.

Check it quickly

Save this as frames.sql and run psql -X -v ON_ERROR_STOP=1 -f frames.sql in a disposable PostgreSQL 18 database. Two events share day one, and a third belongs to day two. Temporary objects and inserted rows disappear on rollback.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE sales (id integer, day integer, amount integer);
INSERT INTO sales VALUES (1,1,10),(2,1,20),(3,2,5);
CREATE TEMP VIEW framed AS
SELECT *,
  sum(amount) OVER (ORDER BY day) AS default_sum,
  sum(amount) OVER (ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_sum,
  last_value(amount) OVER (ORDER BY day,id) AS default_last,
  last_value(amount) OVER (ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS partition_last,
  sum(amount) OVER () AS whole_sum
FROM sales;
SELECT * FROM framed ORDER BY id;
DO $$ BEGIN
  IF (SELECT array_agg(default_sum ORDER BY id) FROM framed)
     IS DISTINCT FROM ARRAY[30,30,35]::bigint[] THEN RAISE EXCEPTION 'wrong peer frame'; END IF;
  IF (SELECT array_agg(row_sum ORDER BY id) FROM framed)
     IS DISTINCT FROM ARRAY[10,30,35]::bigint[] THEN RAISE EXCEPTION 'wrong row frame'; END IF;
  IF (SELECT array_agg(default_last ORDER BY id) FROM framed)
     IS DISTINCT FROM ARRAY[10,20,5] THEN RAISE EXCEPTION 'wrong current-frame end'; END IF;
  IF EXISTS (SELECT FROM framed WHERE partition_last IS DISTINCT FROM 5 OR whole_sum IS DISTINCT FROM 35) THEN
    RAISE EXCEPTION 'whole partition values wrong';
  END IF;
END $$;
ROLLBACK;

PostgreSQL 18.6 returned default sums 30, 30, 35: both first-day rows see the complete first-day peer group. The explicit ROWS sum, ordered by day and id, returned 10, 30, 35. The id deliberately defines the order between individual events; it is not included in the date-based default window.

The default last_value column returned 10, 20, 5 because its day-and-id order makes every row its own peer group. The full-partition frame returned 5 everywhere, while the unordered sum returned 35 everywhere. The assertions check these separate results and will fail if a frame is silently changed.

This small fixture establishes answer semantics, not resource costs. Frames that need future rows and large partitions can require substantial buffering or sorting. Inspect realistic execution plans and data distributions separately, preserving the tested tie and boundary behavior while tuning performance.

Sources