One of the hardest problems in database performance engineering is not finding that a query is slow.
It is reproducing why it is slow.
A production query may take 30 seconds while the same query in a test environment takes 200 milliseconds.
The SQL is identical.
The database version is identical.
The indexes appear identical.
And yet the execution plan is different.
The obvious solution is often:
Copy production data to the test environment.
For modern enterprise systems, that is frequently impossible.
The data may contain:
- personal information,
- financial information,
- customer records,
- confidential business data,
- regulated information.
Even when copying is technically possible, a multi-terabyte production database is not something you casually duplicate for troubleshooting.
This creates an interesting DBRE problem:
Can we reproduce the performance characteristics of production without reproducing the production data?
Recent database research suggests that the answer may increasingly be yes.
The Real Problem Is Not the SQL
When a production query becomes slow, DBAs often start with the SQL statement.
That makes sense.
But SQL text is only one part of the equation.
Query performance also depends on:
- data distribution,
- cardinality,
- statistics,
- indexes,
- optimizer settings,
- parameter values,
- join strategies,
- available resources,
- concurrent workload.
Consider:
SELECT *
FROM orders
WHERE customer_id = :customer_id;The statement is simple.
But imagine two databases.
Database A
Customer 123 has 12 orders.
Database B
Customer 123 has 12 million orders.
The SQL is identical.
The optimal execution strategy may not be.
This is why simply copying the SQL statement into a development database is often insufficient for reproducing a production performance problem.
Execution Plans Are a Fingerprint of the Problem
An execution plan tells us how the database decided to execute a query.
It contains information about:
- access paths,
- joins,
- indexes,
- scans,
- estimated cardinality,
- cost,
- sorting,
- aggregation,
- parallelism.
If the production system chooses a different plan than the test system, the performance characteristics may be completely different.
This leads to an important DBRE principle:
To reproduce a performance problem, we often need to reproduce the conditions that caused the optimizer to choose the plan.
Not necessarily the entire production database.
Why Copying Production Data Is Often the Wrong Solution
Traditional troubleshooting sometimes follows this pattern:
- Production query is slow.
- Copy production data.
- Restore it somewhere else.
- Reproduce the problem.
- Investigate.
For small systems, this can work.
For enterprise systems, it creates serious problems.
Security
Production data may contain sensitive information.
Compliance
Some data cannot legally or contractually leave the production environment.
Cost
Copying terabytes of data requires storage, network bandwidth and time.
Time
A performance incident cannot always wait twelve hours for a database copy.
Reproducibility
Even a copied database may not reproduce the exact workload conditions that caused the problem.
So the traditional approach has limitations.
What If We Could Synthesize the Database?
A recent research project called DBRepro explores exactly this direction.
Instead of copying the production data, the system attempts to create a synthetic database that preserves the characteristics relevant to query optimization.
The goal is not to reproduce every individual record.
The goal is to reproduce the conditions that matter for the execution plan.
The researchers describe a hybrid constraint-solving approach that uses lightweight metadata and query-specific constraints to synthesize a proxy database.
The result is intended to reproduce the physical execution plan and performance characteristics of the problematic query without exposing the original production dataset.
That is a very interesting idea for DBRE.
Metadata Can Be More Valuable Than You Think
A database contains enormous amounts of information.
But not all of it needs to be copied.
For performance analysis, useful information can include:
- table cardinality,
- column statistics,
- value distributions,
- distinct-value counts,
- index definitions,
- constraints,
- data types,
- partitioning,
- query predicates.
This metadata can provide enough information to approximate the environment in which the optimizer makes its decisions.
The objective is not:
Recreate production.
It is:
Recreate the conditions that matter for this particular failure.
That is a much more scalable problem.
The DBRE Incident Workflow Changes
Imagine a production query suddenly becomes slow.
A traditional workflow might be:
Production → Copy data → Test → Investigate
A more advanced workflow could become:
Production → Capture metadata → Reproduce plan → Test → Fix → Validate
This has several advantages.
The diagnostic environment can be:
- smaller,
- faster,
- cheaper,
- safer,
- easier to reproduce,
- easier to automate.
And that is exactly what we want from reliability engineering.
Performance Troubleshooting Should Be Repeatable
A common problem in database teams is that performance troubleshooting depends heavily on one experienced DBA.
The DBA sees the problem.
The DBA remembers a similar incident.
The DBA knows which statistics to inspect.
The DBA knows which execution plan to compare.
The DBA fixes the problem.
Then the incident is closed.
Six months later, someone else encounters a similar issue.
The investigation starts again from zero.
This is a reliability problem.
The goal of DBRE should be to convert expert knowledge into:
- monitoring,
- automation,
- runbooks,
- reproducible tests,
- diagnostics,
- standard operating procedures.
From DBA Knowledge to Engineering Capability
Consider a senior DBA who knows:
“This looks like a cardinality estimation problem.”
That is valuable expertise.
But a mature database platform should go further.
It should be able to answer automatically:
- Which plan changed?
- When did it change?
- Which statistics changed?
- How different are estimated and actual rows?
- Which tables contribute to the regression?
- Is the problem reproducible?
- Which previous plan was faster?
- Can we test a candidate fix safely?
The objective is not to replace the DBA.
It is to give the DBA better tools.
This Is Where AI Becomes Interesting
AI is often presented as:
“Ask an AI why my query is slow.”
That is not particularly interesting by itself.
The more interesting application is using AI and automation to connect multiple sources of evidence.
For example:
Query history
↓
Execution-plan history
↓
Statistics changes
↓
Schema/index changes
↓
Workload changes
↓
Incident timeline
↓
Synthetic reproduction
↓
Candidate solution
That creates an engineering workflow rather than a chatbot.
The system is not simply answering:
“What does this SQL do?”
It is helping answer:
“Why did production behavior change?”
But Synthetic Data Must Be Used Carefully
There is an important limitation.
A synthetic database is not production.
It may reproduce:
- cardinality,
- distributions,
- execution plans,
- relative performance.
But it may not reproduce:
- real concurrency,
- storage behaviour,
- cache state,
- network latency,
- locking patterns,
- external dependencies,
- production workload interactions.
Therefore synthetic reproduction should be treated as a diagnostic environment, not as a perfect replica.
The final validation still belongs in a controlled production-like environment.
The Same Idea Applies to PostgreSQL
PostgreSQL DBAs already work heavily with:
EXPLAIN,EXPLAIN ANALYZE,pg_stat_statements,- planner statistics,
- indexes,
- table statistics,
- query history.
The important next step is connecting these sources.
For example:
Query latency increased by 600%.
Then:
Execution plan changed.
Then:
Estimated rows changed from 100 to 4,000,000.
Then:
Statistics were refreshed two hours earlier.
Then:
The new plan can be reproduced using a synthetic dataset.
Now we have a much stronger explanation.
Instead of saying:
“PostgreSQL chose a bad plan.”
we can say:
“A statistics change caused a cardinality estimation error that resulted in a different join strategy and a 600% latency increase.”
That is actionable.
Reliability Is About Reducing Time to Understanding
Most people think of reliability in terms of uptime.
But incident response has another critical metric:
Time to Understanding.
How long does it take to understand what actually happened?
Consider two organizations.
Organization A
Incident occurs.
Senior DBA investigates for six hours.
Problem eventually identified.
Fix applied.
Organization B
Incident occurs.
Monitoring identifies the changed query.
Plan history identifies the regression.
Statistics history identifies the triggering change.
Automated reproduction confirms the hypothesis.
Engineer validates the fix.
The second organization is not necessarily smarter.
It has simply encoded more knowledge into the platform.
That is DBRE.
The Future of Database Troubleshooting
The database administrator of the future will still need deep technical knowledge.
You will still need to understand:
- Oracle,
- PostgreSQL,
- SQL Server,
- MySQL,
- indexes,
- execution plans,
- transactions,
- locks,
- statistics,
- storage,
- replication.
But the environment around the DBA will become increasingly automated.
A mature platform may automatically:
- capture plan changes,
- detect regressions,
- correlate changes,
- build diagnostic environments,
- reproduce problematic workloads,
- test candidate fixes,
- measure the result,
- document the incident.
The senior DBA becomes the engineer who understands why the automation should trust one result and reject another.
Don’t Copy the Database. Reproduce the Problem.
This is the key idea.
When investigating a production performance incident, the objective should not automatically be:
“How do we get a copy of production?”
The better question is:
“What characteristics of production are actually necessary to reproduce the failure?”
Sometimes the answer is the complete dataset.
Often it isn’t.
If we can reproduce:
- the relevant statistics,
- cardinality,
- data distribution,
- indexes,
- optimizer conditions,
- execution plan,
we may be able to diagnose the problem much faster and much more safely.
This Is What Database Reliability Engineering Looks Like
DBRE is not just:
- keeping databases online,
- writing backup scripts,
- monitoring CPU,
- responding to incidents.
It is about building a system in which database problems become:
detectable, explainable, reproducible and recoverable.
The difference between a traditional DBA operation and a mature DBRE platform is often not the database technology.
It is the engineering around the database.
And one of the most valuable capabilities we can build is the ability to reproduce a production problem without reproducing the production data.
That is a much safer way to debug.
And potentially a much faster one.
About the Author
Tomáš Solař is a Principal Database Reliability Engineer with more than 20 years of experience in enterprise database environments.
His expertise includes Oracle, PostgreSQL, Microsoft SQL Server and MySQL, with a focus on Database Reliability Engineering, High Availability, Disaster Recovery, performance engineering, database architecture, automation and observability.
His approach combines deep hands-on DBA experience with reliability engineering and platform thinking — helping organizations build database platforms that remain predictable during normal operation, change, growth and failure.
Available for permanent, contract, remote and hybrid engagements.
Email: tom@tomas-solar.com
Phone / WhatsApp: +420 731 196 647
Web: tomas-solar.com