Database maintenance

Included from Basic tier

Database maintenance is the leader-run statistics-and-bloat job: it keeps the query planner's statistics fresh on the tables OpsChain churns hardest, and reclaims dead-tuple bloat on a weekly off-peak cadence — plus an on-demand surface (GET /api/admin/database and the POST …/analyze / POST …/vacuum verbs) so an operator can inspect and act without waiting for the schedule.

Its neighbours: the scheduler supplies cron leadership; retention (opschain docs retention) invokes the post-sweep ANALYZE hook; the system-quiesce posture (opschain docs platform-halt) pauses the scheduled lanes; config.yaml's database.maintenance block (opschain docs config) owns the policy.

The lanes

Lane	What runs	When
Scheduled ANALYZE	Per-table ANALYZE "<table>" over the maintained set (PostgreSQL), or a single PRAGMA optimize (SQLite)	Every analyze_interval_minutes (default 1440 — daily) on the cron leader
Post-retention ANALYZE	The same pass, trigger post_retention	Immediately after any retention sweep that deleted, expired or released anything (wired only while maintenance is enabled)
Weekly VACUUM	Per-table VACUUM (ANALYZE) "<table>" (PostgreSQL), or PRAGMA optimize (SQLite — plain VACUUM is reserved for the backup lane)	Once per weekly window: vacuum_day (default sunday) within vacuum_window (default 02:00-04:00, server-local). An end at or before the start crosses midnight (23:00-01:00 wraps)
On demand	POST /api/admin/database/analyze / POST /api/admin/database/vacuum, optionally with a body {"tables": ["…"]} to target a subset	Whenever called — synchronous, answering with the run's per-table timings

On PostgreSQL a vacuum run also samples pg_stat_user_tables.n_dead_tup before and after, so each table's result carries dead_tuples_before / dead_tuples_after.

Leadership, cadence state and HA

• Leader-only, quiesce-aware. The scheduled loop ticks once a minute; only the scheduler's cron leader acts, and the serve composition wires the leader check through leaderUnlessQuiesced, so the scheduled lanes also stop from system-quiesce drain entry until the posture lifts. There is no extra lock: single-leader execution is the locking.
• Durable last-run state. Each run upserts one row per task into database_maintenance_runs (keyed by task: analyze or vacuum), so the cadence decision survives restarts and is HA-safe — every replica reads the same state, and a freshly booted (or failed-over) leader catches up on a missed cadence at its first tick rather than restarting the interval. A missed vacuum window is not back-filled mid-week: the vacuum lane only fires inside the window, and "due" means the last run finished before the current window opened.

The maintained table set

The built-in PostgreSQL churn set — the tables retention sweeps, run execution and the event spine write hardest, in analyze order:

changes, events, go_port_shims, log_lines, log_segments,
properties_versions, queue_ready, scheduler_done,
settings_versions, step_transitions, steps, trigger_firings,
workflow_runs, workflow_steps

database.maintenance.tables overrides the set wholesale; an on-demand request's tables body overrides both for that run. Every name must be a plain lower-case SQL identifier (^[a-z_][a-z0-9_]*$) — maintenance statements interpolate table names, so anything else is refused before any SQL is built with invalid table name: "<name>" (HTTP 400 on the on-demand verbs). On SQLite the table set is irrelevant: every run is one PRAGMA optimize.

Status and on-demand surface

• GET /api/admin/database — the backend (postgres/sqlite), whether the scheduled lanes are enabled, the effective analyze interval and vacuum day/window, the durable last-run rows, and per-table statistics: on PostgreSQL pg_stat_user_tables (live/dead tuples, last analyze/autoanalyze/vacuum/autovacuum) plus total relation sizes; on SQLite the database and freelist byte counts (PRAGMA page_count / page_size / freelist_count).
• POST /api/admin/database/analyze, POST /api/admin/database/vacuum — synchronous on-demand runs (trigger on_demand); the response is the run summary with per-table durations and any per-statement errors.

All three are admin-gated routes. CLI: opschain docs cli/admin-database-status, opschain docs cli/admin-database-analyze, opschain docs cli/admin-database-vacuum.

Backup readiness and estate export

The same /api/admin/database family carries the lanes. Division of labour: OpsChain makes the system safe and verifiable; the DBA moves the bytes — there is no built-in PostgreSQL byte streaming.

