PostgreSQL 17 Streaming Direct I/O Buffer Pool Invariant

--- description: PostgreSQL 17 Streaming Direct I/O Buffer Pool Invariant - Enforces PostgreSQL 17 streaming I/O configurations and ring-buffer allocation invariants across high-throughput backend services. Prevents kernel page-cache duplication, dirty write stalls, and NVMe bus contention during concurrent high-volume sequential scans and OLTP transactions. globs: src/infrastructure/database/**/*.ts, src/repositories/**/*.ts, src/config/database.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-streaming-direct-io-buffer-guardrail" --- # PostgreSQL 17 Streaming Direct I/O Buffer Pool Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Sequential Scans and Default Direct I/O Mismatches Inducing OS Cache Double-Buffering ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Streaming I/O and Shared Buffer Pool Invariants. You must ensure all sequential scan queries, bulk ingestion pipelines, and connection pool configurations leverage PostgreSQL 17 streaming read abstractions and ring-buffer allocation guards without double-buffering into the OS page cache or evicting OLTP hot sets. ## Strict Guidelines - Engineers MUST ALWAYS isolate heavy sequential read operations to dedicated bulk-read pool partitions that declare bounded ring-buffer parameters (e.g., SET LOCAL io_combine_limit and cursor-based windowing) to prevent eviction of active OLTP buffer pages. - Engineers MUST NOT execute ad-hoc, unbounded table scans in primary transactional sessions where shared_buffers are allocated for low-latency point queries and hot row updates. - Code SHOULD verify and telemetry-sample pg_stat_io eviction vectors (specifically reads, writes, and extends per context) to assert zero memory thrashing across shared vs. direct read contexts. - Services MUST configure PostgreSQL 17 streaming I/O connection parameters with deterministic batch sizes aligned with underlying NVMe physical block chunk limits (e.g., matching or sub-dividing kernel direct I/O boundaries). - Components NEVER execute raw bulk export or backfill operations without explicitly invoking session-scoped temporary buffer boundaries or read-ahead limits. ## Bad vs Good Implementations ### Configuring PostgreSQL 17 Bulk Streaming Context with Session-Level I/O Bounding #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing full sequential reads through standard pool connections without setting PostgreSQL 17 streaming parameters or cursor buffers evicts hot transactional cache blocks in shared_buffers, induces kernel double-buffering, and stalls active OLTP write transactions. ```typescript import { PoolClient } from 'pg'; // ANTI-PATTERN: Executing an unthrottled, unbounded bulk sequential scan directly // on the default transactional client pool. This causes PostgreSQL shared_buffers pollution, // kernel page-cache duplication, and evicts hot OLTP working sets under concurrent write load. export async function exportTransactionAuditLogs(client: PoolClient, tenantId: string): Promise<any[]> { const query = ` SELECT id, payload, created_at FROM audit_events WHERE tenant_id = $1 ORDER BY created_at ASC; `; const result = await client.query(query, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Enforces an isolated read-only transaction utilizing a bounded cursor, tuneable PG17 streaming parameters (io_combine_limit), and bulk chunk processing. This forces PostgreSQL to leverage localized buffer rings without thrashing global shared_buffers or corrupting the OS page cache. ```typescript import { PoolClient } from 'pg'; import Cursor from 'pg-cursor'; // PRODUCTION FIX: Configure PostgreSQL 17 streaming read parameters inside an isolated // transaction boundary, constraining ring-buffer allocation via cursor batching to saturate NVMe Direct I/O // without polluting shared_buffers or inducing OS page-cache thrashing. export async function exportTransactionAuditLogs( client: PoolClient, tenantId: string, onBatch: (rows: any[]) => Promise<void> ): Promise<void> { const BATCH_SIZE = 4096; // Aligned with PG17 streaming vector read allocations await client.query('BEGIN READ ONLY;'); try { // Tune session-level streaming I/O vector combining for PG17 NVMe saturation await client.query('SET LOCAL io_combine_limit = "128kB";'); await client.query('SET LOCAL synchronous_commit = OFF;'); const queryText = ` DECLARE audit_stream NO SCROLL CURSOR FOR SELECT id, payload, created_at FROM audit_events WHERE tenant_id = $1 ORDER BY created_at ASC; `; await client.query(queryText, [tenantId]); let hasMore = true; while (hasMore) { const fetchRes = await client.query(`FETCH FORWARD ${BATCH_SIZE} FROM audit_stream;`); if (fetchRes.rows.length === 0) { hasMore = false; } else { await onBatch(fetchRes.rows); } } await client.query('CLOSE audit_stream;'); await client.query('COMMIT;'); } catch (error) { await client.query('ROLLBACK;'); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Streaming Direct I/O Buffer Pool Invariant

PostgreSQL 17 Streaming Direct I/O Buffer Pool Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 streaming I/O configurations and ring-buffer allocation invariants across high-throughput backend services. Prevents kernel page-cache duplication, dirty write stalls, and NVMe bus contention during concurrent high-volume sequential scans and OLTP transactions. 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: Unbounded Sequential Scans and Default Direct I/O Mismatches Inducing OS Cache Double-Buffering 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	Unbounded Sequential Scans and Default Direct I/O Mismatches Inducing OS Cache Double-Buffering	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 isolate heavy sequential read operations to dedicated bulk-read pool partitions that declare bounded ring-buffer parameters (e.g., SET LOCAL io_combine_limit and cursor-based windowing) to prevent eviction of active OLTP buffer pages.

Engineers MUST ALWAYS isolate heavy sequential read operations to dedicated bulk-read pool partitions that declare bounded ring-buffer parameters (e.g., SET LOCAL io_combine_limit and cursor-based windowing) to prevent eviction of active OLTP buffer pages.

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.