Adaptive Database Optimisation of Industrial IoT Workloads Using Machine Learning and Local Generative AI
Abstract
Industrial Internet of Things (IIoT) systems generate sensor workloads that require scalable and workload-aware database management. This study evaluates an adaptive PostgreSQL optimisation framework combining conventional indexing, machine-learning (ML) query-latency prediction, local Generative AI (GenAI), and Hybrid ML–GenAI reasoning. A High-Rack Storage System dataset with 19,634 observations and 18 sensor variables was normalised into 353,412 atomic sensor readings and replayed at four scales up to 10,602,360 records. Eight SQL workload families were benchmarked under no-secondary-index, single-index, and composite-index configurations, producing 480 measured executions. A leakage-aware ML pipeline predicted candidate latency, while local Qwen3:4B generated indexing recommendations from SQL structure, database scale, and pre-execution plan information. Across eight held-out workloads, all 24 GenAI requests completed successfully with 100% recommendation stability. Conventional composite indexing, ML-only, GenAI-only, and Hybrid ML–GenAI each achieved a 20.044% mean improvement over the unindexed baseline, 50% exact agreement with the empirical oracle, and 7.499 ms mean regret. The results show that AI can support database optimisation, but added decision complexity did not outperform a strong conventional strategy within the constrained action space.