You are still writing correlated subqueries that iterate row by row, or joining aggregated subqueries that scan the same table twice. Postgres LATERAL JOINs solve both problems in a single pass: the top-N-per-group pattern, the nearest-neighbor search, and the set-returning-function explode, all with an index-friendly execution plan.
Your "latest status per device" query takes 800 ms with window functions and self-joins. Postgres DISTINCT ON solves it in 8 ms with a single index scan. Here is the syntax, the index strategy, and the gotchas that make the difference between a fast dashboard and a slow one.
Your concurrent transactions pass tests individually but corrupt data in production. Read Committed, Repeatable Read, and Serializable behave differently in Postgres than any other database. Here is how dirty reads, lost updates, phantom reads, and write skew actually manifest in application code, and the isolation level that fixes each one.
Hierarchical data (org charts, comment threads, file trees) looks unfriendly in SQL until you discover recursive CTEs. One query, no application loops, no N+1. Here is the pattern, the four common shapes, and the performance considerations that decide whether it scales.
Most developers learn SQL aggregations, hit a wall, and pull data into the application to compute running totals or per-group ranks. Window functions are the SQL feature that replaces that round trip with one query, and most teams use less than 10% of what they can do.