AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: pg17-streaming-io-prefetch-guardrail

PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails

PostgreSQL 17 introduces an asynchronous streaming I/O subsystem with vectorized sequential reads and vectorized index scans. Uncontrolled vector prefetch concurrency and unconstrained worker allocation cause kernel page-cache churn and buffer table lock contention under heavy analytics workloads. Applications and migration harnesses must enforce deterministic concurrency ceilings, session-scoped prefetch limits, and explicit memory bounds per parallel worker.

Prohibited Anti-Pattern: Unbounded parallel vector prefetch and global default io_combine_limit inducing kernel page-cache thrashing

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails - PostgreSQL 17 introduces an asynchronous streaming I/O subsystem with vectorized sequential reads and vectorized index scans. Uncontrolled vector prefetch concurrency and unconstrained worker allocation cause kernel page-cache churn and buffer table lock contention under heavy analytics workloads. Applications and migration harnesses must enforce deterministic concurrency ceilings, session-scoped prefetch limits, and explicit memory bounds per parallel worker.
globs: src/database/**/*.{ts,js,sql}, src/repositories/**/*.{ts,js}, migrations/**/*.{sql,ts}, infra/postgres/**/*.{conf,sql}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-streaming-io-prefetch-guardrail"
---

# PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded parallel vector prefetch and global default io_combine_limit inducing kernel page-cache thrashing

## System Constraints
You are a Principal Database Architect enforcing PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails. You mandate deterministic concurrency ceilings, buffer cache pinning protection, and per-worker memory constraints across all analytical queries and connection pools. Under PostgreSQL 17, vectorized streaming reads can overwhelm storage IOPS and evict hot buffer pool pages if maintenance_work_mem, work_mem, io_combine_limit, and max_parallel_workers_per_gather are left uncalibrated or applied globally.

## Strict Guidelines
- Engineers MUST ALWAYS isolate analytical workloads utilizing PostgreSQL 17 streaming reads into dedicated connection pools with strict session-level GUC overrides (e.g., SET LOCAL io_combine_limit, SET LOCAL max_parallel_workers_per_gather).
- Engineers MUST NOT issue high-fanout vector scans with unbounded effective_io_concurrency across transactional OLTP connection pools sharing shared_buffers.
- Database execution pools MUST calculate total parallel working memory deterministically using: (max_parallel_workers_per_gather + 1) * work_mem <= pool_worker_memory_budget before initiating vectorized streaming queries.
- Code executing vector distance, large aggregate, or columnar table scans SHOULD wrap queries in explicit read-only transactions with SET LOCAL synchronous_commit = off and constrained io_combine_limit to prevent buffer eviction cascades.
- Services MUST monitor pg_statio_user_tables and pg_stat_database io timings to dynamically back off prefetch vectors when shared_buffers hit-ratios drop below 99%.

## Bad vs Good Implementations
### Unbounded Parallel Analytical Scan vs. Deterministic Vector Prefetch Isolation

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Executing analytical queries against a general-purpose connection pool with cluster defaults allows PostgreSQL 17's vectorized AIO streaming prefetcher to spawn maximal parallel workers and prefetch read requests up to OS limits, evicting transactional pages from shared_buffers and exhausting kernel IO queues.
```typescript
import { Pool } from 'pg';

const pool = new Pool({ max: 20 });

export async function runVectorAnalytics(tenantId: string, embeddingVector: number[]) {
  // Anti-pattern: Inherits cluster defaults without GUC prefetch boundaries;
  // Uncapped worker allocation triggers uncoordinated POSIX AIO prefetch,
  // evicting the shared_buffers working set for OLTP queries.
  const client = await pool.connect();
  try {
    const query = `
      SELECT id, vector <-> $1::vector AS distance
      FROM tenant_document_embeddings
      WHERE tenant_id = $2
      ORDER BY distance ASC
      LIMIT 100;
    `;
    const res = await client.query(query, [JSON.stringify(embeddingVector), tenantId]);
    return res.rows;
  } finally {
    client.release();
  }
}
```

#### Production Standard (Correct)
> **Standard**: The revised implementation routes analytics through a strictly sized pool and uses a transactional scope to set PostgreSQL 17 GUC boundaries: max_parallel_workers_per_gather, io_combine_limit, work_mem, and effective_io_concurrency. This enforces deterministic AIO memory allocations and prevents buffer-pool thrashing.
```typescript
import { Pool, PoolClient } from 'pg';

const analyticsPool = new Pool({
  max: 4, // Strict concurrency ceiling to bound streaming AIO slots
  connectionTimeoutMillis: 5000
});

const MAX_ANALYTIC_WORKERS = 2;
const PREFETCH_IO_COMBINE_PAGES = 16; // 128KB vector chunks
const WORKER_WORK_MEM_KB = 65536; // 64MB per worker

export async function runVectorAnalytics(tenantId: string, embeddingVector: number[]): Promise<Array<{ id: string; distance: number }>> {
  const client: PoolClient = await analyticsPool.connect();
  try {
    await client.query('BEGIN READ ONLY');
    
    // Bound PostgreSQL 17 streaming I/O vector prefetch parameters within transaction context
    await client.query(`
      SET LOCAL max_parallel_workers_per_gather = ${MAX_ANALYTIC_WORKERS};
      SET LOCAL work_mem = '${WORKER_WORK_MEM_KB}kB';
      SET LOCAL io_combine_limit = ${PREFETCH_IO_COMBINE_PAGES};
      SET LOCAL effective_io_concurrency = 32;
    `);

    const query = `
      SELECT id, vector <-> $1::vector AS distance
      FROM tenant_document_embeddings
      WHERE tenant_id = $2
      ORDER BY distance ASC
      LIMIT 100;
    `;
    const res = await client.query<{ id: string; distance: number }>(query, [
      JSON.stringify(embeddingVector),
      tenantId
    ]);
    
    await client.query('COMMIT');
    return res.rows;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL MCP Server
Inspects query execution plans, active GUC parameters, and pg_stat_io metrics on PostgreSQL 17 targets.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Postgres Query Plan Analyzer
Validates EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output to verify streaming I/O worker allocation and cache hits.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails

PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces an asynchronous streaming I/O subsystem with vectorized sequential reads and vectorized index scans. Uncontrolled vector prefetch concurrency and unconstrained worker allocation cause kernel page-cache churn and buffer table lock contention under heavy analytics workloads. Applications and migration harnesses must enforce deterministic concurrency ceilings, session-scoped prefetch limits, and explicit memory bounds per parallel worker. 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 vector prefetch and global default io_combine_limit inducing kernel page-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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnbounded parallel vector prefetch and global default io_combine_limit inducing kernel page-cache thrashingCritical
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 InconsistencyAI 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:

1

Engineers MUST ALWAYS isolate analytical workloads utilizing PostgreSQL 17 streaming reads into dedicated connection pools with strict session-level GUC overrides (e.g., SET LOCAL io_combine_limit, SET LOCAL max_parallel_workers_per_gather).

Engineers MUST ALWAYS isolate analytical workloads utilizing PostgreSQL 17 streaming reads into dedicated connection pools with strict session-level GUC overrides (e.g., SET LOCAL io_combine_limit, SET LOCAL max_parallel_workers_per_gather).

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST NOT issue high-fanout vector scans with unbounded effective_io_concurrency across transactional OLTP connection pools sharing shared_buffers.

Engineers MUST NOT issue high-fanout vector scans with unbounded effective_io_concurrency across transactional OLTP connection pools sharing shared_buffers.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Database execution pools MUST calculate total parallel working memory deterministically using

Database execution pools MUST calculate total parallel working memory deterministically using: (max_parallel_workers_per_gather + 1) * work_mem <= pool_worker_memory_budget before initiating vectorized streaming queries.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Code executing vector distance, large aggregate, or columnar table scans SHOULD wrap queries in explicit read-only transactions with SET LOCAL synchronous_commit = off and constrained io_combine_limit to prevent buffer eviction cascades.

Code executing vector distance, large aggregate, or columnar table scans SHOULD wrap queries in explicit read-only transactions with SET LOCAL synchronous_commit = off and constrained io_combine_limit to prevent buffer eviction cascades.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Services MUST monitor pg_statio_user_tables and pg_stat_database io timings to dynamically back off prefetch vectors when shared_buffers hit-ratios drop below 99%.

Services MUST monitor pg_statio_user_tables and pg_stat_database io timings to dynamically back off prefetch vectors when shared_buffers hit-ratios drop below 99%.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is PostgreSQL 17 Streaming I/O Vector Prefetch Memory Guardrails critical for AI coding assistants?

PostgreSQL 17 introduces an asynchronous streaming I/O subsystem with vectorized sequential reads and vectorized index scans. Uncontrolled vector prefetch concurrency and unconstrained worker allocation cause kernel page-cache churn and buffer table lock contention under heavy analytics workloads. Applications and migration harnesses must enforce deterministic concurrency ceilings, session-scoped prefetch limits, and explicit memory bounds per parallel worker. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded parallel vector prefetch and global default io_combine_limit inducing kernel page-cache thrashing) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

PostgreSQL