PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Over-allocating maintenance_work_mem with unbounded autovacuum workers triggering OOM termination during concurrent radix-tree dead tuple index passes) 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.

PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant

--- description: PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant - PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore) for vacuuming, fundamentally altering memory consumption dynamics under high write loads. This rule enforces deterministic calibration of maintenance_work_mem and autovacuum_max_workers to eliminate out-of-memory kernel panics and index bloat during concurrent streaming table cleanups. globs: src/**/*.ts, src/infrastructure/**/*.ts, src/database/**/*.ts, migrations/**/*.sql, infra/**/*.tf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-tidstore-vacuum-memory-invariant" --- # PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Over-allocating maintenance_work_mem with unbounded autovacuum workers triggering OOM termination during concurrent radix-tree dead tuple index passes ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Memory-Optimized Streaming Vacuum Invariants. PostgreSQL 17 introduces an adaptive radix tree (TidStore) dead tuple collector that significantly decreases memory consumption per TID while enabling memory utilization up to maintenance_work_mem ceiling per autovacuum worker. You must prevent aggregate maintenance memory overcommitment, enforce granular per-table vacuum thresholds on write-heavy tables, and ensure provisioning formulas account for concurrent radix tree compaction workers to avoid cgroup or host OOM crashes. ## Strict Guidelines - Engineers MUST ALWAYS bound aggregate maintenance memory such that (autovacuum_max_workers * autovacuum_work_mem) <= (System_Available_RAM - shared_buffers - (max_connections * work_mem)). - Engineers MUST set autovacuum_work_mem explicitly when maintenance_work_mem is scaled up for manual DDL, isolating automated TidStore allocations from administrative commands. - Engineers MUST configure reloptions (autovacuum_vacuum_cost_limit, autovacuum_vacuum_scale_factor) on burst-heavy tables to throttle index scanning and prevent single-worker starvation. - Engineers MUST NOT allow autovacuum_work_mem to exceed 1GB per worker, as PostgreSQL 17 TidStore exhibits diminishing returns beyond 1GB even on tables exceeding tens of billions of dead rows. - Database provisioning scripts MUST validate that PostgreSQL 17 instances disable OS overcommit (vm.overcommit_memory = 2) when provisioning high autovacuum concurrency. - Diagnostic queries MUST inspect pg_stat_progress_vacuum (specifically max_dead_tuples and dead_tuples) to detect fallback index cycles caused by insufficient TidStore allocation. ## Bad vs Good Implementations ### Database Provisioning & Per-Table Autovacuum Calibration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Defaulting autovacuum_work_mem to maintenance_work_mem allows up to 5 workers to allocate 4GB each (20GB total) exclusively for Postgres 17 TidStore radix trees. Combined with shared buffers, this triggers kernel OOM crashes. Furthermore, a 0.2 scale factor on high-volume tables defers cleanup until millions of dead TIDs accumulate. ```typescript import { Client } from 'pg'; export async function configureDatabase(client: Client) { // Flawed: Leaves autovacuum_work_mem at -1, defaulting to maintenance_work_mem. // If 5 workers run concurrently with 4GB maintenance_work_mem, TidStore consumes 20GB, // causing cgroup OOM killer invocation during burst ingestion. await client.query(` ALTER SYSTEM SET maintenance_work_mem = '4GB'; ALTER SYSTEM SET autovacuum_max_workers = 5; `); // Uncapped dead-tuple accumulation on high-throughput streaming table await client.query(` ALTER TABLE audit_events SET ( autovacuum_vacuum_scale_factor = 0.2 ); `); } ``` #### Production Standard (Correct) > **Standard**: Decouples autovacuum_work_mem from maintenance_work_mem, dynamically calculating a bounded per-worker cap for PG17 TidStore trees (up to 1GB). Applies aggressive absolute thresholds and cost limits to write-heavy tables so dead tuples are cleared incrementally without exhausting worker memory. ```typescript import { Client } from 'pg'; interface PostgresMemoryTopology { availableRamMb: number; sharedBuffersMb: number; maxConnections: number; workMemMb: number; } export function calculateSafeAutovacuumMem(topology: PostgresMemoryTopology, maxWorkers: number): number { const overheadBudgetMb = topology.availableRamMb - topology.sharedBuffersMb - (topology.maxConnections * topology.workMemMb); const maxTotalAutovacuumBudgetMb = Math.floor(overheadBudgetMb * 0.3); // Dedicate 30% of non-shared memory const perWorkerMemMb = Math.floor(maxTotalAutovacuumBudgetMb / maxWorkers); // PostgreSQL 17 TidStore radix trees achieve optimal dead tuple packing at <= 1024MB return Math.max(64, Math.min(perWorkerMemMb, 1024)); } export async function configureDatabase(client: Client, topology: PostgresMemoryTopology) { const autovacuumWorkers = 4; const safeWorkerMemMb = calculateSafeAutovacuumMem(topology, autovacuumWorkers); await client.query(` ALTER SYSTEM SET autovacuum_max_workers = ${autovacuumWorkers}; ALTER SYSTEM SET autovacuum_work_mem = '${safeWorkerMemMb}MB'; ALTER SYSTEM SET maintenance_work_mem = '2GB'; `); // Streamlined per-table thresholds for intense ingestion tables await client.query(` ALTER TABLE audit_events SET ( autovacuum_vacuum_threshold = 50000, autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_cost_limit = 10000, autovacuum_vacuum_cost_delay = 2 ); `); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant

PostgreSQL 17 TidStore Vacuum Memory and Worker Concurrency Invariant defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore) for vacuuming, fundamentally altering memory consumption dynamics under high write loads. This rule enforces deterministic calibration of maintenance_work_mem and autovacuum_max_workers to eliminate out-of-memory kernel panics and index bloat during concurrent streaming table cleanups. 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: Over-allocating maintenance_work_mem with unbounded autovacuum workers triggering OOM termination during concurrent radix-tree dead tuple index passes 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	Over-allocating maintenance_work_mem with unbounded autovacuum workers triggering OOM termination during concurrent radix-tree dead tuple index passes	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 aggregate maintenance memory such that (autovacuum_max_workers * autovacuum_work_mem) <= (System_Available_RAM - shared_buffers - (max_connections * work_mem)).

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.