PostgreSQL upgrades are easy to underestimate. A new version may look compatible at the application level, but the real question is whether your existing queries, indexes, transactions, connection pooling, and ORM-generated SQL continue to behave as expected.
This becomes even more important when testing a beta release such as PostgreSQL 19. A beta is useful for finding compatibility and performance issues before an upgrade, but it should not be treated as a production database.
For .NET teams using PostgreSQL through applications built with ASP.NET Core, Entity Framework Core, Dapper, or Npgsql, the best approach is to test PostgreSQL 19 against queries that resemble actual application workloads.
This article explains how to build that test environment, capture representative queries, run repeatable tests, and identify problems before they reach production.
Why Test PostgreSQL 19 With Production-Like Queries?
A database version upgrade can affect more than SQL syntax.
Your application depends on several layers working together:
PostgreSQL behavior
Npgsql driver compatibility
Entity Framework Core or Dapper
Connection pooling
Transactions
Indexes and execution plans
Data types
Extensions
Application-specific query patterns
A simple SELECT test does not cover these interactions.
For example, an application might execute a query containing multiple joins, filtering, pagination, ordering, and parameters:
SELECT
o.id,
o.customer_id,
o.total_amount,
o.created_at
FROM orders o
WHERE o.customer_id = @customerId
AND o.status = @status
ORDER BY o.created_at DESC
LIMIT @limit OFFSET @offset;
The important question is not whether PostgreSQL can execute this query.
The important question is whether it behaves acceptably with your application's real data volume, indexes, parameter patterns, concurrency, and transaction behavior.
What Does Production-Like Testing Mean?
Production-like does not mean copying your entire production database into a test environment.
Instead, it means reproducing the characteristics that influence database behavior.
A useful test dataset should approximate:
| Area | Production-Like Requirement |
|---|---|
| Row counts | Similar order of magnitude |
| Data distribution | Similar common and uncommon values |
| Indexes | Same important indexes |
| Schema | Same tables, constraints, and types |
| Queries | Representative application queries |
| Parameters | Common and edge-case values |
| Concurrency | Realistic concurrent requests |
| Transactions | Typical application transaction patterns |
| Connection pooling | Similar pool configuration |
For example, testing a query against 10,000 rows when production contains tens or hundreds of millions of rows can produce misleading results.
Set Up PostgreSQL 19 for Testing
The first rule is simple: keep the beta database isolated from production.
A containerized environment is convenient for repeatable testing.
For example:
services:
postgres19:
image: postgres:19
container_name: postgres19-test
environment:
POSTGRES_DB: appdb
POSTGRES_USER: appuser
POSTGRES_PASSWORD: testpassword
ports:
- "5433:5432"
volumes:
- postgres19_data:/var/lib/postgresql/data
volumes:
postgres19_data:
The exact PostgreSQL 19 image/tag should be selected according to the beta build you are evaluating. Beta releases can change, so record the exact version used in every test run.
You can verify the server version with:
SELECT version();
Also capture the server settings that could influence your results.
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SHOW effective_cache_size;
The goal is not necessarily to duplicate every production setting. It is to document the differences so test results can be interpreted correctly.
Use the Same Schema as Production
A useful compatibility test should begin with the same database structure used by the application.
That includes:
Tables
Primary keys
Foreign keys
Unique constraints
Indexes
Views
Functions
Extensions
Data types
For example:
CREATE TABLE customers
(
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders
(
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status TEXT NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX ix_orders_customer_created
ON orders(customer_id, created_at DESC);
Do not simplify the schema too aggressively for testing. Removing an index or constraint can completely change query behavior.
Test the .NET Data Access Layer
The database is only one part of the compatibility chain.
A .NET application might use Npgsql directly:
using Npgsql;
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
const string sql = """
SELECT id, name, email
FROM customers
WHERE email = @email
""";
await using var command = new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue("email", "[email protected]");
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine(reader.GetString(1));
}
This test checks more than SQL compatibility. It also exercises the driver-to-server communication path.
For applications using Entity Framework Core, run the application's actual LINQ queries rather than manually rewriting them as SQL.
var orders = await dbContext.Orders
.Where(x => x.CustomerId == customerId &&
x.Status == "Completed")
.OrderByDescending(x => x.CreatedAt)
.Take(50)
.ToListAsync();
This is important because an ORM-generated query can differ from the SQL a developer expects.
Build a Representative Query Set
Do not randomly select queries.
Create a test suite based on the operations your application performs most often.
A useful query set might contain:
Read Queries
Simple lookups
Filtered searches
Multi-table joins
Aggregations
Pagination
Reporting queries
Queries using JSON or array types
Write Queries
Inserts
Updates
Deletes
Bulk operations
Upserts
Transaction Tests
await using var transaction = await dbContext.Database
.BeginTransactionAsync();
try
{
order.Status = "Completed";
await dbContext.SaveChangesAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
The goal is to test the patterns your application actually depends on.
Compare Execution Plans
One of the most useful tools for database upgrade testing is EXPLAIN.
Start with:
Join the conversation! Your thoughts help the community grow.