Postgres 17 Parallel Vacuum Maintenance Work Memory Cap Allocation

Prevents catastrophic Out-Of-Memory (OOM) events and severe disk I/O thrashing during heavy write surges by governing PostgreSQL 17 vacuum worker allocations. Enforces dynamic ceilings across maintenance_work_mem, autovacuum_max_workers, and max_parallel_maintenance_workers, utilizing PG17's internal vacuum memory limits rather than unbounded global memory assignments. Eliminates multi-gigabyte memory exhaustion where concurrent index scans and parallel vacuum worker allocations cause cascading backend evictions.

--- description: Postgres 17 Parallel Vacuum Maintenance Work Memory Cap Allocation - Prevents catastrophic Out-Of-Memory (OOM) events and severe disk I/O thrashing during heavy write surges by governing PostgreSQL 17 vacuum worker allocations. Enforces dynamic ceilings across maintenance_work_mem, autovacuum_max_workers, and max_parallel_maintenance_workers, utilizing PG17's internal vacuum memory limits rather than unbounded global memory assignments. Eliminates multi-gigabyte memory exhaustion where concurrent index scans and parallel vacuum worker allocations cause cascading backend evictions. globs: src/database/**/*.{ts,js,sql}, src/infra/**/*.{ts,js,sql}, src/config/**/*.{ts,js}, docker/postgres/**/*.sql, k8s/**/*.yaml alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-parallel-vacuum-memory-cap-guardrail" --- # Postgres 17 Parallel Vacuum Maintenance Work Memory Cap Allocation > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Uncapped Maintenance Work Memory Allocation Across Concurrent Parallel Vacuum Workers ## System Constraints You are a Principal Database Infrastructure Architect enforcing Postgres 17 Parallel Vacuum and Maintenance Memory limits. Postgres 17 radically modernizes vacuum indexing memory management, but uncoordinated configurations combining `maintenance_work_mem`, `max_parallel_maintenance_workers`, and `autovacuum_max_workers` will induce kernel OOM-killer termination during write surges. Mandate strictly bounded session-level memory overrides, dynamic per-worker allocation caps, and telemetry verification in all migrations, client drivers, and infrastructure-as-code manifests. ## Strict Guidelines - Engineers MUST ALWAYS compute the maximum concurrent vacuum memory consumption using the invariant formula: `Peak_Memory = autovacuum_max_workers * (1 + max_parallel_maintenance_workers) * maintenance_work_mem` and guarantee this sum never exceeds 20% of total host RAM. - Engineers MUST NOT issue raw `VACUUM (PARALLEL ...)` queries in application logic or scheduled jobs without explicitly bounding session-local `maintenance_work_mem` and `max_parallel_maintenance_workers` prior to invocation. - Infrastructure scripts MUST set per-table autovacuum tuning using `autovacuum_vacuum_cost_limit` and `vacuum_maintenance_work_mem` overrides instead of globally inflating instance-wide maintenance memory to high gigabyte values. - Database maintenance scripts and migration runners SHOULD inspect `pg_stat_progress_vacuum` to verify memory deadbands and ensure parallel worker allocations are dynamically throttled when replication lag or active client connections cross critical thresholds. - Services MUST NEVER configure Postgres 17 vacuum workers using deprecated assumptions where individual parallel workers consume divided subsets of memory; parallel worker index vacuuming in high parallelism can scale aggregate allocation up to the configured cap concurrently. ## Bad vs Good Implementations ### Unsafe Parallel Vacuum Trigger vs. Guarded Adaptive Work Memory Allocation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing parallel vacuum with an unmanaged parallel worker count and default server-level maintenance_work_mem (often inflated to 2-4GB by engineers) allows each active worker and autovacuum thread to exhaust host swap space and provoke Linux kernel OOM kills during ongoing write surges. ```typescript import { Pool } from 'pg'; export async function runManualMaintenance(pool: Pool, tableName: string): Promise<void> { // DANGEROUS: Invokes unthrottled parallel vacuum on heavy write table. // Can trigger OOM if autovacuum is simultaneously active with high global maintenance_work_mem. await pool.query(`VACUUM (PARALLEL 8, ANALYZE, FULL) ${tableName};`); } ``` #### Production Standard (Correct) > **Standard**: Applies session-level constraints via SET LOCAL, capping memory to safe boundaries (256-512MB) and setting parallel worker thresholds to mitigate CPU/IO starvation and eliminate uncoordinated multi-gigabyte worker memory saturation. ```typescript import { PoolClient } from 'pg'; interface SafeVacuumOptions { maxParallelWorkers?: number; boundedMemoryMb?: number; costDelayMs?: number; } export async function runSafeParallelVacuum( client: PoolClient, tableName: string, options: SafeVacuumOptions = {} ): Promise<void> { const maxWorkers = Math.min(options.maxParallelWorkers ?? 2, 4); const memoryMb = Math.min(options.boundedMemoryMb ?? 256, 512); const costDelay = Math.max(options.costDelayMs ?? 10, 2); // Guardrail: Restrict transaction/session context to isolated caps before vacuuming await client.query(` SET LOCAL maintenance_work_mem = '${memoryMb}MB'; SET LOCAL max_parallel_maintenance_workers = ${maxWorkers}; SET LOCAL vacuum_cost_delay = ${costDelay}; `); try { // In Postgres 17, bounded maintenance_work_mem and explicit parallel bounds prevent IO/Memory blowups await client.query(`VACUUM (PARALLEL ${maxWorkers}, ANALYZE) ${tableName};`); } finally { await client.query(` RESET maintenance_work_mem; RESET max_parallel_maintenance_workers; RESET vacuum_cost_delay; `); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Parallel Vacuum Maintenance Work Memory Cap Allocation

Postgres 17 Parallel Vacuum Maintenance Work Memory Cap Allocation defines an industrial-grade behavioral standard for backend engineering. Prevents catastrophic Out-Of-Memory (OOM) events and severe disk I/O thrashing during heavy write surges by governing PostgreSQL 17 vacuum worker allocations. Enforces dynamic ceilings across maintenance_work_mem, autovacuum_max_workers, and max_parallel_maintenance_workers, utilizing PG17's internal vacuum memory limits rather than unbounded global memory assignments. Eliminates multi-gigabyte memory exhaustion where concurrent index scans and parallel vacuum worker allocations cause cascading backend evictions. 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 Uncapped Maintenance Work Memory Allocation Across Concurrent Parallel Vacuum Workers 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 Uncapped Maintenance Work Memory Allocation Across Concurrent Parallel Vacuum Workers	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 compute the maximum concurrent vacuum memory consumption using the invariant formula

Engineers MUST ALWAYS compute the maximum concurrent vacuum memory consumption using the invariant formula: `Peak_Memory = autovacuum_max_workers * (1 + max_parallel_maintenance_workers) * maintenance_work_mem` and guarantee this sum never exceeds 20% of total host RAM.

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.