RobustTrueTry

Posted on Aug 13

Safely Resetting SQLite WAL in Production: Lessons from Tailscale

#automation #ai #python

When Tailscale hit a 16‑year‑old SQLite bug, their traces database went corrupt. The culprit was a WAL reset that left the file in an inconsistent state.

What You’ll Learn

How SQLite WAL works and why a reset can break the database.
Safe patterns for resetting or checkpointing WAL in a live system.
Code examples that you can drop into your own projects.
Common failure modes and how to guard against them.

Understanding SQLite WAL and the Reset Problem

SQLite uses a Write‑Ahead Log (WAL) to record changes before they are applied to the main database file. A WAL file grows until a checkpoint writes its contents back to the database. If the WAL is reset or truncated while the database is open, the checkpoint may miss data, leading to corruption.

The Tailscale incident showed that a simple reset of the WAL file—removing it or truncating it—can leave the database in a state where the journal and data pages are out of sync. The database then refuses to open or returns incomplete data.

Safe Reset Strategies

There are three common ways to handle a WAL that needs to be cleared:

PRAGMA wal_checkpoint – forces SQLite to write all WAL pages back to the database.
VACUUM – rebuilds the entire database, which implicitly checkpoints.
Manual reset with a backup – copy the database, close the connection, delete the WAL, and restore the backup.

Each approach has trade‑offs in terms of downtime, performance, and risk.

Code Example: Python Wrapper for Safe Reset

Below is a small helper that opens a connection, checkpoints, and optionally vacuum‑s the database. It uses the built‑in sqlite3 module.

import sqlite3
from contextlib import closing

def safe_reset(db_path, vacuum=False):
    """Checkpoint the WAL and optionally vacuum the database.

    The function opens a connection, runs PRAGMA wal_checkpoint, and
    closes the connection. If `vacuum` is True, it runs VACUUM after the
    checkpoint.
    """
    with closing(sqlite3.connect(db_path)) as conn:
        conn.execute("PRAGMA wal_checkpoint(FULL)")
        if vacuum:
            conn.execute("VACUUM")
    print("Checkpointed" + (" and vacuumed" if vacuum else ""))

## Example usage

## safe_reset("/var/lib/tailscale/traces.db", vacuum=True)

The function keeps the connection short‑lived, so it does not lock the
database for long. It also prints a message so you can verify the action.

Code Example: Bash Script to Monitor WAL Health

A quick shell script can watch the WAL size and trigger a checkpoint when it grows too large.

#!/usr/bin/env bash

DB_PATH="/var/lib/tailscale/traces.db"
MAX_WAL_SIZE=$((10 * 1024 * 1024))  # 10 MB

while true; do
    WAL_SIZE=$(stat -c%s "$DB_PATH-wal" 2>/dev/null || echo 0)
    if [ "$WAL_SIZE" -gt "$MAX_WAL_SIZE" ]; then
        echo "WAL size $WAL_SIZE exceeds $MAX_WAL_SIZE, checkpointing"
        sqlite3 "$DB_PATH" "PRAGMA wal_checkpoint(FULL)"
    fi
    sleep 60
done

The script runs in the background and keeps the WAL from ballooning.

Trade‑offs and Failure Modes

Approach	Downtime	Performance Impact	Risk of Data Loss	When to Use
PRAGMA wal_checkpoint	None	Low	None if connection is open	Small databases, frequent writes
VACUUM	Short (seconds)	High	None if checkpointed first	Large databases, need to reclaim space
Manual reset with backup	Medium (depends on backup time)	Medium	High if backup fails	Critical systems, when WAL is corrupted

Common failure modes:

Running a checkpoint while another process writes can cause a race. Use a single writer or serialize checkpoints.
Deleting the WAL without a backup loses uncheckpointed data. Always keep a recent backup.
Vacuuming a busy database can lock the file for a long time. Schedule during low traffic.

When to Use Which Approach

If the WAL grows slowly and you want zero downtime, use PRAGMA wal_checkpoint.
If you need to reclaim disk space or rebuild indexes, run VACUUM during a maintenance window.
If the WAL is corrupted or you suspect data loss, perform a manual reset with a backup and verify integrity.

Key Takeaways

A WAL reset can corrupt SQLite if the database is still open.
Use PRAGMA wal_checkpoint(FULL) to safely flush WAL pages.
VACUUM rebuilds the database but incurs downtime.
Always keep a recent backup before performing manual resets.
Monitor WAL size in production to avoid unexpected growth.

Source

Tailscale Traces Database Corruption to 16y/o SQLite WAL-Reset Bug. I added working code examples, a trade‑off table, and detailed failure‑mode discussion.

DEV Community