IRO Journals
Journal of ISMAC

Adaptive Database Optimisation of Industrial IoT Workloads Using Machine Learning and Local Generative AI

Open Access
Published: 28 September, 2026
Pages: 324-344
  • Lakmali Shashika Karunarathne , Lakmali Shashika Karunarathne
    Lecturer, Department of Computer and Data Science, York St John University London Campus, United Kingdom
    Lecturer, Department of Computer and Data Science, York St John University London Campus, United Kingdom
  • Nalinda Somasiri Nalinda Somasiri
    Associate Professor, Department of Computer and Data Science, York St John University London Campus, United Kingdom
    Associate Professor, Department of Computer and Data Science, York St John University London Campus, United Kingdom
view PDF
view PDF

How to Cite

Karunarathne, Lakmali Shashika, and Nalinda Somasiri. 2026. “Adaptive Database Optimisation of Industrial IoT Workloads Using Machine Learning and Local Generative AI”. Journal of ISMAC 8 (4): 324-44. https://doi.org/10.36548/jismac.2026.4.001.
ACM ACS APA ABNT Chicago Harvard IEEE MLA Turabian Vancouver AMA

Download Citation

Endnote/Zotero/Mendeley (RIS) BibTeX

Keywords

Industrial Internet of Things
Database Optimisation
Machine Learning
Generative AI
Query Performance Prediction
Self-Tuning Databases

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.

References

  1. ur Rehman, M. H., I. Yaqoob, K. Salah, M. Imran, P. P. Jayaraman, and C. Perera. “The Role of Big Data Analytics in Industrial Internet of Things.” Future Generation Computer Systems 99 (2019): 247–259.
  2. Das, S., M. Grbic, I. Ilic, I. Jovandic, A. Jovanovic, V. R. Narasayya, M. Radulovic, M. Stikic, G. Xu, and S. Chaudhuri. “Automatically Indexing Millions of Databases in Microsoft Azure SQL Database.” In Proceedings of the 2019 International Conference on Management of Data, Association for Computing Machinery, 2019, 666–679.
  3. Pavlo, A., G. Angulo, J. Arulraj, H. Lin, J. Lin, L. Ma, P. Menon, T. C. Mowry, M. Perron, I. Quah, S. Santurkar, A. Tomasic, S. Toor, D. Van Aken, Z. Wang, Y. Wu, R. Xian, and T. Zhang. “Self-Driving Database Management Systems.” In 8th Biennial Conference on Innovative Data Systems Research (CIDR), 2017.
  4. Van Aken, D., A. Pavlo, G. J. Gordon, and B. Zhang. “Automatic Database Management System Tuning through Large-Scale Machine Learning.” In Proceedings of the 2017 ACM International Conference on Management of Data, Association for Computing Machinery, 2017, 1009–1024.
  5. Kraska, T., A. Beutel, E. H. Chi, J. Dean, and N. Polyzotis. “The Case for Learned Index Structures.” In Proceedings of the 2018 International Conference on Management of Data, Association for Computing Machinery, 2018, 489–504.
  6. Marcus, R., P. Negi, H. Mao, C. Zhang, M. Alizadeh, T. Kraska, O. Papaemmanouil, and N. Tatbul. “Neo: A Learned Query Optimizer.” Proceedings of the VLDB Endowment 12, no. 11 (2019): 1705–1718.
  7. Marcus, R., P. Negi, H. Mao, N. Tatbul, M. Alizadeh, and T. Kraska. “Bao: Making Learned Query Optimization Practical.” In Proceedings of the 2021 International Conference on Management of Data, Association for Computing Machinery, 2021, 1275–1288.
  8. Zou, B., J. You, Q. Wang, X. Wen, and L. Jia. “Survey on Learnable Databases: A Machine Learning Perspective.” Big Data Research 27 (2022): 100304.
  9. Bagui, S., M. Malagutti, R. Morelli, and M. Tamascelli. “Large Language Models in Database Management System Optimization: A Survey.” ACM Transactions on Intelligent Systems and Technology 17, no. 5 (2026): 1–37.
  10. Zhang, X., K. Khedri, and R. Rawassizadeh. “Can LLMs Substitute SQL? Comparing Resource Utilization of Querying LLMs versus Traditional Relational Databases.” In Proceedings of the 62nd Annual Meeting of the Association for Computational Linguistics, Volume 4: Student Research Workshop, Association for Computational Linguistics, 2024, 465–472.
  11. Karunarathne, L., S. Ganesan, K. Karunarathne, and N. Somasiri. “Database Optimization for Low-Latency Analytics with Adaptive Indexing.” Journal of Data Science and Intelligent Systems (2026).
  12. inIT – Institute Industrial IT and Ostwestfalen-Lippe University of Applied Sciences. High Storage System Data for Energy Optimization. Kaggle dataset, 2018. CC BY-NC-SA 4.0. https://www.kaggle.com/datasets/inIT-OWL/high-storage-system-data-for-energy-optimization.
  13. PostgreSQL Global Development Group. “PostgreSQL 18.3 Release Notes.” 2026. https://www.postgresql.org/docs/release/18.3/.
  14. PostgreSQL Global Development Group. “PostgreSQL 18 Documentation: Using EXPLAIN.” 2026. https://www.postgresql.org/docs/18/using-explain.html.
  15. PostgreSQL Global Development Group. “PostgreSQL 18 Documentation: Chapter 11 – Indexes.” 2026. https://www.postgresql.org/docs/18/indexes.html.
  16. PostgreSQL Global Development Group. “PostgreSQL 18 Documentation: Multicolumn Indexes.” 2026. https://www.postgresql.org/docs/18/indexes-multicolumn.html.
  17. Chen, T., and C. Guestrin. “XGBoost: A Scalable Tree Boosting System.” In Proceedings of the 22nd ACM SIGKDD International Conference on Knowledge Discovery and Data Mining, Association for Computing Machinery, 2016, 785–794.
  18. Yang, A., A. Li, B. Yang, et al. “Qwen3 Technical Report.” arXiv:2505.09388 (2025). https://arxiv.org/abs/2505.09388.