# Migrate legacy file payloads to `file_data`

Online procedure to move the legacy file payloads (`file.data` and labelled
`file_change.data`) to `file_data` and to delete the orphan `file_change`
rows that carry `data` without a `label`.

- Migration: `backend/src/app/migrations/sql/0157-migrate-legacy-file-data.sql`
- Issue: <https://github.com/penpot/penpot/issues/12784>

The result has to be **exactly** the end state of the migration, because on a
big instance the migration is marked as applied and never runs. This procedure
runs the same statements by hand, in small batched transactions.

Use this only if the automatic migration would take too long on the instance
(millions of snapshots, hundreds of GB in TOAST). Small and medium instances
run the migration automatically on boot and need nothing from here.

Run it before upgrading to 2.19.0.

## What changes

- Every `file_change` row with `data IS NOT NULL AND label IS NOT NULL`
  (a snapshot) gets a `file_data` row with `type='snapshot'`,
  `backend='db'`, `metadata=NULL`, `created_at` from `created_at`,
  `modified_at` from `updated_at`, and `deleted_at` copied as is. Then the
  legacy `file_change.data` is set to `NULL`.
- Every `file` row with `data IS NOT NULL` gets a `file_data` row with
  `type='main'` (and `file_id = id`), the same `backend`/`metadata` mapping
  and the timestamps copied one to one. Then the legacy `file.data` is set
  to `NULL`.
- Every `file_change` row with `data IS NOT NULL AND label IS NULL` (an
  orphan revision) is deleted outright. No read path reaches these rows:
  snapshots resolve through `label IS NOT NULL`, lagged changes read the
  `changes` column (not `data`), and nothing references `file_change` rows
  from other tables. Rows carrying `deleted_at` would be collected by
  `objects-gc` anyway; undated ones would sit in the table and its TOAST
  for ever.

`deleted_at` is copied on purpose: the write path always writes it to
`file_data`, the GC only collects `file_data` rows carrying it, and reads
never filter on `file_data.deleted_at`. Without the copy, migrated rows of
deleted files and snapshots would be orphaned: nobody reads them and nobody
collects them.

## Why this mechanism

A single-statement copy inside the transaction of the startup does not fit a
big instance, so the copy runs here in small batched transactions:

- One statement is one transaction. Never wrap several batches in one
  transaction. Run a single stream of batches at a time.
- `FOR UPDATE SKIP LOCKED`: the batches never block the application and are
  never blocked by it. A row skipped by a lock stays in the index and the
  next execution picks it up.
- Partial temp indexes hold only the pending rows, so every execution finds
  its next batch without rescanning what is done, and migrated rows
  (`data = NULL`) and deleted orphans drop out of the index on their own.
  No cursor is needed. Do not `VACUUM` mid-run to clean them: it would walk
  the whole TOAST. Drop them at the end.
- No `ON CONFLICT DO NOTHING` here on purpose: a duplicate key must abort
  the batch (atomically: it migrates nothing) so the conflict is
  investigated instead of silently skipped. The automatic migration is
  lenient so a small instance never fails to boot; here strictness is what
  keeps the data auditable.

---

## Phase 0 — Before anything

### 0.1 Mark the migration as applied

Do this first: it registers the automatic migration as done, so a backend
restart mid-procedure never runs it on boot (it would copy the whole legacy
payload inside the startup transaction).

```sql
INSERT INTO migrations (module, step)
VALUES ('main', '0157-migrate-legacy-file-data');
```

The module is `main` and the step has no `.sql` (see
`backend/src/app/util/migrations.clj`).

### 0.2 Counters

Repeat the counters any time to follow the progress. Each batch loop ends
when it returns 0; the check in phase 3 confirms nothing is pending.

```sql
-- Remaining legacy rows.
SELECT (SELECT count(*) FROM file_change
         WHERE data IS NOT NULL AND label IS NOT NULL) AS snapshots_remaining,
       (SELECT count(*) FROM file_change
         WHERE data IS NOT NULL AND label IS NULL)     AS orphans_remaining,
       (SELECT count(*) FROM file
         WHERE data IS NOT NULL)                       AS files_remaining;
```

