Write-Ahead Logging (WAL) is a fundamental feature of PostgreSQL, ensuring data integrity and facilitating critical functionalities like crash recovery, replication, and backup.

This series of experimentation explores WAL in detail, its importance, how it works, and provides examples to demonstrate its usage.

What is Write-Ahead Logging (WAL)?

WAL is a logging mechanism where changes to the database are first written to a log file before being applied to the actual data files. This ensures that in case of a crash or unexpected failure, the database can recover and replay these logs to restore its state.

Your question is right !

Why do we need a WAL, when we do a periodic backup ?

Write-Ahead Logging (WAL) is critical even when periodic backups are in place because it complements backups to provide data consistency, durability, and flexibility in the following scenarios.

1. Crash Recovery

• Why It’s Important: Periodic backups only capture the database state at specific intervals. If a crash occurs after the latest backup, all changes made since that backup would be lost.
• Role of WAL: WAL ensures that any committed transactions not yet written to data files (due to PostgreSQL’s lazy-writing behavior) are recoverable. During recovery, PostgreSQL replays the WAL logs to restore the database to its last consistent state, bridging the gap between the last checkpoint and the crash.

Example:

• Backup Taken: At 12:00 PM.
• Crash Occurs: At 1:30 PM.
• Without WAL: All changes after 12:00 PM are lost.
• With WAL: All changes up to 1:30 PM are recovered.

2. Point-in-Time Recovery (PITR)

• Why It’s Important: Periodic backups restore the database to the exact time of the backup. However, this may not be sufficient if you need to recover to a specific point, such as just before a mistake (e.g., accidental data deletion).
• Role of WAL: WAL records every change, enabling you to replay transactions up to a specific time. This allows fine-grained recovery beyond what periodic backups can provide.

Example:

• Backup Taken: At 12:00 AM.
• Mistake Made: At 9:45 AM, an important table is accidentally dropped.
• Without WAL: Restore only to 12:00 AM, losing 9 hours and 45 minutes of data.
• With WAL: Restore to 9:44 AM, recovering all valid changes except the accidental drop.

3. Replication and High Availability

• Why It’s Important: In a high-availability setup, replicas must stay synchronized with the primary database to handle failovers. Periodic backups cannot provide real-time synchronization.
• Role of WAL: WAL enables streaming replication by transmitting logs to replicas, ensuring near real-time synchronization.

Example:

• A primary database sends WAL logs to replicas as changes occur. If the primary fails, a replica can quickly take over without data loss.

4. Handling Incremental Changes

• Why It’s Important: Periodic backups store complete snapshots of the database, which can be time-consuming and resource-intensive. They also do not capture intermediate changes.
• Role of WAL: WAL allows incremental updates by recording only the changes made since the last backup or checkpoint. This is crucial for efficient data recovery and backup optimization.

5. Ensuring Data Durability

• Why It’s Important: Even during normal operations, a database crash (e.g., power failure) can occur. Without WAL, transactions committed by users but not yet flushed to disk are lost.
• Role of WAL: WAL ensures durability by logging all changes before acknowledging transaction commits. This guarantees that committed transactions are recoverable even if the system crashes before flushing the changes to data files.

6. Supporting Hot Backups

• Why It’s Important: For large, active databases, taking a backup while the database is running can result in inconsistent snapshots.
• Role of WAL: WAL ensures consistency by recording changes that occur during the backup process. When replayed, these logs synchronize the backup, ensuring it is valid and consistent.

7. Debugging and Auditing

• Why It’s Important: Periodic backups are static snapshots and don’t provide a record of what happened in the database between backups.
• Role of WAL: WAL contains a sequential record of all database modifications, which can help in debugging issues or auditing transactions.

Feature	Periodic Backups	Write-Ahead Logging
Crash Recovery	Limited to the last backup	Ensures full recovery to the crash point
Point-in-Time Recovery	Restores only to the backup time	Allows recovery to any specific point
Replication	Not supported	Enables real-time replication
Efficiency	Full snapshot	Incremental changes
Durability	Relies on backup frequency	Guarantees transaction durability

In upcoming sessions, we will all experiment each one of the failure scenarios for understanding.