PostgreSQL 18 Asynchronous I/O (io_uring) & NVMe Config

Supanote Core Team @supanote · 2 min read

plain (ini) 1 day ago · 44 lines · 4 views

Artifact

Verified Blueprint PostgreSQL 18

Production PostgreSQL 18 postgresql.conf tuning asynchronous I/O (io_method = io_uring), read-ahead concurrency, and NVMe cost planners.

✓ Enables Linux io_uring asynchronous reads (2-3x faster sequential scans, bitmap heap scans, and VACUUM)
✓ Tunes io_combine_limit and effective_io_concurrency specifically for modern cloud/bare-metal NVMe drives
✓ Includes live pg_aios and pg_stat_io diagnostic SQL queries to verify non-blocking kernel I/O queues

Quick Fetch

# PostgreSQL 18 Production NVMe & Asynchronous I/O Configuration
# Target: PostgreSQL 18+ on Linux 6.1+ (32 GB RAM / 8 vCPU / NVMe SSD)
# Place in conf.d/99-pg18-aio-nvme.conf and restart PostgreSQL (io_method requires restart)

# 1. PostgreSQL 18 Native Asynchronous I/O Subsystem
# 'io_uring' uses shared kernel ring buffers on Linux (lowest CPU + syscall overhead).
# Fallback to 'worker' (with io_workers = 4) on non-Linux or restricted container seccomp profiles.
io_method = 'io_uring'
io_combine_limit = '256kB'
effective_io_concurrency = 256
maintenance_io_concurrency = 128

# 2. Memory & Shared Buffers (25% RAM baseline for dedicated DB host)
shared_buffers = '8GB'
effective_cache_size = '24GB'
work_mem = '32MB'
hash_mem_multiplier = 2.0
maintenance_work_mem = '2GB'
huge_pages = 'try'

# 3. NVMe Cost Planner Calibration
# Lower random_page_cost so planner favors index/bitmap scans over sequential scans on NVMe
seq_page_cost = 1.0
random_page_cost = 1.1
cpu_tuple_cost = 0.01

# 4. Write-Ahead Log (WAL) & Smooth Checkpoints
wal_compression = 'zstd'
wal_buffers = '64MB'
min_wal_size = '2GB'
max_wal_size = '16GB'
checkpoint_completion_target = 0.9
checkpoint_timeout = '15min'

# 5. Observability (pg_stat_io + pg_aios)
track_io_timing = on
track_wal_io_timing = on

# --- Verification SQL (run in psql after restart) ---
# SHOW io_method;
# SELECT pid, io_generation, state, operation, target_desc, length FROM pg_aios;
# SELECT backend_type, object, context, reads, read_time, writes, write_time
# FROM pg_stat_io WHERE reads > 0 OR writes > 0 ORDER BY read_time DESC;

1# PostgreSQL 18 Production NVMe & Asynchronous I/O Configuration

2# Target: PostgreSQL 18+ on Linux 6.1+ (32 GB RAM / 8 vCPU / NVMe SSD)

3# Place in conf.d/99-pg18-aio-nvme.conf and restart PostgreSQL (io_method requires restart)

5# 1. PostgreSQL 18 Native Asynchronous I/O Subsystem

6# 'io_uring' uses shared kernel ring buffers on Linux (lowest CPU + syscall overhead).

7# Fallback to 'worker' (with io_workers = 4) on non-Linux or restricted container seccomp profiles.

8io_method = 'io_uring'

9io_combine_limit = '256kB'

10effective_io_concurrency = 256

11maintenance_io_concurrency = 128

13# 2. Memory & Shared Buffers (25% RAM baseline for dedicated DB host)

14shared_buffers = '8GB'

15effective_cache_size = '24GB'

16work_mem = '32MB'

17hash_mem_multiplier = 2.0

18maintenance_work_mem = '2GB'

19huge_pages = 'try'

21# 3. NVMe Cost Planner Calibration

22# Lower random_page_cost so planner favors index/bitmap scans over sequential scans on NVMe

23seq_page_cost = 1.0

24random_page_cost = 1.1

25cpu_tuple_cost = 0.01

27# 4. Write-Ahead Log (WAL) & Smooth Checkpoints

28wal_compression = 'zstd'

29wal_buffers = '64MB'

30min_wal_size = '2GB'

31max_wal_size = '16GB'

32checkpoint_completion_target = 0.9

33checkpoint_timeout = '15min'

35# 5. Observability (pg_stat_io + pg_aios)

36track_io_timing = on

37track_wal_io_timing = on

39# --- Verification SQL (run in psql after restart) ---

40# SHOW io_method;

41# SELECT pid, io_generation, state, operation, target_desc, length FROM pg_aios;

42# SELECT backend_type, object, context, reads, read_time, writes, write_time

43# FROM pg_stat_io WHERE reads > 0 OR writes > 0 ORDER BY read_time DESC;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

PostgreSQL 18 Asynchronous I/O (io_uring) & NVMe Config

Replies 0

Related PostgreSQL 18 & Database, Cache & SQL Blueprints

PostgreSQL 18 Native uuidv7() Time-Ordered Primary Keys

Live PostgreSQL Lock Tree (pg_blocking_pids) & Safe Killer

Production PgBouncer Transaction Connection Pooling Config

PostgreSQL Slow Query Logging & auto_explain Configuration