Postgres 17 Direct I/O and Streaming Read Buffer Saturation Guardrail

--- description: Postgres 17 Direct I/O and Streaming Read Buffer Saturation Guardrail - Enforces strict concurrency, prefetch limits, and pool isolation on Postgres 17 streaming reads and Direct I/O operations. Prevents aggressive asynchronous read-ahead routines from saturating kernel I/O pipelines, triggering shared buffer eviction cascades, and starving latency-critical OLTP transactions. Ensures analytical or bulk scans apply session-level prefetch throttling and bypass primary OLTP connection pools. globs: src/database/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, infra/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-direct-io-streaming-read-saturation-guardrail" --- # Postgres 17 Direct I/O and Streaming Read Buffer Saturation Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Streaming Sequential Scans Evicting Hot OLTP Shared Buffers and Saturating Disk I/O ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 Direct I/O and Streaming Read Buffer Saturation guardrails. Postgres 17 introduces modernized streaming read prefetchers and Direct I/O mechanics that, if configured or queried naively, bypass standard query pacing and rapidly evict active working sets from shared_buffers while generating severe kernel queue saturation. You must ensure all bulk scans, data pipelines, and streaming export endpoints implement transaction-scoped read-ahead limits, route reads to dedicated read replicas, and maintain strict memory safety invariants. ## Strict Guidelines - Engineers MUST ALWAYS isolate large sequential or streaming scans to dedicated replica pools with capped 'max_parallel_workers_per_gather' and strictly bounded prefetch parameters. - Engineers MUST NOT execute full-table streaming queries or unindexed range scans against primary OLTP connection pools using default unbounded streaming drivers. - Queries streaming over 10,000 rows MUST set transaction-local prefetch and I/O limits (e.g., 'SET LOCAL io_combine_limit = 4' or 'SET LOCAL effective_io_concurrency = 1') to prevent queue saturation. - Code SHOULD prefer indexed keyset pagination over unconstrained server-side streaming cursors when operating on hot tables to avoid holding shared buffer pin cascades. - Services MUST monitor and bound buffer pool churn rates; any streaming read batching MUST introduce cooperative backpressure and yield intervals to preserve OS page-cache equilibrium. ## Bad vs Good Implementations ### Postgres 17 Streaming Read Throttling and Pool Segregation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: This code streams millions of rows through the primary OLTP pool without session-scoped prefetch throttle limits. In Postgres 17, the streaming read engine will prefetch aggressively, monopolizing I/O bandwidth, evicting transactional pages from shared_buffers, and degrading concurrent write operations. ```typescript import { Pool } from 'pg'; import QueryStream from 'pg-query-stream'; // ANTI-PATTERN: Executing unbounded streaming scan on the primary OLTP connection pool. // In Postgres 17, unrestrained streaming reads leverage aggressive prefetching, // blowing through shared_buffers and causing OS page cache churn that degrades concurrent writes. export async function exportTransactionAudit(oltpPool: Pool): Promise<NodeJS.ReadableStream> { const client = await oltpPool.connect(); // No session-level prefetch bounds, default io_combine_limit, scanning millions of rows const query = new QueryStream('SELECT * FROM audit_logs WHERE created_at > NOW() - INTERVAL \'90 days\''); const stream = client.query(query); stream.on('end', () => client.release()); stream.on('error', () => client.release()); return stream; } ``` #### Production Standard (Correct) > **Standard**: The invariant-compliant fix isolates execution to a replica pool, encapsulates execution in a READ ONLY transaction, sets local bounds on Postgres 17 I/O parameters (`io_combine_limit` and `effective_io_concurrency`), disables parallel scan bloat, and provides deterministic resource reclamation on stream completion. ```typescript import { Pool, PoolClient } from 'pg'; import QueryStream from 'pg-query-stream'; // PRODUCTION FIX: Route bulk scans to a dedicated Read-Only Replica pool, // enforce transaction-local I/O limits, and establish cooperative backpressure. export async function exportTransactionAudit( replicaPool: Pool, batchSize: number = 2000 ): Promise<{ stream: NodeJS.ReadableStream; cleanup: () => Promise<void> }> { const client: PoolClient = await replicaPool.connect(); try { await client.query('BEGIN READ ONLY'); // Bound Postgres 17 prefetch concurrency and I/O batching for this session await client.query('SET LOCAL io_combine_limit = 4'); await client.query('SET LOCAL effective_io_concurrency = 2'); await client.query('SET LOCAL max_parallel_workers_per_gather = 0'); const query = new QueryStream( 'SELECT id, entity_id, payload, created_at FROM audit_logs WHERE created_at > NOW() - INTERVAL \'90 days\'', [], { batchSize } ); const stream = client.query(query); const cleanup = async (): Promise<void> => { try { await client.query('ROLLBACK'); } finally { client.release(); } }; stream.on('end', cleanup); stream.on('error', cleanup); return { stream, cleanup }; } catch (err) { client.release(); throw err; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Direct I/O and Streaming Read Buffer Saturation Guardrail

Postgres 17 Direct I/O and Streaming Read Buffer Saturation Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict concurrency, prefetch limits, and pool isolation on Postgres 17 streaming reads and Direct I/O operations. Prevents aggressive asynchronous read-ahead routines from saturating kernel I/O pipelines, triggering shared buffer eviction cascades, and starving latency-critical OLTP transactions. Ensures analytical or bulk scans apply session-level prefetch throttling and bypass primary OLTP connection pools. 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 Streaming Sequential Scans Evicting Hot OLTP Shared Buffers and Saturating Disk 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	Unbounded Streaming Sequential Scans Evicting Hot OLTP Shared Buffers and Saturating Disk 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 ALWAYS isolate large sequential or streaming scans to dedicated replica pools with capped 'max_parallel_workers_per_gather' and strictly bounded prefetch parameters.

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.