Benchmarking Machine Learning Pipelines in PostgreSQL with TPCx-AI
摘要
Driven by advancements in model capabilities and ease of access, machine learning (ML) and artificial intelligence (AI) are increasingly applied across industry and government sectors. Traditionally, ML training and serving either relies on big external service providers such as AWS or MS Azure, or requires data to be transferred from databases or data lakes to local or cloud environments. Apart from dependencies on external ML frameworks, these type transfers not only introduce significant overhead but also pose risks to data security and data integrity. Integrating these technologies directly within database systems promises significant advantages, particularly for production environments. However, the performance and capability of database systems for various ML scenarios remain unclear. To address these uncertainties, this paper proposes transferring the TPCx-AI benchmark toolkit into PostgreSQL using the MADlib extension. This enables the entire ML pipeline-from data loading and preprocessing to training, scoring, and serving-to be executed within the database system. We present the implementation details and compare its performance with the traditional Python-based approach from the toolkit. The implementation focuses on traditional ML algorithms and does not include Deep Learning techniques. Our evaluation, leveraging the synthetic data generator PDGF and use cases provided by TPCx-AI, offers a comprehensive analysis of the benefits and shortcomings of in-database ML training with PostgreSQL and MADlib. On an aggregated level, it shows a comparable performance between both system for most use cases. While the Python approach excels at model training, PostgreSQL with Apache MADlib demonstrates superior performance in data processing and inference tasks.