# blondig/lero-on-postgresql architecture

> Architecture diagram of the GitHub repository blondig/lero-on-postgresql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-11). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/blondig/lero-on-postgresql
- Repository: https://github.com/blondig/lero-on-postgresql

## Overview

##### 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).

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Subgraph: Modified PostgreSQL Engine
    subgraph "Modified PostgreSQL Engine"
        postgresPatch["PostgreSQL Patch"]:::database
        postgresConf["PostgreSQL Config"]:::database
    end

    %% External TPC-H Workload
    tpch["TPC-H Workload"]:::utility

    %% Subgraph: Lero Server with internal modules
    subgraph "Lero Server"
        serverEngine["Plan Generation Engine"]:::server
        subgraph "ML Inference Module"
            mlModel["ML Model"]:::ml
            featureExtraction["Feature Extraction"]:::ml
            cardPicker["Card Picker"]:::ml
            treeConvolution["Tree Convolution Module"]:::ml
        end
    end

    %% Subgraph: Training & Experimentation
    subgraph "Training & Experimentation"
        trainingScript["Training Script"]:::utility
        testModelTraining["Test Model Training"]:::utility
        visualization["Visualization"]:::utility
        reproducibility["Reproducibility Module"]:::utility
        testingScripts["Testing Scripts"]:::utility
    end

    %% Connections between External Components and Lero Server
    tpch -->|"supplies queries"| serverEngine
    postgresConf -->|"sends queries"| serverEngine
    serverEngine -->|"executes plan"| postgresPatch

    %% Internal flow within Lero Server
    serverEngine -->|"generate candidates"| mlModel
    mlModel -->|"extract features"| featureExtraction
    featureExtraction -->|"select cards"| cardPicker
    mlModel ---|"uses"| treeConvolution
    cardPicker -->|"ranking result"| serverEngine

    %% Training and Model Update Loop
    trainingScript -->|"update"| mlModel
    testModelTraining -->|"triggers update"| trainingScript
    testingScripts -->|"validates"| trainingScript
    reproducibility -->|"provides data"| trainingScript

    %% Styles
    classDef database fill:#aee1ff,stroke:#0b3d91,stroke-width:2px;
    classDef server fill:#c1f0c1,stroke:#006400,stroke-width:2px;
    classDef ml fill:#c1ffc1,stroke:#008000,stroke-width:2px;
    classDef utility fill:#ffd59a,stroke:#cd853f,stroke-width:2px;

    %% Click Events
    click postgresPatch "https://github.com/blondig/lero-on-postgresql/blob/main/0001-init-lero.patch"
    click postgresConf "https://github.com/blondig/lero-on-postgresql/blob/main/lero/server.conf"
    click serverEngine "https://github.com/blondig/lero-on-postgresql/blob/main/lero/server.py"
    click mlModel "https://github.com/blondig/lero-on-postgresql/blob/main/lero/model.py"
    click featureExtraction "https://github.com/blondig/lero-on-postgresql/blob/main/lero/feature.py"
    click cardPicker "https://github.com/blondig/lero-on-postgresql/blob/main/lero/card_picker.py"
    click treeConvolution "https://github.com/blondig/lero-on-postgresql/tree/main/lero/TreeConvolution/"
    click trainingScript "https://github.com/blondig/lero-on-postgresql/blob/main/lero/train.py"
    click testModelTraining "https://github.com/blondig/lero-on-postgresql/blob/main/lero/test_script/train_model.py"
    click visualization "https://github.com/blondig/lero-on-postgresql/blob/main/lero/test_script/visualization.ipynb"
    click reproducibility "https://github.com/blondig/lero-on-postgresql/tree/main/lero/reproduce/"
    click testingScripts "https://github.com/blondig/lero-on-postgresql/tree/main/lero/test_script/"
```
