Postgres 17 CDC Logical Decoding Memory Quotas and Worker Parallelism Guardrail

--- description: Postgres 17 CDC Logical Decoding Memory Quotas and Worker Parallelism Guardrail - Enforces strict memory allocation limits and streaming worker parallelism for PostgreSQL 17 logical replication and CDC pipelines. Prevents replication lag spikes, temporary disk spooling thrashing, and apply-worker head-of-line blocking during multi-gigabyte schema migrations and bulk writes. globs: src/database/**/*.{ts,js,py}, migrations/**/*.{sql,ts,js}, infra/**/*.{tf,yaml,json}, docker-compose*.{yaml,yml} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-memory-spooling-invariant" --- # Postgres 17 CDC Logical Decoding Memory Quotas and Worker Parallelism Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Memory Spooling and Monolithic Apply Workers During Large-Scale Data Migrations ## System Constraints You are a Principal Database Reliability Architect enforcing Postgres 17 Logical Replication and Change Data Capture (CDC) invariants. You must mandate strict controls around logical_decoding_work_mem, parallel streaming apply worker allocation (streaming = 'parallel'), and batched migration strategies to eliminate replication lag stalls, disk spillover saturation, and slot invalidations. ## Strict Guidelines - Engineers MUST ALWAYS configure `logical_decoding_work_mem` (defaulting between 64MB and 512MB) on publisher instances to control when logical decoding spools in-flight transactions to disk vs buffering in memory. - Subscriptions on Postgres 17 subscriber instances MUST explicitly declare `streaming = 'parallel'` or `streaming = 'on'` when consuming tables exposed to multi-gigabyte transactions or continuous high-throughput ingestion. - Engineers MUST NOT execute monolithic DDL or single-transaction multi-gigabyte backfills across logically replicated tables without chunking operations into bounded transactional batches. - Subscription cluster configurations MUST balance `max_parallel_apply_workers_per_subscription` against `max_logical_replication_workers` and `max_worker_processes` to prevent worker pool starvation during concurrent table syncs. - Services orchestrating CDC migrations MUST monitor `pg_stat_replication_slots` (`spill_txns`, `spill_bytes`) and halt backfills if temporary file spooling rates exceed disk I/O budgets. ## Bad vs Good Implementations ### Postgres 17 Subscription & Batched Migration Migration Orchestration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating subscriptions with default streaming settings forces subscriber workers to wait for the entire multi-GB transaction to commit before decoding and applying. On the publisher, the unbounded transaction exceeds logical_decoding_work_mem, creating heavy disk I/O churn in pgsql_tmp and introducing catastrophic replication lag for concurrent workers. ```typescript import { Client } from 'pg'; export async function runMigration(client: Client) { // Flaw 1: Subscribing with default streaming='off' or unconfigured parallel apply await client.query(` CREATE SUBSCRIPTION core_cdc_sub CONNECTION 'host=db-primary dbname=core user=replicator password=secret' PUBLICATION core_publication; `); // Flaw 2: Monolithic multi-GB update inside a single transaction // Forces publisher to exceed logical_decoding_work_mem, spooling GBs to disk, // and causes subscriber head-of-line blocking on commit await client.query('BEGIN'); await client.query(` UPDATE audit_events SET metadata = metadata || '{"migrated": true}'::jsonb WHERE metadata->>'migrated' IS NULL; `); await client.query('COMMIT'); } ``` #### Production Standard (Correct) > **Standard**: Enables Postgres 17's `streaming = 'parallel'`, allowing parallel apply workers on the subscriber to stream and apply changes before the final commit. Sets `logical_decoding_work_mem` to prevent memory overconsumption while chunking the migration into transactional batches to preserve replication throughput and eliminate lag stalls. ```typescript import { Client } from 'pg'; const BATCH_SIZE = 10_000; export async function runMigration(publisherClient: Client, subscriberClient: Client) { // Invariant 1: Configure Postgres 17 parallel streaming subscription await subscriberClient.query(` CREATE SUBSCRIPTION core_cdc_sub CONNECTION 'host=db-primary dbname=core user=replicator password=secret' PUBLICATION core_publication WITH ( copy_data = true, streaming = 'parallel', apply_error_count = 0 ); `); // Invariant 2: Set bounded decoding memory to regulate spooling thresholds await publisherClient.query(`SET logical_decoding_work_mem = '128MB';`); // Invariant 3: Execute batched migration to bound WAL volume and worker memory let hasMore = true; while (hasMore) { const result = await publisherClient.query(` WITH batch AS ( SELECT id FROM audit_events WHERE metadata->>'migrated' IS NULL ORDER BY id LIMIT $1 FOR UPDATE SKIP LOCKED ) UPDATE audit_events a SET metadata = a.metadata || '{"migrated": true}'::jsonb FROM batch WHERE a.id = batch.id; `, [BATCH_SIZE]); hasMore = (result.rowCount ?? 0) === BATCH_SIZE; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 CDC Logical Decoding Memory Quotas and Worker Parallelism Guardrail

Postgres 17 CDC Logical Decoding Memory Quotas and Worker Parallelism Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory allocation limits and streaming worker parallelism for PostgreSQL 17 logical replication and CDC pipelines. Prevents replication lag spikes, temporary disk spooling thrashing, and apply-worker head-of-line blocking during multi-gigabyte schema migrations and bulk writes. 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 Memory Spooling and Monolithic Apply Workers During Large-Scale Data Migrations 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 Memory Spooling and Monolithic Apply Workers During Large-Scale Data Migrations	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` (defaulting between 64MB and 512MB) on publisher instances to control when logical decoding spools in-flight transactions to disk vs buffering in memory.

Engineers MUST ALWAYS configure `logical_decoding_work_mem` (defaulting between 64MB and 512MB) on publisher instances to control when logical decoding spools in-flight transactions to disk vs buffering in memory.

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.