How Datadog Bits Proves a Query Rewrite Is Actually Faster — Not Just Theoretically

Database optimization recommendations are easy to generate and hard to trust. Execution plan estimates lie. Query analyzers suggest indexes that help in isolation and hurt under concurrent load. The real test is whether the rewrite actually performs better on data that behaves like production — not on random test fixtures.

Datadog’s Bits Database Optimization takes a different approach: it creates a controlled simulation environment before making any recommendation, then applies a conservative statistical threshold before calling something an improvement.

How It Works

Bits creates fresh database instances within Datadog’s infrastructure to benchmark proposed rewrites. These simulations recreate your production schema — tables, indexes, constraints — using metadata already collected by the Datadog Agent during standard monitoring. The key innovation is in the data population strategy.

Rather than filling test tables with random data, Bits generates synthetic data that approximates the characteristics of your actual datasets: cardinality, column-level distributions, and value patterns. This matters because the query planner’s decisions depend on statistics about the data — random data produces different query plans than production data, making the benchmark useless.

The simulation accuracy measurement Datadog developed is tracked on two dimensions:

Cost fidelity compares query planner cost estimates between simulated and production databases. Across tested query types, median cost fidelity ranged from 1.00x to 1.05x — meaning simulated estimates diverged by at most 5% from production. Random data generation produced 7.17x median divergence on equality filters alone.

Read fidelity measures actual data blocks accessed during execution — a more granular signal than cost estimates. Results show median read fidelity between 1.04x and 1.81x across most query classes, substantially better than random baseline approaches.

Before benchmarking, Bits warms up the cache to prevent cold-start effects from skewing results. For queries that modify data, warm-up operations execute inside rolled-back transactions, preserving database state between runs.

The Recommendation Threshold

Each query variant runs 50 times. Bits evaluates average, median, 95th percentile, and maximum values across those runs on three dimensions: execution time (server-reported duration), logical reads (total blocks accessed), and dirtied blocks (modified blocks requiring persistence).

A rewrite must demonstrate over 20% improvement relative to baseline across all three metrics. Critically, that improvement must appear in at least three of the four summary statistics. This three-of-four rule filters out improvements driven by outliers or statistical noise — ensuring the recommendation holds under real-world variance, not just in the ideal case.

Bits Database Optimization currently supports PostgreSQL, with MySQL, SQL Server, and Oracle planned.

What This Means for You

The simulation approach bypasses two common barriers to query optimization: security constraints around providing production data access to external tooling, and the noise-to-signal problem that makes manual benchmarking unreliable. Teams don’t need to stand up test environments or instrument their production databases differently — the metadata already collected by standard Datadog monitoring feeds the simulation.

The three-of-four threshold makes the recommendations conservative by design. A rewrite that improves average execution time but spikes the 95th percentile doesn’t clear the bar — which is the right behavior for production systems where tail latency matters as much as median.

Source: Datadog Blog

Content created with AI assistance and reviewed for accuracy.