PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass Invariant

--- description: PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass Invariant - Enforces strict I/O isolation for high-volume sequential analytics operations to prevent eviction of hot OLTP working sets from PostgreSQL shared_buffers and operating system page caches. Leverages PostgreSQL 17 streaming I/O architecture, vector reads, and session-level buffer-bypass configurations. Prohibits unbounded sequential scans on transaction-critical primary nodes. globs: src/**/*.ts, src/**/*.sql, migrations/**/*.sql, infra/postgres/**/*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-streaming-direct-io-cache-bypass" --- # PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded sequential analytical scans polluting shared_buffers and evicting mission-critical OLTP working sets ## System Constraints You are a Principal Database Architect and Infrastructure Reliability Engineer enforcing PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass invariants. You must prevent large sequential operations from evicting hot OLTP buffer sets by mandating ring buffer limits, replica routing for analytics, and explicit session-level streaming I/O parameter tuning. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical, reporting, or batch scanning workloads to dedicated read-replicas configured with direct I/O or strict shared_buffers protections. - Engineers MUST NOT execute large sequential scans or batch ETL extracts against the primary OLTP pool without constraining ring-buffer eviction policies. - Code executing table scans exceeding 100,000 blocks MUST set local session knobs (such as PostgreSQL 17 streaming I/O parameters like `io_combine_limit` or restrictive ring buffer settings) to avoid polluting operating system buffer caches. - Services MUST route write-path queries and sub-millisecond point lookups through dedicated OLTP connection pools distinct from batch/analytics connection pools. - Data access layers MUST enforce statement timeouts (`SET LOCAL statement_timeout`) and cost limits (`SET LOCAL max_parallel_workers_per_gather`) when executing sequential batch operations. ## Bad vs Good Implementations ### Routing Analytical Batch Scan with PostgreSQL 17 Ring-Buffer & Cache-Bypass Protection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing full sequential scans on the primary OLTP connection pool loads megabytes or gigabytes of cold historical audit blocks into shared_buffers and OS filesystem caches. This forces synchronous cache eviction of hot account balances and lock rows, precipitating tail-latency spikes across the entire transactional API. ```typescript import { Pool } from 'pg'; // ANTI-PATTERN: Shared OLTP pool executing unbounded sequential scans on primary node, // destroying shared_buffers and OS page cache for concurrent transactional traffic. export async function exportTransactionAuditHistory( oltpPool: Pool, startDate: Date, endDate: Date ) { const client = await oltpPool.connect(); try { const query = ` SELECT id, account_id, amount, payload, created_at FROM ledger_entries WHERE created_at BETWEEN $1 AND $2 ORDER BY created_at ASC; `; const result = await client.query(query, [startDate, endDate]); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Directs heavy scans to a read-replica pool. Wraps the operation in a transaction setting explicit PG17 streaming parameters (`io_combine_limit`, sequential scan synchronization, and memory limits), keeping the sequential read confined to a streaming ring buffer without flushing the hot working set. ```typescript import { Pool } from 'pg'; // PRODUCTION INVARIANT: Dedicated analytical replica pool with strict session guards, // utilizing PG17 vector streaming I/O controls and bounded ring-buffer isolation. export async function exportTransactionAuditHistory( analyticsReplicaPool: Pool, startDate: Date, endDate: Date ) { const client = await analyticsReplicaPool.connect(); try { await client.query('BEGIN READ ONLY'); // Constrain session to ring buffer semantics, set streaming combine limits, and apply short statement timeout await client.query(` SET LOCAL statement_timeout = '30s'; SET LOCAL work_mem = '64MB'; SET LOCAL io_combine_limit = '128kB'; SET LOCAL synchronize_seqscans = on; `); const query = ` SELECT id, account_id, amount, payload, created_at FROM ledger_entries WHERE created_at BETWEEN $1 AND $2 ORDER BY created_at ASC; `; const result = await client.query(query, [startDate, endDate]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass Invariant

PostgreSQL 17 Streaming I/O and Direct I/O Cache-Bypass Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict I/O isolation for high-volume sequential analytics operations to prevent eviction of hot OLTP working sets from PostgreSQL shared_buffers and operating system page caches. Leverages PostgreSQL 17 streaming I/O architecture, vector reads, and session-level buffer-bypass configurations. Prohibits unbounded sequential scans on transaction-critical primary nodes. 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 analytical scans polluting shared_buffers and evicting mission-critical OLTP working sets 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 analytical scans polluting shared_buffers and evicting mission-critical OLTP working sets	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 analytical, reporting, or batch scanning workloads to dedicated read-replicas configured with direct I/O or strict shared_buffers protections.

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.