Skip to content

Instantly share code, notes, and snippets.

@streamer45
Created November 8, 2021 12:16
Show Gist options
  • Save streamer45/997b726a86b5d2a624ac2af435a66086 to your computer and use it in GitHub Desktop.
Save streamer45/997b726a86b5d2a624ac2af435a66086 to your computer and use it in GitHub Desktop.
Mattermost v6.1.0 schema migration analysis

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment