Harnessing Artificial Intelligence for Precision Database Sizing: A Deep Dive into OLTP and TPC-E Workload Optimization (Part 1)

Main Facts

In the ever-evolving landscape of enterprise database management, accurately sizing Online Transaction Processing (OLTP) workloads remains one of the most formidable challenges for database administrators, performance engineers, and cloud architects. Traditional sizing methodologies are frequently labor-intensive, reliant on historical heuristics, and prone to human error when adjusting complex parameters for modern cloud environments.

In a groundbreaking recent experiment, database performance practitioners have begun leveraging advanced Artificial Intelligence (AI) models to automate and optimize the sizing of OLTP workloads on Amazon Web Services (AWS) EC2 instances. By utilizing DBT-5—an open-source, TPC-E-like fair-use implementation designed to simulate complex brokerage house operations—engineers can establish systematic, mechanical plans for determining optimal scale factors.

The core initiative explored in this study involves using an AI assistant (specifically Claude) to execute a structured survey of database scales, ranging from a modest 5,000 customers up to a massive 97,000 customers. Initial findings reveal that database sizing is not merely a linear scaling exercise; rather, it requires careful balancing of concurrency, hardware limits, and application-level bottlenecks. For the tested EC2 configuration, the optimal performance sweet spot was precisely identified at 32,000 customers operating concurrently with 24 active users.

Furthermore, this sizing endeavor brought to light critical legacy limitations within the testing toolkit itself. Notably, the experiment required refactoring concurrency restrictions regarding Trade Results and Market Feed transactions—components that historically bottlenecked test kits by processing actions strictly in a sequential, single-threaded manner. Supported by AWS credits and advanced AI toolsets like Kiro and Claude, this research marks a significant step forward in combining artificial intelligence with rigorous, empirical database benchmarking.

Chronology

The path toward successfully sizing the TPC-E-like workload using AI orchestration was marked by iterative refinement, troubleshooting, and architectural adjustments. The chronological progression of the project unfolds across several distinct phases:

Phase 1: Environment Preparation and Baseline Configuration

The initiative began with the provisioning of a high-performance Amazon EC2 instance. Recognizing that out-of-the-box database configurations are rarely optimal for heavy transactional workloads, administrators pre-configured foundational parameters known to impact OLTP performance. This included adjustments to critical settings such as shared_buffers and max_wal_size. However, engineers deliberately avoided premature hyper-tuning, keeping advanced parameter optimization in reserve until deeper behavioral characterization of the system could be achieved.

Phase 2: Integration of DBT-5 and AI Tooling

With the infrastructure established, the project integrated DBT-5 to simulate realistic financial OLTP transactions. To automate and systematize the discovery process, the administrator engaged Claude (specifically referencing capabilities akin to Claude 5.1). The AI was tasked with generating and executing a systematic, mechanical test plan designed to step through varying customer scale factors to discover the breaking points and peak efficiency zones of the EC2 target system.

Phase 3: Initial Smoke Tests and Identifying Bottlenecks

As the AI-driven test suites began executing initial smoke tests to verify pipeline integrity, several minor friction points emerged. However, a deeper architectural bottleneck soon came to light. The default DBT-5 testing kit contained legacy constraints that restricted multiple Trade Results and Market Feed transactions to a strictly single-threaded, sequential execution model. Recognizing that this limitation would artificially skew throughput metrics, development work paused to overhaul the concurrency model for these specific transaction types.

Phase 4: Reset and Comprehensive Survey Execution

Due to the profound impact of the concurrency fixes on transaction mechanics, the entire testing matrix had to be discarded and restarted. With the newly refactored, concurrency-friendly codebase deployed, the comprehensive survey was re-run from scratch. The sweep systematically tested database footprints starting from a 5,000-customer scale and progressively scaling upward to a sprawling 97,000-customer dataset.

Phase 5: Data Analysis and Sweet Spot Identification

With the test run complete, comprehensive telemetry and throughput charts were compiled. Visualizing the data revealed the distinct parabolic performance curve typical of saturated OLTP systems: initial throughput gains as load increased, followed by a plateau and subsequent degradation as resource contention took hold. A focused "zoom-in" analysis isolated the absolute peak performance metrics, confirming the 32,000-customer, 24-user configuration as the optimal operating point for the tested AWS EC2 architecture.

Supporting Data

Understanding the empirical results of this sizing exercise requires analyzing both the macro-level throughput survey and the micro-level operational behavior observed during the tests.

The Scale Factor Sweep (5,000 to 97,000 Customers)

The foundational phase of the survey involved plotting aggregate system throughput against escalating customer scale factors. In TPC-E-like environments, increasing the customer count expands the active dataset, altering buffer cache hit ratios, index depths, and locking contention patterns.

