[Paper Review] Estudo de Viabilidade de uma Plataforma de Baixo Custo para Data Warehouse

Eduardo Cunha de Almeida|arXiv (Cornell University)|Aug 3, 2011

Big Data and Business Intelligence3 citations

TL;DR

This paper evaluates the feasibility of a low-cost data warehouse platform using open-source technologies—specifically PostgreSQL and GNU/Linux—through benchmarking complex, multi-user workloads. Results demonstrate that PostgreSQL can handle complex analytical queries, though performance bottlenecks in aggregation and query optimization highlight areas for improvement in data warehouse workloads.

ABSTRACT

Often corporations need tools to improve their decision making in a competitive market. In general, these tools are based on data warehouse platforms to mange and analyze large amounts of data. However, several of these corporations do not have enough resources to buy such platforms because of the high cost. This work is dedicated to a feasibility study of a low cost platform to data warehouse. We consider as a low cost platform the use of open source software like the PostgreSQL database system and the GNU/Linux operational system. We verify the feasibility of this platform by executing two benchmarks that simulate a data warehouse workload. The workload reproduces a multi-user environment with the execution of complex queries, which executes: aggregations, nested sub queries, multi joins, in-line views and more. Considering the results we were able to highlight some problems on the PostgreSQL database system, and discuss improvements in the context of data warehouse.

Motivation & Objective

• To assess the viability of a low-cost data warehouse platform using open-source software to reduce deployment costs for small and medium enterprises.
• To evaluate PostgreSQL's performance under realistic data warehouse workloads involving complex queries and multi-user access.
• To identify performance limitations and scalability issues in PostgreSQL when used as a data warehouse database system.
• To provide actionable recommendations for enhancing PostgreSQL's suitability for data warehouse environments.
• To demonstrate that open-source solutions can serve as cost-effective alternatives to commercial data warehouse platforms.

Proposed method

• Design and implement a benchmark suite simulating a real-world data warehouse workload with complex SQL operations.
• Use PostgreSQL as the database management system and GNU/Linux as the operating system to form the low-cost platform.
• Execute two benchmark tests under a multi-user environment to simulate concurrent complex queries.
• Include operations such as aggregations, nested subqueries, multi-joins, and in-line views to stress-test query execution.
• Measure performance metrics including query execution time, resource utilization, and system scalability.
• Analyze query execution plans and execution statistics to identify performance bottlenecks in PostgreSQL.

Experimental results

Research questions

• RQ1 Can a low-cost data warehouse platform based on PostgreSQL and GNU/Linux effectively handle complex analytical queries?
• RQ2 How does PostgreSQL perform under concurrent, complex query workloads typical of data warehouse environments?
• RQ3 What are the primary performance bottlenecks in PostgreSQL when used for data warehouse workloads?
• RQ4 To what extent can open-source software replace commercial data warehouse solutions in terms of functionality and scalability?
• RQ5 What optimizations are necessary to improve PostgreSQL’s performance for data warehouse workloads?

Key findings

• PostgreSQL successfully executed complex data warehouse queries involving aggregations, subqueries, and multiple joins.
• Query execution times were acceptable for moderate workloads, but performance degraded significantly with high concurrency and complex nested queries.
• The query planner in PostgreSQL showed suboptimal execution plans for certain types of complex queries, particularly those involving multiple self-joins.
• Index usage and statistics collection were critical for performance; outdated or missing statistics led to poor query plans.
• Resource contention and memory management issues emerged under heavy concurrent workloads, indicating scalability limitations.
• The study identified specific areas for improvement, such as enhanced query optimization and better support for materialized views in PostgreSQL for data warehouse workloads.