2025-10-02 –, Coffee room
Language: English
We use DuckDB as a backend to generate JuMP variables and constraints using SQL. This enables memory-efficient slicing, reusable indices, and traceable constraints. In TulipaEnergyModel.jl, this reduced preprocessing time and memory usage by up to 50%, improving scalability and clarity.
Data-centric modelling in energy, logistics, and finance often starts with a familiar dilemma: the dataset lives happily in a column store, while the optimisation problems are represented as arrays and sparse matrices. In this talk, we share a pragmatic solution to bridge that gap, using DuckDB as a lightweight SQL backend to generate JuMP variables and constraints directly from database queries. Our workflow keeps the data where it is efficient—inside DuckDB—while producing linearised indices that JuMP processes quickly and efficiently. We decouple index construction from model construction. This separation brings three concrete benefits:
* Memory-aware slicing. DuckDB streams the relevant rows, so the Julia heap never needs to see the full fact table.
* Reusable indices. Once built, the same index table feeds multiple model constraints.
* Transparent provenance. Each constraint row carries its original SQL, providing an inherent audit trail.
When implemented in the package Tulipaenergymodel.jl, the DuckDB approach helped reduce preprocessing time and memory footprint by 40 - 50%, making the code cleaner and more scalable for large optimization problems.
Research Software Engineer at the Netherlands eScience Center.
