|
| 1 | +<!-- SPDX-License-Identifier: MIT --> |
| 2 | +<!-- SPDX-FileCopyrightText: 2025-2026 Marcus Quinn --> |
| 3 | + |
| 4 | +# OpenCode Database Maintenance |
| 5 | + |
| 6 | +Periodic SQLite maintenance for opencode's session database to reduce |
| 7 | +"database is locked" errors under concurrent session load. |
| 8 | + |
| 9 | +## The problem |
| 10 | + |
| 11 | +opencode stores session state in `~/.local/share/opencode/opencode.db` |
| 12 | +(SQLite with WAL journaling). Under heavy use: |
| 13 | + |
| 14 | +- **DB grows large**. Active users routinely accumulate 1 GB+ of message |
| 15 | + and part rows. `message` and `part` dominate the schema (typical ratio: |
| 16 | + 65% message, 30% part, 5% indexes). |
| 17 | +- **Multiple connections per process**. Each opencode binary opens at |
| 18 | + least 2 DB connections — one for reads, one for writes. A single TUI |
| 19 | + with 10+ FDs on the DB is already multi-writer from SQLite's perspective. |
| 20 | +- **WAL single-writer limit**. SQLite WAL allows concurrent readers but |
| 21 | + only *one* writer at a time. When a write transaction runs long |
| 22 | + (large tool outputs are a common trigger — see anomalyco/opencode |
| 23 | + #21000), other writers block. |
| 24 | +- **busy_timeout caps retry time**. opencode applies `PRAGMA busy_timeout |
| 25 | + = 5000ms` on its connections. When a writer exceeds 5 seconds, retries |
| 26 | + exhaust and SQLite returns `SQLITE_BUSY`, surfacing as the red "database |
| 27 | + is locked" banner. The session halts mid-turn. |
| 28 | + |
| 29 | +The architectural fix is upstream — per-session-tree sharding (PR |
| 30 | +anomalyco/opencode#21579) or switching to a multi-writer backend. Until |
| 31 | +that lands, the best mitigation is to keep the DB compact and the WAL |
| 32 | +truncated so write transactions hold the lock for as short a time as |
| 33 | +possible. |
| 34 | + |
| 35 | +## What this routine does |
| 36 | + |
| 37 | +`r913 Weekly opencode DB maintenance` runs every Sunday at 04:00 local |
| 38 | +time and invokes `opencode-db-maintenance-helper.sh auto`, which: |
| 39 | + |
| 40 | +1. **Guards**. Exits 0 silently if opencode isn't installed. Throttles |
| 41 | + if the last successful run was within 6 days. Refuses to run while |
| 42 | + opencode processes are active (requires exclusive DB access). |
| 43 | +2. **wal_checkpoint(TRUNCATE)**. Folds pending WAL writes back into the |
| 44 | + main DB and truncates the WAL file to zero. Prevents WAL bloat during |
| 45 | + the following week's burst writes. |
| 46 | +3. **PRAGMA optimize**. Refreshes query planner statistics so common |
| 47 | + session queries pick the right indexes. |
| 48 | +4. **VACUUM** (conditional). Rewrites the DB file, reclaiming free pages |
| 49 | + from deleted rows (opencode prunes old sessions in the background). |
| 50 | + Runs when the DB is larger than 500 MB or free pages exceed 10% of |
| 51 | + total pages. Typical reclaim: 20–40% on DBs with heavy prune activity. |
| 52 | + |
| 53 | +## Subcommands |
| 54 | + |
| 55 | +```bash |
| 56 | +opencode-db-maintenance-helper.sh check # preflight: DB exists, no locks, integrity OK |
| 57 | +opencode-db-maintenance-helper.sh report # stats (size, pages, free list, top tables) |
| 58 | +opencode-db-maintenance-helper.sh maintain # run once (refuses if processes active) |
| 59 | +opencode-db-maintenance-helper.sh maintain --force # run anyway (may cause session errors) |
| 60 | +opencode-db-maintenance-helper.sh auto # scheduled mode (silent, throttled) |
| 61 | +opencode-db-maintenance-helper.sh help |
| 62 | +``` |
| 63 | + |
| 64 | +### Sample output |
| 65 | + |
| 66 | +``` |
| 67 | +== OpenCode DB Report == |
| 68 | +
|
| 69 | + Path: /Users/alice/.local/share/opencode/opencode.db |
| 70 | + DB size: 1.22 GB |
| 71 | + WAL size: 3.0 MB |
| 72 | + Pages: 320588 (page_size=4096B) |
| 73 | + Free pages: 29312 (9.14% of total) |
| 74 | +
|
| 75 | + PRAGMAs (fresh CLI connection — not what opencode uses): |
| 76 | + journal_mode = wal |
| 77 | + synchronous = 1 |
| 78 | + busy_timeout = 0 |
| 79 | + mmap_size = 0 |
| 80 | +
|
| 81 | + Top 5 tables/indexes by size: |
| 82 | + message 793.3 MB |
| 83 | + part 561.2 MB |
| 84 | + part_message_id_id_idx 12.0 MB |
| 85 | + part_session_idx 6.7 MB |
| 86 | + sqlite_autoindex_part_1 6.7 MB |
| 87 | +``` |
| 88 | + |
| 89 | +### Why the PRAGMA section says `busy_timeout = 0` |
| 90 | + |
| 91 | +The PRAGMAs displayed are from a *fresh* `sqlite3` CLI connection, not |
| 92 | +from opencode's internal connections. opencode applies |
| 93 | +`PRAGMA busy_timeout = 5000` at connection open time, but that setting |
| 94 | +is per-connection and does not persist to the DB file. The report |
| 95 | +surfaces the SQLite defaults so you can see what the DB file itself |
| 96 | +knows about — useful when diagnosing edge cases where opencode opens |
| 97 | +a new connection path that forgets to apply the standard pragmas. |
| 98 | + |
| 99 | +## Configuration |
| 100 | + |
| 101 | +All thresholds overrideable via environment variables: |
| 102 | + |
| 103 | +| Variable | Default | Description | |
| 104 | +|---|---|---| |
| 105 | +| `VACUUM_FREELIST_THRESHOLD` | `0.10` | VACUUM if free-page fraction >= this | |
| 106 | +| `FORCE_VACUUM_SIZE_MB` | `500` | Always VACUUM above this size (MB) | |
| 107 | +| `AUTO_MIN_SECONDS_BETWEEN` | `518400` | Throttle for `auto` mode (6 days) | |
| 108 | + |
| 109 | +Example: force VACUUM on every run regardless of fragmentation: |
| 110 | + |
| 111 | +```bash |
| 112 | +FORCE_VACUUM_SIZE_MB=0 VACUUM_FREELIST_THRESHOLD=0.0 \ |
| 113 | + opencode-db-maintenance-helper.sh maintain |
| 114 | +``` |
| 115 | + |
| 116 | +## Safety |
| 117 | + |
| 118 | +- **Never runs with active opencode processes** in `auto` or plain |
| 119 | + `maintain` mode. A running opencode TUI holds WAL locks; VACUUM |
| 120 | + requires exclusive access and would fail or (worse) conflict. The |
| 121 | + check uses `pgrep` for `opencode-ai/bin/.opencode`. |
| 122 | +- **No data loss from VACUUM**. SQLite VACUUM rewrites the DB file |
| 123 | + page by page, preserving every row. If interrupted, SQLite's |
| 124 | + journal restores the previous state. |
| 125 | +- **Throttled**. `auto` mode skips if `last-run.json` shows success |
| 126 | + within the last 6 days. Manual `maintain` is not throttled. |
| 127 | + |
| 128 | +## State |
| 129 | + |
| 130 | +- `~/.aidevops/.agent-workspace/work/opencode-maintenance/last-run.json` |
| 131 | + — last run outcome, timestamps, reclaimed bytes, VACUUM decision |
| 132 | +- `~/.aidevops/.agent-workspace/work/opencode-maintenance/maintenance.log` |
| 133 | + — append-only history |
| 134 | + |
| 135 | +## Related upstream issues |
| 136 | + |
| 137 | +- [anomalyco/opencode#21215](https://github.com/anomalyco/opencode/issues/21215) |
| 138 | + — opencode run: concurrent sessions crash with SQLITE_BUSY |
| 139 | +- [anomalyco/opencode#21000](https://github.com/anomalyco/opencode/issues/21000) |
| 140 | + — Bash tool hangs on fast-exiting processes and locks database on massive output |
| 141 | +- [anomalyco/opencode#20935](https://github.com/anomalyco/opencode/issues/20935) |
| 142 | + — Per-session-tree database sharding (architectural fix, open) |
| 143 | +- [anomalyco/opencode#21579](https://github.com/anomalyco/opencode/pull/21579) |
| 144 | + — Harden per-session SQLite sharding (PR, open) |
| 145 | +- [anomalyco/opencode#19521](https://github.com/anomalyco/opencode/issues/19521) |
| 146 | + — database is locked (original report) |
| 147 | + |
| 148 | +## What aidevops cannot fix from outside opencode |
| 149 | + |
| 150 | +These require opencode source changes (tracked upstream): |
| 151 | + |
| 152 | +- Raising `busy_timeout` from 5000ms → 30000ms (per-connection, must |
| 153 | + be set by opencode at connection open) |
| 154 | +- Enabling `mmap_size` (per-connection — would significantly reduce |
| 155 | + read contention by memory-mapping the DB file) |
| 156 | +- Retry-with-exponential-backoff on `SQLITE_BUSY` in the write path |
| 157 | + (write wrapper change in opencode's session.processor service) |
| 158 | +- Switching long `BEGIN IMMEDIATE` transactions to `BEGIN DEFERRED` |
| 159 | + for read paths (transaction-mode choice in application code) |
| 160 | +- Session-tree DB sharding (PR #21579) |
| 161 | + |
| 162 | +Periodic VACUUM + checkpoint is the only mitigation applicable from |
| 163 | +outside opencode's process. |
0 commit comments