Recovery, Logging & Checkpoints

Recovery, log ও checkpoint

Read: ~30 min Hard 10 practice problems Live SQLite runner

1. The Durability Problem

A garments factory in Gazipur runs a payroll script at 2 a.m. It updates 4,300 worker accounts. Halfway through, REB cuts the power. The database server reboots. Did all 4,300 transfers go through? Did some? None? When the worker shows up at the bank in the morning, what should happen?

This is the durability problem — the "D" of ACID. The database promises that once a transaction has been told it is committed, the change is permanent, even across power loss. The toolkit that delivers this promise is logging plus a clever recovery algorithm. This module shows the inside.

ACID-এর "D" মানে durability — একবার commit হয়ে গেলে সেই পরিবর্তন স্থায়ী, এমনকি বিদ্যুৎ চলে গেলেও। কিন্তু কম্পিউটার-এর memory (RAM) volatile, আর disk-এ লেখার মাঝখানে যদি current চলে যায়? সমাধান: প্রতিটি পরিবর্তন প্রথমে একটি log-এ লিখে disk-এ flush করো, তারপর মূল data-তে লেখো। এই log-এর নাম Write-Ahead Log (WAL)।

2. Why "Just Write to Disk" Is Not Enough

A database stores data in fixed-size pages (typically 4 KB or 8 KB). The OS keeps these pages in RAM (the buffer pool) and writes them to disk only occasionally. So a COMMIT usually does not trigger an immediate write to the actual data file — that would be far too slow.

Worse, modifying a page in place is dangerous. Suppose we change page #42 from "balance=1000" to "balance=1500". The OS writes 4 KB. If the power dies after only 2 KB are written, page #42 is now torn — half old, half new. The row is corrupted. There is no way to even tell what the original value was, because we just overwrote it.

Database data file-এ সরাসরি ওভাররাইট করা বিপজ্জনক — power cut হলে page অর্ধেক পুরোনো অর্ধেক নতুন হয়ে যেতে পারে (torn write)। তাই দরকার একটি log যেখানে আমরা পরিবর্তনের intent আগে লিখব, তারপর মূল file-এ লিখব। কোনো ব্যাঘাত হলে log থেকে সব ঠিক করে আনা যাবে।

2.1 Three Kinds of Failure

Failure	What's lost	Recovery tool
Transaction failure (rollback, deadlock victim)	Just the in-flight transaction's writes	Undo log
System crash (power cut, OS panic)	Buffer pool contents, in-flight transactions	WAL + redo + undo
Media failure (disk dies)	Entire data file	Backup + WAL replay (PITR)

3. Write-Ahead Log (WAL) — The Golden Rule

Write-Ahead Logging is one of the most influential ideas in computer systems. The rule has two halves, sometimes called the WAL invariants:

Log before data. Before you flush a modified page to the data file, the corresponding log record must already be on disk.
Log before commit. Before COMMIT returns success, the commit record must already be on disk.

With these two rules, no matter when the power dies, the log on disk holds enough information to reconstruct the world: redo the committed work, undo the in-flight work.

Figure 33.1 — Write-Ahead Logging-এর কর্মপ্রবাহ।

WAL-এর দুটি অপরিবর্তনীয় নিয়ম: (১) data page disk-এ যাবার আগে সংশ্লিষ্ট log record disk-এ থাকতে হবে; (২) COMMIT success return করার আগে commit record disk-এ থাকতে হবে। এই দুটি নিয়ম মেনে চললে যেকোনো crash থেকে recovery সম্ভব।

4. Undo and Redo — Two Logs in One

A typical log record carries enough information to do both directions:

LSN=1042  TXN=78  PAGE=42  OFFSET=128
  BEFORE: balance = 1000        ← used for UNDO
  AFTER : balance = 1500        ← used for REDO

Undo: "this transaction did not commit; restore the BEFORE image". Used during rollback and during crash recovery for in-flight transactions.
Redo: "this transaction did commit; reapply the AFTER image". Used during crash recovery for committed transactions whose dirty pages had not yet reached the data file.

Each record has a unique, monotonically increasing LSN (Log Sequence Number). Pages on disk carry the LSN of the latest log record that updated them — recovery uses LSN comparisons to know exactly what to redo and skip.

