Source-linked AI summary
DBRepro: Automated Database Synthesis via a Hybrid Constraint-Solving Approach for Reproducing Slow Queries
Zhaoyang Zhang, Shuang Liu, Dengfeng Xu, Wei Lu, Jianquan Leng, Sheng Du, Xiaoyong Du
TL;DR
Slow-query diagnosis needs privacy-preserving proxy databases that preserve global statistics while enforcing exact local cardinalities and production-equivalent plans. DBRepro formulates synthesis as constrained distribution synthesis, progressively adjusting a statistics-based distribution with hybrid solvers. On benchmarks and a nearly 1 TB KingbaseES dataset, it improves cardinality, latency-proportion, and plan-consistency outcomes over baselines.
Problem
Offline diagnosis requires proxy databases because live debugging risks resource contention and privacy regulations restrict copying production data, while existing methods cannot preserve global statistics and exact local cardinalities together.
Method
DBRepro progressively adjusts an initial statistics-based distribution using heuristic inference and distribution fusion for selection constraints and Scaling Factor-enhanced constraint programming for non-PK-FK joins.
Results
DBRepro reduces cardinality error by up to 20.3% over data-driven baselines, lowers latency-proportion error by 21.5%, reproduces 15% more consistent execution plans than workload-aware methods, and is validated on a nearly 1 TB KingbaseES dataset.
Takeaways & Limitations
DBRepro supports high-fidelity offline slow-query reproduction using non-intrusive metadata without online query re-execution or base-table scans.
Takeaways & Limitations
Evaluation covers PostgreSQL and KingbaseES, so the selected systems cannot represent all relational database systems.
Abstract
from arXiv · showhide
Slow queries frequently cause severe performance bottlenecks in database management systems. Diagnosing their root causes online risks exacerbating resource contention, while data privacy regulations often prohibit copying production data to test environments. Synthesizing a proxy database from non-intrusive metadata that induces the query optimizer to generate the same physical execution plans is therefore critical for offline diagnosis. High-fidelity reproduction requires preserving global statistical distributions while enforcing exact local cardinalities. Existing data-driven and workload-aware approaches cannot satisfy both requirements simultaneously. We present DBRepro, an automated end-to-end framework that formulates database generation as a constrained distribution synthesis problem. DBRepro initializes a global distribution from lightweight column statistics, extracts execution constraints from target queries, and progressively adjusts the distribution to satisfy these constraints while preserving the global distribution. Experiments on TPC-H and SSB show that DBRepro reduces cardinality error by up to 20.3% over a data-driven baseline while maintaining identical plan consistency. Compared with a workload-aware baseline, it reproduces 15% more consistent execution plans and reduces latency proportion error by 21.5%. We further validate DBRepro on a nearly 1 TB real-world dataset managed by KingbaseES, where it reproduces the execution performance of complex slow queries with high fidelity.
1 Introduction
DBRepro addresses offline slow-query diagnosis by synthesizing proxy databases from non-intrusive metadata while targeting production-equivalent execution plans. It combines global statistical preservation with exact local cardinality constraints through hybrid constraint solving.
- Slow analytical queries can monopolize CPU, memory, and I/O resources, causing severe contention, reduced throughput, or service unavailability.
- Offline proxy-database synthesis is needed because live diagnosis risks worsening resource contention and privacy regulations restrict copying production data.
- Existing data-driven methods generally preserve plan structure but struggle with cross-column correlations and multi-table join semantics, preventing strict local-cardinality control.
- DBRepro formulates synthesis as constrained distribution synthesis, progressively adjusting an initial statistics-based distribution to satisfy query constraints while preserving global characteristics.
- DBRepro combines heuristic probabilistic inference and distribution fusion for selection constraints with Scaling Factor-enhanced constraint programming for non-PK-FK joins.
- DBRepro achieves high-fidelity reproduction on TPC-H, SSB, and a nearly 1 TB real-world dataset, outperforming state-of-the-art baselines in fidelity and practicality.
2 Preliminaries
DBRepro uses schema, statistics, workload queries, and physical plans as non-intrusive inputs for synthesizing a structurally valid proxy database. Annotated plans yield cardinality constraints that specify exact operator outputs, including selection and join requirements.
- Production extraction is limited to aggregated statistics and workload artifacts, including schema, column statistics, queries, and physical execution plans.
- Database schema H specifies tables, attributes, data types, primary keys, and foreign keys that the synthetic database must preserve.
- MCVs with frequencies and histogram buckets provide discrete and continuous descriptions of column distributions.
- Preserving column statistics supports the optimizer’s selection of join orders and physical operators, whereas distortion can produce divergent plans.
- Cardinality constraints define required operator output sizes, while selection constraints cover unary, arithmetic, and logical predicates.
- Join cardinality constraints require specified joins to produce exact matching-row counts, including joins involving non-key columns.
3 Problem Formulation
The paper defines high-fidelity synthesis as preserving schema and statistics while reproducing production plan structure, operator cardinalities, and relative latency. It also requires these properties to generalize to unseen queries from the same application scenario.
- High-fidelity synthesis must satisfy cardinality constraints while maximally preserving the global statistical distributions defined by database statistics.
- The formal problem takes schema H, statistics S, slow queries Q, and annotated query plans P̂ as inputs to produce a synthetic proxy database D̃.
- Schema consistency requires the proxy database to conform to the structural and integrity constraints defined by H.
- Statistical consistency requires synthetic single-column statistics to closely match production statistics and preserve the optimizer’s global cost baseline.
- Plan structural consistency requires generated plans to be topologically identical to annotated production plans, preserving join orders and operator types.
- Latency proportion consistency compares operator latency relative to total query latency because absolute times may differ across production and simulation environments.
- Generalizability requires unseen queries from the same application scenario to maintain plan, cardinality, and latency properties.
4 Methodology
DBRepro captures production metadata, models an initial global distribution, and progressively solves selection and join cardinality constraints to produce a constraint-compliant final distribution.
- Runtime Context Capture and Analysis: DBRepro captures schema, column statistics, queries, and physical execution plans without accessing underlying raw production data.The Runtime Context Analyzer processes these artifacts into abstract query plans and cardinality constraints for simulation.
- Runtime Context Capture and Analysis: The Data Distribution Manager initializes a global distribution from column statistics, while the Constraint Manager receives processed selection and join constraints.Statistics and constraints are routed separately after analysis in the production environment.
- Data Distribution Modeling: Statistics are merged into a cumulative distribution function alongside most-common values to support probabilistic data-distribution inference.This modeling combines discrete MCVs with continuous histograms.
- Hybrid Constraint Solving: Iterative Proportional Fitting adjusts the initial distribution to satisfy deterministic cardinalities while minimizing deviation from global statistical distributions.The paper identifies KL divergence as an example of the minimized deviation.
- Solving Selection Cardinality Constraints: DBRepro resolves selection constraints progressively using heuristic inference, distribution fusion, data binding, and post-generation sampling.Unary constraints are handled first, followed by logical and arithmetic constraints.
- Solving Join Cardinality Constraints: For non-PK-FK joins, DBRepro uses frequency-based scaling factors to model fan-out and replace rigid primary-key uniqueness assumptions with weighted constraints.The scaling factor for each key is its frequency in the left view; the method is illustrated with a target join cardinality of 9 and frequencies {3, 2, 1}.
- Hybrid Constraint Solving: After resolving selection and join constraints, DBRepro produces a final distribution that satisfies global statistics and local cardinalities and drives tuple generation.The final distribution is the blueprint for physically synthesizing the database.
5 Evaluation
The evaluation examines DBRepro’s fidelity and efficiency against existing methods, its generalization to unseen queries, and its performance on commercial DBMSs in industrial scenarios.
- Research Questions: DBRepro is evaluated against existing generation methods for high-fidelity slow-query reproduction and end-to-end efficiency.This is addressed by research question RQ1.
- Research Questions: The evaluation tests DBRepro’s generalization to unseen queries on the same benchmark schema.This is addressed by research question RQ2.
- Research Questions: The evaluation measures DBRepro’s performance on commercial DBMSs in real-world industrial scenarios.This is addressed by research question RQ3.
5.1 Experimental Setup
The experiments use PostgreSQL on TPC-H and SSB, compare DBRepro with data-driven and workload-aware baselines, and include a KingbaseES industrial-scale evaluation.
- Industrial Environment: The RQ3 practicality evaluation uses KingbaseES V8R6C9B14 on dual 128-core HiSilicon Kunpeng-920 processors with 1 TB RAM.The commercial system manages terabyte-scale data.
- Baselines: DBRepro is compared with RSGen, a workload-independent data-driven generator, and Hydra, a workload-aware linear-programming regenerator.Hydra supports only a restricted set of filter and equi-join operators and requires client-side schema anonymization.
5.2 RQ1: Reproduction Fidelity
DBRepro combines global statistical preservation with exact local cardinality constraints to reproduce query plans and downstream execution behavior. Across TPC-H and SSB, it improves cardinality and latency fidelity while reducing online extraction overhead despite higher offline generation cost.
- Plan Structural Consistency: DBRepro and RSGen achieve the highest structural consistency because they preserve global statistical distributions.Workload-aware methods distort the cost model by enforcing cardinality constraints without preserving global statistics.
- Relative Error of Cardinality: 12.4% selection error and over 25.6% join error were observed for RSGen on valid queries.The errors result from assuming column independence and failing to capture complex multi-column and cross-table correlations.
- Relative Error of Cardinality: 20.3% lower all-queries cardinality error than data-driven baselines.Progressive distribution evolution enforces exact cardinalities while preserving non-constrained data according to authentic statistics.
- Relative Error of Latency Proportion: 3.29% TPC-H and 1.06% SSB valid-query latency proportion errors were achieved by DBRepro.Accurate statistics preserve plan structures, while exact cardinality control prevents unintended memory spilling and physical overheads.
- Extraction and Generation Efficiency: Figure 6 separates extraction and generation efficiency using upper hatched and lower solid bars, respectively.Touchstone reports only generation time because its constraint formulation requires manual construction and lacks an automated extraction stage.
- Extraction and Generation Efficiency: DBRepro is approximately 2× slower than Mirage during data generation.Its offline generation cost is offset by extraction that avoids full-table scans and is independent of underlying data volume, unlike RSGen and Mirage.
5.3 RQ2: Generalization and Statistical Fidelity
DBRepro generalizes to unseen queries by preserving global distributions while satisfying complex constraint chains. It maintains execution structures across complexity levels and approaches the reference statistics, with targeted MCV adjustments supporting exact selection constraints.
- Unseen Query Generalization: DBRepro’s generalization evaluation uses 20 unseen queries spanning three complexity levels.The queries were synthesized by GPT-4o from benchmark schemas and workloads, then manually verified for syntactic correctness and successful execution.
- Statistical Fidelity: Workload-aware methods substantially distort base distributions by enforcing isolated query constraints without a global statistical anchor.RSGen and DBRepro remain closer to the distributions underlying optimizer estimates.
- Statistical Fidelity: DBRepro approaches the PostgreSQL sampling reference for NDV and histograms, while its average MCV error is 5% higher.Distribution Fusion adjusts selected MCV frequencies when exact selection constraints conflict with initial statistics.
- Statistical Fidelity: DBRepro and RSGen outperform workload-aware methods at every range-query selectivity from 1% to 50%.The range queries probe histogram fidelity and probability-mass allocation across value ranges.
5.4 RQ3: Practicality in Commercial DBMS
DBRepro is evaluated on KingbaseES using a nearly 1 TB proxy database and six complex industrial slow queries. It reproduces plan structures and closely matches cardinality and latency behavior, including the challenging Q3 case.
- Industrial evaluation: A nearly 1 TB KingbaseES proxy database represented five schemas, nearly 200 base tables, over 5,000 partitions, and six production slow queries.The target queries averaged more than 100 seconds of execution time.
- Reproduction fidelity: DBRepro reproduced identical plan structures across all six real-world slow queries.The queries span 47–207 GB, 1–6 base tables, 2–8 scans, 0–5 joins, and execution times of 10.8–234.0 seconds.
- Reproduction fidelity: 0.11%, 3.27%, and 4.71% were the mean SCC, JCC, and latencyproportion errors, respectively, while SCC error stayed below 0.64% for every query.Q6 was most difficult because most joins were non-PK-FK, requiring estimated scaling factors from lightweight statistics.
- Q3 case study: Q3 used a five-table plan with a four-table left-join tree and scalar InitPlan, and its original execution took nearly four minutes.The plan included join elimination, a 200-partition sequential scan, and a non-key join requiring 9.1 million output rows.
- Q3 case study: 0% SCC error and exact input cardinalities were achieved for Q3 table scans, while bottleneck operators above 5% of total latency stayed below 1% relative error.The Scaling Factor mechanism addressed fan-out error for the non-key join, and the reproduced total time was 4m7s versus 3m54s originally.
5.5 Threats to Validity
The evaluation addresses implementation and measurement validity, but external validity is limited by testing only PostgreSQL and KingbaseES.
- External validity: External validity is constrained because PostgreSQL and KingbaseES cannot represent every RDBMS or optimizer architecture.The authors argue applicability may extend to systems exposing comparable catalog metadata used by cost-based optimizers.
- Internal validity: Latency measurements may be affected by environmental noise despite cache flushing and arithmetic means over multiple runs.The authors identify this as an internal-validity threat and describe these mitigations.
- Internal validity: The analyzer covers a comprehensive subset of relational operators, including selections and PK-FK and non-key joins.Pair programming was used to check implementation correctness.
6 Related Work
Related database synthesis work includes independent, constraint-based, coverage-based, and learning-based paradigms, serving different generation and testing objectives.
- Independent synthesis: Independent synthesis constructs databases from scratch and is widely used to generate scalable datasets for standard performance benchmarks.Statistical model-based tools support more flexible user-specified data distributions.
- Constraint and coverage-based methods: Constraint-based methods formulate synthesis as constraint satisfaction to generate tuples satisfying specific intermediate-result sizes.Coverage-based approaches instead generate test databases and SQL inputs to maximize objectives such as code or execution-path coverage.
- Testing and learning-based methods: Coverage-based and related DBMS-testing techniques generate SQL inputs for coverage, semantic differential testing, or empirical bug-pattern-driven strategies.The passage also identifies learning-based approaches using supervised autoregressive models.
7 Conclusion
DBRepro combines data-driven and workload-aware synthesis to reproduce slow queries offline from non-intrusive metadata. Evaluations report high-fidelity reproduction across structural, cardinality, and latency metrics, including on a nearly 1 TB KingbaseES dataset.
- Framework: DBRepro progressively adjusts a global statistical distribution to satisfy exact local cardinality constraints.Its hybrid solver combines heuristic probabilistic inference and Distribution Fusion with a Scaling Factor-enhanced CP model for non-PK-FK joins.
- Framework: DBRepro extracts runtime context from native slow-query records and catalog metadata without online re-execution, base-table scans, or sensitive raw tuples.The extraction cost is independent of stored data volume.
- Evaluation: 20.3% lower cardinality error than data-driven baselines and 21.5% lower latencyproportion error than workload-aware methods were reported, alongside 15% more consistent plans.The framework was additionally validated on a 1 TB KingbaseES industrial dataset.