Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing Standard

--- description: Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing Standard - Governs Postgres 17 streaming I/O lookahead prefetching and kernel page cache allocations during vectorized asynchronous sequential and bitmap heap scans. Enforces strict session-level memory fences and concurrency ceilings to prevent OS page cache bloat and container cgroup OOM killer termination. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/infrastructure/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-streaming-io-vectorized-prefetch-memory-fence" --- # Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unfenced Vectorized Asynchronous Prefetching Triggering cgroup OOM Evictions ## System Constraints You are a Principal Software Architect enforcing Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing. Mandate strict memory fences on high-throughput asynchronous scans, control ring-buffer allocations, limit prefetch lookaheads within containerized cgroup environments, and reject unbounded parallel queries capable of exhausting Linux OS page cache. ## Strict Guidelines - Engineers MUST ALWAYS isolate high-volume batch scans in explicit transactions that clamp 'effective_io_concurrency' and 'maintenance_io_concurrency' to prevent unbuffered kernel page cache exhaustion. - Engineers MUST NOT allow unbounded vectorized sequential scans across partitioned analytics workloads without defining session-level 'work_mem' and batch prefetch boundaries. - Code SHOULD enforce cursor-based chunking with bounded fetch counts (e.g., 500-2000 rows) instead of loading full vectorized result sets into Node.js client memory alongside active database prefetch rings. - Services MUST verify container cgroup limits against database shared buffers and kernel dirty page ratio before enabling aggressive streaming I/O combine limits ('io_combine_limit'). - Components NEVER execute raw bulk queries that bypass connection pool sanitization of session-level prefetch parameters, ensuring reset on connection release via 'DISCARD ALL' or pooler transaction mode. ## Bad vs Good Implementations ### Vectorized Batch Scan Memory Fencing with Node-Postgres #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing an unbounded sequential scan without tuning Postgres 17 streaming I/O parameters allows the engine to initiate aggressive vectorized lookaheads. In memory-constrained containers, this saturates both shared_buffers and the OS page cache, risking Linux OOM-killer termination. ```typescript import { Pool } from 'pg'; const pool = new Pool(); export async function exportMassiveAuditLog(tenantId: string): Promise<any[]> { const client = await pool.connect(); try { // ANTI-PATTERN: Unfenced query in Postgres 17 leverages full vectorized prefetch // lookahead across massive tables, inflating page cache and triggering cgroup OOM kills. const result = await client.query( `SELECT * FROM audit_logs WHERE tenant_id = $1 ORDER BY recorded_at ASC`, [tenantId] ); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Enforces deterministic memory fencing via transaction-local parameters ('SET LOCAL effective_io_concurrency = 2', 'work_mem = 16MB') and processes data via bounded cursor chunks. This prevents runaway vectorized prefetching and eliminates cgroup page cache memory blowout. ```typescript import { Pool, PoolClient } from 'pg'; import Cursor from 'pg-cursor'; const pool = new Pool(); export async function exportMassiveAuditLog( tenantId: string, rowProcessor: (row: Record<string, unknown>) => Promise<void> ): Promise<void> { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // INVARIANT: Fence streaming I/O prefetch and memory consumption for this transaction await client.query(` SET LOCAL effective_io_concurrency = 2; SET LOCAL work_mem = '16MB'; SET LOCAL synchronize_seqscans = off; `); const queryText = `SELECT id, recorded_at, payload FROM audit_logs WHERE tenant_id = $1 ORDER BY recorded_at ASC`; const cursor = client.query(new Cursor(queryText, [tenantId])); const BATCH_SIZE = 1000; let rows: Record<string, unknown>[]; do { rows = await cursor.read(BATCH_SIZE); for (const row of rows) { await rowProcessor(row); } } while (rows.length > 0); await cursor.close(); await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing Standard

Postgres 17 Streaming I/O Vectorized Prefetch Memory Fencing Standard defines an industrial-grade behavioral standard for backend engineering. Governs Postgres 17 streaming I/O lookahead prefetching and kernel page cache allocations during vectorized asynchronous sequential and bitmap heap scans. Enforces strict session-level memory fences and concurrency ceilings to prevent OS page cache bloat and container cgroup OOM killer termination. 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: Unfenced Vectorized Asynchronous Prefetching Triggering cgroup OOM Evictions 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	Unfenced Vectorized Asynchronous Prefetching Triggering cgroup OOM Evictions	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 high-volume batch scans in explicit transactions that clamp 'effective_io_concurrency' and 'maintenance_io_concurrency' to prevent unbuffered kernel page cache exhaustion.

Engineers MUST ALWAYS isolate high-volume batch scans in explicit transactions that clamp 'effective_io_concurrency' and 'maintenance_io_concurrency' to prevent unbuffered kernel page cache exhaustion.

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.