Benchmark PostgreSQL 18 Async I/O for RAG Workloads

To benchmark PostgreSQL 18 asynchronous I/O for retrieval-augmented generation, compare io_method configurations on the same PostgreSQL 18 patch, storage, data snapshot, plans, and arrival schedule. Measure vector recall and end-to-end retrieval latency alongside I/O. Do not assume AIO accelerates every pgvector query: PostgreSQL 18 initially applies it to operations including sequential scans, bitmap heap scans, and vacuum.

This article defines a protocol and reports no invented result.

Start with the documented scope

PostgreSQL 18 introduced an AIO subsystem that lets backends queue multiple reads. The official PostgreSQL 18 release notes name sequential scans, bitmap heap scans, vacuum, and other eligible operations. They also introduce the pg_aios system view and byte counters in pg_stat_io.

The current resource-configuration reference defines three io_method choices:

• worker, the default, using I/O worker processes;
• io_uring, available only when PostgreSQL was built with liburing support;
• sync, which executes AIO-eligible work synchronously.

io_method and related capacity settings require server restart. The comparison must record the exact PostgreSQL patch, build flags, kernel, filesystem, storage device, virtualization, and settings. “Postgres 18 with AIO” is not a complete configuration.

Define RAG workload families separately

A production RAG pipeline includes operations with different I/O shapes:

The official pgvector repository documents exact search, HNSW, IVFFlat, distance operators, filtering, and iterative scans. Pin its release and index parameters. A PostgreSQL major upgrade can change the planner, while a pgvector upgrade can change access behavior; change one at a time.

Freeze data and relevance truth

Use one immutable corpus snapshot with realistic chunk sizes, metadata cardinality, tenant distribution, embedding dimension, and deletion history. Save checksums for rows and index definitions. Generate a fixed query set with expected relevant document IDs from human labels or a reviewed deterministic source.

Measure retrieval quality for every configuration:

• recall at the candidate cutoff;
• filter correctness and tenant isolation;
• empty-result and timeout rates;
• ranking agreement with an exact-search reference where feasible.

An AIO setting that appears faster because a query plan returns fewer correct neighbors is not an improvement. Our HNSW recall and latency guide explains the quality side of that tradeoff.

Compare within PostgreSQL 18 first

The cleanest AIO contrast uses the same PostgreSQL 18 patch and switches only the method:

Condition A: io_method = sync
Condition B: io_method = worker
Condition C: io_method = io_uring, only on a supported build

Restart between conditions, verify with SHOW io_method, and save all non-default settings. Randomize condition order across repeated trials so thermal state, storage background work, and time trends do not consistently favor one method.

A PostgreSQL 17 versus 18 comparison answers a broader upgrade question, not an isolated AIO question. Planner, statistics, and other features differ. Run it as a separate experiment and disclose the confounding changes.

Test hot, warm, and storage-bound states

RAG queries can be served from PostgreSQL shared buffers, the operating-system page cache, or storage. Label each regime:

• Hot: repeated query set intentionally fits in cache.
• Warm mixed: production-like popularity and churn.
• Storage-bound: dataset and access pattern force meaningful physical reads.

Do not clear host caches on a shared or production machine. For cold/storage-bound trials, use isolated disposable hosts or volumes and a repeatable initialization sequence. Record cache warm-up and exclude it only under a preregistered rule.

Use a dataset large enough for the intended regime. AIO cannot improve a run that performs no physical I/O, and an oversized synthetic scan may not represent indexed production retrieval.

Preserve query plans and I/O evidence

For each query family, save EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON) on a safe representative sample. The PostgreSQL EXPLAIN reference says BUFFERS reports shared, local, and temporary block activity and I/O timing when tracking is enabled. ANALYZE executes the query, so never apply it casually to mutating statements.

Enable track_io_timing only after measuring its overhead on the platform; the runtime statistics documentation notes that timing appears in pg_stat_io, database statistics, EXPLAIN, vacuum output, and pg_stat_statements when enabled.

Snapshot pg_stat_io before and after each run. Use pg_aios as an instantaneous diagnostic of in-use AIO handles, not as the primary aggregate benchmark: PostgreSQL describes it as mainly useful to developers and sometimes tuning.

Also record CPU, memory, storage latency and throughput, queue depth, WAL, checkpoints, autovacuum, replication lag, connections, and pgvector index statistics. Client-side latency remains the user-visible measure.

Generate a controlled arrival schedule

Use both fixed concurrency and open-loop request-rate tests. Fixed concurrency shows saturation for a known number of clients; request rate reveals queue growth when demand continues during slowdown.

PostgreSQL’s pgbench supports custom scripts, fixed clients, request-rate throttling, latency limits, per-transaction logs, and detailed failures. It is suitable when the SQL can be represented faithfully. A purpose-built RAG harness may be better for vector fixtures and relevance checks, but it must preserve scheduled send time so generator delay is counted.

For every condition:

1. Validate schema, row counts, checksums, statistics, and plans.
2. Warm according to the declared regime.
3. Ramp to the target rate.
4. Hold long enough to observe checkpoints and stable queues.
5. Run a fixed query mix with deterministic IDs.
6. Log every latency, result IDs, failure, and scheduled time.
7. Run maintenance workloads separately and concurrently in declared trials.
8. Cool down and verify no backlog remains.

Keep the load generator off the database host when it competes for the same CPU or I/O, and record network latency.

Report distributions and causal slices

Publish p50, p95, and p99 latency, throughput, schedule lag, timeout/error rate, recall, buffer hits/reads, I/O bytes and time, CPU, storage utilization, WAL, and vacuum duration. Slice by:

• query family and filter selectivity;
• index method;
• hot/warm/storage-bound state;
• input top-k and candidate count;
• concurrent maintenance;
• AIO method and configuration.

Use repeated trials and uncertainty intervals over independent run units. Do not pool millions of correlated query samples and report an implausibly narrow interval. Include failed and skipped requests in the capacity conclusion.

The sustainable operating point is where latency, recall, errors, and recovery all meet the declared objective. Maximum instantaneous QPS is not production capacity.

Diagnose a null or negative result

If AIO changes little, check whether the workload was cache-resident, the plan used an access method outside the initial AIO scope, storage queue depth was already saturated, CPU or vector distance computation dominated, or the query generator became the bottleneck. If performance worsens, inspect worker contention, configuration, small-request overhead, plan drift, and background maintenance.

Do not tune io_workers, concurrency, or combine limits from generic advice. Sweep one parameter within safe bounds and retest the complete workload. A configuration optimized for exact scans may harm interactive indexed retrieval.

Roll out PostgreSQL 18 independently from AIO tuning

Rehearse upgrade, extension compatibility, backup/restore, replica behavior, and rollback first. Canary read traffic, compare plans and recall, then introduce AIO tuning as a separate change. Preserve the previous configuration and restart procedure. Monitor physical reads, tail latency, errors, replication, and maintenance after expansion.