Postgres 17 Direct I/O and Streaming Prefetch Memory Budget Guardrail

--- description: Postgres 17 Direct I/O and Streaming Prefetch Memory Budget Guardrail - Enforces strict memory budgeting and I/O concurrency sizing for PostgreSQL 17 deployments utilizing Direct I/O (`io_direct`) and the modern streaming read prefetch engine on high-IOPS NVMe hardware. Prevents Linux OOM-killer invocations, unbuffered queue depth exhaustion, and tail-latency degradation caused by legacy page cache assumptions. globs: infra/**/*.ts, src/config/**/*.ts, src/database/**/*.ts, scripts/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-direct-io-streaming-prefetch-guardrail" --- # Postgres 17 Direct I/O and Streaming Prefetch Memory Budget Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Legacy OS Buffer Assumptions with Unbounded Asynchronous Prefetching on Direct I/O ## System Constraints You are a Principal Database Infrastructure Architect enforcing Postgres 17 Direct I/O and Streaming Prefetch calibration invariants. When configuring or generating Postgres 17 instances with `io_direct` enabled on high-IOPS NVMe drives, you must ensure that `shared_buffers` accounts for the bypassed OS page cache without causing system OOM, and that `effective_io_concurrency` and streaming prefetch windows are tightly bounded against NVMe hardware controller queue depths. ## Strict Guidelines - Engineers MUST allocate 60% to 75% of total system RAM to `shared_buffers` when `io_direct = 'data'` or `io_direct = 'data,wal'` is enabled, explicitly rejecting the legacy 25% guideline which relies on the OS page cache. - Engineers MUST calculate `effective_io_concurrency` by dividing the physical NVMe queue depth (typically 256 to 1024) across active parallel workers, NEVER setting arbitrary unbound values (e.g., > 1024) that trigger hardware queue starvation. - Engineers MUST calibrate `work_mem` and `hash_mem_multiplier` in relation to `max_connections` and streaming prefetch buffer allocations, ensuring total aggregate memory consumption (Shared Buffers + Max Connections * (work_mem * prefetch_overhead)) stays strictly below 90% of host RAM. - Configuration scripts MUST validate kernel hugepage support (`vm.nr_hugepages`) and pin `huge_pages = on` or `try` when expanding `shared_buffers` under Direct I/O to prevent TLB miss amplification. - Code generating database parameters MUST throw configuration compile-time errors if `io_direct` is set without proportional adjustments to `maintenance_io_concurrency` and `shared_buffers`. ## Bad vs Good Implementations ### PostgreSQL 17 Dynamic Parameter Generator for NVMe Storage #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Allocating only 25% of RAM to `shared_buffers` when Direct I/O bypasses the Linux page cache results in severe cache thrashing, as unbuffered reads must be served from storage. Concurrently, setting `effective_io_concurrency` to 2000 oversaturates hardware NVMe submission queues, and multiplying 300 connections by 128MB work_mem guarantees an OOM crash under streaming read loads. ```typescript import { PostgresConfig } from './types'; export function generatePgConfig(totalRamMb: number): PostgresConfig { // ANTI-PATTERN: Relying on legacy 25% shared_buffers with Postgres 17 io_direct bypasses OS cache // without giving memory to the engine, while setting arbitrary high concurrency that starves NVMe queues. return { sharedBuffers: `${Math.floor(totalRamMb * 0.25)}MB`, ioDirect: 'data,wal', effectiveIoConcurrency: 2000, // Exceeds physical controller queue depths maintenanceIoConcurrency: 500, workMem: '128MB', maxConnections: 300, hugePages: 'off' }; } ``` #### Production Standard (Correct) > **Standard**: Correctly budgets 65% of RAM to `shared_buffers` to compensate for the elimination of the kernel page cache under Direct I/O. Partitions the physical NVMe queue depth proportionally across active parallel streaming readers to prevent queue overflow, caps `work_mem` strictly within non-shared RAM boundaries, and mandates `hugePages: 'on'` to mitigate TLB cache pressure. ```typescript import { PostgresConfig } from './types'; interface SystemProfile { totalRamMb: number; nvmeQueueDepth: number; // typically 256 - 1024 for modern PCIe 4.0/5.0 NVMe maxConnections: number; expectedActiveParallelWorkers: number; } export function generatePg17DirectIoConfig(profile: SystemProfile): PostgresConfig { const { totalRamMb, nvmeQueueDepth, maxConnections, expectedActiveParallelWorkers } = profile; // When Direct I/O bypasses OS page cache, shared_buffers must retain the working set (65% of RAM) const sharedBuffersMb = Math.floor(totalRamMb * 0.65); // Distribute device queue depth evenly among concurrent streaming read consumers to prevent controller starvation const safeConcurrency = Math.max(1, Math.floor(nvmeQueueDepth / Math.max(1, expectedActiveParallelWorkers))); // Ensure dynamic per-connection working memory cannot trigger the kernel OOM killer (budget for 20% headroom) const remainingRamMb = totalRamMb - sharedBuffersMb; const safePerConnectionMemMb = Math.floor((remainingRamMb * 0.70) / maxConnections); const workMemMb = Math.max(4, Math.min(safePerConnectionMemMb, 64)); return { sharedBuffers: `${sharedBuffersMb}MB`, ioDirect: 'data,wal', effectiveIoConcurrency: safeConcurrency, maintenanceIoConcurrency: Math.min(safeConcurrency * 2, nvmeQueueDepth), workMem: `${workMemMb}MB`, maxConnections, hugePages: 'on' }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Direct I/O and Streaming Prefetch Memory Budget Guardrail

Postgres 17 Direct I/O and Streaming Prefetch Memory Budget Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory budgeting and I/O concurrency sizing for PostgreSQL 17 deployments utilizing Direct I/O (`io_direct`) and the modern streaming read prefetch engine on high-IOPS NVMe hardware. Prevents Linux OOM-killer invocations, unbuffered queue depth exhaustion, and tail-latency degradation caused by legacy page cache assumptions. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Legacy OS Buffer Assumptions with Unbounded Asynchronous Prefetching on Direct I/O Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Legacy OS Buffer Assumptions with Unbounded Asynchronous Prefetching on Direct I/O	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST allocate 60% to 75% of total system RAM to `shared_buffers` when `io_direct = 'data'` or `io_direct = 'data,wal'` is enabled, explicitly rejecting the legacy 25% guideline which relies on the OS page cache.

Engineers MUST allocate 60% to 75% of total system RAM to `shared_buffers` when `io_direct = 'data'` or `io_direct = 'data,wal'` is enabled, explicitly rejecting the legacy 25% guideline which relies on the OS page cache.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.