PostgreSQL 17 Direct I/O and Block Prefetch Alignment Standard

--- description: PostgreSQL 17 Direct I/O and Block Prefetch Alignment Standard - Enforces PostgreSQL 17 Direct I/O (io_direct) configuration and NVMe block-prefetch alignment to eradicate double-caching overhead between kernel page cache and Postgres shared buffers. Requires shared_buffers reallocation up to 60-75% of total system memory, block-size sector alignment, and aggressive asynchronous prefetch tuning to ensure deterministic sub-millisecond p99 latencies on enterprise NVMe storage. globs: infra/**/*.ts, src/config/database*.ts, infra/pulumi/**/*.ts, infra/cdk/**/*.ts, **/postgresql*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-direct-io-block-prefetch-alignment-standard" --- # PostgreSQL 17 Direct I/O and Block Prefetch Alignment Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Relying on Default OS Page Caching and Conventional 25% shared_buffers Allocation on High-IOPS NVMe Arrays ## System Constraints You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 Direct I/O and Block Prefetch Alignment standards. When systems operate on enterprise NVMe storage, you must guarantee kernel page cache bypass via `io_direct`, adjust shared buffers to account for the absence of double caching, and match asynchronous I/O prefetch limits to hardware controller queue depths. ## Strict Guidelines - Engineers MUST ALWAYS enable `io_direct = 'data,wal'` in PostgreSQL 17 configuration when provisioned on dedicated high-IOPS NVMe block devices. - Engineers MUST scale `shared_buffers` to 60%-75% of total dedicated system RAM when `io_direct` includes `data`, replacing the legacy 25% OS-cache-dependent sizing heuristic. - Engineers MUST set `effective_io_concurrency` and `maintenance_io_concurrency` between 256 and 1024, explicitly matching the queue depth capabilities of the underlying NVMe controller. - Engineers MUST NOT rely on default Linux kernel page cache buffering (`io_direct = ''`) for bulk OLTP/HTAP workloads where OS cache eviction introduces tail-latency spikes. - Storage provisioning scripts MUST verify filesystem allocation unit size matches PostgreSQL's 8KB block boundary (`BLCKSZ`) and is an exact multiple of the underlying NVMe 4KB (4Kn) physical sector size. - WAL volume mounts MUST be isolated on dedicated NVMe namespaces or formatted with 4KB sector alignment using direct I/O with `wal_sync_method = fdatasync` or `io_direct = 'wal,data'`. ## Bad vs Good Implementations ### PostgreSQL 17 NVMe Storage & Direct I/O Infrastructure Validator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Allocating only 25% of RAM to shared_buffers while leaving io_direct unset forces PostgreSQL to use buffered I/O. On high-IOPS NVMe, data is duplicated in both shared buffers and the OS page cache, causing high memory churn, frequent dirty page flushing stalls, and anemic queue-depth prefetching. ```typescript import { Resource } from 'aws-cdk-lib'; // ANTI-PATTERN: Outdated PostgreSQL 16 heuristics on PG17 with NVMe export function generatePostgresConfig(totalMemoryMb: number): Record<string, string | number> { return { // Legacy rule-of-thumb relying on OS page cache for remaining 75% shared_buffers: `${Math.floor(totalMemoryMb * 0.25)}MB`, // Missing io_direct setting entirely, causing page cache double-buffering effective_io_concurrency: 1, maintenance_io_concurrency: 10, wal_level: 'replica', checkpoint_completion_target: 0.9 }; } ``` #### Production Standard (Correct) > **Standard**: Explicitly targets PostgreSQL 17 features: sets `io_direct = 'data,wal'` to eliminate kernel page cache overhead, allocates 70% of RAM directly to `shared_buffers` to compensate for zero OS cache, and scales prefetch concurrency up to NVMe hardware queue depths. ```typescript import { z } from 'zod'; const Postgres17DirectIoConfigSchema = z.object({ shared_buffers: z.string(), io_direct: z.literal('data,wal'), effective_io_concurrency: z.number().min(256).max(1024), maintenance_io_concurrency: z.number().min(256).max(1024), wal_init_zero: z.literal('off'), wal_recycle: z.literal('on'), checkpoint_completion_target: z.number().min(0.9), max_worker_processes: z.number().min(8) }); export type Postgres17DirectIoConfig = z.infer<typeof Postgres17DirectIoConfigSchema>; export function configurePostgres17Nvme( totalMemoryMb: number, nvmeQueueDepth: number = 512, isDirectIoSupportedStorage: boolean = true ): Postgres17DirectIoConfig { if (!isDirectIoSupportedStorage) { throw new Error('PostgreSQL 17 Direct I/O requires 4Kn/aligned NVMe block storage.'); } // When OS cache is bypassed via io_direct, shared_buffers must retain hot data const directIoSharedBuffersMb = Math.floor(totalMemoryMb * 0.70); const safeConcurrency = Math.min(Math.max(nvmeQueueDepth, 256), 1024); return Postgres17DirectIoConfigSchema.parse({ shared_buffers: `${directIoSharedBuffersMb}MB`, io_direct: 'data,wal', effective_io_concurrency: safeConcurrency, maintenance_io_concurrency: safeConcurrency, wal_init_zero: 'off', // Direct I/O handles block writes without pre-zeroing overhead wal_recycle: 'on', checkpoint_completion_target: 0.93, max_worker_processes: 16 }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Direct I/O and Block Prefetch Alignment Standard

PostgreSQL 17 Direct I/O and Block Prefetch Alignment Standard defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 Direct I/O (io_direct) configuration and NVMe block-prefetch alignment to eradicate double-caching overhead between kernel page cache and Postgres shared buffers. Requires shared_buffers reallocation up to 60-75% of total system memory, block-size sector alignment, and aggressive asynchronous prefetch tuning to ensure deterministic sub-millisecond p99 latencies on enterprise NVMe storage. 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: Relying on Default OS Page Caching and Conventional 25% shared_buffers Allocation on High-IOPS NVMe Arrays 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	Relying on Default OS Page Caching and Conventional 25% shared_buffers Allocation on High-IOPS NVMe Arrays	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 ALWAYS enable `io_direct = 'data,wal'` in PostgreSQL 17 configuration when provisioned on dedicated high-IOPS NVMe block devices.

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.