Postgres 17 Parallel Apply Slot-Affinity Invariant for High-Write Ingestion

--- description: Postgres 17 Parallel Apply Slot-Affinity Invariant for High-Write Ingestion - Enforces deterministic entity hash partitioning across Postgres 17 logical replication subscriptions and parallel apply workers. Prevents inter-worker relational deadlocks on foreign key constraints and share-lock contention during high-throughput distributed CDC ingestion. Guarantees that interdependent transactional trees route to deterministic parallel stream channels. globs: src/ingestion/**/*.ts, src/replication/**/*.ts, src/database/**/*.ts, src/workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-parallel-apply-slot-affinity" --- # Postgres 17 Parallel Apply Slot-Affinity Invariant for High-Write Ingestion > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Non-deterministic worker distribution of transactional CDC events causing cross-worker foreign-key deadlocks in parallel apply pipelines. ## System Constraints You are a Principal Backend Systems Architect enforcing strict Postgres 17 Logical Replication Parallel Apply invariants. In high-concurrency CDC ingestion architectures, parallel apply workers operating on unpartitioned streams trigger catastrophic distributed deadlocks when evaluating foreign key integrity across concurrent transactions. You must strictly mandate that all parallel replication slots and downstream ingestion pipelines enforce deterministic entity-root hash affinity, ensuring all mutations within an aggregate graph are sequentially processed by the exact same apply worker thread. ## Strict Guidelines - Engineers MUST ALWAYS compute an affinity hash using the root aggregate entity ID (e.g., parent foreign key or tenant partition key) before dispatching transactional CDC batches to parallel apply workers. - Engineers MUST NOT round-robin or randomly assign streaming logical replication chunks across parallel worker threads when foreign key dependencies exist between tables. - Replication subscription setup DDL SHOULD explicitly configure `streaming = parallel` in conjunction with table publication filters partitioned by structural hash keys. - Services MUST serialize transaction groups referencing the same foreign key roots onto an isolated slot affinity queue to prevent `LockTupleExclusive` and `LockTupleShare` circular waits. - Components NEVER acknowledge logical replication sequence LSNs out-of-order across parallel slot-affinity worker partitions. ## Bad vs Good Implementations ### Dispatching Replication Events to Parallel Apply Workers with Slot Affinity #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Round-robin assignment routes parent and child updates across distinct concurrent worker processes. During parallel apply, concurrent evaluation of foreign keys acquires conflicting row share locks and exclusive tuple locks out of order, generating cyclic dependency deadlocks in Postgres 17 apply workers. ```typescript import { EventEmitter } from 'node:events'; interface ReplicationEvent { txId: string; table: string; parentEntityId: string; payload: Record<string, unknown>; } export class CDCApplyManager { private workers: Array<(event: ReplicationEvent) => Promise<void>>; private nextWorkerIdx = 0; constructor(workers: Array<(event: ReplicationEvent) => Promise<void>>) { this.workers = workers; } // ANTI-PATTERN: Round-robin distribution of related transactional records // causes concurrent workers to process foreign key child/parent pairs simultaneously, // leading to Postgres parallel apply deadlocks (ShareLock vs ExclusiveLock on FK checks). public async dispatchEvent(event: ReplicationEvent): Promise<void> { const worker = this.workers[this.nextWorkerIdx]; this.nextWorkerIdx = (this.nextWorkerIdx + 1) % this.workers.length; await worker(event); } } ``` #### Production Standard (Correct) > **Standard**: The good implementation enforces slot-affinity partitioning by hashing the root entity identifier (`parentEntityId`). All interdependent parent-child rows map strictly to the same execution slot queue, preventing parallel apply workers from interleaving foreign key checks and eliminating database lock escalation deadlocks. ```typescript import { createHash } from 'node:crypto'; interface ReplicationEvent { txId: string; table: string; parentEntityId: string; payload: Record<string, unknown>; } export class CDCApplyManager { private workers: ReadonlyArray<(event: ReplicationEvent) => Promise<void>>; private queues: Map<number, Promise<void>>; constructor(workers: ReadonlyArray<(event: ReplicationEvent) => Promise<void>>) { if (workers.length === 0) { throw new Error('Worker pool must contain at least one apply worker.'); } this.workers = workers; this.queues = new Map(); } // INVARIANT: Deterministic slot-affinity partition ensures all foreign-key related mutations // route to the same parallel apply worker, eliminating cross-worker locking deadlocks. private computeAffinitySlot(partitionKey: string): number { const hash = createHash('sha256').update(partitionKey).digest(); return hash.readUInt32BE(0) % this.workers.length; } public async dispatchEvent(event: ReplicationEvent): Promise<void> { const slotIndex = this.computeAffinitySlot(event.parentEntityId); const worker = this.workers[slotIndex]; const previousQueueTail = this.queues.get(slotIndex) ?? Promise.resolve(); const currentTask = previousQueueTail .catch(() => {}) // Prevent unhandled rejection chain termination .then(async () => { await worker(event); }); this.queues.set(slotIndex, currentTask); await currentTask; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Parallel Apply Slot-Affinity Invariant for High-Write Ingestion

Postgres 17 Parallel Apply Slot-Affinity Invariant for High-Write Ingestion defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic entity hash partitioning across Postgres 17 logical replication subscriptions and parallel apply workers. Prevents inter-worker relational deadlocks on foreign key constraints and share-lock contention during high-throughput distributed CDC ingestion. Guarantees that interdependent transactional trees route to deterministic parallel stream channels. 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 worker distribution of transactional CDC events causing cross-worker foreign-key deadlocks in parallel apply pipelines. 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 worker distribution of transactional CDC events causing cross-worker foreign-key deadlocks in parallel apply pipelines.	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 compute an affinity hash using the root aggregate entity ID (e.g., parent foreign key or tenant partition key) before dispatching transactional CDC batches to parallel apply workers.

Engineers MUST ALWAYS compute an affinity hash using the root aggregate entity ID (e.g., parent foreign key or tenant partition key) before dispatching transactional CDC batches to 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.