প্রতিটি log record-এ থাকে BEFORE এবং AFTER image — অর্থাৎ পরিবর্তনের আগের ও পরের মান। তাই একই log দিয়ে দুদিকেই যাওয়া যায়: ব্যর্থ transaction-কে UNDO দিয়ে আগের অবস্থায় ফিরিয়ে আনা, আর সফল transaction-কে REDO দিয়ে আবার apply করা।

5. Checkpoints — Bounding Recovery Time

Without checkpoints, recovery would have to scan the entire WAL since the database was created — possibly terabytes. A checkpoint is a moment at which the system flushes all dirty pages from the buffer pool to disk and writes a marker into the log. After a crash, recovery only has to scan backwards from the most recent checkpoint.

Modern systems use fuzzy checkpoints: they don't pause writes. Instead they record an "active transactions" list and let dirty-page flushing happen in the background. The checkpoint marker says: before this point, every committed change is already on disk.

Checkpoint: একটি বিশেষ মুহূর্ত যেখানে সব dirty page disk-এ flush করা হয় এবং log-এ একটি marker লেখা হয়। ফলে crash হলে recovery কেবল সর্বশেষ checkpoint থেকে শুরু হবে — পুরো log scan করতে হবে না। এতে recovery time-এর একটি upper bound পাওয়া যায়।

Figure 33.2 — Checkpoint থেকে crash পর্যন্ত শুধু segment scan।

6. ARIES — The Algorithm Inside Every Major DB

ARIES (Algorithm for Recovery and Isolation Exploiting Semantics), invented at IBM in 1992, is the algorithm that real databases — DB2, Postgres, MySQL InnoDB, SQL Server — all roughly follow. It runs in three sequential phases:

Phase	Direction	What it does	বাংলায়
Analysis	Forward, from last checkpoint	Reads the log to figure out which transactions were active at crash and which pages were dirty.	Crash-এর সময় কে কে চলছিল, কোন page dirty ছিল, সেটা বের করা।
Redo	Forward	Re-applies every log record (committed or not) whose effect might be missing from disk. After this, disk reflects the moment of crash exactly.	প্রতিটি log record আবার apply করা — disk-কে crash-মুহূর্তের অবস্থায় ফিরিয়ে আনা।
Undo	Backward	Walks the log backwards for transactions that did not commit, undoing their effects via BEFORE images.	যেগুলো commit হয়নি, তাদের পরিবর্তন BEFORE image দিয়ে রোলব্যাক।

Three big tricks make ARIES work on real workloads:

Repeating history during redo — even uncommitted transactions are redone, so the system reaches a consistent, well-defined state before undo begins.
Logging undo operations — undo writes its own "compensation log records" (CLRs), so a crash during recovery is also recoverable.
LSNs on every page — each page's pageLSN tells redo which records have already been applied, so redo is idempotent.

ARIES তিন ধাপে কাজ করে: Analysis (কে চলছিল, কোন page dirty), Redo (সব log record আবার apply করো), Undo (uncommitted transaction গুলোকে রিভার্স)। প্রায় সব আধুনিক ডেটাবেস (PostgreSQL, MySQL InnoDB, SQL Server, DB2) এই algorithm-এর কোনো-না-কোনো রূপ ব্যবহার করে।

7. SQLite's WAL Mode — Inside Your Phone

SQLite has two journal modes: classic rollback-journal (default in older versions) and WAL (default in modern Android, iOS and most apps that use SQLite).

Mode	How it works	Pros	Cons
rollback-journal	Copy old pages into a side file, write into main DB, delete journal on commit.	Simple. Data file is always consistent.	Writers block readers and vice versa.
WAL	Append new versions to a `-wal` file. Readers see old + new via index file `-shm`.	Writers don't block readers. Faster.	Three files instead of one. WAL needs periodic checkpointing.

You set the mode with a PRAGMA. We can run it right here:

wal_pragma.sql

-- See what mode this database is in:
PRAGMA journal_mode;

-- In-memory DBs can't be set to WAL, but on a real disk DB you would:
-- PRAGMA journal_mode = WAL;

-- Insert some rows so the journal/WAL has something to log:
INSERT INTO log_demo(msg) VALUES ('payroll start');
INSERT INTO log_demo(msg) VALUES ('paid 4300 workers');
INSERT INTO log_demo(msg) VALUES ('payroll done');

SELECT * FROM log_demo;

Two related PRAGMAs govern how aggressively SQLite forces data to disk:

