Introduction
JSON has become a common part of modern application development. Even strongly typed .NET applications often need to store flexible metadata, configuration, event payloads, or third-party data that does not fit neatly into relational columns.
PostgreSQL's jsonb type is particularly useful for these scenarios because it stores JSON in a format that can be indexed and queried efficiently.
For an EF Core application, however, simply changing a property from a string to JSONB is not enough. Query patterns, index selection, data distribution, write frequency, and generated SQL all influence performance.
When evaluating PostgreSQL 19 during its beta cycle, JSONB indexing is therefore a useful workload for understanding query behavior and identifying possible regressions or improvements before adopting a new database version.
This article walks through a practical approach to benchmarking JSONB indexes with EF Core. The focus is not on claiming a universal performance number, but on building a repeatable benchmark that can be used with your own application workload.
What Is JSONB?
PostgreSQL provides two JSON-related data types:
json
jsonb
The important distinction is that json preserves the original JSON representation, while jsonb stores the data in a binary representation designed for efficient processing.
For application workloads that frequently query JSON content, jsonb is usually the more practical choice.
Consider a customer metadata document:
{
"department": "engineering",
"location": "Bengaluru",
"skills": [
"C#",
"PostgreSQL",
"Azure"
],
"active": true
}
A relational table could store this as:
Id
Name
Metadata
where Metadata uses the PostgreSQL jsonb type.
Why JSONB Indexing Matters
Without an appropriate index, a JSONB query may require PostgreSQL to inspect many rows.
Conceptually:
Query
|
v
Sequential Scan
|
+--> Row 1
+--> Row 2
+--> Row 3
+--> ...
+--> Row N
An index can reduce the amount of data that must be examined.
Query
|
v
JSONB Index
|
v
Candidate Rows
|
v
Result
The important point is that the best index depends on the query pattern.
Example EF Core Entity
Consider an EF Core entity:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public string Metadata { get; set; } = "{}";
}
The property can be mapped to PostgreSQL jsonb:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>()
.Property(x => x.Metadata)
.HasColumnType("jsonb");
}
The actual CLR representation can vary depending on how the application models JSON. The important database-level requirement is that PostgreSQL stores the value as jsonb.
Use a Strongly Typed JSON Model Where Appropriate
For more maintainable applications, you may prefer a structured CLR type.
For example:
public sealed class CustomerMetadata
{
public string? Department { get; set; }
public string? Location { get; set; }
public bool Active { get; set; }
public List<string> Skills { get; set; } = [];
}
Then map the property according to the JSON mapping capabilities used by the application's EF Core and provider versions.
The benchmark should use the same mapping strategy as the real application because serialization and generated SQL can affect the workload.
JSONB Query Patterns
Before creating an index, identify the queries the application actually performs.
Common patterns include:
Find rows where a property equals a value
Find rows containing a JSON object
Find rows containing an array element
Check whether a key exists
Filter by nested JSON properties
For example:
{
"department": "engineering"
}
A query might search for customers whose metadata contains that value.
The benchmark should reproduce actual production query patterns rather than testing arbitrary JSON expressions.
GIN Index
A common index strategy for JSONB workloads is a GIN index.
Conceptually:
JSONB Data
|
v
GIN Index
|
+--> Keys
+--> Values
+--> Containment Information
A basic PostgreSQL index can be created with:
CREATE INDEX ix_customer_metadata
ON customers
USING GIN (metadata);
The EF Core migration can execute provider-specific SQL when the normal EF Core index API does not express the required PostgreSQL index configuration.
For example:
migrationBuilder.Sql("""
CREATE INDEX ix_customer_metadata
ON customers
USING GIN (metadata);
""");
The exact migration syntax should follow the application's EF Core and PostgreSQL provider versions.
GIN Operator Classes Matter
Not every GIN index represents the same tradeoff.
PostgreSQL supports different operator classes for JSONB indexing, and the appropriate choice depends on the operators used by the application.
A benchmark should therefore compare the actual index strategy used by the workload rather than assuming that one GIN configuration is always optimal.
A useful experiment could compare:
No JSONB index
|
v
GIN index
|
v
Alternative JSONB index configuration
The goal is to understand the query/index relationship.
Benchmark Dataset Design
A benchmark is only useful if the data resembles the real workload.
Avoid generating every row with identical JSON.
Instead, introduce realistic distributions.
For example:
Department:
Engineering 45%
Sales 20%
Support 15%
Finance 10%
Other 10%
Similarly, some JSON keys should be common while others should be relatively selective.
This matters because query performance can change substantially depending on selectivity.
Data Volume
Run the benchmark at multiple dataset sizes.
For example:
| Dataset | Purpose |
|---|
| 10K rows | Development baseline |
| 100K rows | Medium workload |
| 1M rows | Large workload |
| 10M rows | Stress workload |
These are benchmark sizes, not recommendations for a specific production system.
The important point is to observe how query plans behave as the dataset grows.
A query that looks fast on 10,000 rows may behave differently at several million rows.
Benchmark Query Types
A useful benchmark suite should contain several JSONB operations.
Equality-Style Filtering
metadata -> department = engineering
Containment
metadata contains:
{
"department": "engineering"
}
Key Existence
metadata contains key:
"department"
Array Membership
skills contains:
"C#"
Nested Property Search
preferences.language = "en"
Each query should be measured independently.
EF Core Query Example
The application query might look conceptually like:
var customers = await db.Customers
.Where(c => /* JSONB condition */)
.ToListAsync();
The exact LINQ expression depends on the JSON mapping approach and EF Core/provider version.
For benchmarking, inspect the generated SQL rather than assuming that the LINQ expression translates into the SQL you expect.
Always Inspect Generated SQL
EF Core makes it easy to write a convenient LINQ query.
That does not mean the generated SQL is necessarily optimal.
You can inspect SQL using:
var query = db.Customers
.Where(c => /* JSONB condition */);
Console.WriteLine(query.ToQueryString());
The generated SQL should then be analyzed alongside PostgreSQL's execution plan.
This is especially important for JSONB queries because small differences in operators can affect index usage.
EXPLAIN ANALYZE
The database execution plan is more important than application-level timing alone.
A useful investigation starts with:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
FROM customers
WHERE ...;
This provides information about:
Execution time
Planning time
Scan type
Rows examined
Rows returned
Buffer activity
Index usage
For example, you may see:
Index Scan
or:
Bitmap Index Scan
Bitmap Heap Scan
or:
Seq Scan
The benchmark should record the plan, not just the final duration.
Why Sequential Scans Are Not Always Bad
Seeing Seq Scan does not automatically mean PostgreSQL made a poor decision.
Suppose a query returns a large percentage of the table.
Using an index may involve:
Index
|
v
Many matching rows
|
v
Many table accesses
A sequential scan may be cheaper.
Therefore, the benchmark should test different selectivity levels rather than assuming that an index must always be used.
Benchmark Selectivity
For example:
High Selectivity:
1% of rows match
Medium Selectivity:
20% of rows match
Low Selectivity:
80% of rows match
Then compare query plans and latency.
This can reveal the point at which PostgreSQL changes its preferred execution strategy.
Benchmark Read Performance
For each query, record:
Rows returned
Execution time
Planning time
Buffers read
Buffers hit
Execution plan
A benchmark result might look like:
| Query | Index | Rows | P50 | P95 |
|---|
| Department filter | None | 1,000 | Measure | Measure |
| Department filter | GIN | 1,000 | Measure | Measure |
| Key existence | None | 20,000 | Measure | Measure |
| Key existence | GIN | 20,000 | Measure | Measure |
The values should come from the actual benchmark environment.
Benchmark Write Performance
Indexes are not free.
Every inserted or updated JSONB value may require corresponding index maintenance.
Therefore, measure writes too.
Test:
INSERT
UPDATE JSONB
UPDATE unrelated column
DELETE
This is particularly important for applications with frequent JSONB updates.
A benchmark that measures only reads may recommend an index that produces excellent query latency while creating unacceptable write overhead.
Measure Index Size
Index storage should also be part of the benchmark.
Conceptually:
Table Size
+
JSONB Index Size
+
Other Indexes
=
Storage Footprint
A larger index can affect:
Storage requirements
Backup size
Cache behavior
Maintenance operations
Write performance
Therefore, record index size alongside query latency.
Benchmark EF Core End-to-End Performance
Database execution time is only one part of an EF Core application's latency.
The complete request may look like:
HTTP Request
|
v
ASP.NET Core
|
v
EF Core
|
v
Npgsql
|
v
PostgreSQL
|
v
JSONB Index
|
v
Rows
|
v
Materialization
|
v
HTTP Response
Measure both:
Database Execution Time
and:
End-to-End Application Time
Otherwise, an optimization at the database level may appear more significant than it actually is at the API level.
Use Warm and Cold Cache Tests
Database cache state can significantly influence results.
A warm-cache benchmark might repeatedly execute:
Same query
Same data
Same index
A cold-cache scenario may require substantially more physical I/O.
Do not mix the two.
At minimum, label benchmark runs as:
Warm Cache
Cold/Reduced Cache
The exact method for producing a cold-cache test should be appropriate to the test environment and should not be confused with production behavior.
Benchmark Methodology
A reliable benchmark should follow a repeatable sequence.
Prepare Dataset
|
v
Create Index
|
v
Warm Database
|
v
Run Queries
|
v
Capture EXPLAIN ANALYZE
|
v
Capture Application Metrics
|
v
Repeat
|
v
Compare
Run enough iterations to reduce noise.
Discarding the first few warm-up iterations can also be useful when measuring application-level behavior, but the methodology should be documented.
Example Benchmark Model in C#
A simple result record can keep the measurements structured.
public sealed record JsonbBenchmarkResult(
string QueryName,
string IndexStrategy,
int DatasetSize,
int Iterations,
double P50Ms,
double P95Ms,
double P99Ms,
long IndexSizeBytes,
bool UsesExpectedIndex);
The important part is consistency.
Every index strategy should be tested against the same dataset and query workload.
Avoid Application-Level Noise
When measuring database indexing, reduce unrelated variability.
Keep these factors consistent:
Application version
EF Core version
PostgreSQL configuration
Provider version
Dataset
Query
Connection settings
Hardware
Concurrent workload
Cache state
If one benchmark runs with connection pooling disabled and another does not, the comparison becomes difficult to interpret.
Compare PostgreSQL Versions Carefully
When evaluating a PostgreSQL beta release, use the same workload against the baseline PostgreSQL version.
The comparison should look like:
Same Dataset
|
+--> PostgreSQL Baseline
|
+--> PostgreSQL Beta
Keep the following consistent:
Same:
Schema
Indexes
Queries
Data
Hardware
EF Core
Provider
Configuration
This helps isolate database-version effects.
Do not conclude that a performance difference comes from PostgreSQL alone if other components changed simultaneously.
Detecting Query Plan Changes
One of the most valuable outputs of the benchmark is the execution-plan comparison.
For example:
Baseline:
Bitmap Index Scan
+
Bitmap Heap Scan
Beta:
Seq Scan
That change deserves investigation.
However, a changed plan is not automatically a regression.
The new plan may actually be faster for the specific data distribution.
Always compare:
Plan
+
Execution Time
+
Buffers
+
Rows
together.
Common Benchmarking Mistakes
Testing Only One JSON Document Shape
Real applications usually contain different keys, nesting levels, and array sizes.
Measuring Only Query Latency
Index storage and write overhead also matter.
Assuming GIN Is Always Faster
Index usefulness depends on the query operator, selectivity, data distribution, and workload.
Ignoring Generated SQL
The LINQ query is not the final database query.
Ignoring Execution Plans
A latency number without the corresponding plan makes diagnosis difficult.
Testing Only Small Datasets
Small datasets can hide scaling problems.
Mixing Warm and Cold Cache Results
This can produce misleading comparisons.
Changing Multiple Variables
If PostgreSQL, EF Core, provider, schema, and hardware all change simultaneously, identifying the cause becomes difficult.
Best Practices
Benchmark real JSONB query patterns.
Use representative JSON document distributions.
Test multiple dataset sizes.
Compare indexed and non-indexed workloads.
Test different JSONB operator patterns.
Inspect SQL generated by EF Core.
Capture EXPLAIN ANALYZE output.
Include buffer statistics.
Measure both reads and writes.
Track index storage size.
Test multiple selectivity levels.
Separate cold-cache and warm-cache experiments.
Measure P50, P95, and P99 latency.
Keep PostgreSQL version comparisons controlled.
Validate application-level latency in addition to database execution time.
Frequently Asked Questions
Is JSONB always better than JSON?
Not necessarily. JSONB is generally more suitable when the application needs efficient querying and indexing, while json can be appropriate when preserving the original JSON representation is important.
Is a GIN index always required for JSONB?
No. The appropriate indexing strategy depends on the application's query patterns. Some workloads may benefit from more targeted indexing approaches.
Should JSONB queries be benchmarked through EF Core or directly in PostgreSQL?
Both. PostgreSQL-level benchmarks help isolate database behavior, while EF Core end-to-end tests reveal the impact on the actual application.
Why should write performance be measured?
Indexes require maintenance when indexed data changes. An index that improves reads may increase insert or update costs.
Why can PostgreSQL choose a sequential scan when an index exists?
The optimizer estimates that scanning the table may be cheaper, especially when a large percentage of rows matches the query.
Are beta benchmark results enough to decide on a production upgrade?
No. Beta testing can reveal compatibility and performance characteristics, but production adoption should also consider release stability, migration testing, application compatibility, and the final release behavior.
Conclusion
JSONB provides .NET applications with a practical way to work with flexible data while retaining PostgreSQL's relational capabilities. But JSONB performance depends heavily on how the data is queried and indexed.
A useful EF Core benchmark should therefore go beyond measuring a single query's execution time. It should compare realistic JSONB workloads, different data volumes, selectivity levels, index strategies, write operations, index sizes, generated SQL, execution plans, and end-to-end application latency.
When evaluating a PostgreSQL beta against an existing version, the most valuable result is not simply a statement such as "the new version is faster." A stronger benchmark shows which query changed, which execution plan PostgreSQL selected, how much work was performed, how index behavior changed, and whether the difference remains meaningful at realistic application scale.