1
# Safe recipes
2
3
## Add a NOT NULL column
4
5
Unsafe: `ADD COLUMN x text NOT NULL DEFAULT 'a'` on an old engine rewrites the table.
6
7
Safe:
8
9
1. Add the column nullable, no default.
10
2. Deploy code that writes it on every new row.
11
3. Backfill existing rows in batches.
12
4. Add the `NOT NULL` constraint, validated separately where the engine allows.
13
14
## Rename a column
15
16
Never rename in place while code is running.
17
18
1. Add the new column.
19
2. Deploy code that writes both and reads the new one, falling back to the old.
20
3. Backfill.
21
4. Deploy code that only uses the new one.
22
5. Drop the old column, in a later release.
23
24
## Drop a column
25
26
1. Deploy code that never reads or writes it. Confirm in production over some days.
27
2. Drop it.
28
29
Never in one step. The old running instances will still be selecting it.
30
31
## Add an index
32
33
Use the concurrent form where available.
34
35
```sql
36
CREATE INDEX CONCURRENTLY idx_name ON table (col);
37
```
38
39
It cannot run inside a transaction, takes longer, and can leave an invalid index if it
40
fails. Check validity afterwards and drop and retry if needed.
41
42
## Change a column type
43
44
Usually a table rewrite. Treat it as a rename:
45
46
1. Add a new column of the new type.
47
2. Dual-write.
48
3. Backfill in batches.
49
4. Switch reads.
50
5. Drop the old column later.
51
52
## Add a foreign key
53
54
Adding it validated locks both tables while it checks every row.
55
56
1. Add the constraint `NOT VALID`. It applies to new rows only, and takes a brief lock.
57
2. `VALIDATE CONSTRAINT` separately. This takes a weaker lock and can run for a while.
58
59
## Delete a lot of rows
60
61
Never one statement.
62
63
```sql
64
-- Repeat until zero rows affected, pausing between batches.
65
DELETE FROM t WHERE id IN (
66
  SELECT id FROM t WHERE <predicate> LIMIT 5000
67
);
68
```
69
70
Watch replication lag between batches, and stop if it grows.
71