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: postgres-17-radix-vacuum-memory-accounting

PostgreSQL 17 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming

PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore), lifting the legacy 1GB dead-tuple indexing limitation. Infrastructure orchestration and database access layers must dynamically account for concurrent autovacuum worker memory and radix-tree overhead, enforcing per-table scale factors and bounded maintenance budgets to prevent container OOMs and transaction ID wraparound under heavy ingest.

Prohibited Anti-Pattern: Unbounded Radix Allocation and Static Maintenance Memory Leading to OOM or Wraparound Halts

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming - PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore), lifting the legacy 1GB dead-tuple indexing limitation. Infrastructure orchestration and database access layers must dynamically account for concurrent autovacuum worker memory and radix-tree overhead, enforcing per-table scale factors and bounded maintenance budgets to prevent container OOMs and transaction ID wraparound under heavy ingest.
globs: src/database/**/*.{ts,js}, src/infra/**/*.{ts,js}, src/jobs/**/*.{ts,js}, src/monitoring/**/*.{ts,js}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-radix-vacuum-memory-accounting"
---

# PostgreSQL 17 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Radix Allocation and Static Maintenance Memory Leading to OOM or Wraparound Halts

## System Constraints
You are a Principal Database Infrastructure Engineer enforcing strict memory safety and transaction integrity for PostgreSQL 17 workloads. You must prevent memory thrashing, autovacuum multi-pass index scanning degradation, container OOM kills, and catastrophic transaction ID (TXID) wraparound during write-heavy ingestion. Enforce programmatic guardrails around PostgreSQL 17 TidStore radix tree dead tuple tracking, dynamic autovacuum_work_mem calculations, and real-time vacuum progress telemetry.

## Strict Guidelines
- Engineers MUST ALWAYS bound dynamic autovacuum_work_mem and manual maintenance_work_mem to ensure (autovacuum_max_workers * autovacuum_work_mem) <= (Available Container RAM - OS & Shared Buffers Reservation).
- Engineers MUST NOT rely on PostgreSQL 16 legacy 1GB dead-tuple array sizing heuristics; PostgreSQL 17 radix trees eliminate the 1GB cap but consume variable memory based on dead-tuple sparsity and clustering.
- Code executing ingestion pipeline vacuums MUST inspect pg_stat_progress_vacuum to track dead_tuples_collected against radix tree memory limits, failing fast if index_passes exceed 0 during bulk loads.
- Services orchestrating high-throughput ingestion MUST dynamically tune autovacuum_vacuum_scale_factor to 0 and rely exclusively on absolute autovacuum_vacuum_threshold to trigger deterministic, single-pass index sweeps before tuple counts exceed the radix tree target budget.
- Components NEVER execute raw maintenance scripts that raise maintenance_work_mem globally; any transient elevation MUST be scoped strictly to a localized transaction session and verified against cgroup v2 memory limits.

## Bad vs Good Implementations
### Unsafe Static Maintenance Memory & Ignorant Vacuum Execution

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Arbitrarily allocating 8GB maintenance_work_mem across multiple connection threads disregards cgroup boundaries and the PostgreSQL 17 radix-tree TidStore allocation profile. Under high concurrency, autovacuum workers combined with high manual allocations cause host memory exhaustion and container eviction. Furthermore, lack of progress tracking allows undetected multi-pass index vacuuming.
```typescript
import { Pool } from 'pg';

export async function runIngestMaintenance(pool: Pool, tableName: string): Promise<void> {
  const client = await pool.connect();
  try {
    // ANTI-PATTERN: Blindly elevating maintenance_work_mem without accounting for concurrent
    // PG17 autovacuum radix tree overhead or cgroup limits, risking immediate container OOM.
    await client.query('SET maintenance_work_mem = "8GB";');
    
    // Unbounded vacuum without monitoring index passes or TXID age risks locking and thrashing.
    await client.query(`VACUUM (ANALYZE) ${tableName};`);
  } finally {
    client.release();
  }
}
```

