Database performance problems in .NET applications are often blamed on the application layer.
A slow API may appear to be caused by:
Entity Framework Core
LINQ queries
Connection pooling
JSON serialization
Thread-pool contention
But sometimes the real bottleneck is much lower in the stack: database I/O.
PostgreSQL 18 introduces a new asynchronous I/O (AIO) subsystem that allows PostgreSQL to issue multiple I/O operations concurrently instead of waiting for each operation sequentially. PostgreSQL documents AIO support for workloads including sequential scans, bitmap heap scans, and vacuum.
For .NET developers, the interesting question is not simply:
Is PostgreSQL 18 faster?
The more useful question is:
Which .NET database workloads actually benefit from PostgreSQL 18 AIO, and how should we measure the difference?
This article builds a practical benchmark methodology using PostgreSQL, C#, Npgsql, and BenchmarkDotNet.
What PostgreSQL 18 AIO Changes
Traditional synchronous I/O can be thought of as:
Request I/O
↓
Wait
↓
Receive data
↓
Process
↓
Request next I/O
AIO allows the database to initiate multiple I/O operations without waiting for every individual operation to finish before continuing.
Conceptually:
Request I/O 1 ────────┐
Request I/O 2 ────────┤
Request I/O 3 ────────┤
Request I/O 4 ────────┘
↓
Process available results
PostgreSQL describes AIO as separating the initiation of an I/O operation from waiting for its result, allowing multiple operations to be initiated concurrently.
This matters most when the workload is actually I/O-bound.
A query that spends most of its time on CPU processing may see little benefit from AIO.
PostgreSQL 18 AIO Execution Methods
PostgreSQL 18 exposes the io_method configuration parameter.
The documented options are:
worker
io_uring
sync
worker uses PostgreSQL worker processes, io_uring uses Linux io_uring when PostgreSQL was built with the required support, and sync executes AIO-eligible operations synchronously. The documented default is worker.
You can inspect the current configuration with:
SHOW io_method;
You can also inspect related I/O settings:
SHOW io_method;
SHOW io_workers;
The exact available settings depend on the PostgreSQL version and build.
Why .NET Developers Should Care
The .NET application does not directly call PostgreSQL AIO.
The architecture looks like this:
.NET Application
↓
Npgsql
↓
PostgreSQL Protocol
↓
PostgreSQL Executor
↓
PostgreSQL AIO
↓
Operating System
↓
Storage
This distinction is important.
Changing:
await connection.ExecuteAsync(...);
does not automatically enable PostgreSQL AIO.
AIO is a PostgreSQL server-side execution capability.
Npgsql remains responsible for communication between the .NET application and PostgreSQL.
AIO Is Not the Same as .NET Async
This distinction causes confusion.
In .NET:
await command.ExecuteReaderAsync();
means the application can asynchronously wait for the database operation.
PostgreSQL AIO means PostgreSQL can manage eligible storage I/O asynchronously.
These are different layers:
Application Layer
↓
.NET async/await
↓
Npgsql
↓
Database Layer
↓
PostgreSQL AIO
↓
Storage
You can use both at the same time.
They solve different problems.
Build a Reproducible Benchmark
A useful benchmark should compare the same workload under controlled PostgreSQL configurations.
For example:
Configuration A
io_method = sync
Configuration B
io_method = worker
Configuration C
io_method = io_uring
Do not change other major variables between runs.
Record:
PostgreSQL version
.NET version
Npgsql version
CPU
RAM
Storage type
Operating system
Dataset size
Row count
Indexes
PostgreSQL configuration
io_method
io_workers
Cache state
Concurrency
Without these details, benchmark numbers are difficult to reproduce.
Create a Representative Table
For example:
CREATE TABLE orders
(
id BIGINT GENERATED ALWAYS AS IDENTITY,
customer_id BIGINT NOT NULL,
created_at TIMESTAMPTZ NOT NULL,
status TEXT NOT NULL,
amount NUMERIC(12, 2) NOT NULL,
description TEXT
);
For an I/O-oriented benchmark, the table should be large enough that the workload can exercise storage rather than fitting entirely into memory.
The appropriate size depends on the machine being tested.
Do not choose a fixed row count and assume it represents production.
Generate Test Data
A simple PostgreSQL-side generator can create repeatable data:
INSERT INTO orders
(
customer_id,
created_at,
status,
amount,
description
)
SELECT
(random() * 100000)::bigint,
NOW() - (random() * INTERVAL '365 days'),
CASE
WHEN random() < 0.25 THEN 'Pending'
WHEN random() < 0.50 THEN 'Processing'
WHEN random() < 0.75 THEN 'Shipped'
ELSE 'Completed'
END,
round((random() * 1000)::numeric, 2),
repeat('benchmark-data ', 10)
FROM generate_series(1, 10000000);
The exact dataset size should be selected based on the hardware and benchmark objective.
After loading data, update statistics:
ANALYZE orders;
PostgreSQL uses statistics during query planning, so benchmark runs should not accidentally compare a freshly loaded and poorly analyzed table against a properly analyzed table.
Start With a Sequential Scan
AIO is particularly relevant to sequential scans.
Start with:
SELECT COUNT(*)
FROM orders;
Then inspect the execution plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM orders;
Look for:
Seq Scan
Buffers
Execution Time
Planning Time
The goal is not simply to obtain one execution-time number.
You want to understand what the database actually did.
Compare Query Plans
A benchmark should establish that the query plan is comparable between configurations.
For example:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';
Record:
Plan type
Rows scanned
Rows returned
Buffers
Execution time
If one configuration uses a different plan, the comparison is no longer simply an AIO comparison.
The planner may have made a different decision.
Understand Cache Effects
This is one of the biggest problems in database benchmarking.
Run 1 may read data from storage:
Application
↓
PostgreSQL
↓
Storage
Run 2 may find much of the same data in memory:
Application
↓
PostgreSQL
↓
Memory
The second run may therefore be much faster without any AIO change.
PostgreSQL's pg_stat_io and related statistics can help investigate I/O behavior, although PostgreSQL notes that its I/O statistics do not distinguish every case of data coming from disk versus the operating system page cache.
Therefore, benchmark both:
Cold-ish cache scenario
Warm-cache scenario
and clearly document how each was produced.
Do not call an ordinary repeated execution a "cold-cache benchmark."
Inspect PostgreSQL I/O Statistics
PostgreSQL 18 provides pg_stat_io.
A starting query is:
SELECT
backend_type,
object,
context,
reads,
read_time,
writes,
write_time
FROM pg_stat_io;
The available columns should be checked against the exact PostgreSQL version being tested.
The purpose is to correlate:
Query performance
+
Database I/O activity
rather than relying only on application latency.
Benchmark From .NET
For a .NET application, Npgsql is the PostgreSQL ADO.NET provider.
A basic query method can look like:
using Npgsql;
public static async Task<long> CountOrdersAsync(
string connectionString)
{
await using var connection =
new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command =
new NpgsqlCommand(
"""
SELECT COUNT(*)
FROM orders;
""",
connection);
var result =
await command.ExecuteScalarAsync();
return Convert.ToInt64(result);
}
This benchmark measures the end-to-end database call from the .NET application's perspective.
That is useful because it includes:
Npgsql
+
Network
+
PostgreSQL execution
+
Result transfer
It does not isolate PostgreSQL storage time by itself.
That is why application-level measurements should be combined with PostgreSQL execution plans and I/O statistics.
Add BenchmarkDotNet
BenchmarkDotNet provides a structured way to execute repeated .NET benchmarks.
A basic benchmark can look like:
using BenchmarkDotNet.Attributes;
using Npgsql;
[MemoryDiagnoser]
public class PostgreSqlBenchmark
{
private NpgsqlDataSource _dataSource = default!;
[GlobalSetup]
public void Setup()
{
var connectionString =
Environment.GetEnvironmentVariable(
"POSTGRES_CONNECTION")
?? throw new InvalidOperationException(
"POSTGRES_CONNECTION is missing.");
_dataSource =
NpgsqlDataSource.Create(
connectionString);
}
[Benchmark]
public async Task<long> CountOrders()
{
await using var command =
_dataSource.CreateCommand(
"""
SELECT COUNT(*)
FROM orders;
""");
var result =
await command.ExecuteScalarAsync();
return Convert.ToInt64(result);
}
[GlobalCleanup]
public async Task Cleanup()
{
await _dataSource.DisposeAsync();
}
}
Then run:
dotnet run -c Release
BenchmarkDotNet can provide statistical summaries rather than relying on a single execution.
Avoid Measuring Connection Creation
Do not accidentally benchmark:
Create connection
Open connection
Execute query
Close connection
when your production application normally uses pooled connections.
For example:
await using var command =
dataSource.CreateCommand(sql);
await command.ExecuteScalarAsync();
using a shared NpgsqlDataSource better represents a typical pooled application architecture.
The benchmark should reflect the behavior you actually want to compare.
Benchmark Multiple Workload Types
Do not use one query to conclude that AIO improves every workload.
Test different categories.
Sequential Scan
SELECT COUNT(*)
FROM orders;
This is a natural starting point because PostgreSQL explicitly lists sequential scans among AIO-supported operations.
Large Result Scan
SELECT
id,
customer_id,
created_at,
status,
amount
FROM orders;
This tests a different result-transfer profile.
Filtered Scan
SELECT COUNT(*)
FROM orders
WHERE status = 'Completed';
The planner may choose different strategies depending on indexes and statistics.
Bitmap Heap Scan
Create an index:
CREATE INDEX ix_orders_customer_id
ON orders(customer_id);
Then test:
SELECT *
FROM orders
WHERE customer_id BETWEEN 1000 AND 5000;
Inspect the plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE customer_id BETWEEN 1000 AND 5000;
PostgreSQL 18 specifically lists bitmap heap scans among AIO-supported workloads.
Do Not Disable Indexes Without a Reason
You may be tempted to force a sequential scan:
SET enable_indexscan = off;
SET enable_bitmapscan = off;
This can be useful for controlled experiments.
But do not use forced plans to represent normal production behavior unless that is actually the production plan.
A benchmark should distinguish between:
Natural production plan
and:
Artificially forced plan
Benchmark Different Concurrency Levels
Single-user performance is not enough for a web API.
Test:
1 concurrent request
8 concurrent requests
32 concurrent requests
64 concurrent requests
128 concurrent requests
The exact values should match the hardware and expected workload.
The objective is to observe how throughput and latency behave as concurrency increases.
For example:
Concurrency
↓
Database workload
↓
I/O pressure
↓
Latency
↓
Throughput
A configuration that performs well at concurrency 1 may behave differently under load.
Measure More Than Average Latency
Average latency can hide important behavior.
Record:
Mean
Median
P95
P99
Throughput
Errors
CPU utilization
I/O utilization
Memory
For an API workload, P95 and P99 are often more useful than the average alone.
However, do not claim that one metric is universally the correct production metric.
The appropriate SLO depends on the application.
Compare End-to-End and Database Timing
Suppose the .NET application records:
Total request:
250 ms
while PostgreSQL reports:
Execution:
220 ms
The remaining time may include:
Connection acquisition
Network
Serialization
Application processing
This is why both layers matter.
A useful diagnostic model is:
API latency
|
+-- Connection pool
+-- Network
+-- PostgreSQL execution
+-- Result transfer
+-- Application processing
Do not attribute the entire API latency difference to PostgreSQL AIO.
Measure Connection Pool Behavior
Npgsql connection pooling can influence application-level results.
If the benchmark creates a new physical database connection for every operation, you may measure connection overhead rather than database execution.
Use a long-lived NpgsqlDataSource:
var dataSource =
NpgsqlDataSource.Create(
connectionString);
Then create commands from the data source.
This allows the provider's connection pooling to operate normally.
Test EF Core Separately
If the application uses Entity Framework Core, create a separate benchmark layer.
For example:
public async Task<int> CountOrdersAsync()
{
return await dbContext.Orders.CountAsync();
}
Then compare:
EF Core
↓
Npgsql
↓
PostgreSQL
against:
Npgsql
↓
PostgreSQL
This helps determine whether a result is caused by:
ORM behavior
or:
Database execution
Do not assume that an EF Core benchmark is automatically a PostgreSQL benchmark.
Keep LINQ Translation Constant
If comparing PostgreSQL configurations, do not simultaneously change:
LINQ expression
EF Core version
Npgsql version
Tracking behavior
Query projection
Otherwise, multiple variables change at once.
A clean experiment changes one major variable:
PostgreSQL I/O configuration
while keeping the application stack constant.
Example EF Core Query
A read-only query might look like:
var count =
await dbContext.Orders
.AsNoTracking()
.Where(x => x.Status == "Completed")
.CountAsync();
If this is part of the benchmark, capture the generated SQL and verify that it remains equivalent across runs.
Benchmark Vacuum Separately
PostgreSQL 18 AIO also applies to vacuum operations.
You can measure:
VACUUM (ANALYZE) orders;
But vacuum benchmarks should be treated differently from request/response query benchmarks.
Measure:
Duration
I/O activity
CPU
Impact on concurrent queries
Do not mix vacuum execution time into a normal API query latency benchmark.
Compare sync and worker
For a controlled experiment, configure:
ALTER SYSTEM SET io_method = 'sync';
Join the conversation! Your thoughts help the community grow.