PostgreSQL is widely used for applications that combine relational data with semi-structured information. A common example is a business application where core entities such as customers, orders, and invoices use relational columns, while flexible attributes are stored in jsonb.
For .NET applications using Entity Framework Core, this creates an interesting performance problem.
A typical model might look like this:
Customer
|
+-- Relational Columns
| +-- Id
| +-- Name
| +-- Status
|
+-- JSONB
+-- Preferences
+-- Metadata
+-- CustomFields
The flexibility of jsonb is useful, but performance depends heavily on how the data is shaped, indexed, queried, updated, and mapped through EF Core.
When evaluating a PostgreSQL beta release against an existing production database version, the right question is not simply:
Is the newer database faster?
The useful question is:
How does the new PostgreSQL version behave under the application's actual EF Core and JSONB workload?
This article presents a practical benchmarking methodology for PostgreSQL 19 Beta with JSONB-heavy EF Core applications. The focus is on query latency, indexing, JSONB operators, EF Core translation, write performance, concurrency, query plans, and regression analysis.
Introduction
JSONB is useful when application data does not fit neatly into a fixed relational schema.
For example:
{
"preferences": {
"language": "en",
"notifications": true
},
"features": [
"analytics",
"exports"
],
"metadata": {
"source": "mobile"
}
}
A relational table can store this document in a PostgreSQL jsonb column:
CREATE TABLE customers
(
id bigint PRIMARY KEY,
name text NOT NULL,
status text NOT NULL,
metadata jsonb NOT NULL
);
EF Core can map the JSON structure into .NET types.
The application can then execute queries that combine relational predicates with JSONB predicates:
status = 'Active'
AND
metadata contains specific attribute
This is where benchmarking becomes important.
A database version change can affect:
Query planning
JSONB operators
Index usage
Sorting
Aggregation
Parallel execution
Write behavior
Concurrency
EF Core-generated SQL
A benchmark should measure these behaviors directly.
Why Benchmark PostgreSQL Beta Releases?
A beta database release should not be evaluated only through synthetic microbenchmarks.
Your application may behave differently because it has a specific workload.
For example:
Application
|
v
EF Core
|
v
Generated SQL
|
v
PostgreSQL
|
+-- Relational Columns
|
+-- JSONB
|
+-- Indexes
A benchmark that tests only:
SELECT *
FROM customers
WHERE metadata @> ...;
may tell you something about PostgreSQL itself.
It does not necessarily tell you whether your EF Core application benefits from the upgrade.
The strongest benchmark therefore contains both database-level and application-level measurements.
Understand the JSONB Workload
Before benchmarking, classify the queries your application actually runs.
Common JSONB patterns include:
Containment
metadata @> '{"region":"EU"}'
Key Existence
metadata ? 'preferences'
JSON Path Queries
jsonb_path_exists(
metadata,
'$.preferences.language ? (@ == "en")'
)
Extraction
metadata -> 'preferences' ->> 'language'
Relational + JSONB Filtering
SELECT *
FROM customers
WHERE status = 'Active'
AND metadata @> '{"region":"EU"}';
These queries should be represented in the benchmark.
Build a Representative Entity Model
A simple EF Core model might be:
public sealed class Customer
{
public long Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Status { get; set; } = string.Empty;
public CustomerMetadata Metadata { get; set; } = new();
}
public sealed class CustomerMetadata
{
public string Region { get; set; } = string.Empty;
public string Segment { get; set; } = string.Empty;
public bool NotificationsEnabled { get; set; }
}
The exact EF Core JSON mapping depends on the EF Core version and the application's chosen modeling approach.
The important benchmarking principle is to use the same model and mapping configuration against both database versions.
Keep the Application Layer Constant
A fair comparison changes only the database version.
Keep these constant:
.NET Runtime
EF Core Version
Application Code
Connection Pool Configuration
Entity Model
Queries
Dataset
Indexes
Hardware
Container Resources
Change:
PostgreSQL Version
If you change EF Core, PostgreSQL, query structure, and indexes at the same time, the benchmark cannot tell you what caused the performance difference.
Create a Controlled Dataset
Dataset size matters.
A useful benchmark might include:
100,000 customers
1,000,000 customers
10,000,000 customers
The exact sizes should reflect the application's expected scale.
Generate realistic JSONB distributions.
For example:
{
"region": "EU",
"segment": "Enterprise",
"preferences": {
"language": "en",
"notifications": true
},
"features": [
"analytics",
"exports"
]
}
Avoid generating identical JSON for every row.
Real applications usually contain variation.
Control JSONB Cardinality
The distribution of values can dramatically affect query performance.
For example:
region = EU 45%
region = US 35%
region = APAC 15%
region = Other 5%
This is different from:
region = EU 99%
region = Other 1%
A benchmark should therefore record the distribution of important JSONB fields.
Benchmark With and Without Indexes
Do not benchmark only the indexed case.
Create scenarios such as:
Scenario A
No JSONB index
Scenario B
GIN index
Scenario C
Expression index
Scenario D
Relational + JSONB indexes
The goal is to understand not only query speed but also the cost of maintaining the indexes.
GIN Index
A common JSONB indexing strategy is a GIN index.
For example:
CREATE INDEX ix_customers_metadata
ON customers
USING GIN (metadata);
This can be useful for containment and other JSONB operations.
But an index is not automatically beneficial for every query.
The query planner decides whether using it is worthwhile.
Expression Indexes
If the application frequently queries a specific JSON value, an expression index may be appropriate.
For example:
CREATE INDEX ix_customers_region
ON customers ((metadata ->> 'region'));
Now a query targeting the extracted value can potentially use that index.
This is fundamentally different from indexing the entire JSONB document.
The benchmark should compare both approaches where they represent realistic application designs.
Measure Write Performance
JSONB indexes have a write cost.
Consider:
INSERT
|
+--> Table
|
+--> JSONB Index
and:
UPDATE JSONB
|
+--> Table Update
|
+--> Index Maintenance
Therefore, a database design that makes reads faster can make writes slower.
Benchmark:
Insert throughput
Bulk insert throughput
JSONB update latency
Mixed read/write workload
Index maintenance cost
Benchmark Partial JSONB Updates
One important workload is updating a small part of a large JSON document.
For example:
metadata
|
+-- preferences
| +-- language
| +-- notifications
|
+-- features
+-- region
Changing only:
preferences.notifications
can have different performance characteristics from replacing the entire JSON document.
Benchmark both if the application performs these operations.
EF Core Query Translation Matters
A LINQ query may look simple:
var customers = await db.Customers
.Where(c => c.Metadata.Region == "EU")
.ToListAsync();
But the important question is:
What SQL does EF Core generate?
Inspect the generated SQL.
For example:
var query = db.Customers
.Where(c => c.Metadata.Region == "EU");
Console.WriteLine(
query.ToQueryString());
The benchmark should record the SQL shape.
This helps separate:
EF Core Translation
from:
PostgreSQL Execution
Do Not Benchmark LINQ Alone
If an EF Core query becomes slower, possible causes include:
LINQ Query
|
v
SQL Translation
|
v
Generated SQL
|
v
Query Plan
|
v
Database Execution
A database benchmark should therefore capture both the generated SQL and the PostgreSQL execution plan.
Use EXPLAIN ANALYZE
For important queries:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM customers
WHERE metadata @> '{"region":"EU"}';
This provides information about:
Actual execution time
Rows returned
Rows removed by filtering
Buffer activity
Scan strategy
The benchmark should preserve query plans for comparison.
Compare Query Plans Across Versions
Suppose PostgreSQL version A uses:
Bitmap Index Scan
|
v
Bitmap Heap Scan
while PostgreSQL 19 Beta chooses:
Sequential Scan
The query may become faster or slower depending on data size and selectivity.
Therefore, a performance difference should not be reported without examining the query plan.
Measure Cache Effects
Database benchmarks can be heavily influenced by cache state.
Compare:
Cold Cache
Warm Cache
A warm-cache test may represent normal production behavior for frequently accessed data.
A cold-cache test can reveal storage and I/O characteristics.
Do not mix the two in one result set.
Connection Pooling
EF Core applications usually use connection pooling.
Benchmark with realistic pool settings.
For example:
Minimum Pool Size
Maximum Pool Size
Connection Lifetime
Connection Timeout
Changing the pool configuration between PostgreSQL versions makes the comparison less useful.
Benchmark Concurrency
Single-user latency is only one measurement.
Test:
1 concurrent request
10 concurrent requests
50 concurrent requests
100 concurrent requests
Measure:
p50
p95
p99
Requests/sec
Database CPU
Database memory
I/O
Connection usage
JSONB workloads can behave differently under contention.
Read-Heavy Workload
A read-heavy benchmark might look like:
80% SELECT
20% UPDATE
For example:
Query customer
Query customer by JSONB region
Query customer by segment
Update customer metadata
This better resembles many API workloads.
Write-Heavy Workload
A write-heavy workload might be:
30% SELECT
70% INSERT/UPDATE
This is useful for applications where JSONB is frequently modified.
Indexes that look excellent under read-heavy workloads may produce a very different result here.
Mixed Workload
A realistic benchmark could include:
50% Reads
30% Inserts
15% JSONB Updates
5% Complex Queries
The exact distribution should come from application telemetry where possible.
Benchmark Large JSON Documents
JSONB performance depends partly on document size.
Create scenarios such as:
1 KB JSON
5 KB JSON
25 KB JSON
100 KB JSON
500 KB JSON
Measure:
Insert latency
Read latency
Update latency
Index size
Storage growth
Query performance
A JSONB-heavy workload with 2 KB documents is very different from one containing 500 KB documents.
Benchmark JSONB Depth
Test different nesting levels:
{
"region": "EU"
}
versus:
{
"customer": {
"preferences": {
"communication": {
"email": {
"marketing": {
"enabled": true
}
}
}
}
}
}
Deeply nested data can create different query and indexing requirements.
Benchmark Arrays
JSON arrays are another common pattern:
{
"roles": [
"admin",
"reporting",
"billing"
]
}
Queries involving array membership should be included when they represent production behavior.
For example:
SELECT *
FROM customers
WHERE metadata @> '{"roles":["billing"]}';
Compare JSONB With Relational Columns
A useful benchmark should not assume JSONB is always the right storage model.
Compare:
Option A
region stored in JSONB
Join the conversation! Your thoughts help the community grow.