Upgrading an existing install

For on-prem and self-hosted installs that already hold data. A fresh install needs none of this.

Before you upgrade

1. Back up the database and verify that the backup restores. Use pg_dump -Fc and restore it into a scratch TimescaleDB of the same version (timescale/timescaledb:2.28.2-pg15). Wrap the restore in SELECT timescaledb_pre_restore(); … SELECT timescaledb_post_restore();. A dump you have not restored is not a backup.
2. Back up CONNECTOR_ENCRYPTION_KEY with it. It encrypts provider and connector credentials and every tenant's evidence-signing key. A restore without it cannot decrypt credentials. Each tenant also silently gets a new signing key (new kid) on its next export. Bundles signed earlier still verify against the old public key, but anyone who pinned the kid will see a change.
3. Deploy by immutable image tag (the full commit SHA CI publishes), not latest. The Helm chart refuses to render without global.imageTag.

What every boot does

Each API worker runs the migrations at startup. Workers take a Postgres advisory lock, so only one migrates at a time. A statement the catalog shows is already applied (a column, index, table or dropped constraint) is skipped, not re-run. Before this, every boot re-ran about 120 no-op ALTER TABLE … IF NOT EXISTS statements, each taking an ACCESS EXCLUSIVE lock on agent_runs or agent_steps, which deadlocked live ingest whenever a worker restarted under load (tests/test_migration_cost.py).

What the first boot does

The API runs its schema migrations on every boot (backend/init_db.py). After the Phase G2 changes, the boot does only work that is cheap whatever the table size:

• ADD COLUMN … NULL (catalog-only).

• Backfills that touch only rows still NULL, and none on agent_steps. Steps stored without span timing show their own timestamp instead (filled in when read).

• The ON DELETE CASCADE rewrite of foreign keys to agent_runs, added as NOT VALID. Existing rows already passed the old constraint; new rows are checked immediately.

• Indexes on agent_steps and agent_runs, only while the table is under AGENTWATCH_BOOT_INDEX_ROW_LIMIT estimated rows (default 1,000,000). They are built outside the migration transaction: per chunk on the agent_steps hypertable (timescaledb.transaction_per_chunk) and CONCURRENTLY on agent_runs. Above the limit the boot skips them and prints:

  WARNING: indexes not built at boot (table too large): ix_agent_steps_step_type, …
  

Measured

Measured with backend/scripts/migration_scale_test.py, which upgrades a populated database from the schema at c0b9a2e (scratch TimescaleDB 2.28.2, Apple-silicon laptop, Docker):

Dataset	Code	First (upgrade) boot	Every later boot
5M steps / 200k runs	before G2 (ec8c4a9)	549.8 s	49.4 s
1M steps / 40k runs	before G2 (ec8c4a9)	38.5 s	1.9 s
1M steps / 40k runs	after G2	2.1 s	0.3 s

Before G2, the 5M-row boot spent 320 s and 148 s on two UPDATE agent_steps backfills that ran on every boot (steps sent without span timing kept re-qualifying). The next costs were 19 s re-validating foreign keys and about 16 s per index. All four are gone from the boot path.

The 5M run was not repeated after the change: on the test laptop, seeding a second 5M-row database filled the disk. At 1M rows the remaining boot cost is the last_activity_at backfill (0.7 s, first boot only). The out-of-boot scripts.migrate --indexes --validate-fks step took 0.5 s at 1M rows: 18 foreign keys validated, 0.2 s of that on agent_steps.

After the boot: out-of-band steps

Run these once, from the API container or any host with DATABASE_URL. They are idempotent and safe while the API is serving.

cd backend
python -m scripts.migrate --status          # what is still pending
python -m scripts.migrate --indexes         # build indexes the boot skipped
python -m scripts.migrate --validate-fks    # VALIDATE the NOT VALID foreign keys

• --indexes uses the same per-chunk / CONCURRENTLY builds; reads and writes continue. Expect about 16 s per agent_steps index per 5M rows. Until it runs, queries filtering on step_type, network_destination or memory_namespace are slower; nothing is incorrect.
• --validate-fks takes SHARE UPDATE EXCLUSIVE, which does not block reads or writes.

Framework-reference backfill

Rows written before framework tagging (or tagged under an older mapping) are brought up to date by a batched script that never runs on boot:

cd backend
python scripts/backfill_framework_refs.py --dry-run
python scripts/backfill_framework_refs.py --batch 1000 --checkpoint /var/tmp/refs.json

Throughput, measured on a development database (863 finding rows scanned in 0.4 s, dry run): about 2,000 rows/s. Only anomalous steps are scanned, not every step, so a 5M-step install at a 1 % anomaly rate has about 50k rows: under a minute.

--checkpoint saves the last committed id per table after every batch. An interrupted run started again with the same file continues where it stopped. Delete the file to start over. Re-running a finished backfill changes nothing.

Rolling back

Rolling the image back to the previous tag works. Every schema change in this release is additive (new nullable columns, new indexes, new tables), and older code ignores what it doesn't know.

What a rollback does not undo:

• The ON DELETE CASCADE foreign keys stay. Deleting a run deletes its dependent rows (steps, steering state, tags, security events, …), as it does in the new release. Older code doesn't rely on the old NO ACTION behaviour, so this is safe, but it is not reversible without a hand-written migration.
• An older image's boot runs the old agent_steps backfills again, so its boot is slow on a large table, as before.

To restore the old schema exactly, restore the backup from step 1.