Synchronization Patterns for Modern Distributed Systems: Beyond Eventual Consistency

-- postgresql.conf
wal_level = logical
max_replication_slots = 5

CREATE PUBLICATION sync_pub FOR ALL TABLES;

{
  "source": "postgres",
  "table": "orders",
  "op": "UPDATE",
  "lsn": 4829103,
  "payload": {
    "id": "ord_8839",
    "sta

tus": "shipped", "updated_at": "2024-03-12T14:22:00Z", "_version": 7 } }

#### Step 3: Build the Sync Coordinator (TypeScript)
The coordinator consumes the stream, validates schema, deduplicates, and routes to sinks.

```typescript
import { Kafka, Consumer, EachMessagePayload } from 'kafkajs';
import { Redis } from 'ioredis';

interface SyncEvent {
  source: string;
  table: string;
  op: 'INSERT' | 'UPDATE' | 'DELETE';
  lsn: number;
  payload: Record<string, any>;
  _version: number;
}

export class SyncCoordinator {
  private consumer: Consumer;
  private dedupCache: Redis;
  private readonly DEDUP_TTL = 86400; // 24h

  constructor(kafkaBroker: string, redisUrl: string) {
    const kafka = new Kafka({ clientId: 'sync-coordinator', brokers: [kafkaBroker] });
    this.consumer = kafka.consumer({ groupId: 'sync-workers' });
    this.dedupCache = new Redis(redisUrl);
  }

  async start() {
    await this.consumer.connect();
    await this.consumer.subscribe({ topic: 'db-changes', fromBeginning: false });

    await this.consumer.run({
      eachMessage: async ({ message }: EachMessagePayload) => {
        if (!message.value) return;

        const event: SyncEvent = JSON.parse(message.value.toString());
        const dedupKey = `sync:${event.source}:${event.table}:${event.lsn}`;

        // Idempotency guard
        const exists = await this.dedupCache.set(dedupKey, '1', 'EX', this.DEDUP_TTL, 'NX');
        if (!exists) {
          console.debug(`Duplicate skipped: ${dedupKey}`);
          return;
        }

        await this.applyChange(event);
      }
    });
  }

  private async applyChange(event: SyncEvent) {
    switch (event.op) {
      case 'INSERT':
      case 'UPDATE':
        await this.upsertToTarget(event.table, event.payload, event._version);
        break;
      case 'DELETE':
        await this.softDeleteFromTarget(event.table, event.payload.id);
        break;
    }
  }

  private async upsertToTarget(table: string, payload: Record<string, any>, version: number) {
    // Replace with actual DB/ORM call
    // Ensure conditional update: WHERE _version < ${version}
    console.log(`Upserting ${table} v${version}:`, payload);
  }

  private async softDeleteFromTarget(table: string, id: string) {
    console.log(`Soft deleting ${table}:${id}`);
  }

  async stop() {
    await this.consumer.disconnect();
  }
}

Step 4: Implement Version-Gated Application

Target systems must reject stale writes. Use optimistic concurrency control:

UPDATE orders 
SET status = $1, updated_at = $2, _version = _version + 1 
WHERE id = $3 AND _version < $4;

If rows affected = 0, the event is stale. Route to a reconciliation queue or log for manual audit.

Step 5: Monitor Sync Health

Expose three critical metrics:

• sync_lag_seconds: Difference between source LSN timestamp and consumer apply time
• sync_duplicate_rate: Ratio of skipped events to total consumed
• sync_deadletter_count: Events failing version check or schema validation

Export to Prometheus/Grafana. Alert on lag > 30s or duplicate rate > 2%.

---

Pitfall Guide

1. Assuming Exactly-Once Delivery Without Idempotency

Message brokers guarantee at-least-once delivery. Network partitions, consumer rebalances, and broker failovers guarantee duplicates. Without deduplication keys (LSN/offset + operation type), sinks apply mutations twice, corrupting state. Fix: Store processed offsets in a fast cache (Redis, Memcached) or use broker transactional offsets. Always gate application on idempotency.

2. Synchronous Dual-Writes for Consistency

Writing to two databases in the same request appears simple but creates tight coupling. If the second write fails, the first remains committed. Rollback requires compensating transactions, which introduce latency and failure modes. Fix: Use async CDC + stream. Accept eventual consistency. Design sinks to handle out-of-order or duplicate events gracefully.

3. Ignoring Schema Evolution in Sync Streams

Adding a column, renaming a field, or changing a type breaks consumers that expect a fixed schema. Silent failures occur when JSON payloads lack versioning or type hints. Fix: Embed _schema_version or use a schema registry (Avro, Protobuf, JSON Schema). Route unknown versions to a dead-letter queue for transformation.

4. Unbounded Retry Queues Causing Memory Leaks

When a sink fails, naive retry logic queues messages indefinitely. Memory consumption grows linearly until OOM. Fix: Implement exponential backoff with jitter. Cap retry attempts. Move permanently failed events to a dead-letter topic. Monitor queue depth as a primary health signal.

5. Missing Conflict Resolution for Multi-Writer Setups

When multiple sources write to the same entity, last-write-wins based on wall clock is unreliable. Clock skew, network latency, and transaction ordering break deterministic merges. Fix: Use vector clocks, logical timestamps, or CRDTs for collaborative data. For most business domains, enforce single-writer-per-entity with CDC routing.

6. Over-Indexing on Real-Time Sync

Not all data requires sub-second synchronization. Syncing audit logs, configuration snapshots, or historical aggregates in real-time wastes compute and increases failure surface. Fix: Classify data by consistency SLA. Apply CDC only to hot/transactional data. Use batch replication for cold/historical data.

7. No Observability for Sync Lag

Standard APM tracks request latency, not data freshness. Teams discover drift only when users report mismatches. Fix: Instrument source commit timestamps. Measure consumer apply time. Alert on lag thresholds. Expose sync health as a dedicated dashboard.

---

Production Bundle

Action Checklist

• Enable logical replication or CDC on all source databases with retention policy aligned to consumer recovery SLA
• Deploy a durable message broker with partitioned topics and consumer group semantics for sync events
• Implement idempotent consumers using source LSN/offset + operation type as deduplication keys
• Add version-gated application logic to reject stale writes and prevent clock-skew conflicts
• Configure schema versioning or registry integration to handle column/type evolution without consumer crashes
• Set up dead-letter queues for schema mismatches, version conflicts, and permanently failed deliveries
• Export sync lag, duplicate rate, and dead-letter depth to monitoring stack with alerting thresholds
• Document single-writer-per-entity boundaries to eliminate multi-writer conflict resolution complexity

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Multi-region SaaS with user data sync	CDC + Stream	Deterministic ordering, replayability, partition tolerance	Medium infrastructure, low engineering debt
Offline-first mobile app	CRDTs or Local-First Sync	Mathematical convergence, offline write support, conflict-free merge	High cognitive overhead, moderate compute
Internal admin dashboard (low traffic)	Polling or Webhooks	Simplicity, low latency acceptable, minimal failure modes	Low infrastructure, high manual maintenance
Financial ledger or audit trail	CDC + Idempotent Sink + Version Gates	Strict ordering, replayability, compliance-ready audit log	Medium infrastructure, high reliability
Real-time collaborative editing	CRDTs / Operational Transform	Concurrent edits, instant convergence, no central lock	High implementation cost, optimal UX

Configuration Template

# sync-pipeline.config.yaml
source:
  database: postgres
  logical_replication: true
  slots:
    - name: sync_primary
      publication: sync_pub
      tables: [orders, users, inventory]

stream:
  broker: kafka
  topic: db-changes
  partitions: 6
  retention_hours: 168
  schema_registry: true
  format: json

consumer:
  group_id: sync-workers
  deduplication:
    backend: redis
    ttl_seconds: 86400
    key_pattern: "sync:{source}:{table}:{lsn}"
  retry:
    max_attempts: 5
    backoff_base_ms: 1000
    backoff_max_ms: 30000
    jitter: true

sink:
  consistency: eventual
  version_gate: true
  conflict_strategy: source_lsn_priority
  dead_letter:
    enabled: true
    topic: db-changes.dlq
    alert_threshold: 50

monitoring:
  metrics:
    - sync_lag_seconds
    - sync_duplicate_rate
    - sync_deadletter_count
  alerts:
    lag_critical: 30
    duplicate_rate_warning: 0.02
    deadletter_critical: 100

Quick Start Guide

1. Enable CDC on source: Configure logical replication on your primary database and create a publication for target tables. Verify WAL generation with SELECT * FROM pg_logical_slot_peek_binary_changes('sync_primary', null, null);
2. Deploy stream consumer: Run the TypeScript SyncCoordinator against your Kafka/Redpanda cluster. Connect to Redis for deduplication. Validate event consumption with kafkajs consumer logs.
3. Configure sink idempotency: Implement version-gated upserts in your target database. Ensure WHERE _version < $4 prevents stale overwrites. Route conflicts to a reconciliation queue.
4. Instrument observability: Export sync_lag_seconds, sync_duplicate_rate, and sync_deadletter_count to Prometheus. Set up Grafana dashboard and alerting rules. Verify lag stays under 10s under normal load.