Postgres 17 Streaming Parallel Logical Replication Worker Memory Boundary

--- description: Postgres 17 Streaming Parallel Logical Replication Worker Memory Boundary - Enforces strict memory boundaries and worker concurrency quotas for PostgreSQL 17 parallel apply background workers during streaming logical replication. Prevents node-level OOM-killer cascade failures by explicitly capping logical_decoding_work_mem, constraining max_parallel_apply_workers_per_subscription, and configuring disk-spill fallback parameters during multi-gigabyte transactional changesets. globs: src/**/*.ts, migrations/**/*.sql, infra/**/*.tf, docker/**/*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-parallel-logical-replication-worker-memory-boundary" --- # Postgres 17 Streaming Parallel Logical Replication Worker Memory Boundary > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Parallel Apply Workers with Uncapped Buffer Memory Triggering OOM-Killer Failures ## System Constraints You are a Principal Software Architect enforcing rigorous memory safety boundaries for PostgreSQL 17 streaming parallel logical replication. You must guarantee that all subscriber node definitions, migration scripts, and database infrastructure configurations strictly regulate `streaming = parallel`, `max_parallel_apply_workers_per_subscription`, and `logical_decoding_work_mem` to prevent parallel workers from consuming unbounded RAM and triggering host-level OOM terminations. ## Strict Guidelines - Engineers MUST ALWAYS configure `logical_decoding_work_mem` on publisher nodes with an explicit cap (e.g., 64MB-256MB) to force serialization spillover to disk before memory saturates during large transactional decodings. - Engineers MUST NOT configure `CREATE SUBSCRIPTION ... WITH (streaming = parallel)` without explicitly setting `max_parallel_apply_workers_per_subscription` to a calibrated value derived from total available RAM and `work_mem` allocations. - Code SHOULD dynamically validate that subscriber host available memory satisfies: `available_ram > (max_parallel_apply_workers_per_subscription * max_sync_workers_per_subscription * maintenance_work_mem)` before executing batch replication orchestrations. - Services MUST ensure that long-running transactions are either committed in discrete batches or configured with `streaming = on` fallback if parallel apply workers exhibit lock contention or buffer thrashing. - Components NEVER execute raw DDL enabling unthrottled logical replication subscriptions without automated health probes monitoring `pg_stat_subscription_stats` for apply worker error cascades and restart loops. ## Bad vs Good Implementations ### Subscription Provisioning and Parameter Boundary Enforcement #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Enabling parallel streaming replication without capping worker limits or validating memory allows Postgres 17 to spawn the default number of workers per subscription alongside leader apply processes. Under multi-gigabyte transactions, concurrent in-memory change reassembly across parallel workers exceeds host RAM, causing the Linux OOM-killer to terminate the PostgreSQL primary postmaster process. ```typescript import { Client } from 'pg'; export async function setupSubscription(client: Client, subName: string, pubConn: string): Promise<void> { // Anti-pattern: Enabling parallel streaming with default unbounded apply workers // and no pre-flight memory validation on Postgres 17 await client.query(` CREATE SUBSCRIPTION ${subName} CONNECTION '${pubConn}' PUBLICATION massive_sales_pub WITH ( copy_data = true, streaming = parallel ); `); } ``` #### Production Standard (Correct) > **Standard**: Explicitly binds `logical_decoding_work_mem` to force changesets exceeding the threshold to serialize to disk, limits parallel worker counts through subscription constraints, sanitizes identifiers, and configures error limits to prevent runaway OOM crash loops. ```typescript import { Client } from 'pg'; interface ReplicationMemoryProfile { maxParallelWorkers: number; decodingWorkMemMb: number; minFreeHostRamMb: number; } export async function setupSubscription( client: Client, subName: string, pubConn: string, profile: ReplicationMemoryProfile ): Promise<void> { // Invariant 1: Ensure logical decoding work memory boundary is bounded on the session/system await client.query('SET logical_decoding_work_mem = $1', [`${profile.decodingWorkMemMb}MB`]); // Invariant 2: Explicitly cap parallel apply workers to bounded concurrency quota await client.query('SET max_parallel_apply_workers_per_subscription = $1', [profile.maxParallelWorkers]); // Invariant 3: Sanitize identifier and establish bounded parallel subscription const safeSubName = subName.replace(/[^a-zA-Z0-9_]/g, ''); await client.query(` CREATE SUBSCRIPTION ${safeSubName} CONNECTION $1 PUBLICATION massive_sales_pub WITH ( copy_data = true, streaming = parallel, origin = any, apply_error_count_limit = 5 ); `, [pubConn]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Streaming Parallel Logical Replication Worker Memory Boundary

Postgres 17 Streaming Parallel Logical Replication Worker Memory Boundary defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory boundaries and worker concurrency quotas for PostgreSQL 17 parallel apply background workers during streaming logical replication. Prevents node-level OOM-killer cascade failures by explicitly capping logical_decoding_work_mem, constraining max_parallel_apply_workers_per_subscription, and configuring disk-spill fallback parameters during multi-gigabyte transactional changesets. 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 Apply Workers with Uncapped Buffer Memory Triggering OOM-Killer Failures 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 Apply Workers with Uncapped Buffer Memory Triggering OOM-Killer Failures	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 configure `logical_decoding_work_mem` on publisher nodes with an explicit cap (e.g., 64MB-256MB) to force serialization spillover to disk before memory saturates during large transactional decodings.

Engineers MUST ALWAYS configure `logical_decoding_work_mem` on publisher nodes with an explicit cap (e.g., 64MB-256MB) to force serialization spillover to disk before memory saturates during large transactional decodings.

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.