Database performance is often discussed in terms of queries, indexes, and CPU usage.
But for many real applications, storage I/O is still an important part of the execution path.
A query may need to:
Read table pages
|
v
Read index pages
|
v
Fetch heap pages
|
v
Return rows
When those reads cannot be served from memory, storage latency becomes part of query latency.
PostgreSQL 18 introduces a new asynchronous I/O subsystem designed to allow multiple I/O requests to be processed concurrently. The initial AIO support covers workloads including sequential scans, bitmap heap scans, and vacuum operations. PostgreSQL also provides configuration options for selecting the I/O method and controlling concurrency.
For .NET developers, the interesting question is not simply:
"Is PostgreSQL 18 AIO faster?"
A more useful question is:
"What difference does PostgreSQL 18 AIO make when a real ASP.NET Core application accesses PostgreSQL through EF Core?"
That requires measuring the complete path:
ASP.NET Core
|
v
EF Core
|
v
Database Driver
|
v
PostgreSQL 18
|
v
Storage
This article presents a practical benchmark methodology for that scenario.
What PostgreSQL 18 AIO Changes
Before PostgreSQL 18, database I/O generally depended more heavily on synchronous operations and operating-system readahead.
PostgreSQL 18 introduces an AIO subsystem that allows the database to queue multiple I/O requests rather than waiting for individual requests sequentially. The release documentation specifically identifies sequential scans, bitmap heap scans, and vacuum among the workloads that can benefit.
The key idea is:
Traditional approach
Read page 1
|
Wait
|
Read page 2
|
Wait
|
Read page 3
versus:
AIO
Submit page 1
Submit page 2
Submit page 3
Submit page 4
|
v
Process completions
This does not mean every PostgreSQL query automatically becomes faster.
The benefit depends heavily on whether the workload is actually I/O-bound.
PostgreSQL 18 AIO Methods
PostgreSQL 18 provides the io_method setting.
The documented options include:
worker
io_uring
sync
The worker method uses PostgreSQL I/O worker processes.
The io_uring method uses the Linux io_uring interface when PostgreSQL has been built with the required support.
The sync method preserves synchronous execution for AIO-eligible operations.
A simplified configuration looks like:
io_method = worker
or, on an appropriate Linux deployment:
io_method = io_uring
For comparison:
io_method = sync
can be used as a baseline.
Do not assume that io_uring is automatically the fastest option.
Benchmark the actual environment.
Why EF Core Changes the Benchmark
A database-only benchmark answers:
How fast can PostgreSQL execute this workload?
An EF Core benchmark answers a broader question:
How does the application behave when
EF Core generates and executes the workload?
The complete path includes:
LINQ
|
v
EF Core Query Translation
|
v
SQL
|
v
Database Driver
|
v
PostgreSQL
|
v
Storage
|
v
Result Materialization
If you measure only SQL execution time, you are not measuring the application experience.
If you measure only HTTP response time, you may not know where the time was spent.
A good benchmark measures both.
Define the Benchmark Goal
Start with a specific hypothesis.
For example:
Hypothesis:
PostgreSQL 18 AIO reduces execution time for
I/O-bound EF Core queries over large datasets.
Then define what you will measure:
Query latency
Throughput
Rows returned
Logical reads
Physical reads
Database execution time
EF Core materialization time
HTTP response time
CPU usage
Storage activity
This prevents the benchmark from becoming a collection of unrelated numbers.
Build a Realistic .NET Model
Consider an order-management application.
public sealed class Order
{
public long Id { get; set; }
public long CustomerId { get; set; }
public decimal TotalAmount { get; set; }
public string Status { get; set; } = null!;
public DateTime CreatedAt { get; set; }
public string Region { get; set; } = null!;
}
Configure it with EF Core:
public sealed class AppDbContext
: DbContext
{
public DbSet<Order> Orders => Set<Order>();
public AppDbContext(
DbContextOptions<AppDbContext> options)
: base(options)
{
}
}
The model should resemble the workload you actually care about.
Avoid creating a benchmark table with one integer column and calling it a production benchmark.
Generate a Large Dataset
A small dataset may fit entirely in memory.
That can hide storage behavior.
For example:
INSERT INTO orders
(
customer_id,
total_amount,
status,
created_at,
region
)
SELECT
(random() * 1000000)::bigint,
round((random() * 10000)::numeric, 2),
CASE
WHEN random() < 0.7 THEN 'Completed'
WHEN random() < 0.9 THEN 'Pending'
ELSE 'Cancelled'
END,
now() - (random() * interval '365 days'),
CASE
WHEN random() < 0.25 THEN 'North'
WHEN random() < 0.50 THEN 'South'
WHEN random() < 0.75 THEN 'East'
ELSE 'West'
END
FROM generate_series(1, 10000000);
The exact row count should depend on your hardware.
The objective is to create a dataset large enough to expose the behavior you are trying to measure.
Do Not Benchmark Only Warm Cache
A warm-cache query may behave very differently from an I/O-bound query.
Consider:
Cold / pressured cache
|
v
Storage reads
|
v
AIO can matter
Warm cache
|
v
Shared memory
|
v
Less storage I/O
If every benchmark query is served from memory, changing the I/O method may have little visible effect.
That does not mean AIO is ineffective.
It means the workload is not exercising the relevant part of the system.
Design Three Test Scenarios
A useful EF Core benchmark can include three categories.
Scenario 1: Point Lookup
var order = await db.Orders
.SingleOrDefaultAsync(
x => x.Id == orderId,
cancellationToken);
This primarily exercises an indexed lookup.
Scenario 2: Filtered Query
var orders = await db.Orders
.Where(x =>
x.Region == region &&
x.Status == "Completed")
.OrderByDescending(x => x.CreatedAt)
.Take(100)
.ToListAsync(cancellationToken);
This can involve index access, filtering, sorting, and row retrieval.
Scenario 3: Large Scan
var count = await db.Orders
.Where(x =>
x.CreatedAt >= startDate &&
x.CreatedAt < endDate)
.CountAsync(cancellationToken);
Depending on indexes, data distribution, and planner decisions, PostgreSQL may choose different access strategies.
This is where I/O behavior becomes especially interesting.
Sequential Scans Matter
A sequential scan reads a substantial portion of a table.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE total_amount > 5000;
If the predicate matches a large percentage of the table, PostgreSQL may choose a sequential scan.
This is one of the workloads explicitly identified as benefiting from PostgreSQL 18 AIO.
Bitmap Heap Scans Matter Too
A different query might produce a bitmap-based plan.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE region = 'North'
AND status = 'Completed';
Depending on the available indexes and data distribution, PostgreSQL may use a bitmap index scan followed by a bitmap heap scan.
Bitmap heap scans are also among the AIO-supported workloads identified in PostgreSQL 18.
Create Appropriate Indexes
A benchmark needs realistic indexes.
For example:
CREATE INDEX ix_orders_region_status
ON orders(region, status);
And:
CREATE INDEX ix_orders_created_at
ON orders(created_at);
But do not add every possible index.
Excessive indexing changes:
Storage requirements
Write cost
Query plans
Cache behavior
Maintenance workload
The benchmark should represent a plausible application schema.
Compare the Query Plan
Before measuring application performance, inspect the SQL plan.
Use:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE region = 'North'
AND status = 'Completed';
Record:
Execution Time
Planning Time
Shared Hit Blocks
Shared Read Blocks
Shared Dirtied Blocks
Shared Written Blocks
This helps determine whether the query is actually performing physical reads.
BUFFERS Is Essential
Consider two results:
Query A
Shared Hit Blocks: 500000
Shared Read Blocks: 10
and:
Query B
Shared Hit Blocks: 100000
Shared Read Blocks: 450000
The second query is much more dependent on reading data from storage.
That distinction matters when evaluating AIO.
If your benchmark contains almost no physical reads, it may not meaningfully exercise the feature.
Use pg_stat_io
PostgreSQL 18 adds additional I/O statistics.
The pg_stat_io view includes byte-oriented measurements such as:
read_bytes
write_bytes
extend_bytes
The release notes describe these additions as part of PostgreSQL 18's I/O observability improvements.
A useful query is:
SELECT
backend_type,
object,
context,
reads,
read_bytes,
writes,
write_bytes
FROM pg_stat_io;
The exact columns and categories available should be checked against the PostgreSQL version being benchmarked.
Inspect Active AIO Operations
PostgreSQL 18 also provides the pg_aios view.
It exposes currently active asynchronous I/O handles and their states. The documentation notes that it is primarily useful for developers but can also assist with tuning.
For example:
SELECT
pid,
io_id,
state
FROM pg_aios;
This is useful when investigating whether AIO is actually active during a workload.
Do not interpret an empty snapshot as proof that AIO is not working.
A query may finish before you inspect the view.
Measure From EF Core
The database plan is only one layer.
Measure the application query:
var stopwatch = Stopwatch.StartNew();
var orders = await db.Orders
.Where(x =>
x.Region == region &&
x.Status == "Completed")
.OrderByDescending(x => x.CreatedAt)
.Take(100)
.ToListAsync(cancellationToken);
stopwatch.Stop();
Console.WriteLine(
$"Elapsed: {stopwatch.ElapsedMilliseconds} ms");
This measures:
EF Core
+
Driver
+
Database
+
Network
+
Materialization
It does not isolate database execution time.
That is why both application-level and database-level measurements are necessary.
Avoid Measuring Only First-Request Latency
ASP.NET Core applications have startup costs.
The first request may include:
Dependency injection
DbContext creation
Connection establishment
Query compilation
JIT
Application initialization
If you compare only the first request, you may measure startup behavior rather than database I/O.
Run a warm-up phase:
Start application
|
v
Warm-up requests
|
v
Benchmark
|
v
Collect measurements
Disable Unrelated Application Work
A database benchmark should not accidentally become a benchmark of:
Logging
Telemetry
Authentication
External APIs
Background jobs
Caching
Keep the benchmark path controlled.
For example:
var orders = await db.Orders
.AsNoTracking()
.Where(x => x.Region == region)
.ToListAsync(cancellationToken);
AsNoTracking() can be appropriate for read-only benchmarks because change tracking itself adds application-side work.
But use it only when it represents the intended production query.
Tracking vs No-Tracking
Compare deliberately.
Tracking
var orders = await db.Orders
.Where(x => x.Status == "Pending")
.ToListAsync(cancellationToken);
No Tracking
var orders = await db.Orders
.AsNoTracking()
.Where(x => x.Status == "Pending")
.ToListAsync(cancellationToken);
If the production application uses tracking, benchmark tracking.
If it uses no-tracking queries for read-heavy operations, benchmark that configuration.
Do not change EF Core behavior just to make the database look faster.
Measure SQL Translation Separately
EF Core query execution has multiple components.
LINQ
|
v
Expression Processing
|
v
SQL Generation
|
v
Database Execution
|
v
Materialization
A database AIO feature primarily affects database-side I/O.
If the total application latency changes by only a small amount, it may be because database I/O is only one part of the request.
This is why end-to-end benchmarks need layer-specific measurements.
Use ToQueryString() During Benchmark Development
You can inspect generated SQL with:
var query = db.Orders
.Where(x =>
x.Region == region &&
x.Status == "Completed");
Console.WriteLine(
query.ToQueryString());
This is useful for confirming that the benchmark is executing the SQL you intended.
Do not use this output as the production query-execution mechanism.
Build a Benchmark Service
A simple application-level benchmark service can look like:
public sealed class OrderBenchmark
{
private readonly IDbContextFactory<AppDbContext>
_factory;
public OrderBenchmark(
IDbContextFactory<AppDbContext> factory)
{
_factory = factory;
}
public async Task<long> RunAsync(
CancellationToken cancellationToken)
{
await using var db =
await _factory.CreateDbContextAsync(
cancellationToken);
var stopwatch = Stopwatch.StartNew();
await db.Orders
.AsNoTracking()
.Where(x =>
x.Region == "North" &&
x.Status == "Completed")
.OrderByDescending(x => x.CreatedAt)
.Take(1000)
.ToListAsync(cancellationToken);
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}
}
Using a context factory makes repeated benchmark iterations easier to control.
Benchmark worker, io_uring, and sync
A useful PostgreSQL comparison is:
Configuration A
io_method = sync
Configuration B
io_method = worker
Configuration C
io_method = io_uring
Run the same workload against each.
The test environment must remain consistent:
Same database
Same dataset
Same indexes
Same PostgreSQL configuration
Same storage
Same EF Core application
Same query
Same concurrency
Change only the variable under investigation.
Be Careful With io_uring
The io_uring method requires appropriate build and operating-system support. PostgreSQL documents that it requires a build with the necessary liburing support.
Therefore, document:
Operating system
Kernel version
PostgreSQL build
io_uring availability
Storage device
Filesystem
Otherwise, another developer may not be able to reproduce the results.
worker Is a Useful Baseline
The worker-based implementation is particularly useful because it does not depend on io_uring.
The PostgreSQL configuration documentation describes worker as asynchronous I/O implemented using worker processes.
That makes it useful for environments where:
io_uring unavailable
or where a portable configuration is preferred.
Tune I/O Concurrency Carefully
PostgreSQL exposes:
effective_io_concurrency
maintenance_io_concurrency
io_max_concurrency
The current documentation describes effective_io_concurrency as controlling the number of concurrent storage I/O operations PostgreSQL expects can be executed simultaneously. Higher values can have more impact on higher-latency storage, while unnecessarily high values can increase latency.
Do not simply increase every I/O setting.
Benchmark each change.
Example Configuration Matrix
A test matrix could look like:
| Test | I/O Method | Concurrency |
|---|---|---|
| Baseline | sync | Default |
| AIO | worker | Default |
| AIO | io_uring | Default |
| AIO | worker | Higher |
| AIO | io_uring | Higher |
Record the results independently.
This helps distinguish:
AIO benefit
from:
I/O concurrency tuning benefit
Test With Different Dataset Sizes
AIO behavior may vary depending on whether the working set fits in memory.
Test at least:
Small
Medium
Large
For example:
Join the conversation! Your thoughts help the community grow.