#### Production Standard (Correct)
> **Standard**: Calculates available system memory dynamically using cgroup-aware capacity formulas, scopes `maintenance_work_mem` safely to the local session, avoids memory thrashing against PostgreSQL 17's radix tree storage, and checks `pg_stat_progress_vacuum` to guarantee that dead-tuple processing completed within deterministic memory bounds.
```typescript
import { PoolClient } from 'pg';
import * as os from 'node:os';

interface VacuumMetrics {
  heapBlksTotal: number;
  heapBlksScanned: number;
  deadTuplesCollected: number;
  numDeadTuples: number;
  indexPasses: number;
}

export class AdaptiveVacuumManager {
  private static readonly SAFETY_BUFFER_MB = 1024;

  public static calculateSafeMaintenanceMemMB(activeAutovacuumWorkers: number): number {
    const freeMemMB = os.freemem() / (1024 * 1024);
    const allocatableMemMB = Math.max(256, freeMemMB - this.SAFETY_BUFFER_MB);
    // PostgreSQL 17 radix trees scale with dead-tuple dispersion; partition safe budget
    const perWorkerAllocation = Math.floor(allocatableMemMB / (activeAutovacuumWorkers + 1));
    return Math.min(2048, perWorkerAllocation);
  }

  public static async executeBoundedVacuum(
    client: PoolClient,
    tableName: string,
    activeWorkers = 3
  ): Promise<void> {
    const safeMemMB = this.calculateSafeMaintenanceMemMB(activeWorkers);

    await client.query('BEGIN;');
    try {
      // Scope memory limits locally to current transaction session
      await client.query(`SET LOCAL maintenance_work_mem = '${safeMemMB}MB';`);
      
      // Execute vacuum with PG17 radix tree optimization parameters
      await client.query(`VACUUM (ANALYZE, INDEX_CLEANUP ON) ${tableName};`);
      
      const progressRes = await client.query<VacuumMetrics>(
        `SELECT heap_blks_total AS "heapBlksTotal",
                heap_blks_scanned AS "heapBlksScanned",
                num_dead_item_ids AS "deadTuplesCollected",
                index_vacuum_count AS "indexPasses"
         FROM pg_stat_progress_vacuum
         WHERE relid = $1::regclass;`,
        [tableName]
      );

      if (progressRes.rows.length > 0 && progressRes.rows[0].indexPasses > 1) {
        console.warn(`[WARN] Table ${tableName} required multiple index passes. Increase threshold frequency.`);
      }
      await client.query('COMMIT;');
    } catch (err) {
      await client.query('ROLLBACK;');
      throw err;
    }
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres Realtime Inspector MCP
Inspects dynamic query performance, autovacuum activity, and pg_stat_progress_vacuum metrics inside live PostgreSQL 17 instances.
Recommended Agent Skills (askills.dev)

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

PostgreSQL Memory Footprint Auditor
Calculates OS memory headroom, buffer allocation limits, and TidStore radix tree safety margins under concurrent autovacuum worker load.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming

PostgreSQL 17 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore), lifting the legacy 1GB dead-tuple indexing limitation. Infrastructure orchestration and database access layers must dynamically account for concurrent autovacuum worker memory and radix-tree overhead, enforcing per-table scale factors and bounded maintenance budgets to prevent container OOMs and transaction ID wraparound under heavy ingest. 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 Radix Allocation and Static Maintenance Memory Leading to OOM or Wraparound Halts 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 Radix Allocation and Static Maintenance Memory Leading to OOM or Wraparound HaltsCritical
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 bound dynamic autovacuum_work_mem and manual maintenance_work_mem to ensure (autovacuum_max_workers * autovacuum_work_mem) <= (Available Container RAM - OS & Shared Buffers Reservation).

Engineers MUST ALWAYS bound dynamic autovacuum_work_mem and manual maintenance_work_mem to ensure (autovacuum_max_workers * autovacuum_work_mem) <= (Available Container RAM - OS & Shared Buffers Reservation).

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 rely on PostgreSQL 16 legacy 1GB dead-tuple array sizing heuristics

Engineers MUST NOT rely on PostgreSQL 16 legacy 1GB dead-tuple array sizing heuristics; PostgreSQL 17 radix trees eliminate the 1GB cap but consume variable memory based on dead-tuple sparsity and clustering.

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

Code executing ingestion pipeline vacuums MUST inspect pg_stat_progress_vacuum to track dead_tuples_collected against radix tree memory limits, failing fast if index_passes exceed 0 during bulk loads.

Code executing ingestion pipeline vacuums MUST inspect pg_stat_progress_vacuum to track dead_tuples_collected against radix tree memory limits, failing fast if index_passes exceed 0 during bulk loads.

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

Services orchestrating high-throughput ingestion MUST dynamically tune autovacuum_vacuum_scale_factor to 0 and rely exclusively on absolute autovacuum_vacuum_threshold to trigger deterministic, single-pass index sweeps before tuple counts exceed the radix tree target budget.

Services orchestrating high-throughput ingestion MUST dynamically tune autovacuum_vacuum_scale_factor to 0 and rely exclusively on absolute autovacuum_vacuum_threshold to trigger deterministic, single-pass index sweeps before tuple counts exceed the radix tree target budget.

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

Components NEVER execute raw maintenance scripts that raise maintenance_work_mem globally

Components NEVER execute raw maintenance scripts that raise maintenance_work_mem globally; any transient elevation MUST be scoped strictly to a localized transaction session and verified against cgroup v2 memory limits.

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 Radix Tree Memory Accounting Invariant for Adaptive Vacuuming critical for AI coding assistants?

PostgreSQL 17 replaces flat dead-tuple arrays with an adaptive radix tree (TidStore), lifting the legacy 1GB dead-tuple indexing limitation. Infrastructure orchestration and database access layers must dynamically account for concurrent autovacuum worker memory and radix-tree overhead, enforcing per-table scale factors and bounded maintenance budgets to prevent container OOMs and transaction ID wraparound under heavy ingest. 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 Radix Allocation and Static Maintenance Memory Leading to OOM or Wraparound Halts) 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