Postgres 17 Streaming I/O Vector Scan Buffer Pool Guardrail

--- description: Postgres 17 Streaming I/O Vector Scan Buffer Pool Guardrail - Enforces deterministic configuration and session-level constraints for PostgreSQL 17 asynchronous streaming I/O and vectorized scans. Saturates high-bandwidth NVMe storage during bulk analytical queries while isolating ring buffer access strategies (BAS_BULKREAD) to prevent eviction of hot OLTP cache blocks from shared buffers. globs: src/infrastructure/database/**/*.{ts,js}, src/db/**/*.{ts,js}, migrations/**/*.{sql,ts}, docker/**/*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-streaming-io-vector-scan-buffer-guardrail" --- # Postgres 17 Streaming I/O Vector Scan Buffer Pool Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unconstrained Table Scans Evicting Low-Latency OLTP Buffer Pools via Unbounded Streaming I/O ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 streaming I/O vector scan and buffer pool preservation guardrails. Your objective is to ensure that asynchronous sequential scans saturate NVMe drive throughput without polluting the primary shared_buffers cache used by low-latency OLTP workloads. Reject any query orchestration or connection pool configuration that disables bulk-read ring buffer strategies or executes unthrottled analytical scans within shared transactional pools. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical scans to an isolated connection pool or replica where session-level streaming parameters (`io_combine_limit`, `effective_io_concurrency`) are tuned specifically for NVMe block sizes without disabling the `BAS_BULKREAD` ring buffer. - Engineers MUST NOT disable the PostgreSQL ring buffer mechanism (e.g., setting `synchronous_commit = off` does not substitute buffer strategy enforcement, nor should workarounds force full sequential scan blocks directly into `shared_buffers`). - Code executing batch vector similarity scans or analytical aggregations MUST configure transaction-scoped `SET LOCAL io_combine_limit` and `SET LOCAL effective_io_concurrency` to avoid degrading parallel OLTP connection threads. - Services MUST query vector embeddings and large columnar-adjacent tables with strict index-scan or partitioned chunk boundaries to prevent unexpected sequential scan fallbacks on tables exceeding `shared_buffers` capacity. - Components NEVER execute raw sequential analytical sweeps on connection pools configured for high-concurrency, single-digit millisecond OLTP point transactions. ## Bad vs Good Implementations ### Postgres 17 NVMe-Optimized Vectorized Scan with Ring Buffer Isolation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Analytical queries run on the primary transactional connection pool without tuning PG17 asynchronous streaming parameters or constraining buffer usage. If the index scan degrades to a parallel sequential scan, it can swamp the NVMe I/O queue and displace hot OLTP pages from shared buffers. ```typescript import { Pool } from 'pg'; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); // ANTI-PATTERN: Running unconstrained analytical vector aggregation across the primary OLTP pool. // Uses default global concurrency and can trigger full sequential scans that flush the shared_buffers // working set, stalling concurrent point lookups on NVMe storage. export async function runAnalyticalScan(tenantId: string, minScore: number) { const client = await pool.connect(); try { const result = await client.query(` SELECT id, title, l2_distance(embedding, $1) AS dist FROM document_vectors WHERE tenant_id = $2 ORDER BY dist ASC LIMIT 1000; `, ['[0.12, 0.45, ...]', tenantId]); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Isolates analytical load to a dedicated pool and applies transaction-scoped PG17 streaming parameters (`io_combine_limit` and `effective_io_concurrency`). This saturates NVMe throughput while PostgreSQL confines the read stream to an ephemeral ring buffer, keeping transactional OLTP pages resident in shared_buffers. ```typescript import { Pool, PoolClient } from 'pg'; // Dedicated pool for analytical and heavy vectorized reads configured with read-only/replica targets const analyticsPool = new Pool({ connectionString: process.env.ANALYTICS_DATABASE_URL, max: 10, idleTimeoutMillis: 30000, }); export async function runAnalyticalScan( tenantId: string, queryVector: string ): Promise<Array<{ id: string; title: string; dist: number }>> { const client: PoolClient = await analyticsPool.connect(); try { await client.query('BEGIN READ ONLY;'); // PG17 Streaming I/O: Set local chunking & concurrency to saturate NVMe // while ensuring PostgreSQL keeps the 256KB-16MB ring buffer (BAS_BULKREAD) // active to protect shared_buffers from cache eviction. await client.query(` SET LOCAL effective_io_concurrency = 256; SET LOCAL io_combine_limit = '128kB'; SET LOCAL max_parallel_workers_per_gather = 4; `); const { rows } = await client.query<{ id: string; title: string; dist: number }>(` SELECT id, title, (embedding <=> $1::vector) AS dist FROM document_vectors WHERE tenant_id = $2 ORDER BY dist ASC LIMIT 1000; `, [queryVector, tenantId]); await client.query('COMMIT;'); return rows; } catch (error) { await client.query('ROLLBACK;').catch(() => {}); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Streaming I/O Vector Scan Buffer Pool Guardrail

Postgres 17 Streaming I/O Vector Scan Buffer Pool Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic configuration and session-level constraints for PostgreSQL 17 asynchronous streaming I/O and vectorized scans. Saturates high-bandwidth NVMe storage during bulk analytical queries while isolating ring buffer access strategies (BAS_BULKREAD) to prevent eviction of hot OLTP cache blocks from shared buffers. 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: Global Unconstrained Table Scans Evicting Low-Latency OLTP Buffer Pools via Unbounded Streaming 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	Global Unconstrained Table Scans Evicting Low-Latency OLTP Buffer Pools via Unbounded Streaming 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 analytical scans to an isolated connection pool or replica where session-level streaming parameters (`io_combine_limit`, `effective_io_concurrency`) are tuned specifically for NVMe block sizes without disabling the `BAS_BULKREAD` ring buffer.

Engineers MUST ALWAYS isolate analytical scans to an isolated connection pool or replica where session-level streaming parameters (`io_combine_limit`, `effective_io_concurrency`) are tuned specifically for NVMe block sizes without disabling the `BAS_BULKREAD` ring buffer.

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.