Postgres 17 Logical Replication DDL Isolation and Catalog Bloat Prevention Standard

--- description: Postgres 17 Logical Replication DDL Isolation and Catalog Bloat Prevention Standard - Enforces strict isolation of concurrent index builds and schema migrations away from logical replication decoding streams in PostgreSQL 17. Prevents WAL sender stalls, disk-exhausting WAL buildup, and catalog bloat caused by pinned catalog_xmin snapshots and unconstrained DDL lock acquisitions. globs: migrations/**/*.ts, src/db/migrations/**/*.ts, src/database/migrations/**/*.ts, src/infrastructure/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-ddl-locks-guardrail" --- # Postgres 17 Logical Replication DDL Isolation and Catalog Bloat Prevention Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing Concurrent Index Creation or Unbounded DDL Within Transaction Blocks on Logical Replication Primaries ## System Constraints You are a Principal Database Reliability Engineer and Software Architect enforcing strict PostgreSQL 17 logical replication safety. You must prevent catastrophic WAL accumulation and replication stalls by ensuring that all DDL operations, especially CREATE INDEX CONCURRENTLY, are decoupled from transactional migration wrappers, strictly bounded by lock_timeout, and protected against catalog_xmin starvation. ## Strict Guidelines - Engineers MUST ALWAYS configure migration runners to execute CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY with transaction: false (outside any BEGIN ... COMMIT block). - Engineers MUST set an explicit, aggressive lock_timeout (e.g., '2s') and statement_timeout before running any table-rewriting or lock-acquiring DDL against databases with active logical replication slots. - Engineers MUST NOT bundle multiple heavy DDL operations into a single long-lived transaction, as this pins catalog_xmin and prevents vacuuming of system catalogs like pg_class and pg_attribute. - Code SHOULD inspect pg_replication_slots for active lagging slots before initiating large-scale DDL or asynchronous schema migrations. - Services MUST implement exponential backoff retry loops on LockNotAvailable errors when attempting DDL under concurrent logical decoding workloads. - Components NEVER execute raw DDL without resetting session timeouts back to cluster defaults after execution. ## Bad vs Good Implementations ### Concurrent Index Build and DDL Execution on PostgreSQL 17 with Logical Replication #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing concurrent index builds inside a default transactional migration causes PostgreSQL to abort immediately. Even if executed outside transactions, failing to set lock_timeout causes the subsequent addColumn operation to wait indefinitely for AccessExclusiveLock, queuing incoming queries behind it, stalling logical replication workers, and preventing catalog vacuuming. ```typescript import { MigrationBuilder } from 'node-pg-migrate'; export async function up(pgm: MigrationBuilder): Promise<void> { // FAILS: Bundling CONCURRENTLY inside a transaction block throws a Postgres error, // and holding unconstrained ACCESS EXCLUSIVE locks freezes pg_catalog decoding, // causing walsender lag and unbounded WAL retention on primary. pgm.createIndex('orders', ['customer_id', 'created_at'], { name: 'idx_orders_customer_created', concurrently: true, }); pgm.addColumn('orders', { processed_at: { type: 'timestamp with time zone', notNull: false }, }); } ``` #### Production Standard (Correct) > **Standard**: The good pattern disables migration wrapping transactions, bounds lock acquisition with a 2-second timeout, verifies replication slot health to prevent WAL buildup, executes CREATE INDEX CONCURRENTLY standalone, and resets configuration parameters in a finally block. ```typescript import { ClientBase } from 'pg'; export interface MigrationContext { client: ClientBase; } // Explicitly flag the migration runner to disable automatic BEGIN/COMMIT encapsulation export const transaction = false; export async function up({ client }: MigrationContext): Promise<void> { // 1. Enforce aggressive lock_timeout to prevent queueing behind read/write transactions await client.query('SET lock_timeout = \'2s\';'); await client.query('SET statement_timeout = \'60s\';'); try { // 2. Pre-flight check: prevent DDL if logical replication slots are critically lagging const slotLagCheck = await client.query<{ slot_name: string; lag_bytes: number; }>( `SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)::bigint AS lag_bytes FROM pg_replication_slots WHERE active = true AND plugin IS NOT NULL;` ); const maxAllowedLagBytes = 1024 * 1024 * 1024; // 1 GiB for (const slot of slotLagCheck.rows) { if (slot.lag_bytes > maxAllowedLagBytes) { throw new Error( `Replication slot ${slot.slot_name} has high lag (${slot.lag_bytes} bytes). Aborting DDL to prevent catalog exhaustion.` ); } } // 3. Isolated non-blocking index creation outside transaction block await client.query( 'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created ON orders (customer_id, created_at);' ); } finally { // 4. Reset timeouts to connection pool defaults await client.query('RESET lock_timeout;'); await client.query('RESET statement_timeout;'); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication DDL Isolation and Catalog Bloat Prevention Standard

Postgres 17 Logical Replication DDL Isolation and Catalog Bloat Prevention Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation of concurrent index builds and schema migrations away from logical replication decoding streams in PostgreSQL 17. Prevents WAL sender stalls, disk-exhausting WAL buildup, and catalog bloat caused by pinned catalog_xmin snapshots and unconstrained DDL lock acquisitions. 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: Executing Concurrent Index Creation or Unbounded DDL Within Transaction Blocks on Logical Replication Primaries 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	Executing Concurrent Index Creation or Unbounded DDL Within Transaction Blocks on Logical Replication Primaries	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 migration runners to execute CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY with transaction

Engineers MUST ALWAYS configure migration runners to execute CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY with transaction: false (outside any BEGIN ... COMMIT block).

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.