PRAGMA synchronous = FULL — fsync() after every write. Strongest durability, slowest. Default.
PRAGMA synchronous = NORMAL — fsync() only at WAL checkpoints. Safe if the OS doesn't crash. Common for apps.
PRAGMA synchronous = OFF — never fsync(). Fast. Lose data on power cut. Not for production.

SQLite-এর WAL mode: লেখাগুলো একটি -wal file-এ append হয়; reader আর writer একসাথে কাজ করতে পারে। মোবাইল অ্যাপগুলো প্রায় সবই এটি ব্যবহার করে। PRAGMA synchronous ঠিক করে দেয় কতবার fsync() হবে — production-এ FULL বা NORMAL, কখনোই OFF নয়।

8. Point-in-Time Recovery (PITR)

What if a disgruntled developer ran DROP TABLE orders at 3:42 p.m. and you didn't notice until 4:00 p.m.? Crash recovery won't help — the drop committed cleanly. The fix is point-in-time recovery:

Take periodic base backups (e.g., nightly).
Stream every WAL segment to safe storage as it is generated.
To recover to time T: restore the most recent base backup before T, then replay WAL up to but not including the offending statement at T.

Big systems (Postgres' recovery.conf, RDS, Oracle Data Guard) automate all of this. The result is recovery to any second in the past few weeks, even after deliberate human error.

PITR: একটি base backup আর তার পরের সব WAL segment থাকলে, আপনি database-কে অতীতের যেকোনো মুহূর্তে ফিরিয়ে আনতে পারবেন। এটি খুবই গুরুত্বপূর্ণ — কারণ অনেক বড় ক্ষতি crash থেকে নয়, ভুল command থেকে আসে (যেমন accidentally DROP TABLE)।

Figure 33.3 — PITR: backup + WAL replay দিয়ে যেকোনো সময়ে ফিরে যাওয়া।

9. Practice Problems

A mix of conceptual and runnable. Most are short — durability is more about reasoning than syntax.

recovery-র বেশিরভাগ অংশ syntax নয়, যুক্তি — তাই উত্তরগুলো ছোট কিন্তু গভীর। প্রতিটি প্রশ্ন আগে নিজে চিন্তা করুন।

State the two WAL invariants in your own words.

নিজের ভাষায় WAL-এর দুটি invariant বলুন।

✨ Show Answer (উত্তর দেখুন)

(1) Before a dirty data page is flushed to disk, its log record must already be on disk. (2) Before COMMIT returns success, the commit log record must already be on disk.
Why does each log record carry both BEFORE and AFTER images?

প্রতিটি log record-এ BEFORE এবং AFTER image দুইটাই কেন থাকে?

✨ Show Answer (উত্তর দেখুন)

BEFORE is needed to UNDO an in-flight transaction (or rolled-back one); AFTER is needed to REDO a committed transaction whose dirty pages didn't reach disk before the crash. Both directions are needed in any real recovery.
Without checkpoints, what is the worst-case recovery time?

Checkpoint না থাকলে recovery-র worst-case সময় কত?

✨ Show Answer (উত্তর দেখুন)

Proportional to the entire WAL since the database was created — possibly hours to days. Checkpoints bound recovery time to roughly the interval between two checkpoints.
List the three phases of ARIES in order and what each one accomplishes.

ARIES-এর তিনটি phase ক্রমে বলুন এবং প্রতিটির কাজ ব্যাখ্যা করুন।

✨ Show Answer (উত্তর দেখুন)

(1) Analysis — scan log forward from last checkpoint to determine in-flight transactions and dirty pages. (2) Redo — replay every log record (even uncommitted) so disk matches the crash moment. (3) Undo — walk backwards undoing transactions that didn't commit, writing CLRs.
Run the SQLite block, then explain what PRAGMA journal_mode reports for an in-memory database.

SQLite block চালান এবং in-memory database-এ PRAGMA journal_mode কী বলে ব্যাখ্যা করুন।
✨ Show Answer (উত্তর দেখুন)
```
PRAGMA journal_mode;
PRAGMA synchronous;
```
In-memory SQLite reports memory for journal_mode — there is no on-disk file to journal to. On a real disk DB you'll see delete (rollback journal) or wal.
Why is "repeating history during redo" required? What would break if redo skipped uncommitted records?

"Redo-তে history repeat করা" কেন দরকার? Uncommitted record skip করলে কী ভাঙবে?

✨ Show Answer (উত্তর দেখুন)

If redo skipped uncommitted records, the disk would be in a hybrid state: some uncommitted pages partially written, others not. Undo couldn't reliably reverse them because page LSNs would lie about what's on disk. Repeating history first ensures disk == crash moment, after which undo's BEFORE images cleanly roll back what didn't commit.
A bKash transfer commits at 14:00:01.234 and the server loses power at 14:00:01.235. Will the user's balance be updated?

bKash transfer 14:00:01.234-এ commit হলো, 14:00:01.235-এ power গেলো — balance update থাকবে?

✨ Show Answer (উত্তর দেখুন)

Yes. WAL invariant #2 says the commit log record was on disk before COMMIT returned. On reboot, redo replays the committed change. The data file may not yet show it, but the WAL does — and recovery makes the data file catch up.
Compare PRAGMA synchronous=NORMAL vs OFF. When (if ever) is OFF acceptable?

synchronous=NORMAL বনাম OFF-এর পার্থক্য। OFF কখন গ্রহণযোগ্য?

✨ Show Answer (উত্তর দেখুন)

NORMAL fsyncs at WAL checkpoints; safe against app crashes, vulnerable to OS-level crashes losing the last second. OFF never fsyncs; loses data on any unclean shutdown. OFF is acceptable only for ephemeral data (a temp build cache, a benchmark, an offline test) — never for user data.
Design a PITR strategy for a small startup with one Postgres instance and a 100 GB DB. Mention frequency and storage.

100GB Postgres DB-র জন্য একটি PITR strategy ডিজাইন করুন।

✨ Show Answer (উত্তর দেখুন)

Take a full base backup nightly with pg_basebackup. Stream WAL continuously to S3 (or equivalent) using archive_command. Retain backups + WAL for 30 days. Test restore monthly. Result: any-second recovery within the last 30 days; storage cost ≈ 100 GB × 30 + WAL volume.
Run the demo block; then write three more INSERTs and re-run. Explain why the rows persist between runs only if the runner shares state — otherwise the in-memory DB resets.

Demo block চালান, আরও তিনটি INSERT যোগ করুন। কেন in-memory DB রান-এর মাঝে state রাখে না — ব্যাখ্যা করুন।
✨ Show Answer (উত্তর দেখুন)
```
INSERT INTO log_demo(msg) VALUES('tx 1');
INSERT INTO log_demo(msg) VALUES('tx 2');
INSERT INTO log_demo(msg) VALUES('tx 3');
SELECT * FROM log_demo;
```
An in-memory database lives in RAM only; the runner builds a fresh DB each run with the data-setup seed, so previous INSERTs are gone — exactly the volatility WAL was designed to protect against on a real disk DB.

Summary — Module 33

Durability is the promise that committed data survives every reasonable failure. Databases keep that promise using write-ahead logging: every change is written to a sequential log on disk before the data file is updated, and the commit record reaches disk before COMMIT returns. Each log record carries both BEFORE (for undo) and AFTER (for redo) images. Checkpoints bound recovery time, and ARIES — the algorithm at the heart of every major DB — does Analysis, Redo, Undo in three sequential passes. SQLite offers WAL via PRAGMA journal_mode = WAL; the tunable PRAGMA synchronous controls how aggressively data is forced to disk. Point-in-time recovery combines a base backup with a continuous WAL stream to rewind to any second in the past — protecting you not only from crashes but from human error.

Database power-cut সহ যেকোনো ফেইলিওর থেকে বাঁচে কারণ সে প্রতিটি পরিবর্তন আগে একটি sequential log-এ লেখে — এটিই WAL। প্রতিটি log record-এ আগের ও পরের মান থাকায় Undo এবং Redo দুটোই সম্ভব। Checkpoint দিয়ে recovery-র সময় সীমাবদ্ধ রাখা হয়, আর ARIES তিন ধাপে (Analysis, Redo, Undo) crash recovery চালায়। SQLite-এর WAL mode আর PRAGMA synchronous ঠিক এই ধারণাগুলোরই বাস্তব রূপ। PITR দিয়ে ভুল command থেকেও বাঁচা যায়।

Next Module → Query Processing & Execution Plans — ENTER চাপার পর database আসলে কী করে।

Previous (পূর্ববর্তী) Next: Query Processing & Execution Plans