PostgreSQL Internals That Support Read Replicas

[hoangquochung1110]

PostgreSQL Internals That Support Read Replicas

PostgreSQL has sophisticated internal mechanisms that enable effective read replicas. Below are the key architectural components that make this possible.

1. Write-Ahead Logging (WAL) System

At the core of PostgreSQL's replication capability is its Write-Ahead Logging system:

• WAL Records: Every database change is first recorded in the WAL before being applied to data files
• WAL Segments: WAL is stored in fixed-size segments (typically 16MB)
• LSN (Log Sequence Number): Each WAL record gets a unique LSN that ensures proper sequencing

The WAL system provides the foundation for all replication by creating a reliable, ordered stream of database changes that can be replayed on replicas.

2. Streaming Replication

PostgreSQL's primary physical replication mechanism works through:

• WAL Sender Process: Runs on the primary server, streams WAL records to replicas
• WAL Receiver Process: Runs on replica servers, receives and stores WAL records
• Replication Protocol: Efficient binary protocol for streaming data changes
• Replication Slots: Ensures WAL segments aren't removed until consumed by replicas

This allows replicas to stay current with minimal lag (typically milliseconds to seconds depending on network conditions).

3. Replication Modes

PostgreSQL supports multiple replication configurations:

• Asynchronous Replication: Primary doesn't wait for replicas to acknowledge receipt (default, lowest latency)
• Synchronous Replication: Primary waits for one or more replicas to confirm receipt before confirming transactions
• Cascading Replication: Replicas can stream changes to other replicas, reducing load on the primary

4. Hot Standby

The hot standby feature enables read-only queries on replica servers:

• Recovery Mode: Replicas operate in continuous recovery mode, applying WAL changes
• Conflict Management: Handles conflicts between read queries and incoming WAL records
• Query Cancellation: Can cancel long-running queries if they conflict with WAL replay
• Feedback Mechanism: Replicas can inform primary about queries to prevent WAL cleanup of needed records

5. AWS RDS Implementation

AWS RDS extends PostgreSQL's native replication capabilities with:

• Automated Provisioning: Simplifies replica creation through the AWS console or API
• Storage-Level Replication: Uses EBS-level snapshots for initial replica synchronization
• Multi-AZ Deployment: Leverages synchronous replication for high availability
• Cross-Region Replication: Manages the complexity of long-distance WAL shipping
• Monitoring: Provides CloudWatch metrics for replication lag and replica health

6. Key Configuration Parameters

Critical settings that control replica behavior:

• max_standby_streaming_delay: How long replicas wait before canceling queries conflicting with WAL replay
• hot_standby_feedback: Allows replicas to report active queries to prevent cleanup of needed WAL
• max_standby_archive_delay: Similar to streaming delay but for archived WAL segments
• synchronous_commit: Controls whether transactions wait for replica acknowledgment
• wal_keep_segments/wal_keep_size: How many WAL segments to retain for replicas

7. Monitoring Replication

PostgreSQL exposes important metrics through system views:

• pg_stat_replication: Shows connection status, replication lag, and sync state
• pg_current_wal_lsn(): Current write position on primary
• pg_last_wal_receive_lsn(): Last WAL position received on replica
• pg_last_wal_replay_lsn(): Last WAL position applied on replica