Postgres LATERAL Joins: Per-Row Subqueries Without Hand-Written Loops

SQL's compositional limits are real. The relational model assumes each row in the FROM clause is independent of the others, which means subqueries in the FROM clause cannot reference the outer query's columns. For most workloads this is fine, but a small set of common patterns—top-N per group, per-row table-valued functions, correlated computations—usually get implemented either as application-side loops or as awkward window-function-plus-filter combinations. LATERAL is the SQL feature that drops the independence requirement and lets a subquery in FROM see the columns of the rows that came before it in the join sequence.

What LATERAL actually does

LATERAL is a join qualifier added to a subquery or function call in the FROM clause. It tells the planner that the subquery may reference columns from preceding items in the FROM clause, and the planner re-evaluates the subquery for each row of the outer relation. The result is functionally equivalent to a loop over the outer relation that runs the subquery once per row, but the planner gets to choose how to execute it.

The canonical example is top-N per group: for each customer, find the three most recent orders. Without LATERAL, this is usually solved with a window function plus a filter on row_number, which works but materializes the full set before filtering. With LATERAL, the query is more direct: for each customer, run a small subquery that picks the top three orders for that customer's ID.

The LATERAL form is often faster on real data because the inner subquery can use an index on (customer_id, created_at DESC) to find the three rows directly, while the window-function approach has to scan all orders for matching customers before filtering.

Where LATERAL fits

The first natural case is top-N per group, as above. Any query that selects "a small number of related rows per outer row" benefits from LATERAL when the inner table has an appropriate index.

The second case is correlated computations that the SQL standard otherwise pushes into scalar subqueries in the SELECT list. A scalar subquery in SELECT can compute one value per outer row, but if you need multiple values (sum, count, max, last), you either write multiple scalar subqueries (each of which scans separately) or restructure with GROUP BY (which often produces awkward LEFT JOIN patterns). LATERAL lets a single subquery compute multiple values per outer row in one pass.

The third case is set-returning functions that need outer-row arguments. Functions like generate_series, unnest, jsonb_to_recordset, and regexp_matches can be called in FROM, but if their arguments depend on the outer row, you need LATERAL to make the dependency visible. Without LATERAL, the function is evaluated once with whatever arguments are constant, and the result is joined as a constant table to every outer row.

The fourth case is per-row table-valued functions, where a stored function returns a row set that varies per outer row. Without LATERAL, the function call is treated as a constant; with LATERAL, the function is called per outer row, with the outer row's columns as arguments.

The syntax and the gotchas

The syntax is LATERAL placed before a subquery or function call in FROM:

SELECT c.id, o.created_at, o.amount FROM customers c, LATERAL (SELECT created_at, amount FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3) o;

Or with explicit JOIN syntax:

SELECT c.id, o.created_at, o.amount FROM customers c LEFT JOIN LATERAL (SELECT created_at, amount FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3) o ON true;

The LEFT JOIN LATERAL form is important when the inner subquery might return zero rows. Without LEFT JOIN, the outer row is excluded when the inner returns empty. With LEFT JOIN, the outer row is kept and the inner columns are NULL.

The ON true clause is the standard idiom for LATERAL joins; the join condition is already expressed inside the subquery's WHERE, so the outer join needs no additional condition. Some dialects require an explicit ON clause, hence ON true.

The gotchas concentrate around performance. LATERAL with an outer relation of millions of rows and an inner subquery that does index-less scans produces quadratic complexity. The inner subquery should have appropriate indexes, ideally a composite (filter_col, sort_col) for the top-N case. The planner cannot save you from a bad inner subquery any more than it can save you from a bad correlated subquery.

The performance comparison

Top-3 orders per customer, on a database with 100K customers and 10M orders:

Window function approach: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) plus WHERE rn <= 3. This scans the orders table once, sorts within each customer partition, computes row numbers, and filters. Cost is proportional to the orders count, regardless of how many you actually want.

LATERAL approach: for each customer, look up the top 3 orders by (customer_id, created_at DESC) index. Cost is proportional to (customer count) * (index lookup), which on this scale is roughly 100K * O(log n) index probes.

For top-3 per customer, the LATERAL form is typically an order of magnitude faster because the work scales with the (small) result set size rather than the (large) source table size. For top-1000 per customer, the window form starts to compete because the per-customer work in LATERAL grows proportional to the requested count.

Set-returning functions and unnest

The classic application of LATERAL with set-returning functions is exploding an array column or JSON column on a per-row basis with access to the row's other columns:

SELECT u.id, u.email, tag FROM users u, LATERAL unnest(u.tags) tag WHERE tag = 'premium';

Without LATERAL, unnest(u.tags) would not be allowed to reference u.tags because the subquery would not see the outer row's column. With LATERAL, the unnest is called once per row with that row's tags, producing one output row per (user, tag) combination.

The same pattern applies to jsonb_to_recordset for exploding JSON arrays of objects, regexp_matches for extracting multiple regex matches, and generate_series for producing range expansions per row.

Three patterns LATERAL is wrong for

First, simple equi-joins. If you want to match rows from two tables on equality, use a normal JOIN. LATERAL is for cases where the join logic involves per-row subqueries or function calls, not for plain matching.

Second, aggregations that can be expressed as GROUP BY. If you want sum(orders.amount) per customer, GROUP BY plus JOIN is fine and the planner will optimize it well. Reach for LATERAL only when you need top-N or multiple aggregates that GROUP BY makes awkward.

Third, queries against tables where the inner table is small enough that materialization is cheap. The LATERAL benefit is largest when the inner table is large and indexed; for small lookup tables, materializing once and joining is simpler and often as fast.

The application-side equivalent

Most ORMs do not generate LATERAL by default. The N+1 problem in ORMs—loading a parent collection, then iterating to load children per parent—is exactly the pattern LATERAL was designed to express in SQL. ORMs that support raw query escape hatches let LATERAL be used directly; ORMs that try to handle everything tend to materialize the full child table and filter in application code.

The application-side workaround for ORMs that do not support LATERAL is usually to issue a single bulk query with a window function or with a denormalized JSON column, then process in application code. This works but moves work off the database where the indexes are, which often hurts performance for small N.

Our use across the four products

DocuMint, CronPing, FlagBit, and WebhookVault are SQLite-based, and SQLite supports LATERAL-equivalent constructs through correlated subqueries and the JSON1 extension, with slightly different syntax and somewhat fewer optimization paths. The patterns transfer: top-N per group, per-row JSON expansion, set-returning function arguments.

The Postgres-migration plan includes LATERAL as part of the query refactoring for several specific endpoints. CronPing's status page endpoint, which loads the most recent N pings per monitor for many monitors, is a canonical LATERAL case. WebhookVault's per-endpoint recent-deliveries endpoint is the same pattern. FlagBit's per-flag recent-evaluations endpoint matches. DocuMint's per-customer recent-invoices endpoint matches.

The deeper observation is that LATERAL was added to the SQL standard in 1999 (SQL:1999) and to PostgreSQL in 9.3 (2013), but it remains underused relative to its value. The feature is mentioned in most Postgres documentation, but the patterns where it pays back the largest are not the ones developers reach for first. The result is a substantial class of queries that look like ORM N+1 problems but are actually one-LATERAL-rewrite away from being efficient single-shot queries against well-indexed data.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.