Upgrading a PostgreSQL database to a new major version is not just an infrastructure task. It can also affect how application queries are planned, executed, and interpreted.
A query that performs well on an existing PostgreSQL installation may behave differently after an upgrade. The database optimizer can choose another execution plan, extensions may introduce compatibility issues, and changes to database behavior can affect existing application logic.
These differences are particularly important for production systems that process large amounts of data, execute complex joins, use full-text search, or depend on carefully optimized SQL statements.
PostgreSQL 19 introduces several improvements to query planning, execution, maintenance, and database management. However, a new feature or optimization does not guarantee that every existing query will become faster.
The safest approach is to test representative production queries against PostgreSQL 19 before upgrading the production database.
This article explains how to create a PostgreSQL 19 test environment, compare query execution plans, identify performance regressions, validate application behavior, and prepare a database upgrade with confidence.
Why You Should Test Queries Before Upgrading PostgreSQL
PostgreSQL major releases can introduce changes to the query planner, execution engine, system catalogs, SQL behavior, and extensions.
These changes can affect existing applications even when the SQL statements remain unchanged.
Consider a query that retrieves customer orders:
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS total_orders
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY
c.customer_id,
c.customer_name
ORDER BY total_orders DESC;
The query may execute efficiently on the current PostgreSQL version.
After upgrading, PostgreSQL may select a different execution plan based on updated planner logic, statistics, available indexes, and configuration settings.
The result may be:
Faster execution because the optimizer selects a better plan.
Slower execution because a different join strategy is selected.
Higher memory consumption during sorting or aggregation.
Increased disk activity due to temporary files.
Different behavior in queries involving locale-sensitive operations.
Compatibility problems with extensions or application dependencies.
The objective of pre-upgrade testing is to discover these differences before they affect production traffic.
Query compatibility and query performance are different
A query can be syntactically valid and return the correct result while still performing poorly.
For example, a query may continue working after an upgrade but change from an index scan to a sequential scan.
Similarly, a query can execute faster but return unexpected results because of changes affecting a particular expression, collation, or extension.
Therefore, query testing should cover two separate areas:
Testing area | What to verify |
|---|
Functional compatibility | Queries execute successfully and return expected results |
Performance compatibility | Execution plans, latency, memory, and I/O remain acceptable |
Both are necessary before upgrading a production database.
Understand the PostgreSQL 19 Changes That May Affect Existing Queries
Before testing, review the PostgreSQL 19 release notes and identify changes relevant to your database workload.
PostgreSQL 19 introduces improvements in query planning and execution, along with changes to several database features.
Some particularly relevant areas include:
Query planner and execution improvements
PostgreSQL 19 includes several planner and execution optimizations.
These improvements can change how the database evaluates joins, expressions, aggregations, and other operations.
The same SQL statement may therefore use a different execution strategy after upgrading.
For example, a query that previously used nested-loop joins may use a hash join under the new version.
Neither strategy is universally better. The appropriate choice depends on table size, available indexes, data distribution, memory, and estimated row counts.
New query planner advice extensions
PostgreSQL 19 introduces pg_plan_advice and pg_stash_advice extensions.
These extensions provide mechanisms for influencing planner decisions and applying plan advice.
They are relevant when investigating query-plan behavior, but they should not be treated as a replacement for understanding the underlying query and its statistics.
When testing an upgrade, first determine whether existing queries perform correctly without introducing additional planner controls.
Changes to full-text search and pg_trgm
PostgreSQL 19 changes how full-text search and pg_trgm use locale information when classifying characters.
Previously, character classification used libc regardless of the database locale provider.
The new behavior uses the database's locale provider. This can affect text-search results and trigram behavior in databases using locale providers such as ICU or the built-in provider, particularly when the configured locale differs from the database's LC_CTYPE setting.
If your application uses full-text search, trigram indexes, or stored tsvector values, include them in upgrade testing.
The PostgreSQL 19 migration notes recommend rebuilding affected indexes and recomputing stored tsvector values in relevant cases.
Changes to fuzzystrmatch
PostgreSQL 19 also changes how some fuzzystrmatch functions handle non-ASCII characters.
Functions such as soundex, metaphone, difference, dmetaphone, and dmetaphone_alt may produce different results depending on the input and locale behavior.
Applications that use these functions for search, matching, or duplicate detection should test representative multilingual data.
These are examples of why testing only common SELECT statements is insufficient. The database features used by the application should determine the test cases.
Create a Separate PostgreSQL 19 Test Environment
Never use the production database as the initial environment for testing a major-version upgrade.
Instead, create an isolated environment that resembles production as closely as possible.
A useful testing environment should include:
A PostgreSQL 19 server.
A representative copy of production data.
The same extensions used by the application.
Similar database configuration.
Matching indexes and constraints.
Application queries and stored procedures.
Representative concurrent workloads.
The more closely the test environment resembles production, the more useful the results will be.
Run PostgreSQL 19 in Docker
Docker provides a convenient way to create an isolated PostgreSQL environment.
For example:
docker run \
--name postgres19-test \
-e POSTGRES_USER=testuser \
-e POSTGRES_PASSWORD=testpassword \
-e POSTGRES_DB=application_test \
-p 5433:5432 \
-d postgres:19
This example assumes that a PostgreSQL 19 image is available from the selected image registry.
Because PostgreSQL 19 is at Release Candidate 2 at the time of writing, verify the image tag and registry availability before using it. For repeatable testing, pin the exact image tag or digest rather than relying on a floating tag.
Check the running version:
docker exec postgres19-test \
psql -U testuser -d application_test \
-c "SELECT version();"
You can also check the server version number:
SHOW server_version;
For a meaningful comparison, keep the existing PostgreSQL instance available separately.
For example:
Environment | PostgreSQL version | Purpose |
|---|
Existing test environment | Current production major version | Baseline |
New test environment | PostgreSQL 19 RC 2 | Compatibility testing |
Production | Current production major version | Live application |
Do not point production applications to the test container or reuse production credentials.
Use a representative production database copy
A database containing only a few sample rows is not sufficient for performance testing.
Query execution plans can change significantly as table sizes and data distributions change.
For example, a nested-loop join may be efficient when one table contains a small number of records but expensive when the same table contains millions of rows.
Restore a sanitized production backup or generate representative test data.
Protect sensitive information during this process. Remove or anonymize customer details, credentials, payment information, and other confidential data according to your organization's data-handling policies.
Capture Baseline Query Performance
Before testing PostgreSQL 19, collect performance information from the existing database.
This baseline allows you to compare the same queries across versions.
Start by identifying the most important queries in the application.
Focus on:
Frequently executed queries.
Queries with high total execution time.
Queries with high average latency.
Queries that read large numbers of rows.
Queries that perform expensive joins or aggregations.
Queries that frequently use temporary files.
Queries that are important to customer-facing operations.
Enable pg_stat_statements
The pg_stat_statements extension helps track execution statistics for SQL statements.
First, configure it in the existing PostgreSQL environment.
Depending on your installation, add the extension to shared_preload_libraries:
shared_preload_libraries = 'pg_stat_statements'
Restart PostgreSQL if required by the configuration change.
Then create the extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
You can retrieve queries with high total execution time:
SELECT
queryid,
calls,
total_exec_time,
mean_exec_time,
rows,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
The output helps identify statements that contribute significantly to database workload.
For example:
Query characteristic | What it indicates |
|---|
High calls | Frequently executed statement |
High total_exec_time | Significant cumulative database time |
High mean_exec_time | Potentially expensive individual executions |
High row count | Large result sets or processing volume |
Do not rely only on average execution time. A query that executes thousands of times may be important even if each execution is relatively fast.
Also consider application-side latency because database execution time does not include every part of the request lifecycle.
Save baseline query plans
For important queries, capture execution plans using EXPLAIN.
For example:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id)
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY
c.customer_id,
c.customer_name;
Important information includes:
EXPLAIN ANALYZE executes the query. Use it carefully with production data, especially for statements that modify data.
For UPDATE, DELETE, and other modifying statements, testing inside a transaction followed by ROLLBACK can help prevent persistent changes, but triggers and external side effects still require attention.
Compare Execution Plans Between PostgreSQL Versions
Once the baseline is available, run the same queries against PostgreSQL 19.
Use the same database structure, representative data, parameter values, and relevant configuration wherever possible.
For example:
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id)
FROM customers c
JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY
c.customer_id,
c.customer_name;
Compare the PostgreSQL 19 plan with the baseline.
Example of a query-plan comparison
Consider the following illustrative comparison:
Metric | Existing version | PostgreSQL 19 test |
|---|
Planning time | 1.2 ms | 1.0 ms |
Execution time | 85 ms | 62 ms |
Shared hit blocks | 1,200 | 1,150 |
Shared read blocks | 350 | 300 |
Actual rows | 5,000 | 5,000 |
These numbers are examples only, not PostgreSQL 19 benchmark results.
In this hypothetical test, PostgreSQL 19 executes the query faster and reads fewer blocks.
However, one successful execution does not establish that the new version is consistently faster.
Repeat the test under comparable conditions and evaluate multiple workloads.
Understand changes in join strategies
PostgreSQL can use different join methods depending on the query and available statistics.
The most common join strategies include:
Join strategy | Typical use |
|---|
Nested Loop | Efficient when the outer input is small and the inner side can be accessed cheaply |
Hash Join | Often useful for joining larger inputs using a hash table |
Merge Join | Useful when both inputs are available in compatible sorted order |
Suppose PostgreSQL 18 uses a nested-loop join while PostgreSQL 19 chooses a hash join.
This is not automatically a regression.
Check the actual row counts, memory consumption, buffer activity, and execution time before deciding whether the new plan is better.
Investigate estimated and actual row differences
One of the most useful parts of EXPLAIN ANALYZE is the comparison between estimated rows and actual rows.
For example:
Hash Join
(cost=...)
(actual time=... rows=120000 loops=1)
If the planner estimates a small number of rows but the actual result contains substantially more records, it may choose an inefficient strategy.
Review table statistics and consider whether ANALYZE is needed.
For example:
ANALYZE customers;
ANALYZE orders;
Do not compare plans from databases with significantly different statistics and assume the planner version alone caused the difference.
The database should be prepared consistently before testing.
Test Queries That Matter Most to Your Application
Running every SQL statement manually is rarely practical in a large application.
Instead, build a query regression suite around the most important database operations.
A useful test suite should include several categories.
SELECT queries
Test:
Example:
SELECT
order_status,
COUNT(*) AS total_orders,
SUM(total_amount) AS total_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY order_status
ORDER BY total_revenue DESC;
Verify that the output is correct and compare the execution plan.
INSERT, UPDATE, and DELETE operations
Write operations should also be tested because application behavior may depend on constraints, triggers, transaction handling, and returned values.
For example:
BEGIN;
UPDATE orders
SET order_status = 'Completed'
WHERE order_id = 1001
RETURNING order_id, order_status;
ROLLBACK;
Verify:
Expected rows are affected.
Constraints are enforced.
Triggers behave correctly.
Returned values are correct.
Transactions roll back as expected.
Use isolated test data and account for triggers or external effects that a database rollback cannot undo.
Stored procedures and functions
Applications often depend on database functions and stored procedures that are not visible in application repositories.
Include them in the test inventory.
For example:
SELECT
calculate_customer_balance(1001);
Verify both the returned result and the behavior under realistic data conditions.
Pay particular attention to functions that use dynamic SQL, locale-sensitive operations, extensions, or complex exception handling.
Full-text search queries
If the application uses PostgreSQL full-text search, test representative search terms, ranking, language configuration, and index behavior.
For example:
SELECT
document_id,
title,
ts_rank(
search_vector,
plainto_tsquery('english', 'database performance')
) AS relevance
FROM documents
WHERE search_vector @@
plainto_tsquery('english', 'database performance')
ORDER BY relevance DESC;
Compare the returned records and ranking behavior across versions.
If the application uses stored tsvector columns or indexes built using locale-sensitive behavior, follow the PostgreSQL 19 migration guidance for the affected objects.
Validate Extensions and Database Dependencies
A PostgreSQL upgrade is not only about the core database engine.
Applications often depend on extensions that provide additional functionality.
Examples include:
Before upgrading, identify every installed extension.
Run:
SELECT
extname,
extversion
FROM pg_extension
ORDER BY extname;
Then verify that compatible extension versions are available for PostgreSQL 19.
For example, an application using pg_trgm should test its similarity queries and indexes.
SELECT
product_id,
product_name,
similarity(product_name, 'wireless keyboard') AS score
FROM products
WHERE product_name % 'wireless keyboard'
ORDER BY score DESC;
Compare the output and execution behavior against the existing version.
Reindex affected objects when required
PostgreSQL 19 includes migration considerations for full-text search, pg_trgm, and fuzzystrmatch.
For relevant databases, the migration notes recommend rebuilding affected indexes after upgrading with pg_upgrade.
For example:
REINDEX INDEX product_search_idx;
For a table:
REINDEX TABLE products;
For a database:
REINDEX DATABASE application_db;
These commands have different scopes and operational implications. Do not run broad reindex operations in production without reviewing locking, disk-space, replication, and maintenance requirements.
Also remember that rebuilding an index does not automatically recompute stored tsvector values. Follow the migration instructions for both indexes and stored data.
Test Application Queries, Not Just SQL Statements
A database upgrade can expose problems that do not appear when individual SQL statements are executed manually.
The application may use:
Therefore, test the application using the same database driver and query execution path used in production.
For example, a .NET application using Npgsql should be tested against PostgreSQL 19 using a compatible Npgsql version.
A simple application query might look like this:
await using var connection =
new NpgsqlConnection(connectionString);
await connection.OpenAsync(cancellationToken);
const string sql = """
SELECT order_id, total_amount
FROM orders
WHERE customer_id = @CustomerId
ORDER BY order_date DESC
LIMIT 20;
""";
await using var command = new NpgsqlCommand(
sql,
connection
);
command.Parameters.AddWithValue(
"CustomerId",
customerId
);
await using var reader =
await command.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
var orderId = reader.GetInt32(0);
var totalAmount = reader.GetDecimal(1);
}
The example uses parameterized SQL and asynchronous database operations.
During upgrade testing, verify that:
The driver connects successfully.
Parameter types are mapped correctly.
Prepared statements behave as expected.
Transactions work correctly.
Connection pooling remains stable.
Cancellation behaves correctly.
Application-generated SQL returns the expected results.
A query that works in a database console may still fail through an application because of differences in parameter types, session settings, or driver behavior.
Build an Automated Query Regression Test Suite
For a large application, manually comparing every important query is time-consuming.
A better approach is to automate functional validation.
Create a test suite that runs the same queries against both database versions using equivalent test data.
For example, an integration test can verify the expected result of a customer order query.
[Fact]
public async Task GetCustomerOrders_ReturnsExpectedOrders()
{
await using var connection =
new NpgsqlConnection(_connectionString);
await connection.OpenAsync();
const string sql = """
SELECT order_id, total_amount
FROM orders
WHERE customer_id = @CustomerId
ORDER BY order_id;
""";
await using var command =
new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue(
"CustomerId",
1001
);
await using var reader =
await command.ExecuteReaderAsync();
var orderIds = new List<int>();
while (await reader.ReadAsync())
{
orderIds.Add(reader.GetInt32(0));
}
Assert.Equal(
new[] { 101, 102, 103 },
orderIds
);
}
This example verifies functional results. A separate performance test should measure query execution under controlled conditions.
Run the same tests against both versions
You can configure a test pipeline with separate database connection strings:
PostgreSQL compatibility tests
|
+---- Existing PostgreSQL version
|
+---- PostgreSQL 19
|
v
Compare results
|
v
Compare execution metrics
|
v
Report differences
The test suite should validate:
For performance comparisons, avoid strict pass/fail thresholds based on a single execution. Run repeated tests and account for cache state, concurrent activity, and hardware differences.
Test the Upgrade Process Separately From Query Compatibility
Query compatibility testing and database migration testing are related, but they are not the same activity.
A database may execute every tested query correctly on PostgreSQL 19 while the actual upgrade process still encounters problems.
Before upgrading, test the migration path itself.
PostgreSQL major-version upgrades require a major-version migration strategy, such as:
The appropriate method depends on database size, downtime requirements, extension support, and operational constraints.
Test using pg_dump and pg_restore
A logical backup and restore provides an opportunity to validate schema and data compatibility.
For example:
pg_dump \
-h source-host \
-U postgres \
-d application_db \
-Fc \
-f application.dump
Restore into the PostgreSQL 19 test environment:
pg_restore \
-h target-host \
-U postgres \
-d application_test \
--verbose \
application.dump
Use compatible client tools and review the restore output for warnings or errors.
After restoring, verify:
Table counts.
Important business records.
Index definitions.
Constraints.
Stored procedures.
Extension availability.
Database ownership and permissions.
Application connectivity.
For large databases, also measure backup and restore duration to understand the operational impact.
Common Mistakes During PostgreSQL Upgrade Testing
Mistake | Why it causes problems | Better approach |
|---|
Testing with a small sample database | Does not represent production query behavior | Use representative data volumes |
Comparing queries only by execution time | Misses changes in plans and resource consumption | Compare EXPLAIN ANALYZE output |
Ignoring extensions | Application functionality may break after migration | Inventory and validate every extension |
Testing only SELECT statements | Write operations and transactions may behave differently | Include complete application workflows |
Comparing different database configurations | Results may reflect configuration differences rather than version changes | Keep test environments comparable |
Ignoring locale-sensitive operations | Search and matching behavior may change | Test multilingual and locale-dependent data |
Assuming faster execution in one run proves improvement | Cache state and system load affect measurements | Use repeated, controlled tests |
Testing directly on production | Creates unnecessary operational risk | Use an isolated staging environment |
Ignoring application drivers | Database connectivity and type mapping may fail | Test through the actual application stack |
A Practical PostgreSQL 19 Upgrade Testing Checklist
Before approving a production upgrade, use a structured validation process.
Review PostgreSQL 19 release notes and migration notes.
Create an isolated PostgreSQL 19 environment.
Restore a representative production database.
Inventory installed extensions and verify compatibility.
Capture baseline query statistics.
Save execution plans for critical queries.
Run the same query suite against PostgreSQL 19.
Compare functional results and execution plans.
Test full-text search and locale-sensitive operations.
Validate stored procedures and database functions.
Test application drivers and ORM-generated SQL.
Run integration and performance tests.
Test the actual backup and migration process.
Review index rebuilding and data recomputation requirements.
Verify replication, backup, and recovery procedures.
Prepare a rollback or recovery plan.
Do not treat the checklist as complete simply because all SQL statements execute successfully. Production readiness also depends on operational testing, recovery planning, monitoring, and application compatibility.
Conclusion
Testing PostgreSQL queries before a major-version upgrade helps identify compatibility problems and performance regressions before they affect production applications.
PostgreSQL 19 introduces improvements to query planning and execution, along with changes to full-text search, pg_trgm, fuzzystrmatch, and other database features. These changes make it important to test real application workloads rather than relying only on simple SQL examples.
Start by creating an isolated PostgreSQL 19 environment with representative data. Capture baseline query statistics and execution plans from the existing database, then run the same workloads against the new version.
Compare functional results, execution strategies, memory usage, buffer activity, and application behavior. Validate extensions, database drivers, stored procedures, and the actual migration process as well.
A successful database upgrade is not simply one where PostgreSQL starts without errors. It is one where application behavior remains correct, critical queries meet performance expectations, and the engineering team has a tested recovery strategy.
Join the conversation! Your thoughts help the community grow.