Postgres 17 Direct I/O and Asynchronous Streaming Read Ring Buffer Isolation

--- description: Postgres 17 Direct I/O and Asynchronous Streaming Read Ring Buffer Isolation - Enforces strict isolation of analytical sequential reads from the OLTP shared buffer pool using Postgres 17 streaming read APIs and Direct I/O configurations. Prevents kernel page-cache pollution, double-buffering overhead, and BufferMapping lock contention during bulk data access. Mandates dedicated connection routing and session-scoped streaming ring buffers for high-throughput scans. globs: src/infra/database/**/*.ts, src/repositories/**/*.ts, src/services/analytics/**/*.ts, config/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-direct-io-prefetch-isolation" --- # Postgres 17 Direct I/O and Asynchronous Streaming Read Ring Buffer Isolation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing Unconstrained Bulk Scans Over OLTP Pools Causing Buffer-Pool Lock Contention and Cache Thrashing ## System Constraints You are a Principal Database Architect and Infrastructure Reliability Engineer enforcing Postgres 17 Direct I/O and streaming read prefetch buffer isolation. You must prohibit raw sequential scans and bulk analytical queries on transactional pools that cause Linux page-cache thrashing and shared_buffers lock contention. Ensure that all analytical batch queries run on isolated read replicas configured with `io_direct` or strictly bound within transaction-level streaming read ring buffers with explicit I/O combine limits. ## Strict Guidelines - Engineers MUST ALWAYS route bulk analytical scans and high-throughput export jobs to dedicated analytical connection pools with ring-buffer session parameters. - Engineers MUST NOT execute ad-hoc, unbounded table scans over the primary OLTP connection pool, risking buffer pool lock contention on `BufferMapping` lwlocks. - Code SHOULD explicitly set transaction-level streaming read prefetch controls (`SET LOCAL io_combine_limit = '128kB'` or query-specific `effective_io_concurrency`) on Postgres 17 connections performing bulk extracts. - Services MUST isolate bulk sequential reader transactions using cursor-based streaming or batch windowing to ensure Postgres engages ring-buffer eviction heuristics (e.g., `BAS_BULKREAD` strategy). - Components NEVER execute raw bulk queries without disabling `synchronize_seqscans` when deterministic prefetch ordering is required to avoid lock-stepping with other background scans. ## Bad vs Good Implementations ### Analytical Scan via OLTP Pool vs. Isolated Prefetched Streaming Read Session #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing full partition or table scans directly on the OLTP connection pool leads to shared_buffers churn, evicting frequently accessed index and point-lookup pages. Without Direct I/O and isolated streaming ring buffers, Postgres and the Linux kernel suffer double-buffering and heavy BufferMapping lightweight lock (lwlock) contention. ```typescript import { Pool } from 'pg'; // ANTI-PATTERN: Using the primary OLTP pool for a multi-million-row analytical scan // Evicts hot OLTP pages from shared_buffers, pollutes the OS page cache, and blocks transactional queries. export async function exportTransactionAudit(oltpPool: Pool, orgId: string) { const client = await oltpPool.connect(); try { const query = ` SELECT id, amount, status, metadata, created_at FROM ledger_entries WHERE organization_id = $1 ORDER BY created_at ASC; `; // Raw unconstrained scan on OLTP pool thrashing buffer cache const result = await client.query(query, [orgId]); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: The compliant implementation routes through an analytical pool, starts a read-only transaction, and applies Postgres 17 streaming read pragmas (`io_combine_limit`, `synchronize_seqscans = off`). It uses a cursor to force the Postgres bulk-read ring buffer allocation (`BAS_BULKREAD`), preventing shared-buffer eviction and OS page-cache thrashing. ```typescript import { Pool, PoolClient } from 'pg'; import { createAsyncIterableStream } from './db-stream-utils'; // PRODUCTION INVARIANT: Use dedicated analytical pool with PG17 streaming read isolation export async function exportTransactionAuditIsolated( analyticsPool: Pool, orgId: string ): Promise<AsyncIterable<Record<string, unknown>>> { const client: PoolClient = await analyticsPool.connect(); try { await client.query('BEGIN READ ONLY;'); // Postgres 17 session tuning: enforce ring-buffer isolation and prefetch chunk limits await client.query(` SET LOCAL synchronize_seqscans = off; SET LOCAL io_combine_limit = '128kB'; SET LOCAL work_mem = '64MB'; `); const cursorName = `cur_ledger_export_${Date.now()}`; await client.query( `DECLARE ${cursorName} NO SCROLL CURSOR FOR SELECT id, amount, status, metadata, created_at FROM ledger_entries WHERE organization_id = $1 ORDER BY created_at ASC;`, [orgId] ); return createAsyncIterableStream(client, cursorName, 5000); } catch (error) { await client.query('ROLLBACK;').catch(() => {}); client.release(); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Direct I/O and Asynchronous Streaming Read Ring Buffer Isolation

Postgres 17 Direct I/O and Asynchronous Streaming Read Ring Buffer Isolation defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation of analytical sequential reads from the OLTP shared buffer pool using Postgres 17 streaming read APIs and Direct I/O configurations. Prevents kernel page-cache pollution, double-buffering overhead, and BufferMapping lock contention during bulk data access. Mandates dedicated connection routing and session-scoped streaming ring buffers for high-throughput scans. 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: Executing Unconstrained Bulk Scans Over OLTP Pools Causing Buffer-Pool Lock Contention and Cache Thrashing 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	Executing Unconstrained Bulk Scans Over OLTP Pools Causing Buffer-Pool Lock Contention and Cache Thrashing	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 route bulk analytical scans and high-throughput export jobs to dedicated analytical connection pools with ring-buffer session 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.