PostgreSQL Logical Replication: AI-Optimized Technical Reference

Configuration Requirements

Critical Settings That Actually Work

• wal_level = logical (REQUIRED): Increases WAL volume by 10-30%, restart required
• max_replication_slots: Set to 2-3x planned subscriptions to avoid connection limits
• max_wal_senders: Match max_replication_slots value
• sync_replication_slots = on (PostgreSQL 17+): Enables slot failover survival

Authentication Setup

• Create dedicated replication user with REPLICATION and CREATE privileges
• Configure pg_hba.conf with replication access
• Use sslmode=require in connection strings (non-negotiable for production)

Critical Failure Modes and Solutions

WAL Disk Space Exhaustion (Production Killer)

Cause: Logical replication slots prevent WAL cleanup until all subscribers catch up
Impact: Server death when disk fills, typically at 3am
Detection: Monitor pg_replication_slots.restart_lsn lag vs current WAL position
Solution:

• Emergency: Drop stuck slots with SELECT pg_drop_replication_slot('slot_name')
• Prevention: Alert on slot lag before disk fills

Tables Without Primary Keys (Performance Destroyer)

Impact: REPLICA IDENTITY FULL sends entire row for every UPDATE
Performance Cost: WAL volume increases 5-6x on busy tables
Solutions:

• Add primary key (preferred)
• Create unique index
• Accept massive WAL overhead

Sequence Drift (Failover Killer)

Cause: Logical replication doesn't sync sequences
Impact: Primary key conflicts on failover to subscriber
Frequency: Happens on every failover
No automatic solution: Manual sequence synchronization required

Resource Requirements and Constraints

Network and Connection Impact

• Each subscription requires dedicated connection to publisher
• Network hiccups cause apply lag that compounds quickly
• Connection limits multiply with number of subscriptions

CPU and Performance

• WAL decoding uses CPU cycles on publisher
• Lighter disk I/O than streaming replication
• Higher CPU usage visible on busy systems with walsender processes

Time Investment

• Setup complexity: Moderate (higher than streaming replication)
• Learning curve: Steep for users coming from streaming replication
• Initial sync: Hours to days on large tables

Version Compatibility and Limitations

Cross-Version Support

• Works between PostgreSQL versions 9.4+
• Major advantage over streaming replication (same version requirement)
• Successfully tested: PostgreSQL 11 → 15, 13 → 16

Unsupported Features

• Schema changes (DDL) don't replicate automatically
• Large objects and TOAST columns not supported
• TRUNCATE not supported before PostgreSQL 11
• No automatic conflict resolution

PostgreSQL 17 Improvements (Released September 26, 2024)

Fixed Critical Issues

• Failover slot synchronization: Slots survive primary failover
• pg_createsubscriber utility: Convert physical standbys without rebuild
• pg_upgrade preservation: Logical replication survives major upgrades

Reliability Assessment

• Early testing shows promise
• Historical PostgreSQL "fixes" have introduced new problems
• Production validation still ongoing

Production Monitoring Requirements

Essential Metrics to Alert On

-- Slot lag monitoring (CRITICAL ALERT)
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as behind
FROM pg_replication_slots WHERE slot_type = 'logical';

-- Apply worker status
SELECT * FROM pg_stat_subscription;

Break-Glass Procedures

• Apply worker restart: ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION
• Emergency slot cleanup: SELECT pg_drop_replication_slot('dead_slot_name')
• Full replication restart: Drop subscription, recreate from scratch

Decision Criteria: When to Use vs Alternatives

Use Case	Logical Replication	Physical Streaming	Assessment
Cross-version migration	✓ Essential	✗ Not supported	Only viable option
Selective table sync	✓ Perfect fit	✗ All-or-nothing	Major advantage
Writable replicas	✓ Fully writable	✗ Read-only	Unique capability
High availability	✗ Complex	✓ Near-instant	Streaming preferred
Multi-region compliance	✓ Row filtering	✗ No filtering	Logical required

Common Implementation Mistakes

Setup Errors

• Using superuser instead of dedicated replication user
• Forgetting to add tables to publication
• Not coordinating DDL changes between publisher and subscriber
• Missing SSL configuration in production

Operational Failures

• No monitoring of slot lag (leads to disk space exhaustion)
• Large transaction batches (crashes apply workers)
• Inadequate connection limits planning
• No backup procedures for stuck replication scenarios

Real-World Performance Impact

WAL Volume Examples

• Normal table with primary key: Minimal overhead
• Table with REPLICA IDENTITY FULL: 5-6x WAL increase on busy systems
• Monitoring graphs show "hockey stick" pattern when misconfigured

Operational Complexity Comparison

• Streaming replication: "Turn on replication and it works"
• Logical replication: "Gotchas, edge cases, and plenty of ways to break things"
• Pain factor: High (lots of moving parts vs medium for streaming)

Emergency Troubleshooting Decision Tree

1. Changes not appearing on subscriber:

   • Check table in publication (90% of cases)
   • Verify publication filters
   • Confirm table has replication identity
   • Check pg_stat_subscription for errors

2. Disk space filling rapidly:

   • Check slot lag immediately
   • Drop inactive/stuck slots
   • Restart failed subscribers

3. Apply workers crashing:

   • Check for unique constraint conflicts
   • Verify data type compatibility
   • Look for large transaction processing

4. Network connectivity issues:

   • Each subscription needs persistent connection
   • Apply lag compounds quickly on network hiccups
   • Connection multiplier effect vs single streaming connection

This technical reference contains all actionable intelligence needed for logical replication implementation, troubleshooting, and operational decision-making without the editorial content from the original source.