Upgrade Experience: v2025.4.2 → v2026.3.1, ~1M-submissions - Cutting migration time from Hours to Minutes

We upgraded a self-hosted Central install across 12 releases in one step.

It worked in the end, but the event-stamping migration from v2026.1.0 would have kept us offline for 3+ hours.

Below are our numbers, the cause we found, the workaround we tested, and some suggestions.

Setup

Install Docker Compose, standard getodk/central checkout
Upgrade v2025.4.2 → v2026.3.1 (12 releases, includes v2026.1.0)
Host Ubuntu, 32 cores, 64 GB RAM, ext4 on LVM, local SSD/RAID
Docker / Compose 29.9.3 / v5.7.1
Postgres Bundled postgres14 (14.24), stock settings at upgrade time
Database 14 GB (pg_database_size), ~963k submissions, ~965k submission defs, ~1.17M audits, ~80k local blobs

Timings

Step Time
docker compose pull + build --pull ~5 min
Migrations up to the final COMMIT ~2 min
Final COMMIT (unpatched) 48 min and still running when cancelled. One core at 100%, I/O idle (disk 10% busy, IO pressure <0.5%). Projected 3+ more hours.
Cancel (pg_terminate_backend) and rollback A few seconds
Full migration batch with the workaround below, test copy 82 s
Same, production ~2 min

During the long COMMIT the frontend only showed "error loading", and nginx logged 502s for API clients. There was no way to see progress: the work runs as deferred triggers inside one transaction, so counters and the current_event row are invisible to other sessions. We estimated progress from the growth of relation files on disk.

What we found

Within a single migration transaction:

  1. 20251127-01-submission-event-stamping adds submissions.event, numbers every row, and installs:
    • blank_submissions_event_on_update (BEFORE UPDATE, sets NEW.event = NULL when old and new are both non-NULL)
    • set_eventstamp_submissions_at_commit (deferred constraint trigger, WHEN (NEW.event IS NULL), calls get_event())
  2. 20260115-01-submission-event-stamping-unshared-events renumbers every submission with a single bulk UPDATE. Because the triggers from step 1 are already installed, the BEFORE trigger blanks event on every row, and the deferred trigger queues one call per row (~963k).
  3. At COMMIT those calls run one by one. Each one UPDATEs the single row in current_event. Inside one transaction, old versions of that row can't be pruned, so each call scans all the previous versions. The cost grows quadratically: about 100 calls/s early on and falling. The end result also simply renumbers the rows that step 2 had just numbered.

Workaround we used (tested on a restored copy first)

File: getodk/central-backend → lib/model/migrations/20260115-01-submission-event-stamping-unshared-events-01.up.sql

Existing:

TRUNCATE TABLE current_event;

UPDATE
    submissions s1
SET
    event = s2.rowno
...
    s1.id = s2.id
;

INSERT INTO current_event (event) (SELECT coalesce(MAX(event), 0) FROM submissions);

Suggested:

TRUNCATE TABLE current_event;

ALTER TABLE submissions
    DISABLE TRIGGER blank_submissions_event_on_update,
    DISABLE TRIGGER set_eventstamp_submissions_at_commit;

UPDATE
    submissions s1
SET
    event = s2.rowno
...
    s1.id = s2.id
;

ALTER TABLE submissions
    ENABLE TRIGGER blank_submissions_event_on_update,
    ENABLE TRIGGER set_eventstamp_submissions_at_commit;

INSERT INTO current_event (event) (SELECT coalesce(MAX(event), 0) FROM submissions);

Checks afterwards: every submission has a unique event number, current_event = max(event), all migrations applied, both triggers enabled, and updating a submission afterwards assigns max + 1 as expected. We mounted the patched file read-only into the service container through docker-compose.override.yml for one start, then removed the mount.

An earlier attempt that only added AND event IS NULL to eventstamp_submissions_triggerfunction did not help, because the BEFORE trigger had already nulled every row.

Suggestions

Migration performance

  1. Disable the event triggers around bulk backfills (as above), or set event before the triggers are created, so large installs don't queue a deferred trigger call for every row.
  2. get_event() updating one counter row once per row inside a single transaction is quadratic for bulk operations. A sequence, or a set-based assignment for bulk paths, would avoid that.
  3. Give a time estimate in the release notes ("about X minutes per 100k submissions") and test the migration on a database with ~1M submissions before release.
  4. Show a "server is upgrading, please wait" page instead of "error loading" while migrations run, and log progress (for example "stamping submissions: 400k / 963k").

nginx keeps the service's old IP address
After docker compose up -d postgres14 service recreated service, the container got a new IP, but nginx kept proxying to the old one ("connect() failed (113: No route to host)"). The UI showed "error loading" and Collect formList calls returned 502 until we ran docker compose restart nginx. Using Docker's resolver (resolver 127.0.0.11 valid=10s; with a variable in proxy_pass) would make nginx look the address up again automatically.

Visibility

  • Enabling track_functions (or logging from the migration runner) would make long migrations easier to diagnose.

Happy to share more detail or test patches on a copy of our data.

1 Like