Actually using USING KEY: Exploring Practical Applications of DuckDB’s New Recursive CTE Semantics
Back in 2023,
A Fix for the Fixation on Fixpoints
proposed new semantics
for recursive Common Table Expressions (CTEs) in SQL. As of DuckDB version 1.3,
we have implemented the proposed USING KEY semantics, which promises significant
improvements in both performance and memory consumption for certain classes of
recursive queries.
Since then, we have seen a growing interest in USING KEY from database
practitioners. In this bachelor thesis, we aim to explore practical applications of
the USING KEY semantics in DuckDB. Specifically, we want to explore how USING KEY
can be effectively utilized in real-world scenarios, particularly in graph algorithms
and hierarchical data processing. We want to identify use cases where USING KEY can
offer substantial benefits over traditional recursive CTEs, and conversely, we want to
identify scenarios where USING KEY may not be the optimal choice.