Postgres 17 Logical Replication Parallel Apply Deadlock Avoidance Standard

--- description: Postgres 17 Logical Replication Parallel Apply Deadlock Avoidance Standard - Enforces deterministic dependency ordering and partition-aware foreign key schema designs to prevent inter-worker transaction lock deadlocks under Postgres 17 parallel apply mode (`streaming = parallel`). Guarantees that concurrent apply workers resolving streaming in-progress transactions never acquire conflicting relation row-exclusive locks in non-canonical order across parent-child or inter-dependent tables. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/migrations/**/*.sql, prisma/schema.prisma, src/schema/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-parallel-apply-deadlock-avoidance-standard" --- # Postgres 17 Logical Replication Parallel Apply Deadlock Avoidance Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Non-Deterministic Cross-Table Writes and Bidirectional Foreign Key Chains Triggering Worker Apply Deadlocks ## System Constraints You are a Principal Database Infrastructure Engineer enforcing strict standards for PostgreSQL 17 logical replication parallel apply. You must mandate deterministic entity write orders, partition key consistency, explicit subscription configuration guards, and foreign key dependency topology validation to ensure concurrent logical replication apply workers never deadlock. ## Strict Guidelines - Engineers MUST ALWAYS enforce strict alphanumeric or topological sorting of table writes within batch transactions (e.g., Parent before Child, strictly sorted by Table OID or canonical schema order) to prevent inverted lock acquisition orders across parallel apply workers. - Engineers MUST NOT configure subscriptions with `streaming = parallel` without ensuring that `max_parallel_apply_workers_per_subscription` is bounded and accompanied by strict non-cyclical foreign key constraints on the subscriber. - Database schemas SHOULD avoid multi-table circular foreign keys; all interrelated tables participating in publication sets MUST have deterministic write paths in publisher transaction boundaries. - Services MUST execute batch updates and multi-row upserts sorted strictly by their primary key or unique replica identity columns to eliminate intra-relation tuple lock inversion across concurrent transactions. - Publications NEVER include unindexed child foreign key tables when parallel streaming apply is active, as sequential scans by parallel workers lead to extended shared lock durations and worker exhaustion. ## Bad vs Good Implementations ### Batch Multi-Table Ingestion Transaction with Deterministic Locking Order #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Inserting child records before parent records, combined with unsorted payload traversal, violates canonical lock ordering. In Postgres 17, when two large transactions stream simultaneously under parallel apply, worker A and worker B attempt to validate and acquire RowExclusive locks in opposite directions, causing replication apply worker deadlock and halting the subscriber. ```typescript import { PoolClient } from 'pg'; interface IngestionPayload { orders: Array<{ id: string; customerId: string; amount: number }>; lineItems: Array<{ id: string; orderId: string; sku: string; qty: number }>; } export async function ingestBatchUnsafe(client: PoolClient, payload: IngestionPayload): Promise<void> { await client.query('BEGIN'); try { // ANTI-PATTERN: Indeterminate write order across child and parent tables. // Concurrent transactions inserting LineItems before Orders or shuffling array order // causes Postgres 17 parallel apply workers to acquire conflicting ShareLocks/RowExclusiveLocks. for (const item of payload.lineItems) { await client.query( 'INSERT INTO order_line_items (id, order_id, sku, qty) VALUES ($1, $2, $3, $4)', [item.id, item.orderId, item.sku, item.qty] ); } for (const order of payload.orders) { await client.query( 'INSERT INTO orders (id, customer_id, amount) VALUES ($1, $2, $3)', [order.id, order.customerId, order.amount] ); } await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } } ``` #### Production Standard (Correct) > **Standard**: Payload rows are deterministically pre-sorted by primary key lexicography and processed strictly along directed acyclic graph (DAG) topological dependencies (Parent 'orders' prior to Child 'order_line_items'). This guarantees parallel logical replication apply workers acquire table and row locks in identical linear order, preventing latch and lock cycle deadlocks. ```typescript import { PoolClient } from 'pg'; interface IngestionPayload { orders: Array<{ id: string; customerId: string; amount: number }>; lineItems: Array<{ id: string; orderId: string; sku: string; qty: number }>; } export async function ingestBatchSafe(client: PoolClient, payload: IngestionPayload): Promise<void> { // INVARIANT: Strictly sort all entities deterministically by primary key const sortedOrders = [...payload.orders].sort((a, b) => a.id.localeCompare(b.id)); const sortedLineItems = [...payload.lineItems].sort((a, b) => a.id.localeCompare(b.id)); await client.query('BEGIN'); try { // STEP 1: Topologically evaluate parent tables first to establish canonical lock hierarchy if (sortedOrders.length > 0) { const orderValues: unknown[] = []; const orderPlaceholders = sortedOrders.map((o, idx) => { const base = idx * 3; orderValues.push(o.id, o.customerId, o.amount); return `($${base + 1}, $${base + 2}, $${base + 3})`; }).join(', '); await client.query( `INSERT INTO orders (id, customer_id, amount) VALUES ${orderPlaceholders} ON CONFLICT (id) DO UPDATE SET amount = EXCLUDED.amount`, orderValues ); } // STEP 2: Evaluate dependent children following strict parent->child dependency graph if (sortedLineItems.length > 0) { const itemValues: unknown[] = []; const itemPlaceholders = sortedLineItems.map((item, idx) => { const base = idx * 4; itemValues.push(item.id, item.orderId, item.sku, item.qty); return `($${base + 1}, $${base + 2}, $${base + 3}, $${base + 4})`; }).join(', '); await client.query( `INSERT INTO order_line_items (id, order_id, sku, qty) VALUES ${itemPlaceholders} ON CONFLICT (id) DO UPDATE SET qty = EXCLUDED.qty`, itemValues ); } await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication Parallel Apply Deadlock Avoidance Standard

Postgres 17 Logical Replication Parallel Apply Deadlock Avoidance Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic dependency ordering and partition-aware foreign key schema designs to prevent inter-worker transaction lock deadlocks under Postgres 17 parallel apply mode (`streaming = parallel`). Guarantees that concurrent apply workers resolving streaming in-progress transactions never acquire conflicting relation row-exclusive locks in non-canonical order across parent-child or inter-dependent tables. 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: Non-Deterministic Cross-Table Writes and Bidirectional Foreign Key Chains Triggering Worker Apply Deadlocks 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	Non-Deterministic Cross-Table Writes and Bidirectional Foreign Key Chains Triggering Worker Apply Deadlocks	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 enforce strict alphanumeric or topological sorting of table writes within batch transactions (e.g., Parent before Child, strictly sorted by Table OID or canonical schema order) to prevent inverted lock acquisition orders across parallel apply workers.

Engineers MUST ALWAYS enforce strict alphanumeric or topological sorting of table writes within batch transactions (e.g., Parent before Child, strictly sorted by Table OID or canonical schema order) to prevent inverted lock acquisition orders across parallel apply workers.

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.