slow schema migrations

[richvdh]

The following migrations were very slow when we rolled them out to matrix.org recently:

2021-06-16 15:05:21.834 UTC [matrix main] LOG:  statement: ALTER TABLE room_stats_current ADD COLUMN knocked_members INT NOT NULL DEFAULT '0'
2021-06-16 15:06:53.980 UTC [matrix main] LOG:  duration: 92145.243 ms

2021-06-16 15:07:05.682 UTC [matrix main] LOG:  statement: ALTER TABLE room_stats_historical ADD COLUMN knocked_members BIGINT NOT NULL DEFAULT '0'
2021-06-16 15:08:22.587 UTC [matrix main] LOG:  duration: 76904.199 ms

2021-06-16 15:08:22.685 UTC [matrix main] LOG:  statement: ALTER TABLE room_stats_current ADD COLUMN knocked_members INT NOT NULL DEFAULT '0'
2021-06-16 15:08:33.102 UTC [matrix main] LOG:  duration: 10416.733 ms

2021-06-16 15:08:33.103 UTC [matrix main] LOG:  statement: ALTER TABLE room_stats_historical ADD COLUMN knocked_members BIGINT NOT NULL DEFAULT '0'
2021-06-16 15:09:41.457 UTC [matrix main] LOG:  duration: 68354.724 ms

(yes they ran twice. We aborted synapse and they got rolled back the first time.)

[richvdh]

These were added in #6739

[richvdh]

columns with DEFAULT need a table rewrite.

[erikjohnston]

I guess the fix here is to make it nullable and either a) add a bg update to rewrite it with zeros, or b) have the code handle null meaning 0?

[babolivier]

If I remember correctly situations like this one happened in the past and option b) was generally what we've gone for.

[richvdh]

given that you have to do (b) anyway even if you're doing (a) (because you have to cope with the data before the bg update runs), yes, we should do (b).

[erikjohnston]

I'll quickly throw up a PR to fix this.

[richvdh]

I'm going to reopen this again to remind us to fix up matrix.org