Postgres 17 Active-Active Logical Replication Conflict Resolution Guardrail

--- description: Postgres 17 Active-Active Logical Replication Conflict Resolution Guardrail - Enforces deterministic origin-filtering and automated timestamp-based last-write-wins (LWW) resolution across bi-directional Postgres 17 replication topologies. Prevents infinite update looping, replication worker stalls, and silent regional data divergence. Mandates origin tracking, immutable monotonically increasing clock vectors, and trigger-enforced row convergence. globs: migrations/**/*.sql, src/db/**/*.ts, src/database/**/*.ts, infra/**/*.sql, schema/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-active-active-conflict-resolution-guardrail" --- # Postgres 17 Active-Active Logical Replication Conflict Resolution Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Naive Bi-Directional Subscriptions Lacking Origin Filtering and Deterministic LWW Conflict Handlers ## System Constraints You are a Principal Database Infrastructure Engineer enforcing strict standards for PostgreSQL 17 active-active logical replication. You must enforce origin-filtering (origin = 'none') on all bi-directional subscriptions to eliminate infinite replication echo loops. You must guarantee that every replicated table possesses an immutable commit timestamp or physical clock vector (`updated_at` with microsecond precision), a deterministic tie-breaker (e.g., node priority or cluster UUID), and a BEFORE UPDATE trigger or logical replication conflict resolver configured to discard stale incoming writes. Reject any schema migration or subscription configuration missing origin-filtering, primary key replica identity, or deterministic conflict resolution. ## Strict Guidelines - Engineers MUST ALWAYS create Postgres 17 subscriptions with `origin = 'none'` in multi-region active-active topologies to prevent infinite replication reflection. - Engineers MUST ALWAYS configure `REPLICA IDENTITY FULL` or specify an immutable primary key with microsecond-precision `updated_at` (TIMESTAMPTZ) and deterministic tie-breaker (e.g., `origin_node_id INT`) columns. - Code MUST NEVER execute raw UPDATE operations that omit explicit monotonic timestamp generation or suppress the active replication origin. - Services MUST implement deterministic Last-Write-Wins (LWW) via Postgres 17 replication conflict handlers or `BEFORE UPDATE OR INSERT` triggers using `pg_trigger_depth() = 0` conditional logic. - Migrations MUST NEVER create unpartitioned tables participating in bi-directional logical replication without explicit conflict logging or dead-letter audit mechanisms. ## Bad vs Good Implementations ### Bi-Directional Subscription and Deterministic LWW Migration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a subscription without origin = 'none' causes incoming replicated transactions to be re-published back to the source node, generating an infinite replication loop. Lacking REPLICA IDENTITY and a deterministic LWW trigger causes subscription worker halt on write conflicts. ```sql -- Anti-pattern: Naive subscription setup in Postgres 17 -- Lacks origin filtering (causes infinite ping-pong loops) -- Lacks deterministic tie-breaker and timestamp-based conflict suppression CREATE TABLE user_profiles ( user_id UUID PRIMARY KEY, display_name TEXT NOT NULL, bio TEXT, updated_at TIMESTAMP DEFAULT clock_timestamp() ); -- Region B subscribing to Region A without origin filtering CREATE SUBSCRIPTION sub_region_a CONNECTION 'host=region-a.internal dbname=app user=rep_user' PUBLICATION pub_user_profiles; -- Conflict on concurrent update will crash the logical replication worker ``` #### Production Standard (Correct) > **Standard**: Setting origin = 'none' ensures the subscription worker only replicates locally originated changes, eliminating echo loops. The BEFORE UPDATE trigger enforces a deterministic LWW policy with a node ID tie-breaker, preventing replication worker halts on concurrent writes. ```sql -- Production Invariant: Postgres 17 origin-filtered bi-directional replication with LWW trigger CREATE TABLE user_profiles ( user_id UUID PRIMARY KEY, display_name TEXT NOT NULL, bio TEXT, updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(), origin_node_id SMALLINT NOT NULL ); ALTER TABLE user_profiles REPLICA IDENTITY FULL; CREATE OR REPLACE FUNCTION resolve_user_profiles_lww() RETURNS TRIGGER AS $$ BEGIN -- When replication worker applies changes, enforce Last-Write-Wins IF (NEW.updated_at < OLD.updated_at) THEN -- Incoming write is older than local write; discard incoming payload RETURN NULL; ELSIF (NEW.updated_at = OLD.updated_at) THEN -- Deterministic tie-breaker on identical timestamps IF (NEW.origin_node_id < OLD.origin_node_id) THEN RETURN NULL; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_user_profiles_lww BEFORE UPDATE ON user_profiles FOR EACH ROW EXECUTE FUNCTION resolve_user_profiles_lww(); -- Postgres 17: origin = 'none' stops forwarding already-replicated rows CREATE SUBSCRIPTION sub_region_a CONNECTION 'host=region-a.internal dbname=app user=rep_user' PUBLICATION pub_user_profiles WITH (origin = 'none', copy_data = false); ``` ### TypeScript Data Layer: Safe Replication Origin Propagation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Client-generated timestamps subject the system to client clock skew, violating monotonicity. Omitting node identity and origin tracking leads to arbitrary overwrites during active-active synchronization. ```typescript import { sql } from 'kysely'; import { db } from './database'; // Anti-pattern: Untracked update omitting origin node and monotonic time verification export async function updateUserBio(userId: string, bio: string) { return await db .updateTable('user_profiles') .set({ bio, // Database clock may skew; no origin awareness updated_at: new Date(), }) .where('user_id', '=', userId) .executeTakeFirst(); } ``` #### Production Standard (Correct) > **Standard**: Forces use of database clock_timestamp() for monotonic microsecond precision and binds the known local cluster origin_node_id, satisfying the active-active LWW trigger constraints. ```typescript import { sql } from 'kysely'; import { db } from './database'; const LOCAL_NODE_ID = Number(process.env.POSTGRES_NODE_ID ?? 1); // Production standard: Server-authoritative microsecond timestamp with node tie-breaker export async function updateUserBio(userId: string, bio: string) { return await db .updateTable('user_profiles') .set({ bio, origin_node_id: LOCAL_NODE_ID, updated_at: sql`clock_timestamp()`, }) .where('user_id', '=', userId) .returning(['user_id', 'updated_at', 'origin_node_id']) .executeTakeFirstOrThrow(); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Active-Active Logical Replication Conflict Resolution Guardrail

Postgres 17 Active-Active Logical Replication Conflict Resolution Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic origin-filtering and automated timestamp-based last-write-wins (LWW) resolution across bi-directional Postgres 17 replication topologies. Prevents infinite update looping, replication worker stalls, and silent regional data divergence. Mandates origin tracking, immutable monotonically increasing clock vectors, and trigger-enforced row convergence. 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: Naive Bi-Directional Subscriptions Lacking Origin Filtering and Deterministic LWW Conflict Handlers 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	Naive Bi-Directional Subscriptions Lacking Origin Filtering and Deterministic LWW Conflict Handlers	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 create Postgres 17 subscriptions with `origin = 'none'` in multi-region active-active topologies to prevent infinite replication reflection.

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.