Optional, reference only: how much is already migrated. Skip it unless
curious: it is a full count over the partitioned table and can take a
while on a big instance. For progress, the remaining counters above and
the per-batch counts are enough.

```sql
SELECT (SELECT count(*) FROM file_data
         WHERE type = 'snapshot' AND backend = 'db') AS snapshots_in_file_data,
       (SELECT count(*) FROM file_data
         WHERE type = 'main' AND backend = 'db')     AS mains_in_file_data;
```

## Phase 1 — Temp indexes

Required by the batch loops, created before them. `CONCURRENTLY` so writes
are not blocked; each statement is its own transaction.

```sql
CREATE INDEX CONCURRENTLY tmp_file_change_mig ON file_change (id)
 WHERE data IS NOT NULL AND label IS NOT NULL;
CREATE INDEX CONCURRENTLY tmp_file_change_orphan_mig ON file_change (id)
 WHERE data IS NOT NULL AND label IS NULL;
CREATE INDEX CONCURRENTLY tmp_file_mig ON file (id)
 WHERE data IS NOT NULL;
```

A failed `CONCURRENTLY` build stays `INVALID` and the planner ignores it
silently, so verify all three indexes exist and are valid:

```sql
SELECT indexrelid::regclass AS index, indisvalid AS valid
  FROM pg_index
 WHERE indexrelid::regclass::text LIKE 'tmp_%mig';
```

## Phase 2 — Batch loops

Repeat each statement until it returns 0. Lower `LIMIT` if a batch hits
`statement_timeout` or memory pressure (`SET statement_timeout = '15min'`
per batch is the usual guard).

### 2.1 Delete orphans

Orphans first (or interleaved with the other two; the row sets are
disjoint, and deleting TOAST-heavy rows early frees space for the rest):

```sql
WITH doomed AS (
  SELECT id
    FROM file_change
   WHERE data IS NOT NULL
     AND label IS NULL
   ORDER BY id
   LIMIT 10000
   FOR UPDATE SKIP LOCKED
),
del AS (
  DELETE FROM file_change fc
   USING doomed
   WHERE fc.id = doomed.id
  RETURNING fc.id
)
SELECT count(*) AS deleted
  FROM del;
```

### 2.2 Migrate snapshots

Repeat until it returns 0 (`~120 MB` per transaction at `LIMIT 10000`):

```sql
WITH batch AS (
  SELECT id, file_id, data, created_at, updated_at, deleted_at
    FROM file_change
   WHERE data IS NOT NULL
     AND label IS NOT NULL
   ORDER BY id
   LIMIT 10000
   FOR UPDATE SKIP LOCKED
),
-- NO ON CONFLICT on purpose: duplicates must abort (see above).
-- upd reads from ins (RETURNING), not from batch.
ins AS (
  INSERT INTO file_data (file_id, id, created_at, modified_at, deleted_at,
                         type, backend, metadata, data)
  SELECT file_id, id, created_at, updated_at, deleted_at,
         'snapshot', 'db', NULL, data
    FROM batch
  RETURNING file_id, id
),
-- Only the origin of what was really inserted is nulled. On conflict
-- the whole transaction aborts, so ins == batch whenever this runs.
upd AS (
  UPDATE file_change fc
     SET data = NULL
    FROM ins
   WHERE ins.file_id = fc.file_id
     AND ins.id = fc.id
  RETURNING fc.id
)
SELECT count(*) AS migrated
  FROM upd;
```

### 2.3 Migrate files

Repeat until it returns 0 (tune `LIMIT` to the average `file.data` size;
start at 5000):

