Postgres 17 Logical Replication Parallel Apply Deterministic Conflict Resolution Invariant

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unordered Mutations and Non-Monotonic Clocks Inducing Parallel Apply Worker Deadlocks and Silent Replica Divergence) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

--- description: Postgres 17 Logical Replication Parallel Apply Deterministic Conflict Resolution Invariant - Enforces strict monotonic commit-timestamp sequencing and canonical key-ordered locking across transactional mutations replicated via PostgreSQL 17 parallel apply workers. Guarantees deterministic convergence and eliminates distributed cross-worker deadlocks in active-active topologies. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/migrations/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-parallel-apply-deterministic-conflict-resolution" --- # Postgres 17 Logical Replication Parallel Apply Deterministic Conflict Resolution Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unordered Mutations and Non-Monotonic Clocks Inducing Parallel Apply Worker Deadlocks and Silent Replica Divergence ## System Constraints You are a Principal Backend Architect enforcing transactional and replication invariants for PostgreSQL 17 Logical Replication with parallel apply enabled (`streaming = parallel`). In parallel apply topologies, separate apply workers process disjoint streaming transactions concurrently. You MUST enforce deterministic primary key sorting before mutation execution to prevent cross-worker lock inversion deadlocks, and mandate monotonic logical sequencing (Hybrid Logical Clocks or strict monotonic commit sequence fencing) to guarantee deterministic conflict resolution and prevent silent state divergence. ## Strict Guidelines - Engineers MUST ALWAYS sort batched entity mutations canonically (e.g., lexicographically by UUID/ID) before executing queries to prevent cross-transaction lock inversion between parallel apply workers. - Engineers MUST ALWAYS include a monotonically increasing revision sequence or Hybrid Logical Clock (HLC) token in replicated table schemas to support deterministic conflict resolution. - Code MUST gate replica writes and updates with monotonic sequence predicates (e.g., `WHERE target.commit_seq < EXCLUDED.commit_seq`) rather than relying on system wall-clock timestamps or transaction arrival order. - Services MUST NOT execute multi-row mutations in non-deterministic iteration orders or rely on unsorted `Promise.all` write fanouts. - Components NEVER execute raw unbounded overwrites without explicit conflict detection predicates on logical replication target tables. ## Bad vs Good Implementations ### Batched State Update with Conflict-Safe Monotonic Ordering #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Mutating rows in random order across transactions causes PostgreSQL 17 parallel apply workers to acquire row-level locks out of order, leading to distributed deadlocks and transaction rollback cascades. Relying on mutable wall-clock timestamps leads to silent replica divergence during out-of-order parallel apply commits. ```typescript import { PoolClient } from 'pg'; interface EntityUpdate { id: string; payload: Record<string, unknown>; } // ANTI-PATTERN: Arbitrary execution order induces cross-worker deadlocks in PG17 parallel apply. // Client-side Date.now() allows silent replica divergence due to wall-clock skew. export async function updateEntitiesBad( client: PoolClient, tenantId: string, updates: EntityUpdate[] ): Promise<void> { for (const update of updates) { await client.query( `UPDATE replicated_entities SET payload = $1, updated_at = $2 WHERE id = $3 AND tenant_id = $4`, [JSON.stringify(update.payload), new Date(), update.id, tenantId] ); } } ``` #### Production Standard (Correct) > **Standard**: Sorting targets by ID ensures strict canonical lock acquisition order, eliminating parallel worker deadlocks. The commit_seq predicate guarantees deterministic, out-of-order-safe conflict resolution across streaming replication workers. ```typescript import { PoolClient } from 'pg'; interface EntityUpdate { id: string; payload: Record<string, unknown>; } // PRODUCTION FIX: Deterministic key-ordered lock acquisition + monotonic commit sequencing invariant. export async function updateEntitiesGood( client: PoolClient, tenantId: string, updates: EntityUpdate[], monotonicCommitSeq: bigint ): Promise<void> { if (updates.length === 0) return; // Invariant 1: Canonical ordering eliminates parallel apply lock inversion deadlocks const sortedUpdates = [...updates].sort((a, b) => a.id.localeCompare(b.id)); for (const update of sortedUpdates) { // Invariant 2: Commit-sequence predicate prevents out-of-order parallel apply worker divergence await client.query( `UPDATE replicated_entities SET payload = $1, commit_seq = $2, updated_at = CLOCK_TIMESTAMP() WHERE id = $3 AND tenant_id = $4 AND commit_seq < $2`, [JSON.stringify(update.payload), monotonicCommitSeq.toString(), update.id, tenantId] ); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication Parallel Apply Deterministic Conflict Resolution Invariant

Postgres 17 Logical Replication Parallel Apply Deterministic Conflict Resolution Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict monotonic commit-timestamp sequencing and canonical key-ordered locking across transactional mutations replicated via PostgreSQL 17 parallel apply workers. Guarantees deterministic convergence and eliminates distributed cross-worker deadlocks in active-active topologies. 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: Unordered Mutations and Non-Monotonic Clocks Inducing Parallel Apply Worker Deadlocks and Silent Replica 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	Unordered Mutations and Non-Monotonic Clocks Inducing Parallel Apply Worker Deadlocks and Silent Replica 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 sort batched entity mutations canonically (e.g., lexicographically by UUID/ID) before executing queries to prevent cross-transaction lock inversion between parallel apply workers.

Engineers MUST ALWAYS sort batched entity mutations canonically (e.g., lexicographically by UUID/ID) before executing queries to prevent cross-transaction lock inversion between 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.