Abstract
Database systems have historically been categorized into two distinct types: those optimized for transactional workloads (OLTP) and those designed for analytical queries (OLAP). Hybrid systems, commonly referred to as HTAP (Hybrid Transactional/Analytical Processing), have since been developed to perform well under mixed workloads. However, these systems are not heavily utilized in practice, with database practitioners instead relying on industry-standard systems such as PostgreSQL for transactional workloads and the emerging DuckDB for analytical ones. Little empirical work exists characterizing how these fundamentally distinct systems handle hybrid workloads, and this study aims to address that gap. While many hybrid benchmarks exist, testing them on non-hybrid systems is not straightforward and introduces many practical challenges. To address this, we designed a lightweight HTAP microbenchmark to specifically evaluate both PostgreSQL and DuckDB under hybrid workloads. The benchmark utilizes a TPC-C-like schema and transactional workload design alongside TPC-H Query 9 as the analytical component, with concurrent workers run in tandem over a shared dataset. We conducted a comprehensive set of experiments, systematically varying transactional workers, analytical workers, and dataset size while maintaining equivalent environments across both systems. A full evaluation of all 22 TPC-H queries across three data scales is also conducted to establish an isolated analytical performance baseline. Results indicate an empirical intersection point between the two systems where their architectural advantages shift. PostgreSQL performs better in lower-concurrency environments, where its throughput and latency are superior, however DuckDB scales significantly better as concurrency increases. At greater scales, DuckDB remains more negatively affected than PostgreSQL. Additionally, DuckDB experiences significant retry behavior and potential failures as concurrency increases, indicating additional overhead. Findings indicate that neither system is inherently superior under hybrid workloads. Rather, the optimal choice depends on concurrency profile, tolerance for completeness, and dataset scale. This study constructs an empirical foundation characterizing the general behavior exhibited by both systems subject to HTAP environments, while the lightweight, open-source microbenchmark allows practitioners to evaluate the intersection point for their specific application, or for researchers to expand on this study’s findings.
Table of Contents
Contents 1 Introduction 1 1.1 Motivation: The Rise of Hybrid Workloads . . . . . . . . . . . . . . . 1 1.2 The Practitioner Gap: Popular Databases vs. Specialized HTAP Systems 2 1.3 Contributions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 2 Background and Related Work 5 2.1 OLTP vs. OLAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.2 PostgreSQL: Architecture and Concurrency Model . . . . . . . . . . . 7 2.3 DuckDB: Architecture and Execution Model . . . . . . . . . . . . . . 8 2.4 The HTAP Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.5 Existing Popular Benchmarks . . . . . . . . . . . . . . . . . . . . . . 10 2.5.1 TPC-C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.5.2 TPC-H . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.5.3 HTAP Benchmarks: The Need For Supplemental Benchmark Development . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3 Hybrid Microbenchmark Design 16 3.1 Design Goals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.2 Transactional Workload: TPC-C-Like Component . . . . . . . . . . . 18 3.2.1 Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.2.2 Load Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 i 3.2.3 NewOrder and Payment Transactional Workload . . . . . . . . 24 3.3 Analytical Workload: TPC-H-Like Component . . . . . . . . . . . . . 26 3.4 Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 4 Experimental Setup and Methodology 30 4.1 HTAP Experimental Parameters . . . . . . . . . . . . . . . . . . . . . 30 4.1.1 Varying OLTP Client Count (Fixed Reporters) . . . . . . . . 32 4.1.2 Varying Reporter Count (Fixed Clients) . . . . . . . . . . . . 33 4.1.3 Varying Dataset Scale (Warehouse Count) . . . . . . . . . . . 34 4.2 TPC-H Methodology . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 4.3 Hardware and Cloud Environment . . . . . . . . . . . . . . . . . . . . 38 5 Results: TPC-H Benchmark 40 5.1 TPC-H 22 Queries Results: DuckDB vs. PostgreSQL . . . . . . . . . 40 6 Results: Hybrid Microbenchmark 46 6.1 Reporting Query - TPC-H Q9: Selection Reasoning . . . . . . . . . . 46 6.2 HTAP Performance at Small Scale (W=20) . . . . . . . . . . . . . . 47 6.2.1 Transactional Performance Under Increasing Analytical Load . 47 6.2.2 Analytical Throughput Under Increasing Analytical Load . . . 55 6.2.3 Transactional Scalability Under Fixed Analytical Load (Reporters = 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 6.2.4 Exploring Behavior at Clients Range 1 Through 8 . . . . . . . 64 6.2.5 Summary: General Behaviors (Warehouses = 20) . . . . . . . 70 6.3 HTAP Performance at Large Scale (W=100) . . . . . . . . . . . . . . 72 6.3.1 Transactional Throughput Under Increasing Analytical Load . 72 6.3.2 Analytical Throughput Under Increasing Analytical Load . . . 78 6.3.3 Transactional Scalability Under Fixed Analytical Load (Reporters = 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 6.3.4 Summary: Data Scaling Behavior (From 20 to 100 Warehouses) 85 7 Conclusion: Practical Guidance For Hybrid Workloads 87 8 Limitations and Necessary Extensions 90 8.1 Microbenchmark Limitations . . . . . . . . . . . . . . . . . . . . . . . 90 8.2 Threats to Validity . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 8.3 Future Improvements And Examples For Further Research . . . . . . 93 A Repository 96 Bibliography 97
About this Honors Thesis
Rights statement
- Permission granted by the author to include this thesis or dissertation in this repository. All rights reserved by the author. Please contact the author for information regarding the reproduction and use of this thesis or dissertation.
| School |
|
|---|
| Department |
|
|---|
| Degree |
|
|---|
| Submission |
|
|---|
| Language |
|
|---|
| Research Field |
|
|---|
| Keyword |
|
|---|
| Committee Chair / Thesis Advisor |
|
|---|
| Committee Members |
|
|---|