PostgreSQL 17 Parallel Apply Worker Conflict and Deadlock Governor

--- description: PostgreSQL 17 Parallel Apply Worker Conflict and Deadlock Governor - Standardizes PostgreSQL 17 logical replication parallel worker provisioning, conflict detection, and transactional dependency tracking. Enforces deterministic apply concurrency limits, explicit replica identities, and lock ordering to prevent cross-worker deadlocks and out-of-order commit hazards in multi-subscriber clusters. globs: src/db/**/*.ts, src/replication/**/*.ts, migrations/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-parallel-apply-deadlock-governor" --- # PostgreSQL 17 Parallel Apply Worker Conflict and Deadlock Governor > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unconstrained Parallel Apply Allocation and Ambiguous Replica Identities Causing Worker Deadlocks and Data Divergence ## System Constraints You are a Principal Database Infrastructure Engineer enforcing PostgreSQL 17 logical replication standards. You mandate explicit parallel apply worker governance, deterministic conflict resolution strategies, foreign key dependency serialization, and strict subscription configuration invariants to eliminate deadlocks and replication stalls. ## Strict Guidelines - Engineers MUST ALWAYS verify that all replicated tables define an explicit REPLICA IDENTITY (USING INDEX or FULL) prior to provisioning parallel apply subscriptions. - Engineers MUST NOT configure subscriptions with unconstrained 'max_parallel_apply_workers_per_subscription' without tuning 'max_sync_workers_per_subscription' and connection pool limits. - Services MUST enable 'streaming = parallel' only on subscriptions whose publishers guarantee conflict-free key partitioning or isolated table publications. - Code SHOULD inspect 'pg_stat_subscription_stats' and configure automated alert triggers for non-zero 'confl_apply', 'confl_unique', or 'confl_foreign_key' counters. - Components NEVER execute concurrent out-of-band writes to subscriber tables managed by active parallel apply workers without explicit origin tracking ('origin = any' or custom filter). ## Bad vs Good Implementations ### Configuring PostgreSQL 17 Parallel Apply Subscription and Dependency Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Allocates 16 parallel apply workers blindly without verifying subscriber table replica identities or checking foreign key dependencies. When parallel transactions modify interrelated parent-child entities or trigger unique constraints, parallel apply workers deadlock on shared row locks and crash the apply process, stalling replication indefinitely. ```typescript import { Client } from 'pg'; export async function setupSubscription(client: Client, subName: string, connString: string) { // Anti-Pattern: Unchecked parallel apply, unbounded workers, blind apply // Missing REPLICA IDENTITY checks, no conflict logging, and vulnerable to cross-worker deadlocks await client.query(` CREATE SUBSCRIPTION ${subName} CONNECTION '${connString}' PUBLICATION core_publication WITH (copy_data = true, streaming = parallel, max_parallel_apply_workers_per_subscription = 16); `); } ``` #### Production Standard (Correct) > **Standard**: Validates that target tables do not rely on default replica identities, bounds parallel workers within a resilient ceiling, ensures stream concurrency isolation, and sets up transaction tracking to diagnose and resolve logical conflicts deterministically. ```typescript import { Client } from 'pg'; interface SubscriptionConfig { subscriptionName: string; publicationName: string; connectionString: string; maxParallelWorkers: number; } export async function setupGovernedSubscription( client: Client, config: SubscriptionConfig ): Promise<void> { const { subscriptionName, publicationName, connectionString, maxParallelWorkers } = config; // 1. Verify all published tables have definitive REPLICA IDENTITY const identityCheckQuery = ` SELECT relname, relreplident FROM pg_class c JOIN pg_publication_tables pt ON c.relname = pt.tablename WHERE pt.pubname = $1 AND c.relreplident = 'd'; `; const missingIdentities = await client.query(identityCheckQuery, [publicationName]); if (missingIdentities.rows.length > 0) { const tableNames = missingIdentities.rows.map(r => r.relname).join(', '); throw new Error( `Replication aborted: Tables [${tableNames}] lack explicit REPLICA IDENTITY (FULL or UNIQUE INDEX).` ); } // 2. Bound workers defensively to prevent lock inversions (cap between 1 and 4 for parallel apply) const safeWorkers = Math.min(Math.max(maxParallelWorkers, 1), 4); // 3. Create subscription with explicit origin isolation, parallel streaming, and worker ceilings await client.query(` CREATE SUBSCRIPTION "${subscriptionName}" CONNECTION $1 PUBLICATION "${publicationName}" WITH ( copy_data = true, streaming = parallel, max_parallel_apply_workers_per_subscription = ${safeWorkers}, origin = any, synchronous_commit = 'on' ); `, [connectionString]); // 4. Configure PostgreSQL 17 conflict logging and tracking await client.query(` ALTER SYSTEM SET log_replication_commands = on; ALTER SYSTEM SET track_commit_timestamp = on; `); await client.query('SELECT pg_reload_conf();'); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Parallel Apply Worker Conflict and Deadlock Governor

PostgreSQL 17 Parallel Apply Worker Conflict and Deadlock Governor defines an industrial-grade behavioral standard for backend engineering. Standardizes PostgreSQL 17 logical replication parallel worker provisioning, conflict detection, and transactional dependency tracking. Enforces deterministic apply concurrency limits, explicit replica identities, and lock ordering to prevent cross-worker deadlocks and out-of-order commit hazards in multi-subscriber clusters. 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: Unconstrained Parallel Apply Allocation and Ambiguous Replica Identities Causing Worker Deadlocks and Data Divergence 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	Unconstrained Parallel Apply Allocation and Ambiguous Replica Identities Causing Worker Deadlocks and Data Divergence	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 verify that all replicated tables define an explicit REPLICA IDENTITY (USING INDEX or FULL) prior to provisioning parallel apply subscriptions.

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.