PostgreSQL 17 Vectorized Direct I/O and Parallel Worker Invariant

--- description: PostgreSQL 17 Vectorized Direct I/O and Parallel Worker Invariant - Enforces bounded kernel buffer lock contention and prevents worker exhaustion during concurrent vectorized sequential scans in PostgreSQL 17. Mandates deterministic tuning of io_combine_limit, io_direct, and max_parallel_workers_per_gather to prevent OS page-cache thrashing and worker pool starvation. globs: src/infrastructure/database/**/*.{ts,js,sql}, src/repositories/**/*.{ts,js}, migrations/**/*.sql, config/database/**/*.{ts,json,conf} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-vectorized-direct-io-worker-production-standard" --- # PostgreSQL 17 Vectorized Direct I/O and Parallel Worker Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded parallel sequential scans with uncontrolled block read combinations causing buffer lock saturation and worker exhaustion ## System Constraints You are a Principal Software Architect enforcing the PostgreSQL 17 Vectorized Direct I/O Async Worker Invariant. PostgreSQL 17 introduces modernized streaming I/O and `io_combine_limit` for combining sequential and bitmap heap scan I/O operations. You must enforce strict architectural guardrails: prevent unbounded `max_parallel_workers_per_gather` allocations in high-concurrency APIs, calibrate `io_combine_limit` based on underlying storage chunk alignments to prevent kernel buffer lock contention, ensure analytical vector scans execute within bounded worker quotas, and forbid unconstrained full table scans from starving the PostgreSQL background worker pool. ## Strict Guidelines - Engineers MUST ALWAYS bound parallel gather workers by setting `SET LOCAL max_parallel_workers_per_gather` within transactional analytical scans to prevent background worker starvation. - Engineers MUST configure `io_combine_limit` explicitly (e.g., bounded between '128kB' and '512kB') for batch analytical workloads when utilizing PostgreSQL 17 streaming sequential operations. - Code SHOULD enforce transactional isolation via `SET LOCAL` for I/O and worker concurrency parameters rather than global connection-level overrides. - Services MUST monitor and assert zero worker queue spillover by checking `pg_stat_database_conflicts` and `pg_stat_io` metrics during vectorized scan operations. - Components NEVER execute unbounded parallel table or vector index builds on connections allocated from high-throughput transaction pools. ## Bad vs Good Implementations ### Configuring Vectorized Scan Transaction with PG17 Direct I/O and Worker Bounding #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing heavy analytical scans with unconstrained parallel workers and default combined I/O in PostgreSQL 17 depletes the global max_worker_processes pool and saturates OS/shared buffer locks, causing cascading connection timeouts for concurrent OLTP sessions. ```typescript import { Pool } from 'pg'; export async function runVectorizedBatchScan(pool: Pool, metricThreshold: number) { const client = await pool.connect(); try { // ANTI-PATTERN: Inherits default or unbounded worker allocations. // Unbounded io_combine_limit and max_parallel_workers saturate disk bandwidth // and exhaust the global worker pool, blocking OLTP transactions. const result = await client.query(` SELECT doc_id, vector_score, metadata FROM document_embeddings WHERE vector_score > $1 ORDER BY vector_score DESC; `, [metricThreshold]); return result.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Setting scoped transaction limits via `SET LOCAL max_parallel_workers_per_gather` and aligning `io_combine_limit` prevents the scan from consuming excessive parallel background workers and ensures predictable, bounded direct I/O burst patterns on PostgreSQL 17. ```typescript import { Pool, PoolClient } from 'pg'; interface ScanOptions { maxParallelWorkers?: number; ioCombineLimitKb?: number; } export async function runVectorizedBatchScan( pool: Pool, metricThreshold: number, options: ScanOptions = {} ) { const maxWorkers = options.maxParallelWorkers ?? 2; const combineLimit = options.ioCombineLimitKb ?? 256; // 256kB aligned with NVMe chunk sizes const client: PoolClient = await pool.connect(); try { // INVARIANT: Wrap analytical scan in a transaction with strictly bounded local I/O // and parallel worker limits to prevent buffer lock contention and worker exhaustion. await client.query('BEGIN READ ONLY'); await client.query(` SET LOCAL max_parallel_workers_per_gather = ${maxWorkers}; SET LOCAL io_combine_limit = '${combineLimit}kB'; SET LOCAL synchronize_seqscans = on; `); const result = await client.query(` SELECT doc_id, vector_score, metadata FROM document_embeddings WHERE vector_score > $1 ORDER BY vector_score DESC; `, [metricThreshold]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK').catch(() => {}); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Vectorized Direct I/O and Parallel Worker Invariant

PostgreSQL 17 Vectorized Direct I/O and Parallel Worker Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces bounded kernel buffer lock contention and prevents worker exhaustion during concurrent vectorized sequential scans in PostgreSQL 17. Mandates deterministic tuning of io_combine_limit, io_direct, and max_parallel_workers_per_gather to prevent OS page-cache thrashing and worker pool starvation. 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 parallel sequential scans with uncontrolled block read combinations causing buffer lock saturation and worker exhaustion 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 parallel sequential scans with uncontrolled block read combinations causing buffer lock saturation and worker exhaustion	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 bound parallel gather workers by setting `SET LOCAL max_parallel_workers_per_gather` within transactional analytical scans to prevent background worker starvation.

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.