Web Development, Performance, Distributed Systems, APIs, Databases, Backend Engineering — Daily Update 2026-10-30
Today’s Focus: Write-Ahead Logging Internals — Why Your Checkpoints Matter
Every production database you run — PostgreSQL, MySQL/InnoDB, SQLite, SQL Server — relies on Write-Ahead Logging (WAL) for crash recovery. Yet most teams treat it as a black box. Understanding the checkpoint mechanism is the difference between a 30-second recovery and a 30-minute one.
The WAL Checkpoint Contract
WAL guarantees: no data page reaches disk before its log records do. But log grows unbounded. A checkpoint flushes all dirty pages to disk and records a checkpoint record in WAL, establishing a “known good” recovery start point.
PostgreSQL: checkpoint_completion_target and checkpoint_timeout
PostgreSQL spreads checkpoint I/O over checkpoint_completion_target × checkpoint_timeout (default: 0.9 × 5min = 4.5min). This smooths I/O but extends the recovery window — more WAL to replay after a crash. For low-RTO systems, reduce checkpoint_timeout to 60-120s and checkpoint_completion_target to 0.5-0.7. Accept higher steady-state I/O for faster recovery.
MySQL/InnoDB: Adaptive Flushing and innodb_io_capacity
InnoDB uses a fuzzy checkpoint — it flushes dirty pages continuously based on innodb_io_capacity (default 200 IOPS). If your storage delivers 10,000 IOPS (NVMe), the default throttles you. Set innodb_io_capacity to 80% of your sustained write IOPS. Monitor Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total — keep dirty ratio < 10% for predictable checkpoints.
SQLite: PRAGMA journal_mode=WAL Checkpoint Modes
SQLite WAL checkpoints default to PASSIVE (checkpoint only if no readers). Under read load, checkpoints stall and WAL grows. Use PRAGMA wal_autocheckpoint = 1000 (checkpoint every 1000 pages) and PRAGMA journal_mode=WAL with busy_timeout to allow writer concurrency.
Performance Anti-Pattern: SELECT * in OLTP
Fetching all columns forces heap access even when a covering index exists. In Postgres, EXPLAIN (ANALYZE, BUFFERS) reveals Heap Fetches — if > 0 on an index-only-scan query, you’re paying for unnecessary tuple reads. Select only needed columns; add INCLUDE columns to indexes for true index-only scans.
API Versioning: Header-Based > URL-Based
URL versioning (/v1/, /v2/) breaks HATEOAS, pollutes cache keys, and forces clients to reconstruct URLs. Use Accept: application/vnd.myapi.v2+json — caches key on header, clients negotiate, and you can serve multiple versions from one code path via content negotiation middleware.
Distributed Tracing: Sample Rate Is Not Free
100% trace sampling on a 10K RPS service generates ~10M spans/minute. Most backends (Jaeger, Tempo) charge by ingestion volume. Use probabilistic sampling with tail-based decision: sample 1% by default, but always keep traces with errors, high latency (> P99), or specific business flags. Implement via OpenTelemetry TraceIdRatioBased + custom Sampler.
This daily update series covers production-grade backend engineering: distributed systems patterns, database internals, API design, and operational practices. No filler — only what ships.