Crash Recovery: Why It Matters?

Purpose of Recovery Mechanisms

Ensure database consistency, atomicity, and durability even after system failures.
Two key parts:
1. Actions during normal transaction execution to prepare for failures.
2. Actions after failure to restore the database to a correct state.

Key Challenges

Hardware dependency: Many early (1980s) in-memory DBMSs assumed non-volatile memory (NVM) was available.
Modern reality (Post-2019): NVM exists but is not widely used, so systems still rely on SSDs and HDDs.

Logging Schemes

Logging methods ensure recoverability by tracking changes in transactions.

Logging Scheme	Description	Pros	Cons
Physical Logging	Logs changes at the byte level (e.g., `git diff`)	Simple to implement	Large log size
Logical Logging	Logs high-level SQL operations (e.g., `UPDATE salary SET x = 100`)	Small log size	Harder to recover
Physiological Logging	Hybrid of physical & logical logging; logs page-level changes	Best balance	Implementation complexity

Physical vs. Logical Logging

Comparison Factor	Physical Logging	Logical Logging
Log Size	Larger	Smaller
Ease of Implementation	Easier	Harder
Crash Recovery Complexity	Simple (Redo changes directly)	Hard (Re-execute queries)
Concurrency Handling	Better (handles individual pages)	Worse (uncertainty in execution order)

Most modern DBMSs use physiological logging for efficiency.

Log Flushing Strategies

Approach	Description
All-at-Once Flushing	Waits until a transaction fully commits before writing logs. (No need to store abort records)
Incremental Flushing	Allows the DBMS to write log records before a transaction commits

Optimization: Group Commit

Batches multiple transactions into one fsync operation.