Mattermost v6.1.0 schema migration analysis

migration.md

v6.1.0 Schema Migration

MySQL

#	Query	Duration
1	ALTER TABLE Sessions MODIFY Roles text	~240ms
2	ALTER TABLE ChannelMembers MODIFY Roles text	~2m10s
3	ALTER TABLE TeamMembers MODIFY Roles text	~30ms
4	CREATE INDEX idx_jobs_status_type ON Jobs (Status, Type)	~30ms
5	ALTER TABLE Channels ADD LastRootPostAt bigint DEFAULT '0'	~25s
6	UPDATE Channels INNER Join (SELECT Channels.Id channelid, COALESCE(MAX(Posts.CreateAt), 0) as lastrootpost FROM Channels LEFT JOIN Posts FORCE INDEX(idx_posts_channel_id_update_at) ON Channels.Id = Posts.ChannelId WHERE Posts.RootId = '' GROUP BY Channels.Id) AS q ON q.channelid=Channels.Id SET LastRootPostAt=lastrootpost	~1m30s

Notes

• Query #1,#2,#3,#4,#5 can be executed prior to the upgrade.
  • Query #3 causes full table locking: no change to ChannelMembers will be possible for the whole duration of the query.
  • Query #5 causes full table locking: no change to Channels will be possible for the whole duration of the query.

PostgreSQL

#	Query	Duration
1	ALTER TABLE sessions ALTER COLUMN roles TYPE varchar(256)	~2ms
2	ALTER TABLE channelmembers ALTER COLUMN roles TYPE varchar(256)	~2ms
3	ALTER TABLE teammembers ALTER COLUMN roles TYPE varchar(256)	~2ms
4	CREATE INDEX idx_jobs_status_type ON Jobs (Status, Type)	~6ms
5	ALTER TABLE Channels ADD LastRootPostAt bigint DEFAULT '0'	~3ms
6	WITH q AS (SELECT Channels.Id channelid, COALESCE(MAX(Posts.CreateAt), 0) AS lastrootpost FROM Channels LEFT JOIN Posts ON Channels.Id = Posts.ChannelId WHERE Posts.RootId = '' GROUP BY Channels.Id) UPDATE Channels SET LastRootPostAt=q.lastrootpost FROM q WHERE q.channelid=Channels.Id	32s