Rewriting MySQL-Era SQL With PostgreSQL CTEs and Window Functions

Every codebase that grew up on MySQL 5.x carries scar tissue. Session-variable ranking hacks. Triple-nested derived tables. Self-joins that exist only to find the newest row per group. None of it was bad engineering; it was good engineering against a database that did not have CTEs, window functions, or lateral joins until MySQL 8.0 arrived in 2018.

The problem is that the workarounds outlived the limitation. Teams migrating to PostgreSQL, and frankly teams that upgraded to MySQL 8 years ago, are still shipping 2015-shaped SQL because it works and nobody has revisited it. A migration is the natural moment to revisit it, because you are already touching every query. This article catalogs the classic workarounds and their modern rewrites, valid on PostgreSQL 16 and 17 and, in most cases, on MySQL 8.x too.

The habits MySQL 5.x taught us

It is worth naming the constraints, because they explain the shapes. MySQL before 8.0 had no CTEs, so complex queries became nested derived tables or temporary tables managed by the application. It had no window functions, so ranking and running totals were done with session variables or self-joins. Its optimizer historically handled dependent subqueries poorly, so developers avoided them by habit even when they were the clearest expression of intent. Each constraint produced an idiom, and each idiom survives in code long after the constraint disappeared.

A practitioner's note of fairness: MySQL 8 closed most of this gap. CTEs, recursive CTEs, window functions, and lateral derived tables (as of 8.0.14) are all there. What follows is less "MySQL cannot" and more "your codebase predates when it could." PostgreSQL simply adds a few tools MySQL still lacks: DISTINCT ON, the FILTER clause, and a generally more battle-tested planner for these constructs.

One more reason the migration is the right moment: these rewrites need regression coverage, and a migration project already has a test harness comparing old results to new. Folding the modernization into the port means each query gets verified once instead of twice, and the PostgreSQL version of the codebase starts life idiomatic instead of carrying MySQL 5.x fossils into its second decade.

Session-variable rankings become window functions

The classic: rank rows by initializing a session variable and incrementing it per row. It relied on evaluation order that was never guaranteed, and MySQL 8 deprecated the pattern outright because it genuinely breaks under the newer optimizer.

-- The old MySQL 5.x idiom (order-dependent, now unreliable)
SELECT t.*, @rank := @rank + 1 AS rank
FROM (SELECT * FROM scores ORDER BY points DESC) t,
     (SELECT @rank := 0) r;

-- The rewrite, PostgreSQL and MySQL 8
SELECT player_id,
       points,
       RANK() OVER (ORDER BY points DESC) AS rank
FROM scores;

Once window functions are in your vocabulary, the same OVER clause solves running totals with SUM, gap detection with LAG and LEAD, deduplication with ROW_NUMBER, and percentiles with NTILE. If your application computes any of these in a loop after fetching rows, that loop is a candidate for deletion. The performance story is usually favorable too: the variable hack forced a full sort inside a derived table anyway, while the window version gives the planner an honest view of the computation, and on PostgreSQL an index matching the OVER clause ordering can eliminate the sort entirely.

Greatest-n-per-group without the self-join

"The latest order per customer" is the most rewritten query in history. The MySQL-era versions were a self-join against a MAX() subquery, or the LEFT JOIN ... IS NULL trick, both of which scan the table twice and both of which silently return multiple rows on timestamp ties.

PostgreSQL has a dedicated tool: DISTINCT ON, which keeps the first row per group according to the ORDER BY. It is not standard SQL and MySQL does not have it, but it is so much clearer that I consider it a legitimate reason on its own to prefer PostgreSQL for analytics-flavored application queries.

SELECT DISTINCT ON (customer_id)
       customer_id, id AS order_id, created_at, total
FROM orders
ORDER BY customer_id, created_at DESC;

