Window Function Edge Cases: MySQL 8 vs PostgreSQL Frames, FILTER, and Percentiles

The p90 API latency report moved 40ms overnight, and nothing had deployed. The dashboard service ran the same analytics SQL against both engines — PostgreSQL 15 for the main platform, MySQL 8.0.36 for a legacy tenant shard — and an engineer had just “unified” the percentile query so it would parse on MySQL. The original used percentile_cont(0.9), which MySQL does not have, so the unified version computed the percentile by hand with ROW_NUMBER and picked the row at 90% of the count. Nearest-rank instead of interpolated. On a day’s worth of latency samples the two methods differ by tens of milliseconds, and the drift report flagged it three weeks later when someone finally compared the engines side by side.

That is the shape of every window-function incident I have had between these two databases: the common core — ROW_NUMBER, RANK, LAG, LEAD, SUM OVER — is genuinely portable, and then one corner of the feature set exists on only one engine, or behaves one tie differently, and the numbers move without any error being raised. This article is the map of those corners: what MySQL 8 lacks, what the default frame silently does on both, and how to write window SQL that returns the same answer everywhere.

Which window features does PostgreSQL have that MySQL 8 lacks?

PostgreSQL has five things MySQL 8 does not: GROUPS frame mode, frame exclusion clauses, the FILTER clause on window aggregates, ordered-set aggregates like percentile_cont, and RANGE frames with INTERVAL offsets. MySQL 8 supports ROWS and RANGE frames only, with no EXCLUDE clause, and its RANGE offsets must be numeric expressions over a numeric ordering column. PostgreSQL 11 and later accept all three frame modes, exclusions like EXCLUDE CURRENT ROW, and temporal RANGE offsets:

-- PostgreSQL 11+: trailing 7-day window over timestamps, peers excluded
SELECT day, signups,
       SUM(signups) OVER (
         ORDER BY day
         RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
         EXCLUDE CURRENT ROW
       ) AS prior_week_signups
FROM daily_signups;

-- MySQL 8: no INTERVAL in RANGE, no EXCLUDE, no GROUPS mode.
-- The same logic needs a self-join or a date-spined rewrite.

The FILTER clause is the one I miss most day to day: SUM(amount) FILTER (WHERE status = ‘refunded’) OVER (…) is one clean expression on PostgreSQL, and on MySQL it becomes SUM(CASE WHEN status = ‘refunded’ THEN amount END) OVER (…), which is fine until the CASE forgets the ELSE NULL and starts summing zeros into the wrong buckets. And the ordered-set aggregates — percentile_cont, percentile_disc, mode — simply do not exist on MySQL in any form, which is what started my incident.

Why did the ported percentile query return a different number?

Because percentile_cont interpolates between rows and a hand-rolled ROW_NUMBER replacement picks an actual row, and those are different statistics. PostgreSQL’s ordered-set syntax sorts inside the aggregate:

-- PostgreSQL: continuous percentile, interpolates between neighbors
SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY latency_ms)
FROM api_requests
WHERE ts >= now() - interval '1 day';
-- with 10,001 samples it averages the rows at positions 9000 and 9001

The MySQL-compatible rewrite assigns ROW_NUMBER() OVER (ORDER BY latency_ms), multiplies the count by 0.9, and takes the row at that rank — which is percentile_disc, the discrete variant, with a ceiling thrown in. On skewed latency data the difference is exactly the 40ms the report moved. Neither engine errored; both were correct answers to different questions. The porting rule I now enforce: if the source system used percentile_cont, the target must either reproduce the interpolation arithmetic explicitly or the discrepancy gets documented as an accepted methodology change, signed off by whoever owns the report. Silently swapping continuous for discrete is how you spend a month debugging a “performance regression” that is a statistics change.

How do you write frames that behave identically on both engines?

Spell out the full frame every time, and use ROWS unless you deliberately want peer ties, because the default frame hides the two nastiest shared traps. The default on both engines is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and it has teeth. First trap: LAST_VALUE with the default frame never sees rows after the current one, so LAST_VALUE(x) OVER (ORDER BY t) returns x itself, on both engines, and fixing it means an explicit frame:

-- identical on MySQL 8 and PostgreSQL: explicit ROWS frame, no surprises
SELECT id, status,
       LAST_VALUE(status) OVER (
         ORDER BY changed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS final_status
FROM ticket_events
WHERE ticket_id = 48123;

Second trap: with RANGE and the default frame, peer rows — rows whose ORDER BY value ties with the current row — are included in the frame, so a running SUM jumps in steps at duplicate timestamps instead of incrementing row by row. Both engines do this; both engines confuse the reader. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives the naive running total people expect. One more shared constraint worth knowing before you design around it: neither engine allows DISTINCT inside a window aggregate — PostgreSQL says so outright with “DISTINCT is not implemented for window functions”, and MySQL rejects it at parse time — so COUNT(DISTINCT user_id) OVER (…) needs a two-level rewrite with a grouping pass first on both. The broader rewrite playbook for moving window SQL between these engines is in the rewriting MySQL SQL with PostgreSQL CTEs and window functions field notes.

What does a window function cost on each engine?

On both engines a window costs at least one sort of the full input per distinct PARTITION BY plus ORDER BY combination, and the way you see it differs. PostgreSQL’s EXPLAIN ANALYZE shows a WindowAgg node sitting on top of a Sort, and multiple windows with compatible orderings share one sort. MySQL 8’s EXPLAIN ANALYZE shows windowing as a sort plus a buffer pass, and the single-engine tuning notes in MySQL window functions and CTE performance cover what that looks like when the input stops fitting in memory. The practical porting consequence: a PostgreSQL query with four windows over one ordering often survives a MySQL port with similar cost, but a query that leaned on GROUPS frames or FILTER needs a rewrite that may add a full extra pass, and the only honest way to size that is EXPLAIN ANALYZE on both sides — the EXPLAIN vs EXPLAIN ANALYZE comparison covers the dialect differences between the two plan formats.

The other cost is correctness maintenance: every window query that must run on both engines is a candidate for a golden-file test where the same fixture data is loaded into both and the result sets are diffed. We run exactly that for the six analytics queries the dashboard shares, and it has caught two drift incidents since the percentile one, both before customers saw them.

Where MonPG fits when window queries get slow

Window queries degrade quietly: the sort that fed the WindowAgg fit in memory last quarter and spills this quarter, and the report just gets slower every Monday. MonPG’s PostgreSQL monitoring tracks the statement-level statistics and temp-file volume that make that trend visible, so a degrading window query shows up as rising spill and runtime on a named normalized query, not as a vague complaint from the analytics team.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL half of this article is what it will watch: window-heavy statements climbing the digest rankings with their sort and buffer costs attached. Until then, the MySQL monitoring page tracks that work. And the lesson from the 40ms drift needs no tooling at all: window functions are portable until they are not, the differences are statistical rather than syntactic, and the only query you can trust across two engines is one whose output you have diffed against both.

Related documentation

• MySQL: window functions
• PostgreSQL: window functions