• GET /api/admin/database/backup_info — the backup manifest: server version/commit, goose schema level, exact per-table row counts of the durable tables (tables; the volatile ones — replica membership, leases, runner liveness — are named under tables_excluded and never counted), the blobstore root plus every referenced blob digest, the quiesce posture, a canonical SHA-256 content digest, and the blessed pg_dump --format=custom recipe (with the CloudNativePG note for HA). Refuses with a named 409 while the system is not quiesced (a consistent backup has no other state to run in), and with a 422 on a SQLite store (that lane is the CLI's admin database backup).
• POST /api/admin/database/verify_restore — the manifest replayed against the CURRENT (post-pg_restore) store: manifest digest, schema level (a newer server passes with a replayed-on-boot note; an older one fails), the durable tables' counts (the volatile tables are listed as not compared, never failed) and blobstore presence, answered as per-check pass/fail. Carved out of the write barrier: a database restored from a quiesced backup boots still quiesced, and this endpoint is reads in effect.
• GET /api/admin/database/export[?with_history=true] — the estate definitions archive: every live project subtree in the admin nodes export layout under projects/<code>/, plus system/{users,groups,authorisation,global_settings,maintenance_windows}.json. Secret values never travel: git remote credentials and password hashes are omitted, secured global-settings values are stripped with their paths recorded, secret-vault:// references travel as references. Runtime state stays out; with_history adds run metadata and events as JSONL, never logs or blobs. Refuses 409 unquiesced.

Import has no endpoint of its own: opschain admin database import replays the archive through the ordinary authorised create lanes (skip-not- overwrite), so RBAC applies to every record and the target needs no quiesce. The embedded SQLite backup/restore pair is likewise CLI-local (opschain docs cli/admin-database-backup).

Migration integrity

Both stores record every applied migration's name and content SHA-256 in a migration_ledger table beside goose's own version table (PostgreSQL migration 00045, SQLite 00009), and every boot verifies that ledger against the binary's embedded migration set — after migrations apply, before serving. The incident this answers: a renumbered migration set against an existing database left goose's version numbers "applied" while meaning different content; the binary silently skipped the real migration and the missing table surfaced only as request-time 500s. With the ledger, that boot fails immediately, naming the migrations and the remedy.

What the verifier reports (any finding fails the boot):

• mismatch — a recorded version whose name or content hash differs from the binary's migration at that version (assumed backfill rows — versions applied before the ledger existed, backfilled from the binary's set — compare by name only, since their historical content cannot be proven);
• hole — a version the binary carries below the database's maximum applied version that was never applied;
• unrecorded — an applied version with no ledger row (a truncated or edited ledger; the next boot re-records it as assumed).

Emergency escape hatch: OPSCHAIN_SKIP_MIGRATION_VERIFY=1 (or true) boots anyway, shouting the findings and the remedy to stderr. Nothing is repaired by skipping — the findings remain until the database and binary agree. Use it to hold a server open while diagnosing with the surface below, never as a permanent setting.

The on-demand surface (admin-gated, opschain docs cli/admin-database-migrations):

• GET /api/admin/database/migrations — the per-version view (applied / pending / mismatched / assumed, plus ledger rows a newer build recorded that this binary does not carry);
• GET /api/admin/database/migrations/status — the boot verifier's verdict on demand;
• POST /api/admin/database/migrations/apply — apply pending migrations server-side ({"all": true}, or {"id": "<version>"} for exactly the next pending version; out-of-order ids and mismatched ledgers are refused with a 409 carrying the boot check's error text). An apply that ran records api:database:migrations (versions applied) or warn:database:migrations (a migration failed) — a PostgreSQL events lane, like the maintenance events above.

What changes behaviour, and who owns it

The whole policy is config.yaml's database.maintenance block — deploy-authoritative; see opschain docs config for keys, types and defaults (enabled, analyze_interval_minutes, vacuum_window, vacuum_day, tables). Unlike retention:, an absent block means enabled with the defaults — enabled: false is the explicit opt-out for estates where a DBA owns maintenance. Disabling only stops the scheduled lanes (and the post-retention hook, which is wired only when enabled): GET /api/admin/database and the on-demand analyze/vacuum verbs stay available either way.

Events vocabulary

One event per completed run, visible in opschain events:

• api:database:maintenance — the run completed with zero statement failures.
• warn:database:maintenance — at least one statement failed.

The payload is the run summary: task (analyze | vacuum), trigger (scheduled | on_demand | post_retention), start/finish times, duration_ms, the per-table results (with error per failed statement, and dead-tuple before/after on vacuum) and the failures count. The operator notice mirrors it: database maintenance: <task> (<trigger>) finished in <n>ms over <n> statement(s), <n> failure(s).

PostgreSQL stores only. Server events are a PostgreSQL lane: on the embedded SQLite (trial) store maintenance still runs (PRAGMA optimize), but no event is recorded and opschain events shows nothing for it — the run is visible only as the operator notice above and as the task's last run in opschain admin database status.

Failure modes

• A failed statement never aborts the pass: the error is recorded on that table's result, the remaining tables still run, and the run reports as warn:database:maintenance with the per-table error text. The run is still persisted as the task's last run.
• A missed vacuum window (leader down, or quiesced, through the whole window) is skipped for that week — the lane fires only inside the window, so the next opportunity is the next weekly window. The daily ANALYZE, by contrast, catches up at the first leader tick after the interval has elapsed.
• An invalid vacuum_window/vacuum_day in config does not stop the lane: the job notices database maintenance: <parse error>; using defaults and evaluates the built-in sunday / 02:00-04:00 schedule.
• Unreadable last-run state skips that tick with the notice database maintenance: reading last-run state failed: <error>; a run that cannot be persisted notices database maintenance: recording <task> run failed: <error> but still reports its event (on PostgreSQL — see the events caveat above).
• enabled: false is silent for the schedule but not blind: status reports "enabled": false, and on-demand runs still work and still emit events (on PostgreSQL).

Related pages

• opschain docs config — the database.maintenance.* keys and defaults.
• opschain docs retention — the sweep that triggers the post_retention analyze.
• opschain docs storage — the two backends and their dialect split.
• opschain docs cli/admin-database-status — reading the snapshot.
• opschain docs cli/admin-database-migrations — the migration integrity verbs.