```sql
WITH batch AS (
  SELECT id, data, created_at, modified_at, deleted_at
    FROM file
   WHERE data IS NOT NULL
   ORDER BY id
   LIMIT 5000
   FOR UPDATE SKIP LOCKED
),
ins AS (
  INSERT INTO file_data (file_id, id, created_at, modified_at, deleted_at,
                         type, backend, metadata, data)
  SELECT id, id, created_at, modified_at, deleted_at,
         'main', 'db', NULL, data
    FROM batch
  RETURNING file_id, id
),
upd AS (
  UPDATE file f
     SET data = NULL
    FROM ins
   WHERE ins.file_id = f.id
     AND ins.id = f.id
  RETURNING f.id
)
SELECT count(*) AS migrated
  FROM upd;
```

### 2.4 Duplicate keys

If a batch aborts on duplicate key, stop and do not blind-retry it: it
means `file_data` already holds that key. Locate the overlap:

```sql
-- Snapshots with legacy data AND a file_data key already present.
SELECT c.file_id, c.id, c.created_by, c.deleted_at,
       pg_size_pretty(length(c.data)) AS legacy_size,
       pg_size_pretty(length(fd.data)) AS file_data_size,
       fd.modified_at AS file_data_modified_at
  FROM file_change c
  JOIN file_data fd ON fd.file_id = c.file_id
                   AND fd.id = c.id
                   AND fd.type = 'snapshot'
 WHERE c.data IS NOT NULL
 ORDER BY c.id
 LIMIT 20;

-- Files with legacy data AND a file_data key already present.
SELECT f.id,
       pg_size_pretty(length(f.data)) AS legacy_size,
       pg_size_pretty(length(fd.data)) AS file_data_size,
       fd.modified_at AS file_data_modified_at
  FROM file f
  JOIN file_data fd ON fd.file_id = f.id
                    AND fd.id = f.id
                    AND fd.type = 'main'
 WHERE f.data IS NOT NULL
 ORDER BY f.id
 LIMIT 20;
```

Resolve manually, one row at a time, only when `file_data` holds equal or
newer data (compare `modified_at` and sizes above): the legacy copy is
redundant, null the origin, leave `file_data` untouched.

```sql
-- UPDATE file_change SET data = NULL WHERE file_id = '...' AND id = '...';
-- UPDATE file SET data = NULL WHERE id = '...';
```

## Phase 3 — Verification and cleanup

Each loop above already ended returning 0; confirm nothing is pending (if
anything shows up, repeat that loop):

```sql
-- Remaining legacy rows: all three must read 0.
SELECT (SELECT count(*) FROM file_change
         WHERE data IS NOT NULL AND label IS NOT NULL) AS snapshots_remaining,
       (SELECT count(*) FROM file_change
         WHERE data IS NOT NULL AND label IS NULL)     AS orphans_remaining,
       (SELECT count(*) FROM file
         WHERE data IS NOT NULL)                       AS files_remaining;
```

```sql
-- Spot check: migrated rows exist in file_data with backend 'db'.
SELECT fd.file_id, fd.id, fd.type, fd.backend,
       pg_size_pretty(length(fd.data)) AS size_in_file_data
  FROM file_data fd
 ORDER BY fd.file_id, fd.id
 LIMIT 5;
```

Drop the temp indexes first, so the vacuum below skips them (each batch
left its dead entries behind), then reclaim the dead tuples and refresh
planner stats. Plain `VACUUM` does not block writes; `VERBOSE` prints
progress, which matters on big tables:

```sql
DROP INDEX CONCURRENTLY tmp_file_change_mig;
DROP INDEX CONCURRENTLY tmp_file_change_orphan_mig;
DROP INDEX CONCURRENTLY tmp_file_mig;
VACUUM (ANALYZE ON, VERBOSE ON) file_change;
VACUUM (ANALYZE ON, VERBOSE ON) file;
-- file_data is partitioned into 16 tables: the statement walks all of
-- them, so it can take a while. Without it the planner stats of the fresh
-- inserts stay stale until autovacuum catches up.
VACUUM (ANALYZE ON, VERBOSE ON) file_data;
```

## Phase 4 — Upgrade

The migration was marked as applied in phase 0, so the new version skips
it on boot. Deploy normally.