How blondig/lero-on-postgresql works
Project Type and Purpose
• Lero is a specialized system that extends PostgreSQL with machine-learning capabilities for query plan optimization. Its two primary stages are: (a) generating multiple execution plans via different policies and (b) selecting the optimal plan through a trained ranking model.
• The project is a demo implementation integrating modified PostgreSQL (with an applied patch) and a Python server that orchestrates plan generation, evaluation, and model retraining.
Analyzing the File Structure
• Notice top-level items like the patch file (0001-init-lero.patch) and PostgreSQL configuration (server.conf) – these indicate the custom integration into PostgreSQL.
• The “lero” directory contains the main server (server.py), utility scripts (train.py, test_script, utils.py, feature.py, model.py, card_picker.py), and components related to the Tree Convolution implementation (in lero/TreeConvolution) which is acknowledged in the README.
• The “lero/reproduce” directory holds reproduction files including pre-trained models (imdb_pw, stats_pw, tpch_pw) and SQL/database dump files (stats_db.sql) – these are used to validate or compare experimental results.
• The “test_script” folder includes scripts and configuration for running experiments on TPC-H queries – highlighting how training and live inference are integrated.
Key Components & Their Interactions
• PostgreSQL Engine (Modified):
• Represents the database system that is patched (via 0001-init-lero.patch) and configured in postgresql.conf.
• Acts as a client to the Lero server and uses preset host/port configurations.
• Lero Server:
• Central component (server.py) that communicates with PostgreSQL.
• It embodies both functionality stages: generating alternative query plans and evaluating them with the ML model.
• Reads configuration from server.conf (including model path settings) and initiates model loading and query plan evaluation.
• Machine Learning Model & Utilities:
• Scripts and modules such as model.py, feature.py, card_picker.py, and the TreeConvolution components (in lero/TreeConvolution) define the ranking/modeling part.
• The model is retrained periodically using train_model.py and its associated test and visualization scripts from test_script.
• Experiment and Reproduction Components:
• Scripts in “lero/reproduce” and files like stats_db.sql allow re-creation of experiments.
• They also show how different pre-trained models are loaded and tested via the server.
Relationships and Data Flow
• Database ↔ Lero Server:
• That the server acts as an intermediary that implements two stages: candidate plan generation and plan ranking.
• Within Lero Server:
• Decompose into sub-modules representing “Plan Generation” (which might be triggered from parts of server.py) and “Plan Selection” (ML driven, using model.py and associated utility modules).
• Configuration flow from server.conf into the server initialization.
• Training & Model Update Loop:
• That the updated model can be reloaded by the server, affecting subsequent plan selection decisions.
• External Dependencies:
• An element for external TPC-H data and the join-order benchmarks (as noted in the README), which feed query workload for the experiments.
Architectural Patterns and Design Principles
• The design leverages a separation of concerns:
• Dedicated components for low-level PostgreSQL operations (modified through patch and configuration).
• A standalone Python server that handles business logic (request handling and ML inference).
• A clear training loop that decouples model evaluation from query execution.
• Database components (e.g., in blue),
• Server-side logic and ML model (e.g., in green),
• Utility and experimentation scripts (e.g., in orange).