  Although dominant for tabular data, ML libraries that train tree models over
normalized databases (e.g., LightGBM, XGBoost) require the data to be
denormalized as a single table, materialized, and exported. This process is not
scalable, slow, and poses security risks. In-DB ML aims to train models within
DBMSes to avoid data movement and provide data governance. Rather than modify a
DBMS to support In-DB ML, is it possible to offer competitive tree training
performance to specialized ML libraries...with only SQL?
  We present JoinBoost, a Python library that rewrites tree training algorithms
over normalized databases into pure SQL. It is portable to any DBMS, offers
performance competitive with specialized ML libraries, and scales with the
underlying DBMS capabilities. JoinBoost extends prior work from both
algorithmic and systems perspectives. Algorithmically, we support factorized
gradient boosting, by updating the $Y$ variable to the residual in the
non-materialized join result. Although this view update problem is generally
ambiguous, we identify addition-to-multiplication preserving, the key property
of variance semi-ring to support rmse, the most widely used criterion.
System-wise, we identify residual updates as a performance bottleneck. Such
overhead can be natively minimized on columnar DBMSes by creating a new column
of residual values and adding it as a projection. We validate this with two
implementations on DuckDB, with no or minimal modifications to its internals
for portability. Our experiment shows that JoinBoost is 3x (1.1x) faster for
random forests (gradient boosting) compared to LightGBM, and over an order
magnitude faster than state-of-the-art In-DB ML systems. Further, JoinBoost
scales well beyond LightGBM in terms of the # features, DB size (TPC-DS
SF=1000), and join graph complexity (galaxy schemas).
