How to Increase PostgreSQL 18 Speed: The Ultimate Async I/O Guide

Introduction

The database world is always trying to get faster. For a long time, one of PostgreSQL’s main performance limits especially on busy systems and modern NVMe or cloud storage has been the time spent waiting for disk reads. PostgreSQL already uses the operating system’s cache, but earlier versions could not efficiently handle multiple read operations at the same time while continuing other work, which often caused the database to wait on storage even when CPU resources were available.

With PostgreSQL 18, this improves through enhanced Asynchronous I/O support, which allows PostgreSQL to send multiple read requests in parallel and reduce waiting time on disk. In my previous blog, I covered PostgreSQL 18’s key features, including Async I/O. This is not just a small internal change, it has real performance impact.

I/O Bottleneck: Why Async I/O Matters

In many database workloads, performance is limited not by CPU, but by time spent waiting for disk operations. Traditionally, PostgreSQL handled most data reads in a way where a backend process would request data from storage and then wait before continuing further work. Even if CPU resources were available, progress slowed down because the process had to wait for the disk response.

This was less noticeable with older hardware, but with modern SSDs, NVMe, and cloud storage, systems can handle multiple I/O operations in parallel. The older behavior meant PostgreSQL could not fully use the available storage throughput, leading to underutilized CPU and longer query times.

Asynchronous I/O improves this situation. Instead of waiting for one disk request to finish, PostgreSQL can issue multiple read requests and continue processing other work. This reduces I/O wait time and allows better use of system resources.

As a result, Async I/O can provide:

• Higher throughput – more data read in less time
• Lower latency for large scans and I/O-heavy queries
• Better CPU utilization, as processes spend less time waiting

PostgreSQL 18: What Has Improved

PostgreSQL has always relied on the operating system for buffering and caching, but PostgreSQL 18 improves how the database engine overlaps computation with storage I/O. Backend processes can handle read operations more efficiently, reducing time spent waiting for data blocks and improving parallelism between CPU work and disk activity.

Async I/O delivers the most noticeable gains in the following areas:

• Sequential & Parallel Scans – Faster loading of table blocks into shared buffers, leading to better use of storage bandwidth
• Parallel Query Workers – More efficient read requests during parallel execution, improving large query performance
• Buffer Manager – Better overlap between disk reads and query processing, reducing stalls during large scans
• Maintenance Tasks – Operations like VACUUM and large scans complete with less I/O wait
• Backend Processes – Less time in I/O wait, resulting in improved CPU utilization

Not every disk operation becomes fully asynchronous, and PostgreSQL still depends on the operating system for actual I/O handling. The main improvement in PostgreSQL 18 is how the database engine schedules and overlaps read operations, which delivers the biggest benefits in I/O-heavy workloads.

Async I/O Parameters

Async I/O performance does not depend only on the PostgreSQL version, it also depends on how well the system is configured. The following core parameters help PostgreSQL make better use of parallelism and storage concurrency.

These represent the primary tuning knobs for benefiting from Async I/O. Further optimization depends on workload behavior and hardware capacity.

Execution Plan Comparison: Before and After

This example shows PostgreSQL’s behavior with limited parallelism/I/O vs. a tuned environment with async I/O overlap.

Query

postgres=# EXPLAIN (ANALYZE, BUFFERS)
SELECT c.customer_id,
       COUNT(o.order_id) AS total_orders,
       SUM(o.amount) AS total_amount,
       DATE_TRUNC('month', o.created_date) AS order_month
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.created_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND o.status = 'completed'
GROUP BY c.customer_id, order_month
ORDER BY total_amount DESC

Before: Limited Parallelism and Low I/O Concurrency

GroupAggregate  (actual time=145234.123..182442.567 rows=1250000 loops=1)
  Buffers: shared hit=32455 read=245670
  ->  Sort  (actual time=145234.098..181234.456 rows=4500000 loops=1)
        Sort Key: sum(o.amount) DESC
        Sort Method: external merge  Disk: 1256MB
        Buffers: shared hit=32455 read=245670
        ->  Hash Join  (actual time=12456.234..142345.789 rows=4500000 loops=1)
              Hash Cond: (o.customer_id = c.customer_id)
              Buffers: shared hit=32455 read=245670
              ->  Seq Scan on orders o  (actual time=0.045..112345.678 rows=12500000 loops=1)
                    Filter: ((status = 'completed') AND
                             (created_date >= '2024-01-01'::date) AND
                             (created_date <= '2024-12-31'::date))
                    Rows Removed by Filter: 25000000
                    Buffers: shared hit=28901 read=212340
              ->  Hash  (actual time=1234.567..1234.567 rows=100000 loops=1)
                    Buckets: 65536  Batches: 1  Memory Usage: 4523k
                    Buffers: shared hit=3554
                    ->  Seq Scan on customers c  (actual time=0.023..789.456 rows=100000 loops=1)
                          Buffers: shared hit=3554