The portable rewrite is ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) in a CTE, filtered to row number 1. Both versions handle ties deterministically once you add a tiebreaker column to the ordering, which the old self-join never did. Deciding between them is mostly about scope: DISTINCT ON reads better and, with an index matching its ORDER BY, plans well for modest group counts, while the ROW_NUMBER form generalizes to "keep the top three" and stays portable if part of the fleet remains on MySQL 8 during a long migration.

Derived-table pyramids become CTEs

Without CTEs, a multi-step computation became subqueries nested inside subqueries, each aliased t1, t2, t3, readable only to its author and only that week. The WITH clause flattens the pyramid into named, ordered steps, and recursive CTEs replace the worst offender of all: fetching a tree (categories, org charts, threaded comments) by looping queries in application code, one round trip per level.

One PostgreSQL-specific behavior to know: since version 12, PostgreSQL inlines CTEs into the outer query when it is safe to do so, where older versions always materialized them. If a CTE was being used as an optimization fence, and in ported MySQL code it sometimes accidentally is, the AS MATERIALIZED keyword restores the fence explicitly. Plan changes from CTE inlining are one of the things I explicitly check when reviewing a migration, and it earns a line on the migration review checklist.

Conditional counts with FILTER

Counting subsets in one pass over a table is a pivot-style query every dashboard has. The MySQL idiom is SUM(CASE WHEN ... THEN 1 ELSE 0 END), or the terser SUM(condition) exploiting MySQL's boolean-to-integer coercion, which, as covered elsewhere, does not survive the move to PostgreSQL at all.

SELECT
  count(*) AS total,
  count(*) FILTER (WHERE status = 'failed') AS failed,
  count(*) FILTER (WHERE status = 'failed'
                     AND created_at >= now() - interval '1 hour') AS failed_last_hour
FROM jobs;

FILTER is standard SQL, PostgreSQL has had it since 9.4, and MySQL does not implement it, so this one is a genuine one-way door. It works on any aggregate, not just count: avg of a subset, array_agg of a subset, and so on. The CASE spelling still works in PostgreSQL if you need portability, but for PostgreSQL-only code, FILTER states the intent directly.

Lateral joins for per-row subqueries

The pattern MySQL-era developers avoided hardest was "for each row in A, run a small query against B," because dependent subqueries had a reputation for terrible plans. The workarounds were application-side loops or giant join-then-filter queries. LATERAL makes the pattern first-class: a subquery in the FROM clause that can reference columns from tables to its left, executed per row, with the planner fully aware of it.

The killer use case is top-N per group, which DISTINCT ON cannot do for N greater than 1: for each customer, the three most recent orders becomes a lateral subquery with ORDER BY and LIMIT 3.

SELECT c.id, c.name, recent.id AS order_id, recent.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent;

With an index on orders (customer_id, created_at DESC), each per-customer probe is a short index scan rather than a table pass. The same shape cleanly expresses "the current price as of this row's date" lookups against history tables, and it replaces the application-side loop one query per parent row, N+1 by construction, with a single statement. MySQL 8.0.14 added LATERAL too, so this rewrite is available on both engines; PostgreSQL's planner has simply had a decade longer to get good at it.

How MonPG helps after you land on PostgreSQL

Rewrites like these are not just aesthetic. Collapsing a double table scan into one window pass, or an N-round-trip loop into one recursive CTE, changes the workload shape, and you want evidence that it changed in the right direction. The honest way to confirm is before-and-after query history, not a one-off EXPLAIN on a developer laptop.

MonPG keeps that history for PostgreSQL: pg_stat_statements trends per query family, calls, mean time, and block reads, so a rewrite shows up as a measurable drop rather than a feeling. It will also catch the rewrite that backfired, the accidentally-unfenced CTE or the lateral join missing its supporting index, while it is a slow query and not yet an incident. The PostgreSQL monitoring guide covers the baseline; put it in place before the rewrite sprint, and every one of these changes becomes a graph you can point at.