Introduction
Upgrading PostgreSQL is usually straightforward when an application continues to return the same results.
The harder problem is performance.
A query can produce exactly the same data after a database upgrade while taking significantly longer to execute. For .NET applications using Entity Framework Core, this can be difficult to detect because the problem may not appear in application code at all.
The generated LINQ query may be unchanged. The application may compile successfully. Unit tests may pass. Functional tests may also pass.
But PostgreSQL may choose a different execution plan.
EF Core LINQ
|
v
Generated SQL
|
v
PostgreSQL Optimizer
|
v
Execution Plan
|
v
Query Result
A PostgreSQL upgrade can change the optimizer's decisions because planner behavior, statistics handling, cost estimation, indexing behavior, or execution features may change.
For this reason, testing a PostgreSQL upgrade should include query plan regression testing, not just functional validation.
This article explains how to detect query plan regressions in EF Core applications before moving a PostgreSQL upgrade into production.
What Is a Query Plan Regression?
A query plan regression occurs when a query that previously performed well starts using a less efficient execution strategy.
For example, an older environment might produce:
Index Scan
-> 12 ms
while the upgraded environment produces:
Sequential Scan
-> 480 ms
The SQL may be identical.
SELECT *
FROM orders
WHERE customer_id = 1842;
The difference is the execution plan selected by PostgreSQL.
A simplified comparison looks like this:
Before Upgrade
Query
|
v
Index Scan
|
v
Fast Result
After Upgrade
Query
|
v
Sequential Scan
|
v
Slow Result
That is why application-level testing alone may not catch the problem.
Why EF Core Makes This Important
EF Core applications commonly build queries using LINQ.
For example:
var orders = await dbContext.Orders
.Where(x => x.CustomerId == customerId)
.OrderByDescending(x => x.CreatedAt)
.Take(20)
.ToListAsync();
EF Core translates this into SQL.
The application developer generally cares about the LINQ expression, while PostgreSQL cares about the resulting SQL and available execution strategies.
The complete pipeline is:
LINQ
|
v
EF Core Query Translation
|
v
SQL
|
v
PostgreSQL Planner
|
v
Execution Plan
|
v
Execution
A regression can therefore occur at the database planning stage even when nothing changed in the application.
Functional Tests Are Not Enough
Suppose a regression test executes:
var customer = await dbContext.Customers
.FirstOrDefaultAsync(x => x.Id == 1001);
The test might verify:
Customer exists
Customer name is correct
Customer status is correct
Everything passes.
But the test does not necessarily tell you:
Was an index used?
How many rows were scanned?
How much data was read?
How long did execution take?
Did the planner choose a different join?
Performance regression testing needs additional evidence.
Capture the Generated SQL
The first step is to identify important SQL generated by EF Core.
For example:
var query = dbContext.Orders
.Where(x => x.CustomerId == customerId)
.OrderByDescending(x => x.CreatedAt)
.Take(20);
var sql = query.ToQueryString();
This allows you to inspect the SQL produced by the LINQ expression.
A useful baseline stores:
Query Identifier
Generated SQL
Database Version
Execution Plan
Execution Time
Rows Returned
The SQL itself becomes part of the regression dataset.
Identify Critical Queries
Do not attempt to benchmark every query in an application first.
Start with queries that matter most.
Typical candidates include:
A simple prioritization model can be:
Priority =
Traffic
×
Execution Cost
×
Business Importance
This gives you a practical shortlist.
Capture the Baseline Before the Upgrade
Before changing PostgreSQL versions, execute your benchmark workload against the current production-like database.
Capture:
Query
Execution Time
Planning Time
Rows
Buffers
Plan Shape
Indexes Used
For example:
Query: CustomerOrders
Planning Time: 0.42 ms
Execution Time: 8.7 ms
Plan:
Index Scan
-> Index: IX_orders_customer_id
This becomes your baseline.
Use EXPLAIN ANALYZE Carefully
PostgreSQL provides detailed execution-plan information through EXPLAIN.
A simplified example is:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id = 1842
ORDER BY created_at DESC
LIMIT 20;
This provides much more information than simply measuring application execution time.
Important signals include:
Planning Time
Execution Time
Rows
Buffers
Scan Type
Join Type
Sort
Index Usage
The important word here is ANALYZE.
It executes the query.
That means you should be careful when testing statements that modify data.
For write operations, use a controlled environment or an appropriate transaction strategy.
Understand Common Plan Changes
A regression can appear in many forms.
Index Scan to Sequential Scan
Before:
Index Scan
After:
Seq Scan
This can be a major problem for large tables.
Nested Loop to Hash Join
Neither join strategy is automatically better.
The correct question is whether the new strategy is appropriate for the actual data distribution.
Before:
Nested Loop
After:
Hash Join
A plan change is not automatically a regression.
Sort Added to the Plan
A query that previously used an index to satisfy ordering may begin sorting rows explicitly.
Index Scan
|
v
Result
becomes:
Seq Scan
|
v
Sort
|
v
Result
For large datasets, that can materially increase execution cost.
Plan Shape Matters More Than Exact Text
Execution-plan output can contain details that change between runs.
For example:
actual time=4.123..4.987
should not normally be compared as an exact string.
Instead, extract meaningful properties:
Scan Type
Join Type
Index Name
Estimated Rows
Actual Rows
Sort Operations
Buffer Reads
A structured representation is easier to compare.
Conceptually:
{
"scan": "Index Scan",
"index": "IX_orders_customer_id",
"estimatedRows": 20,
"actualRows": 20,
"executionMs": 8.7
}
Then your regression system can compare properties rather than raw text.
Compare Estimated and Actual Rows
One of the most useful signals is the difference between estimated and actual rows.
Suppose the planner estimates:
Estimated Rows: 20
Actual Rows: 20
That is generally a healthy estimate.
Now consider:
Estimated Rows: 10
Actual Rows: 500,000
That is a significant cardinality estimation problem.
The planner may choose an inefficient strategy because it believes the result set is much smaller than it really is.
Why Statistics Matter
PostgreSQL relies on table statistics when selecting plans.
Consider:
Actual distribution:
Status = Active 95%
Status = Suspended 4%
Status = Closed 1%
If statistics do not accurately represent that distribution, the optimizer can make poor decisions.
After an upgrade, benchmark environments should therefore have realistic statistics.
Do not compare:
Production-like data
with:
Small empty test database
and expect the execution plans to be representative.
Use Production-Like Data
Query plans depend heavily on:
Table size
Data distribution
Indexes
Statistics
Correlation
Selectivity
Number of rows
Join cardinality
For example:
Development:
10,000 orders
Production:
500,000,000 orders
A query that looks perfect in development may behave very differently at production scale.
For upgrade testing, data volume matters.
Test the Same Dataset
A useful migration test environment looks like:
Current PostgreSQL
|
v
Identical Dataset
|
v
Benchmark Queries
Upgraded PostgreSQL
|
v
Same Dataset
|
v
Same Benchmark Queries
This isolates the database-version change.
If both environments have different datasets, it becomes difficult to determine whether a plan change is caused by the PostgreSQL upgrade or by data differences.
Compare Query Plans Automatically
Manual inspection does not scale.
Suppose you have 500 important queries.
You want:
Baseline Plan
|
v
Upgrade Plan
|
v
Plan Diff
|
v
Regression Score
A simple regression record could contain:
{
"queryId": "orders-by-customer",
"baselineMs": 8.7,
"upgradeMs": 12.4,
"baselineScan": "Index Scan",
"upgradeScan": "Index Scan",
"baselineRows": 20,
"upgradeRows": 20
}
This may be acceptable.
Now consider:
{
"queryId": "orders-by-customer",
"baselineMs": 9.1,
"upgradeMs": 487.3,
"baselineScan": "Index Scan",
"upgradeScan": "Seq Scan"
}
That deserves immediate investigation.
Define Regression Thresholds
Not every performance difference should fail the upgrade.
Database execution time naturally varies.
A practical benchmark can define thresholds such as:
Green:
< 10% slower
Yellow:
10–25% slower
Red:
> 25% slower
However, percentage alone is not enough.
Consider:
Query A:
10 ms -> 12 ms
Query B:
2,000 ms -> 2,400 ms
Both are 20% slower.
But the second may have a much greater production impact.
Use both:
Relative Regression
+
Absolute Regression
Watch for Plan Changes With Large Impact
Some plan changes deserve special attention:
Index Scan -> Sequential Scan
Nested Loop -> Cartesian-like expansion
Index-backed ORDER BY -> Explicit Sort
Small estimated result -> Huge actual result
Low buffer reads -> Large buffer reads
These changes can indicate a meaningful regression even when average latency has not yet crossed your threshold.
Benchmark Warm and Cold Conditions
Database caching affects results.
A query may behave differently when relevant pages are already in memory.
Therefore, distinguish:
Cold Cache
from:
Warm Cache
For example:
Cold:
120 ms
Warm:
8 ms
The benchmark should consistently define which condition it is measuring.
For production-like workloads, warm-cache performance is often highly relevant, but cold-cache behavior can expose different problems.
Measure P50, P95, and P99
Average latency can hide outliers.
Suppose:
Average: 30 ms
P95: 180 ms
P99: 1,200 ms
The average looks acceptable.
The tail does not.
For API workloads, capture:
P50
P95
P99
before and after the upgrade.
A plan regression that only affects a subset of parameter values may appear primarily in P95 or P99.
Test Different Parameter Values
Parameter-sensitive queries can behave differently depending on input.
For example:
WHERE customer_id = @customerId
may be fast for one customer and expensive for another.
Benchmark:
Small customer
Medium customer
Large customer
Very large customer
This is especially important when data distribution is highly uneven.
A query that looks healthy using a random parameter may still have serious production problems.
Test Pagination Queries
Pagination is a common source of database performance problems.
For example:
var orders = await dbContext.Orders
.OrderByDescending(x => x.CreatedAt)
.Skip(page * pageSize)
.Take(pageSize)
.ToListAsync();
Compare:
Page 1
Page 10
Page 100
Page 1,000
Page 10,000
The query plan may look acceptable at the beginning of the table but become expensive for deep pages.
Keyset pagination may behave differently:
var orders = await dbContext.Orders
.Where(x => x.CreatedAt < lastCreatedAt)
.OrderByDescending(x => x.CreatedAt)
.Take(pageSize)
.ToListAsync();
The benchmark should represent the actual application workload.
Test EF Core Include Queries
EF Core queries using navigation loading can generate complex SQL.
For example:
var customers = await dbContext.Customers
.Include(x => x.Orders)
.ThenInclude(x => x.Items)
.Where(x => x.IsActive)
.ToListAsync();
These queries can involve:
Plan regression testing should include representative queries using relationships.
Test Projection Queries
Projection can produce very different SQL from loading complete entities.
For example:
var result = await dbContext.Orders
.Where(x => x.CustomerId == customerId)
.Select(x => new
{
x.Id,
x.CreatedAt,
x.Total
})
.ToListAsync();
This may allow PostgreSQL to read less data than:
var result = await dbContext.Orders
.Where(x => x.CustomerId == customerId)
.ToListAsync();
Benchmark both when they represent real application patterns.
Test Aggregation Queries
Reporting and dashboard workloads often use aggregation.
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total) AS revenue
FROM orders
GROUP BY customer_id;
These queries may use:
Hash Aggregate
Group Aggregate
Sort
Parallel Execution
Plan changes here can have a large effect because aggregation may process millions of rows.
Test Join-Heavy Queries
A common production query might look conceptually like:
Customers
|
+---- Orders
|
+---- OrderItems
|
+---- Products
The optimizer has multiple possible join strategies.
A benchmark should record:
Join order
Join type
Estimated rows
Actual rows
Buffers
Execution time
A small change in cardinality estimation can produce a very different plan.
Test Index Dependency
Create a list of critical indexes used by application queries.
For example:
IX_orders_customer_id
IX_orders_created_at
IX_orders_customer_created_at
Then verify that the expected indexes are still being used where appropriate.
Do not automatically fail when an index is not used.
The optimizer may legitimately find a better plan.
The test should ask:
Did the new plan materially worsen performance?
Don't Treat Every Plan Change as a Regression
This is one of the most important points.
Suppose:
Before:
Nested Loop
Execution: 120 ms
After:
Hash Join
Execution: 80 ms
The plan changed.
But performance improved.
That is not a regression.
Likewise:
Before:
Index Scan
Execution: 100 ms
After:
Seq Scan
Execution: 70 ms
A sequential scan may be better for a query returning a large percentage of a table.
Therefore:
Plan Change != Regression
Instead:
Plan Change
+
Performance Degradation
+
Meaningful Resource Increase
=
Potential Regression
Build an EF Core Benchmark Harness
A simple benchmark application can execute representative queries.
public async Task<QueryResult> RunCustomerOrdersAsync(
int customerId)
{
var stopwatch = Stopwatch.StartNew();
var orders = await dbContext.Orders
.Where(x => x.CustomerId == customerId)
.OrderByDescending(x => x.CreatedAt)
.Take(20)
.ToListAsync();
stopwatch.Stop();
return new QueryResult(
orders.Count,
stopwatch.Elapsed);
}
The harness can record:
Query ID
Parameters
Execution Time
Rows Returned
Database Version
Plan data can be collected separately through database-side instrumentation.
Run the Upgrade in Parallel
A practical migration process is:
Current Version
|
v
Capture Baseline
|
v
Clone Dataset
|
+------------------+
| |
v v
Current DB PostgreSQL Upgrade
| |
v v
Benchmark Benchmark
| |
+--------+---------+
|
v
Compare
|
v
Regression Report
This makes the upgrade decision evidence-based.
Create a Regression Report
A useful report might contain:
| Query | Before | After | Change | Plan Change | Status |
|---|
| Customer Orders | 8 ms | 9 ms | +12% | None | Pass |
| Invoice Search | 15 ms | 17 ms | +13% | None | Pass |
| Order History | 22 ms | 41 ms | +86% | Index → Seq | Fail |
| Revenue Report | 840 ms | 790 ms | -6% | Join changed | Pass |
| Customer Search | 12 ms | 13 ms | +8% | None | Pass |
This gives the migration team a clear list of queries requiring investigation.
Investigate Regressions Systematically
When a regression appears, check:
1. Generated SQL
2. Execution plan
3. Statistics
4. Indexes
5. Data distribution
6. Query parameters
7. Database configuration
8. PostgreSQL version behavior
Do not immediately rewrite application code.
The problem may be entirely in planning or statistics.
Refresh Statistics
One common investigation step is ensuring statistics are current.
If the benchmark dataset has recently been loaded or changed significantly, stale statistics can distort plan selection.
The benchmark environment should therefore use realistic statistics before comparison.
A useful test process is:
Load Dataset
|
v
Prepare Statistics
|
v
Run Benchmark
rather than benchmarking immediately after bulk loading.
Test With Realistic Database Configuration
A query plan can be affected by configuration.
Your benchmark should keep relevant database settings consistent between environments wherever possible.
Otherwise, you may accidentally test:
Old Version + Configuration A
against:
New Version + Configuration B
and incorrectly attribute every difference to the PostgreSQL upgrade.
The goal is to isolate variables.
Add Regression Gates to CI/CD
Once the benchmark is stable, it can become part of the upgrade pipeline.
Pull Request
|
v
Build
|
v
Unit Tests
|
v
Integration Tests
|
v
Database Benchmark
|
v
Plan Comparison
|
+---- Pass
|
+---- Investigate
Not every query should necessarily block a deployment.
Critical regressions should.
Define Practical Gates
For example:
Fail if:
Critical query > 25% slower
OR
P95 > defined limit
OR
Sequential scan appears on a large critical table
OR
Buffer reads increase beyond threshold
OR
End-to-end API latency exceeds limit
These rules should be based on application behavior rather than arbitrary numbers.
Common Mistakes
Testing Only Query Results
Correct results do not prove good performance.
Comparing Raw Plan Text
Plan output contains dynamic values that make textual comparison noisy.
Using Tiny Test Databases
Small datasets can produce completely different plans.
Testing Only Average Latency
Tail latency often reveals regressions first.
Ignoring Data Distribution
The same query can behave very differently for different parameter values.
Treating Every Plan Change as Bad
A different plan can be faster.
Forgetting Statistics
Poor statistics can make an otherwise healthy query look like an optimizer problem.
Benchmarking Different Datasets
This makes it difficult to identify the cause of a plan change.
Ignoring EF Core SQL Generation
The LINQ expression is not the SQL PostgreSQL executes.
Best Practices
Capture a Baseline
Record plans and performance before the upgrade.
Use Production-Like Data
Plan behavior depends heavily on data volume and distribution.
Benchmark Critical Queries
Prioritize queries that matter to users and business operations.
Compare Structured Plan Information
Focus on scan types, joins, rows, buffers, and timing.
Test Multiple Parameters
Do not benchmark only one representative value.
Measure P95 and P99
Tail behavior can reveal problems hidden by averages.
Investigate Plan Changes With Performance Data
A plan change alone is not necessarily a regression.
Automate the Comparison
A repeatable benchmark is much more useful than manual inspection.
Add Upgrade Gates
Critical query regressions should be detected before production.
A Practical PostgreSQL Upgrade Checklist
Before moving the upgraded database to production:
[ ] Identify critical EF Core queries
[ ] Capture generated SQL
[ ] Capture baseline execution plans
[ ] Record baseline latency
[ ] Prepare production-like data
[ ] Refresh database statistics
[ ] Run identical workload against new version
[ ] Compare scan strategies
[ ] Compare join strategies
[ ] Compare estimated vs actual rows
[ ] Compare buffer usage
[ ] Measure P50/P95/P99
[ ] Test multiple parameter values
[ ] Test pagination workloads
[ ] Test aggregation queries
[ ] Test join-heavy queries
[ ] Investigate significant regressions
[ ] Automate regression reporting
[ ] Define production rollback criteria
Conclusion
A PostgreSQL upgrade should be treated as both a compatibility exercise and a performance experiment. EF Core applications can continue producing correct results while the database optimizer makes different decisions underneath them, and those decisions can have a significant impact on production latency and resource consumption.
The safest approach is to capture a baseline before the upgrade, run the same representative workload against the new PostgreSQL version, compare structured execution-plan information, and investigate meaningful changes in latency, row estimates, buffer usage, scans, and joins.
Most importantly, do not reject an upgrade simply because execution plans changed. PostgreSQL may choose a different plan because it found a better strategy. The real signal is whether the new behavior improves or degrades the application's workload.
For .NET teams, the combination of EF Core query capture, production-like data, PostgreSQL execution-plan analysis, and automated regression thresholds provides a practical way to detect problems before users experience them. A database upgrade is much safer when query performance is measured as part of the release process rather than discovered after deployment.