The pancake SQL pattern: combine your SQLs into one for 50x better performance.
This title could be clearer and more informative.Try out Clickbait Shieldfor free (5 uses left this month).
The 'Pancake SQL Pattern' is a query optimization technique that consolidates multiple SQL queries into a single query using window functions and ranking functions. Developed at Quesma while handling complex dashboards with 15+ panels, the approach reduced a 10-query panel to a single SQL statement by stacking aggregation layers (groupings with limits) and using window functions for metric calculations. This eliminated redundant full table scans, achieving a 50x performance improvement on ClickHouse. The pattern introduces a new intermediate representation called a 'pancake' in the Query DSL-to-SQL transformation pipeline, where aggregations are stacked in layers with metric 'fillings' between grouping layers. Alternatives like temporary tables and materialized views were considered but discarded due to management complexity or limited universality.
Table of contents
When Pancakes saved our SQL performanceThe problem: too many SQLs, too little efficiencyThe inspiration: Pancakes and SQLPancakes: the breakfast of SQL championsThe upside of pancakesAlternatives we considered (and discarded)Wrapping Up: Why Pancakes MatterSort: