PostgreSQL 19 Beta: Testing Planner Changes Before Production

Ananya Desai
1h
65
0
0

Article

A PostgreSQL upgrade can change query performance without changing a single line of application code.

The reason is the query planner.

When an application sends SQL to PostgreSQL, the database does not simply execute the query exactly as written. The optimizer evaluates possible execution strategies and chooses a plan based on statistics, costs, indexes, joins, and available execution methods.

PostgreSQL 19 contains a substantial set of optimizer improvements, including better NOT IN and anti-join transformations, improved semijoin planning, earlier aggregation in some queries, better hash-join handling, incremental-sort planning, and additional optimizer-statistics capabilities.

As of this writing, PostgreSQL 19 is still a beta release. PostgreSQL 19 Beta 2 was released on July 16, 2026, and the PostgreSQL project explicitly recommends testing typical workloads against the beta while advising against using it in production.

That makes this a good time to test planner behavior before a production migration.

Why Planner Changes Need Testing

Consider an application query:

SELECT o.id, o.created_at
FROM orders o
WHERE o.customer_id = @customerId
ORDER BY o.created_at DESC
LIMIT 20;

The SQL is identical before and after an upgrade.

But PostgreSQL may choose a different execution plan because the optimizer itself has changed.

For example:

PostgreSQL 18
    |
    +--> Index Scan
    |
    +--> Nested Loop

PostgreSQL 19
    |
    +--> Different Join Strategy
    |
    +--> Different Sort Strategy

A new plan may be better for one workload and worse for another.

This is why database upgrades should be evaluated with real application queries, not just by checking whether the server starts successfully.

PostgreSQL's own release documentation notes that optimizer improvements are made in almost every release and are generally observed by users as faster queries.

What Is Changing in the PostgreSQL 19 Optimizer?

PostgreSQL 19 includes several planner changes that are particularly interesting for application developers.

Among them are:

More NOT IN clauses can be converted into anti-joins when NULL conditions allow it.
More LEFT JOIN operations can be converted to anti-joins.
Memoize can be used for certain anti-joins with unique inner sides.
Some aggregation can happen before joins.
Hash joins have improved handling of NULL join keys.
Semijoin planning has been improved.
Append and MergeAppend can consider explicit incremental sorts.
Some NULL-related expressions can be simplified earlier.
Optimizer statistics can be used for virtual generated columns.
Optimizer memory planning has additional information available.

These changes do not guarantee a performance improvement for every query.

They change the set of strategies the optimizer can consider.

That distinction is important.

Understanding Anti-Joins

One of the more interesting changes involves NOT IN.

Consider:

SELECT c.id
FROM customers c
WHERE c.id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

Conceptually, this asks:

Return customers
who do not have an order

Historically, NOT IN has special NULL semantics that can make optimization more complicated.

PostgreSQL 19 can convert certain NOT IN clauses into more efficient anti-join forms when the relevant NULL conditions are satisfied.

That makes this a good candidate for a before-and-after plan comparison.

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id
FROM customers c
WHERE c.id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

Capture the plan on the existing PostgreSQL version.

Then execute the same query against PostgreSQL 19 Beta in a controlled test environment.

Do not compare only execution time.

Compare:

Join strategy
Estimated rows
Actual rows
Buffer reads
Buffer hits
Planning time
Execution time

Testing Aggregation Before Joins

PostgreSQL 19 can perform some aggregate processing before joins. The release notes specifically describe this as a way to reduce the number of rows that need to be processed.

Consider an analytical query:

SELECT
    c.id,
    c.name,
    SUM(o.total_amount) AS total_sales
FROM customers c
JOIN orders o
    ON o.customer_id = c.id
GROUP BY
    c.id,
    c.name;

Depending on the available indexes, statistics, and data distribution, the planner may be able to reduce intermediate data before completing later operations.

This is particularly interesting for large tables.

The benchmark should therefore include:

Small dataset
Medium dataset
Large dataset

because a planner strategy that is attractive for one data volume may not be selected for another.

Testing Semijoin Improvements

Semijoins commonly appear in queries using EXISTS.

For example:

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total_amount > 1000
);

This query asks PostgreSQL to return customers for whom at least one qualifying order exists.

PostgreSQL 19 includes improvements to semijoin planning.

This makes EXISTS queries another useful benchmark category.

Compare the execution plan across versions and pay particular attention to:

Nested Loop
Hash Join
Hash Semi Join
Index Scan
Seq Scan

The goal is not to force a particular plan.

The goal is to determine whether PostgreSQL 19 selects an appropriate plan for your data.

Testing Incremental Sort

PostgreSQL 19 also allows Append and MergeAppend to consider explicit incremental sorts.

Sorting becomes particularly relevant when queries combine partitioned or appended data.

For example:

SELECT
    customer_id,
    created_at,
    total_amount
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY customer_id, created_at;

Run:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
    customer_id,
    created_at,
    total_amount
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY customer_id, created_at;

Look for sort-related plan changes.

Do not assume that an incremental sort is automatically faster.

The important question is:

Does the selected plan reduce the amount of work for this workload?

Build a Repeatable Benchmark Dataset

A useful planner benchmark starts with a stable dataset.

For example:

customers
    1 million rows

orders
    20 million rows

order_items
    80 million rows

These numbers are examples of benchmark scale, not recommended production sizes.

The actual dataset should reflect the workload you want to study.

More important than the absolute size is consistency.

Use the same:

Schema
Indexes
Data distribution
PostgreSQL configuration
Statistics
Query parameters

across versions.

Otherwise, the comparison becomes difficult to interpret.

Keep Statistics Consistent

The planner depends heavily on statistics.

Before collecting benchmark results, run:

ANALYZE;

For individual tables:

ANALYZE customers;
ANALYZE orders;
ANALYZE order_items;

Then verify statistics:

SELECT
    schemaname,
    relname,
    n_live_tup,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname;

A common mistake is comparing PostgreSQL versions with different statistics freshness.

That can make a planner comparison look like a version comparison when it is actually a statistics comparison.

Capture Execution Plans

For every important query, save the output of:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

BUFFERS helps show I/O behavior.

SETTINGS helps identify relevant configuration differences.

For production-sensitive queries, also consider:

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    SUMMARY
)
SELECT ...;

The exact options should depend on what you are measuring.

The benchmark should retain the complete plan rather than only recording the final execution time.

Build a Query Benchmark Matrix

Create a list of representative application queries.

Query Type	Example	What to Observe
Simple lookup	Customer by ID	Index behavior
Join	Customer + Orders	Join strategy
EXISTS	Customers with orders	Semijoin
NOT IN	Customers without orders	Anti-join
Aggregation	Sales by customer	Aggregate placement
Sorting	Recent orders	Sort strategy
Partitioned query	Recent partition data	Append/sort
Reporting query	Multi-table aggregation	Overall plan

This is more useful than benchmarking only synthetic queries.

Test Through Entity Framework Core

.NET applications commonly access PostgreSQL through Entity Framework Core and the Npgsql provider.

For example:

var results = await dbContext.Orders
    .Where(o => o.CreatedAt >= cutoff)
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Total = g.Sum(x => x.TotalAmount)
    })
    .ToListAsync();

The application should be benchmarked at two levels:

EF Core LINQ
     |
     v
Generated SQL
     |
     v
PostgreSQL Planner
     |
     v
Execution

First inspect the SQL:

var query = dbContext.Orders
    .Where(o => o.CreatedAt >= cutoff)
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        Total = g.Sum(x => x.TotalAmount)
    });

Console.WriteLine(query.ToQueryString());

Then take that SQL and run it directly through EXPLAIN ANALYZE.

This separates ORM behavior from database planner behavior.

Testing Plan Stability

A query that is fast once is not necessarily stable.

Run the same query with different parameters.

For example:

customerId = 10
customerId = 10000
customerId = 900000

For range queries:

1 day
7 days
30 days
365 days

This matters because PostgreSQL may choose different plans based on estimated selectivity.

A planner upgrade should therefore be tested against a parameter matrix, not just one representative value.

Comparing PostgreSQL 18 and 19

A practical test environment can look like:

             Same Dataset
                  |
        +---------+---------+
        |                   |
        v                   v
 PostgreSQL 18       PostgreSQL 19 Beta
        |                   |
        v                   v
   Query Suite          Query Suite
        |                   |
        +---------+---------+
                  |
                  v
             Compare Plans

Record:

Metric	PostgreSQL 18	PostgreSQL 19 Beta
Planning time	Measure	Measure
Execution time	Measure	Measure
Shared buffer hits	Measure	Measure
Shared buffer reads	Measure	Measure
Rows processed	Measure	Measure
Join strategy	Record	Record
Sort strategy	Record	Record

Do not publish a claim such as "PostgreSQL 19 is 30% faster" unless you have actually run a controlled benchmark that supports that conclusion.

The PostgreSQL project itself encourages users to test their typical workloads against the beta and warns that behavior can still change before the final release.

Watch for Regressions

Benchmarking is not only about finding improvements.

You should actively search for regressions.

Suppose a query changes from:

Index Scan
Execution Time: 40 ms

to:

Sequential Scan
Execution Time: 900 ms

That is exactly the kind of result that should stop an automatic production upgrade.

Investigate:

Statistics
Index availability
Cost settings
Data distribution
Query parameters
Planner changes
Configuration differences

Do not immediately add an index just because the new plan looks different.

The new plan may be correct for the tested data, or the benchmark environment may differ from production.

Use Query Plan Regression Testing

For important queries, store the expected characteristics rather than requiring an identical textual plan.

For example:

Query: CustomerOrderSummary

Expected:
- No sequential scan on orders
- Execution time below internal threshold
- Shared reads below internal threshold
- No unexpected Cartesian join

This is more robust than comparing raw EXPLAIN output byte-for-byte.

PostgreSQL can legitimately change harmless details in a plan while preserving or improving performance.

Important PostgreSQL 19 Compatibility Changes

Planner behavior is not the only upgrade consideration.

PostgreSQL 19 introduces several compatibility changes.

For example, JIT is disabled by default because the previous optimizer costing used to determine when to activate it was considered unreliable. Sites running many large analytical queries may need to evaluate JIT explicitly.

PostgreSQL 19 also forces standard_conforming_strings to remain enabled on the server, and PostgreSQL warns that older dumps created with this setting disabled may not load correctly.

This is another reason an upgrade test should include application behavior and migration testing rather than focusing only on query speed.

Beta Testing Is Not Production Testing

PostgreSQL explicitly states that beta releases are pre-release versions and are not intended for production systems. Feature details and behavior can still change during the beta cycle.

A safe testing strategy is:

Production Database
       |
       | Representative backup/data
       v
Isolated Test Environment
       |
       v
PostgreSQL 19 Beta
       |
       +--> Schema Tests
       +--> Query Tests
       +--> EF Core Tests
       +--> Load Tests
       +--> Regression Tests

Never turn the benchmark environment into an accidental production environment simply because the beta performs well.

Common Mistakes

Comparing Different Data

A planner benchmark is meaningless if the datasets differ significantly.

Forgetting Statistics

Run ANALYZE and verify that statistics are comparable.

Looking Only at Execution Time

A plan that takes 10 ms today may behave differently as the dataset grows.

Benchmarking Only One Query

A PostgreSQL upgrade affects many query patterns.

Forcing a Plan Too Early

Do not use planner hints or configuration changes simply to reproduce the old plan before understanding why the planner changed.

Ignoring ORM-Generated SQL

EF Core developers should inspect the actual SQL generated by the application.

Running Beta in Production

PostgreSQL explicitly advises against production use of beta releases.

Troubleshooting Planner Regressions

If a query becomes slower after moving to PostgreSQL 19 Beta, follow this sequence:

Capture the old execution plan.
Capture the new execution plan.
Compare estimated and actual row counts.
Compare join and sort strategies.
Verify indexes.
Refresh statistics.
Compare PostgreSQL configuration.
Test different parameter values.
Check whether JIT behavior changed.
Reduce the query to the smallest reproducible case.
Confirm whether the behavior is expected from the new planner.
Report a reproducible issue if appropriate.

PostgreSQL's beta process specifically encourages developers to report bugs and compatibility problems discovered during testing.

Best Practices

Test PostgreSQL 19 with real application queries.
Keep the benchmark dataset consistent.
Run ANALYZE before collecting results.
Capture EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
Compare execution plans, not just execution time.
Test multiple parameter values.
Include EF Core-generated SQL in .NET workloads.
Look for regressions as aggressively as improvements.
Keep beta testing isolated from production.
Document PostgreSQL configuration alongside benchmark results.
Repeat important queries instead of relying on a single run.
Re-run the benchmark when a new beta or release candidate becomes available.

Conclusion

PostgreSQL 19 is bringing meaningful changes to the query optimizer. Improvements include additional anti-join transformations, better semijoin planning, earlier aggregation in some workloads, improved hash joins, incremental-sort opportunities, and expanded optimizer statistics capabilities.

But the most important lesson for developers is that planner improvements are workload-dependent.

A query that benefits from a new optimization may be unaffected by another change. A different query may receive a completely different execution plan. And occasionally, a workload can expose a regression that was not visible in generic testing.

That is why PostgreSQL 19 Beta is best approached as a testing opportunity.

Build a representative dataset, capture real queries from your .NET application, compare execution plans between PostgreSQL versions, measure buffer behavior and execution time, and actively search for regressions.

PostgreSQL 19 Beta 2 is explicitly intended for this kind of community testing, while the PostgreSQL project continues toward its expected September/October 2026 final-release window.

The right question before an upgrade is not:

"Does PostgreSQL 19 look faster?"

It is:

"How does PostgreSQL 19 plan and execute the queries that matter to my application?"

That is the benchmark that can give an engineering team confidence before production adoption.

Frequently Asked Questions

Is PostgreSQL 19 safe to use in production?

No. PostgreSQL 19 is currently a beta release, and the PostgreSQL project explicitly advises against using beta releases in production.

Do PostgreSQL planner changes require application code changes?

Not necessarily. Many planner changes affect how existing SQL is executed without requiring SQL or application changes. However, application compatibility and query behavior should still be tested during an upgrade.

How should I compare PostgreSQL 18 and 19?

Use the same schema, data, indexes, configuration, query parameters, and workload. Capture execution plans with EXPLAIN (ANALYZE, BUFFERS, SETTINGS) and compare both plan structure and measured performance.

Should I benchmark only slow queries?

No. Benchmark critical queries, high-frequency queries, representative workloads, and known slow queries. A previously fast query can also experience a planner regression.

Should EF Core developers test PostgreSQL directly?

Yes. Test the application through EF Core, but also capture the generated SQL and test that SQL directly with PostgreSQL's execution-plan tools. This helps separate ORM behavior from database planner behavior.