Folding Window Functions Into Ecto

The article starts from a trip-booking app that needs to highlight the two most popular trips in each category. It first solves the task in naive Elixir by loading all trips, grouping them in memory, sorting them by favorites and name, and taking the top results, then moves the same logic into PostgreSQL with a window function using row_number() partitioned by category.

It then shows how, in Ecto 2.0, the window query is built with Ecto.Adapters.SQL.query and a subquery that filters rows by row_number, because Ecto does not yet support OVER clauses or arbitrary FROM clauses. The benchmark section reports that the naive approach is only about 2x slower even with 20,000 trips, and the post ends by noting a hybrid join-based approach suggested by José Valim.

Reading notes

#

• The example app needs to surface the top two trips in each category.
• The first implementation loads trips into memory, groups them by category, sorts them by favorites and name, and takes the requested number from each group.
• The article treats that in-memory approach as too inefficient for production use when the goal is only a few results.
• PostgreSQL window functions are introduced as a way to calculate across related rows.
• row_number() with PARTITION BY category assigns positions within each category.
• Adding ORDER BY favorites DESC, name ASC makes the ranking deterministic.
• Ecto 2.0 does not provide built-in OVER support or arbitrary FROM clauses.
• The working Ecto version uses a raw SQL query with a subquery and filters rows where row_number is at most the requested limit.
• The benchmark compares the naive in-memory version with the window-function version across 5,000, 10,000, and 20,000 trips.
• The post says the naive strategy ends up only about 2x slower at 20,000 records.
• A later edit adds a hybrid join-based approach suggested by José Valim.