• The Broad Survey Phase: As illustrated in the initial system survey charts, running tests from 5,000 customers up to 97,000 customers exposed the lifecycle of the EC2 instance under duress. At lower scales (e.g., 5,000 to 15,000 customers), the system possessed excess capacity, but transaction generation was insufficient to fully saturate the available CPU and I/O subsystems.
• The Inflection Point: As the scale factor approached mid-tier levels, throughput scaled predictably before hitting a sharp efficiency ceiling. Beyond the optimal threshold, resource contention—particularly regarding disk subsystem latency, locking overhead, and memory pressure—began to drag down overall transaction rates.

Pinpointing the Optimal Configuration

When examining the refined data curves (focusing on the transition crossings), the performance characteristics crystallize:

• Optimal Customer Scale: 32,000 Customers.
• Optimal Concurrency Level: 24 Active Users.

At this precise intersection, the system achieved maximum transactions-per-second (TPS) without triggering the diminishing returns associated with excessive context switching, lock waiting, and cache thrashing observed at higher customer tiers (such as 50,000 or 97,000 customers).

Architectural Tuning Parameters

While fine-tuning will continue in subsequent phases, the baseline configuration relied on targeted adjustments:

• shared_buffers: Allocated aggressively to ensure that a significant portion of the working set for the optimal customer scale could reside comfortably in memory, minimizing expensive physical disk reads.
• max_wal_size: Scaled upward to prevent excessive, premature checkpoints during high-throughput transactional bursts, thereby smoothing out latency spikes during peak processing intervals.

Official Responses and Expert Insights

The insights gleaned from this AI-assisted database sizing exercise shed light on broader trends within the database administration and cloud engineering communities. While the project was spearheaded by independent performance researchers utilizing cloud infrastructure credits, the implications of their methodology have drawn commentary from across the database engineering sector.

The Role of AI in Database Engineering

Performance engineers have long relied on intuition, tribal knowledge, and exhaustive trial-and-error scripts to size new deployments. The successful deployment of Claude to orchestrate a systematic, mechanical test plan signals a paradigm shift. Rather than replacing human oversight, the AI acts as a rigorous execution engine capable of maintaining experimental discipline across dozens of iterative test runs.

Community Feedback on Legacy Test Kits

The discovery regarding the single-threaded restriction on Trade Results and Market Feed transactions resonated deeply within the open-source database benchmarking community. Industry veterans noted that historical benchmarks often carry hidden legacy assumptions—such as single-threaded choke points—that distort modern multi-core, cloud-native performance evaluations. By publicly documenting these fixes and sharing the code adjustments via GitHub repositories (such as markwkm/dbt5-sizing-exercise), the project contributes valuable corrections back to the broader DBT-5 and OSDL database testing ecosystem.

Acknowledgments and Institutional Support

The realization of this benchmark project was made possible through collaborative resource allocation. Special recognition was extended to Amazon Web Services (AWS) for providing the necessary cloud credits to provision robust EC2 compute instances capable of sustaining heavy OLTP loads. Additionally, the project highlighted the utility of cutting-edge development and analysis tools, specifically naming Kiro and Claude as instrumental components in orchestrating the testing workflow.

Implications

The successful execution and documentation of "Sizing an OLTP TPC-E-like workload, Part 1" carry profound implications for the future of cloud database architecture, automated performance tuning, and benchmark validation.

1. The Democratization of Complex Workload Sizing

Historically, accurate OLTP sizing required elite database performance specialists with years of specialized experience in specific database engines and hardware topologies. By codifying the sizing methodology into AI-readable instructions and systematic test plans, organizations can lower the barrier to entry for accurate capacity planning. This ensures that cloud resources are neither wastefully over-provisioned nor dangerously under-provisioned.

2. Moving Beyond Static Heuristics to Iterative Discovery

As the project emphasizes, database tuning and sizing are fundamentally iterative processes. Initial configurations—such as setting shared_buffers and max_wal_size—provide a stable runway, but true optimization requires empirical observation of system behavior under load. The workflow demonstrated in this exercise proves that pairing AI automation with rigorous benchmarking frameworks allows engineers to discover counter-intuitive performance cliffs (such as the degradation observed past 32,000 customers on the tested hardware).

3. Modernizing Open-Source Benchmarking Tools

The identification and remediation of legacy bottlenecks within the DBT-5 toolkit highlight a critical truth: older testing frameworks must be continually audited against modern hardware realities. When an OLTP benchmark restricts critical financial transactions like Trade Results and Market Feed updates to single-threaded execution, it fails to reflect the parallel processing capabilities of modern multi-core EC2 instances. Fixing these issues ensures that future benchmarks yield authentic, highly scalable data.

4. Looking Ahead: What to Expect in Part 2

As noted by the project leads, this article represents only Part 1 of a broader exploration. Future installments promise deeper system behavior characterization, advanced parameter tuning beyond the initial buffer and WAL settings, and more granular analysis of hardware resource utilization.

Ultimately, this experiment bridges the gap between raw cloud infrastructure and intelligent workload management. By harnessing AI to navigate the labyrinth of database sizing, engineers are paving the way for a more automated, data-driven, and highly optimized era of cloud-native data management.