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

# 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;