Planning Time: 4.921 ms
Execution Time: 182.442 s

Issues Observed

• Sequential scan on large orders table
• High disk reads (read=245k blocks)
• External sort spilling 1.25GB to disk
• Single worker performing scan and join
• Execution time ≈ 182 seconds

Tuning Applied

SET max_parallel_workers_per_gather = 4;
SET effective_io_concurrency = 16;
SET work_mem = '256MB';

These settings help PostgreSQL run sorts in memory, use multiple workers for queries, and read data from disk more efficiently.

After: Parallel Query and Improved I/O Overlap

Gather  (actual time=8234.567..45234.123 rows=1250000 loops=1)
  Workers Planned: 4  Workers Launched: 4
  Buffers: shared hit=124567 read=82340
  ->  GroupAggregate  (actual time=4567.234..11234.456 rows=312500 loops=4)
        Buffers: shared hit=31142 read=20585
        ->  Sort  (actual time=4567.123..9234.789 rows=1125000 loops=4)
              Sort Key: sum(o.amount) DESC
              Sort Method: quicksort  Memory: 89MB
              Buffers: shared hit=31142 read=20585
              ->  Parallel Hash Join  (actual time=2345.678..7890.123 rows=1125000 loops=4)
                    Hash Cond: (o.customer_id = c.customer_id)
                    Buffers: shared hit=31142 read=20585
                    ->  Parallel Seq Scan on orders o  (actual time=0.023..4567.890 rows=3125000 loops=4)
                          Filter: ((status = 'completed') AND
                                   (created_date >= '2024-01-01'::date) AND
                                   (created_date <= '2024-12-31'::date))
                          Rows Removed by Filter: 6250000
                          Buffers: shared hit=27890 read=18234
                    ->  Hash  (actual time=234.567..234.567 rows=25000 loops=4)
                          Buckets: 16384  Batches: 1  Memory Usage: 1130kB
                          ->  Parallel Seq Scan on customers c  (actual time=0.012..189.234 rows=25000 loops=4)
                                Buffers: shared hit=3252
Planning Time: 3.456 ms
Execution Time: 45.234 s

Performance Comparison

PostgreSQL 18 async I/O + parallelism transforms complex analytical queries from 182s to 45s without SQL changes

Limitations of Async I/O

While PostgreSQL 18’s Async I/O enhancements provide clear benefits, they do not improve every workload equally.

Parallelism still matters
Async I/O benefits are greater when parallel query execution is enabled. Systems with restricted parallel worker settings may not fully realize the improvements.

Limited impact on write-heavy workloads
Async I/O mainly improves read operations. Write performance is still controlled by WAL, checkpoints, and durability requirements, so writes cannot be made fully asynchronous.

Smaller gains when data fits in memory
If most data is already cached in shared buffers or the OS page cache, disk reads are minimal and the improvement is less noticeable.

CPU-bound workloads
When performance is limited by CPU processing, faster disk access does not significantly change execution time.

Storage-dependent improvement
Systems using traditional spinning disks may not see the same benefits as SSD, NVMe, or cloud storage that supports higher I/O concurrency.

The Future Is Asynchronous

PostgreSQL 18 brings meaningful improvements in how the database handles disk reads by better overlapping I/O operations with query processing. While these changes work internally, their impact becomes clearly visible in I/O-heavy workloads, especially during large scans, joins, and analytical queries.

However, real performance gains come when these internal improvements are supported by proper configuration. Enabling parallel query execution, tuning memory settings, and ensuring the storage layer can handle concurrent I/O are all important factors. Simply upgrading the version is not enough. PostgreSQL performs best when the engine capabilities and system configuration work together.

Async I/O improvements are not a universal speed boost, but in the right workloads and environments, they can significantly reduce execution time and improve overall system responsiveness.