MM-11886: materialize channel search (#9349)

* materialize PublicChannels table

Introduce triggers for each supported database that automatically maintain a subset of the Channels table corresponding to only public channels. This improves corresponding queries that no longer need to filter out 99% DM channels.

This initial commit modifies the channel store directly for easier code reviewing, but the next wraps an experimental version around it to enable a kill switch in case there are unforeseen performance regressions.

This addresses [MM-11886](https://mattermost.atlassian.net/browse/MM-11886) and [MM-11945](https://mattermost.atlassian.net/browse/MM-11945).

* extract the experimental public channels materialization

Wrap the original channel store with an experimental version that
leverages the materialized public channels, but can be disabled to
fallback to the original implementation.

This addresses MM-11947.

* s/ExperimentalPublicChannelsMaterialization/EnablePublicChannelsMaterialization/

* simplify error handling

* move experimental config listener until after store is initialized

12 files changed, 1774 insertions, 622 deletions

diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go
index 820fe1e9f..4103980c5 100644
--- a/store/sqlstore/channel_store.go
+++ b/store/sqlstore/channel_store.go
@@ -301,6 +301,21 @@ func (s SqlChannelStore) CreateIndexesIfNotExists() {
 	s.CreateFullTextIndexIfNotExists("idx_channel_search_txt", "Channels", "Name, DisplayName, Purpose")
 }
 
+func (s SqlChannelStore) CreateTriggersIfNotExists() error {
+	// See SqlChannelStoreExperimental
+	return nil
+}
+
+func (s SqlChannelStore) MigratePublicChannels() error {
+	// See SqlChannelStoreExperimental
+	return nil
+}
+
+func (s SqlChannelStore) DropPublicChannels() error {
+	// See SqlChannelStoreExperimental
+	return nil
+}
+
 func (s SqlChannelStore) Save(channel *model.Channel, maxChannelsPerTeam int64) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
 		if channel.DeleteAt != 0 {
@@ -804,12 +819,12 @@ func (s SqlChannelStore) GetTeamChannels(teamId string) store.StoreChannel {
 		_, err := s.GetReplica().Select(data, "SELECT * FROM Channels WHERE TeamId = :TeamId And Type != 'D' ORDER BY DisplayName", map[string]interface{}{"TeamId": teamId})
 
 		if err != nil {
-			result.Err = model.NewAppError("SqlChannelStore.GetChannels", "store.sql_channel.get_channels.get.app_error", nil, "teamId="+teamId+",  err="+err.Error(), http.StatusInternalServerError)
+			result.Err = model.NewAppError("SqlChannelStore.GetTeamChannels", "store.sql_channel.get_channels.get.app_error", nil, "teamId="+teamId+",  err="+err.Error(), http.StatusInternalServerError)
 			return
 		}
 
 		if len(*data) == 0 {
-			result.Err = model.NewAppError("SqlChannelStore.GetChannels", "store.sql_channel.get_channels.not_found.app_error", nil, "teamId="+teamId, http.StatusNotFound)
+			result.Err = model.NewAppError("SqlChannelStore.GetTeamChannels", "store.sql_channel.get_channels.not_found.app_error", nil, "teamId="+teamId, http.StatusNotFound)
 			return
 		}
 
@@ -962,16 +977,16 @@ var CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY = `
 		TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole,
 		ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole,
 		ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole
-	FROM 
+	FROM
 		ChannelMembers
-	INNER JOIN 
+	INNER JOIN
 		Channels ON ChannelMembers.ChannelId = Channels.Id
 	LEFT JOIN
 		Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id
 	LEFT JOIN
 		Teams ON Channels.TeamId = Teams.Id
 	LEFT JOIN
-		Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id 
+		Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id
 `
 
 func (s SqlChannelStore) SaveMember(member *model.ChannelMember) store.StoreChannel {
@@ -1988,3 +2003,16 @@ func (s SqlChannelStore) ResetLastPostAt() store.StoreChannel {
 		}
 	})
 }
+
+func (s SqlChannelStore) EnableExperimentalPublicChannelsMaterialization() {
+	// See SqlChannelStoreExperimental
+}
+
+func (s SqlChannelStore) DisableExperimentalPublicChannelsMaterialization() {
+	// See SqlChannelStoreExperimental
+}
+
+func (s SqlChannelStore) IsExperimentalPublicChannelsMaterializationEnabled() bool {
+	// See SqlChannelStoreExperimental
+	return false
+}
diff --git a/store/sqlstore/channel_store_experimental.go b/store/sqlstore/channel_store_experimental.go
new file mode 100644
index 000000000..67576ddc1
--- /dev/null
+++ b/store/sqlstore/channel_store_experimental.go
@@ -0,0 +1,819 @@
+// Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved.
+// See License.txt for license information.
+
+package sqlstore
+
+import (
+	"fmt"
+	"net/http"
+	"sort"
+	"strconv"
+	"strings"
+	"sync/atomic"
+
+	"github.com/pkg/errors"
+
+	"github.com/mattermost/mattermost-server/einterfaces"
+	"github.com/mattermost/mattermost-server/mlog"
+	"github.com/mattermost/mattermost-server/model"
+	"github.com/mattermost/mattermost-server/store"
+)
+
+// publicChannel is a subset of the metadata corresponding to public channels only.
+type publicChannel struct {
+	Id          string `json:"id"`
+	DeleteAt    int64  `json:"delete_at"`
+	TeamId      string `json:"team_id"`
+	DisplayName string `json:"display_name"`
+	Name        string `json:"name"`
+	Header      string `json:"header"`
+	Purpose     string `json:"purpose"`
+}
+
+type SqlChannelStoreExperimental struct {
+	SqlChannelStore
+	experimentalPublicChannelsMaterializationDisabled *uint32
+}
+
+func NewSqlChannelStoreExperimental(sqlStore SqlStore, metrics einterfaces.MetricsInterface, enabled bool) store.ChannelStore {
+	s := &SqlChannelStoreExperimental{
+		SqlChannelStore:                                   *NewSqlChannelStore(sqlStore, metrics).(*SqlChannelStore),
+		experimentalPublicChannelsMaterializationDisabled: new(uint32),
+	}
+
+	if enabled {
+		// Forcibly log, since the default state is enabled and we want this on startup.
+		mlog.Info("Enabling experimental public channels materialization")
+		s.EnableExperimentalPublicChannelsMaterialization()
+	} else {
+		s.DisableExperimentalPublicChannelsMaterialization()
+	}
+
+	if s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		for _, db := range sqlStore.GetAllConns() {
+			tablePublicChannels := db.AddTableWithName(publicChannel{}, "PublicChannels").SetKeys(false, "Id")
+			tablePublicChannels.ColMap("Id").SetMaxSize(26)
+			tablePublicChannels.ColMap("TeamId").SetMaxSize(26)
+			tablePublicChannels.ColMap("DisplayName").SetMaxSize(64)
+			tablePublicChannels.ColMap("Name").SetMaxSize(64)
+			tablePublicChannels.SetUniqueTogether("Name", "TeamId")
+			tablePublicChannels.ColMap("Header").SetMaxSize(1024)
+			tablePublicChannels.ColMap("Purpose").SetMaxSize(250)
+		}
+	}
+
+	return s
+}
+
+// migratePublicChannels initializes the PublicChannels table with data created before the triggers
+// took over keeping it up-to-date.
+func (s SqlChannelStoreExperimental) MigratePublicChannels() error {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.MigratePublicChannels()
+	}
+
+	transaction, err := s.GetMaster().Begin()
+	if err != nil {
+		return err
+	}
+
+	if _, err := transaction.Exec(`
+		INSERT INTO PublicChannels
+		    (Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+		SELECT
+		    c.Id, c.DeleteAt, c.TeamId, c.DisplayName, c.Name, c.Header, c.Purpose
+		FROM
+		    Channels c
+		LEFT JOIN
+		    PublicChannels pc ON (pc.Id = c.Id)
+		WHERE
+		    c.Type = 'O'
+		AND pc.Id IS NULL
+	`); err != nil {
+		return err
+	}
+
+	if err := transaction.Commit(); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+// DropPublicChannels removes the public channels table and all associated triggers.
+func (s SqlChannelStoreExperimental) DropPublicChannels() error {
+	// Only PostgreSQL will honour the transaction when executing the DDL changes below.
+	transaction, err := s.GetMaster().Begin()
+	if err != nil {
+		return err
+	}
+
+	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels ON Channels
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP FUNCTION IF EXISTS channels_copy_to_public_channels
+		`); err != nil {
+			return err
+		}
+	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_insert
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_update
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_delete
+		`); err != nil {
+			return err
+		}
+	} else if s.DriverName() == model.DATABASE_DRIVER_SQLITE {
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_insert
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_update_delete
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_update
+		`); err != nil {
+			return err
+		}
+		if _, err := transaction.Exec(`
+			DROP TRIGGER IF EXISTS trigger_channels_delete
+		`); err != nil {
+			return err
+		}
+	} else {
+		return errors.New("failed to create trigger because of missing driver")
+	}
+
+	if _, err := transaction.Exec(`
+		DROP TABLE IF EXISTS PublicChannels
+	`); err != nil {
+		return err
+	}
+
+	if err := transaction.Commit(); err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func (s SqlChannelStoreExperimental) CreateIndexesIfNotExists() {
+	s.SqlChannelStore.CreateIndexesIfNotExists()
+
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return
+	}
+
+	s.CreateIndexIfNotExists("idx_publicchannels_team_id", "PublicChannels", "TeamId")
+	s.CreateIndexIfNotExists("idx_publicchannels_name", "PublicChannels", "Name")
+	s.CreateIndexIfNotExists("idx_publicchannels_delete_at", "PublicChannels", "DeleteAt")
+	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+		s.CreateIndexIfNotExists("idx_publicchannels_name_lower", "PublicChannels", "lower(Name)")
+		s.CreateIndexIfNotExists("idx_publicchannels_displayname_lower", "PublicChannels", "lower(DisplayName)")
+	}
+	s.CreateFullTextIndexIfNotExists("idx_publicchannels_search_txt", "PublicChannels", "Name, DisplayName, Purpose")
+}
+
+func (s SqlChannelStoreExperimental) CreateTriggersIfNotExists() error {
+	s.SqlChannelStore.CreateTriggersIfNotExists()
+
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return nil
+	}
+
+	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+		if !s.DoesTriggerExist("trigger_channels") {
+			transaction, err := s.GetMaster().Begin()
+			if err != nil {
+				return errors.Wrap(err, "failed to create trigger function")
+			}
+
+			if _, err := transaction.ExecNoTimeout(`
+				CREATE OR REPLACE FUNCTION channels_copy_to_public_channels() RETURNS TRIGGER
+				    SECURITY DEFINER
+				    LANGUAGE plpgsql
+				AS $$
+				    DECLARE
+				    	counter int := 0;
+				    BEGIN
+				    	IF (TG_OP = 'DELETE' AND OLD.Type = 'O') OR (TG_OP = 'UPDATE' AND NEW.Type != 'O') THEN
+					    DELETE FROM
+				    		PublicChannels
+				    	    WHERE
+				    		Id = OLD.Id;
+				    	ELSEIF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.Type = 'O' THEN
+				    	    UPDATE
+				    		PublicChannels
+					    SET
+				    	        DeleteAt = NEW.DeleteAt,
+				    		TeamId = NEW.TeamId,
+				    		DisplayName = NEW.DisplayName,
+				    		Name = NEW.Name,
+				    		Header = NEW.Header,
+				    		Purpose = NEW.Purpose
+				    	    WHERE
+				    	        Id = NEW.Id;
+
+				    	    -- There's a race condition here where the INSERT might fail, though this should only occur
+					    -- if PublicChannels had been modified outside of the triggers. We could improve this with 
+					    -- the UPSERT functionality in Postgres 9.5+ once we support same.
+				    	    IF NOT FOUND THEN
+				    	        INSERT INTO
+				    		    PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+				    		VALUES
+				    		    (NEW.Id, NEW.DeleteAt, NEW.TeamId, NEW.DisplayName, NEW.Name, NEW.Header, NEW.Purpose);
+				    	    END IF;
+				    	END IF;
+
+				    	RETURN NULL;
+				    END
+				$$;
+			`); err != nil {
+				return errors.Wrap(err, "failed to create trigger function")
+			}
+
+			if _, err := transaction.ExecNoTimeout(`
+				CREATE TRIGGER
+				    trigger_channels
+				AFTER INSERT OR UPDATE OR DELETE ON
+				    Channels
+				FOR EACH ROW EXECUTE PROCEDURE
+				    channels_copy_to_public_channels();
+			`); err != nil {
+				return errors.Wrap(err, "failed to create trigger")
+			}
+
+			if err := transaction.Commit(); err != nil {
+				return errors.Wrap(err, "failed to create trigger function")
+			}
+		}
+	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
+		// Note that DDL statements in MySQL (CREATE TABLE, CREATE TRIGGER, etc.) cannot
+		// be rolled back inside a transaction (unlike PostgreSQL), so there's no point in
+		// wrapping what follows inside a transaction.
+
+		if !s.DoesTriggerExist("trigger_channels_insert") {
+			if _, err := s.GetMaster().ExecNoTimeout(`
+				CREATE TRIGGER
+				    trigger_channels_insert
+				AFTER INSERT ON
+				    Channels
+				FOR EACH ROW
+				BEGIN
+				    IF NEW.Type = 'O' THEN
+				        INSERT INTO
+					    PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+					VALUES
+					    (NEW.Id, NEW.DeleteAt, NEW.TeamId, NEW.DisplayName, NEW.Name, NEW.Header, NEW.Purpose)
+					ON DUPLICATE KEY UPDATE
+					    DeleteAt = NEW.DeleteAt,
+					    TeamId = NEW.TeamId,
+					    DisplayName = NEW.DisplayName,
+					    Name = NEW.Name,
+					    Header = NEW.Header,
+					    Purpose = NEW.Purpose;
+				    END IF;
+				END;
+			`); err != nil {
+				return errors.Wrap(err, "failed to create trigger_channels_insert trigger")
+			}
+		}
+
+		if !s.DoesTriggerExist("trigger_channels_update") {
+			if _, err := s.GetMaster().ExecNoTimeout(`
+				CREATE TRIGGER
+			  	    trigger_channels_update
+				AFTER UPDATE ON
+				    Channels
+				FOR EACH ROW
+				BEGIN
+				    IF OLD.Type = 'O' AND NEW.Type != 'O' THEN
+				        DELETE FROM
+					    PublicChannels
+					WHERE
+					    Id = NEW.Id;
+				    ELSEIF NEW.Type = 'O' THEN
+					INSERT INTO
+					    PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+					VALUES
+					    (NEW.Id, NEW.DeleteAt, NEW.TeamId, NEW.DisplayName, NEW.Name, NEW.Header, NEW.Purpose)
+					ON DUPLICATE KEY UPDATE
+					    DeleteAt = NEW.DeleteAt,
+					    TeamId = NEW.TeamId,
+					    DisplayName = NEW.DisplayName,
+					    Name = NEW.Name,
+					    Header = NEW.Header,
+					    Purpose = NEW.Purpose;
+				    END IF;
+				END;
+			`); err != nil {
+				return errors.Wrap(err, "failed to create trigger_channels_update trigger")
+			}
+		}
+
+		if !s.DoesTriggerExist("trigger_channels_delete") {
+			if _, err := s.GetMaster().ExecNoTimeout(`
+				CREATE TRIGGER
+				    trigger_channels_delete
+				AFTER DELETE ON
+				    Channels
+				FOR EACH ROW
+				BEGIN
+				    IF OLD.Type = 'O' THEN
+				        DELETE FROM
+					    PublicChannels
+					WHERE
+					    Id = OLD.Id;
+				    END IF;
+				END;
+			`); err != nil {
+				return errors.Wrap(err, "failed to create trigger_channels_delete trigger")
+			}
+		}
+	} else if s.DriverName() == model.DATABASE_DRIVER_SQLITE {
+		if _, err := s.GetMaster().ExecNoTimeout(`
+			CREATE TRIGGER IF NOT EXISTS
+			    trigger_channels_insert
+			AFTER INSERT ON
+			    Channels
+			FOR EACH ROW
+			WHEN NEW.Type = 'O'
+			BEGIN
+			    -- Ideally, we'd leverage ON CONFLICT DO UPDATE below and make this INSERT resilient to pre-existing
+			    -- data. However, the version of Sqlite we're compiling against doesn't support this. This isn't
+		    	    -- critical, though, since we don't support Sqlite in production.
+			    INSERT INTO
+			        PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+			    VALUES
+			        (NEW.Id, NEW.DeleteAt, NEW.TeamId, NEW.DisplayName, NEW.Name, NEW.Header, NEW.Purpose);
+			END;
+		`); err != nil {
+			return errors.Wrap(err, "failed to create trigger_channels_insert trigger")
+		}
+
+		if _, err := s.GetMaster().ExecNoTimeout(`
+			CREATE TRIGGER IF NOT EXISTS
+			    trigger_channels_update_delete
+			AFTER UPDATE ON
+			    Channels
+			FOR EACH ROW
+			WHEN
+			    OLD.Type = 'O'
+			AND NEW.Type != 'O'
+			BEGIN
+			    DELETE FROM
+			        PublicChannels
+			    WHERE
+			        Id = NEW.Id;
+			END;
+		`); err != nil {
+			return errors.Wrap(err, "failed to create trigger_channels_update_delete trigger")
+		}
+
+		if _, err := s.GetMaster().ExecNoTimeout(`
+			CREATE TRIGGER IF NOT EXISTS
+			    trigger_channels_update
+			AFTER UPDATE ON
+			    Channels
+			FOR EACH ROW
+			WHEN
+			    OLD.Type != 'O'
+			AND NEW.Type = 'O'
+			BEGIN
+			    -- See comments re: ON CONFLICT DO UPDATE above that would apply here as well.
+			    UPDATE
+			        PublicChannels
+			    SET
+			        DeleteAt = NEW.DeleteAt,
+			        TeamId = NEW.TeamId,
+			        DisplayName = NEW.DisplayName,
+			        Name = NEW.Name,
+			        Header = NEW.Header,
+			        Purpose = NEW.Purpose
+			    WHERE
+			        Id = NEW.Id;
+			END;
+		`); err != nil {
+			return errors.Wrap(err, "failed to create trigger_channels_update trigger")
+		}
+
+		if _, err := s.GetMaster().ExecNoTimeout(`
+			CREATE TRIGGER IF NOT EXISTS
+			    trigger_channels_delete
+			AFTER UPDATE ON
+			    Channels
+			FOR EACH ROW
+			WHEN
+			    OLD.Type = 'O'
+			BEGIN
+			    DELETE FROM
+			        PublicChannels
+			    WHERE
+				Id = OLD.Id;
+			END;
+		`); err != nil {
+			return errors.Wrap(err, "failed to create trigger_channels_delete trigger")
+		}
+	} else {
+		return errors.New("failed to create trigger because of missing driver")
+	}
+
+	return nil
+}
+
+func (s SqlChannelStoreExperimental) GetMoreChannels(teamId string, userId string, offset int, limit int) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.GetMoreChannels(teamId, userId, offset, limit)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		data := &model.ChannelList{}
+		_, err := s.GetReplica().Select(data, `
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels c ON (c.Id = Channels.Id)
+			WHERE
+			    c.TeamId = :TeamId
+			AND c.DeleteAt = 0
+			AND c.Id NOT IN (
+			    SELECT
+			        c.Id
+			    FROM
+			        PublicChannels c
+			    JOIN
+			        ChannelMembers cm ON (cm.ChannelId = c.Id)
+			    WHERE
+			        c.TeamId = :TeamId
+			    AND cm.UserId = :UserId
+			    AND c.DeleteAt = 0
+			)
+			ORDER BY
+				c.DisplayName
+			LIMIT :Limit
+			OFFSET :Offset
+		`, map[string]interface{}{
+			"TeamId": teamId,
+			"UserId": userId,
+			"Limit":  limit,
+			"Offset": offset,
+		})
+
+		if err != nil {
+			result.Err = model.NewAppError("SqlChannelStore.GetMoreChannels", "store.sql_channel.get_more_channels.get.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError)
+			return
+		}
+
+		result.Data = data
+	})
+}
+
+func (s SqlChannelStoreExperimental) GetPublicChannelsForTeam(teamId string, offset int, limit int) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.GetPublicChannelsForTeam(teamId, offset, limit)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		data := &model.ChannelList{}
+		_, err := s.GetReplica().Select(data, `
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels pc ON (pc.Id = Channels.Id)
+			WHERE
+			    pc.TeamId = :TeamId
+			AND pc.DeleteAt = 0
+			ORDER BY pc.DisplayName
+			LIMIT :Limit
+			OFFSET :Offset
+		`, map[string]interface{}{
+			"TeamId": teamId,
+			"Limit":  limit,
+			"Offset": offset,
+		})
+
+		if err != nil {
+			result.Err = model.NewAppError("SqlChannelStore.GetPublicChannelsForTeam", "store.sql_channel.get_public_channels.get.app_error", nil, "teamId="+teamId+", err="+err.Error(), http.StatusInternalServerError)
+			return
+		}
+
+		result.Data = data
+	})
+}
+
+func (s SqlChannelStoreExperimental) GetPublicChannelsByIdsForTeam(teamId string, channelIds []string) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.GetPublicChannelsByIdsForTeam(teamId, channelIds)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		props := make(map[string]interface{})
+		props["teamId"] = teamId
+
+		idQuery := ""
+
+		for index, channelId := range channelIds {
+			if len(idQuery) > 0 {
+				idQuery += ", "
+			}
+
+			props["channelId"+strconv.Itoa(index)] = channelId
+			idQuery += ":channelId" + strconv.Itoa(index)
+		}
+
+		data := &model.ChannelList{}
+		_, err := s.GetReplica().Select(data, `
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels pc ON (pc.Id = Channels.Id)
+			WHERE
+			    pc.TeamId = :teamId
+			AND pc.DeleteAt = 0
+			AND pc.Id IN (`+idQuery+`)
+			ORDER BY pc.DisplayName
+		`, props)
+
+		if err != nil {
+			result.Err = model.NewAppError("SqlChannelStore.GetPublicChannelsByIdsForTeam", "store.sql_channel.get_channels_by_ids.get.app_error", nil, err.Error(), http.StatusInternalServerError)
+		}
+
+		if len(*data) == 0 {
+			result.Err = model.NewAppError("SqlChannelStore.GetPublicChannelsByIdsForTeam", "store.sql_channel.get_channels_by_ids.not_found.app_error", nil, "", http.StatusNotFound)
+		}
+
+		result.Data = data
+	})
+}
+
+func (s SqlChannelStoreExperimental) AutocompleteInTeam(teamId string, term string, includeDeleted bool) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.AutocompleteInTeam(teamId, term, includeDeleted)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		deleteFilter := "AND c.DeleteAt = 0"
+		if includeDeleted {
+			deleteFilter = ""
+		}
+
+		queryFormat := `
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels c ON (c.Id = Channels.Id)
+			WHERE
+			    c.TeamId = :TeamId
+			    ` + deleteFilter + `
+			    %v
+			LIMIT 50
+		`
+
+		var channels model.ChannelList
+
+		if likeClause, likeTerm := s.buildLIKEClause(term); likeClause == "" {
+			if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId}); err != nil {
+				result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError)
+			}
+		} else {
+			// Using a UNION results in index_merge and fulltext queries and is much faster than the ref
+			// query you would get using an OR of the LIKE and full-text clauses.
+			fulltextClause, fulltextTerm := s.buildFulltextClause(term)
+			likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause)
+			fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause)
+			query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery)
+
+			if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"TeamId": teamId, "LikeTerm": likeTerm, "FulltextTerm": fulltextTerm}); err != nil {
+				result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError)
+			}
+		}
+
+		sort.Slice(channels, func(a, b int) bool {
+			return strings.ToLower(channels[a].DisplayName) < strings.ToLower(channels[b].DisplayName)
+		})
+		result.Data = &channels
+	})
+}
+
+func (s SqlChannelStoreExperimental) SearchInTeam(teamId string, term string, includeDeleted bool) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.SearchInTeam(teamId, term, includeDeleted)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		deleteFilter := "AND c.DeleteAt = 0"
+		if includeDeleted {
+			deleteFilter = ""
+		}
+
+		*result = s.performSearch(`
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels c ON (c.Id = Channels.Id)
+			WHERE
+			    c.TeamId = :TeamId
+			    `+deleteFilter+`
+			    SEARCH_CLAUSE
+			ORDER BY c.DisplayName
+			LIMIT 100
+		`, term, map[string]interface{}{
+			"TeamId": teamId,
+		})
+	})
+}
+
+func (s SqlChannelStoreExperimental) SearchMore(userId string, teamId string, term string) store.StoreChannel {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.SearchMore(userId, teamId, term)
+	}
+
+	return store.Do(func(result *store.StoreResult) {
+		*result = s.performSearch(`
+			SELECT
+			    Channels.*
+			FROM
+			    Channels
+			JOIN
+			    PublicChannels c ON (c.Id = Channels.Id)
+			WHERE
+			    c.TeamId = :TeamId
+			AND c.DeleteAt = 0
+			AND c.Id NOT IN (
+			    SELECT
+			        c.Id
+			    FROM
+			        PublicChannels c
+			    JOIN
+			        ChannelMembers cm ON (cm.ChannelId = c.Id)
+			    WHERE
+			        c.TeamId = :TeamId
+			    AND cm.UserId = :UserId
+			    AND c.DeleteAt = 0
+		        )
+			SEARCH_CLAUSE
+			ORDER BY c.DisplayName
+			LIMIT 100
+		`, term, map[string]interface{}{
+			"TeamId": teamId,
+			"UserId": userId,
+		})
+	})
+}
+
+func (s SqlChannelStoreExperimental) buildLIKEClause(term string) (likeClause, likeTerm string) {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.buildLIKEClause(term)
+	}
+
+	likeTerm = term
+	searchColumns := "c.Name, c.DisplayName, c.Purpose"
+
+	// These chars must be removed from the like query.
+	for _, c := range ignoreLikeSearchChar {
+		likeTerm = strings.Replace(likeTerm, c, "", -1)
+	}
+
+	// These chars must be escaped in the like query.
+	for _, c := range escapeLikeSearchChar {
+		likeTerm = strings.Replace(likeTerm, c, "*"+c, -1)
+	}
+
+	if likeTerm == "" {
+		return
+	}
+
+	// Prepare the LIKE portion of the query.
+	var searchFields []string
+	for _, field := range strings.Split(searchColumns, ", ") {
+		if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+			searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(%s) escape '*'", field, ":LikeTerm"))
+		} else {
+			searchFields = append(searchFields, fmt.Sprintf("%s LIKE %s escape '*'", field, ":LikeTerm"))
+		}
+	}
+
+	likeClause = fmt.Sprintf("(%s)", strings.Join(searchFields, " OR "))
+	likeTerm += "%"
+	return
+}
+
+func (s SqlChannelStoreExperimental) buildFulltextClause(term string) (fulltextClause, fulltextTerm string) {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.buildFulltextClause(term)
+	}
+
+	// Copy the terms as we will need to prepare them differently for each search type.
+	fulltextTerm = term
+
+	searchColumns := "c.Name, c.DisplayName, c.Purpose"
+
+	// These chars must be treated as spaces in the fulltext query.
+	for _, c := range spaceFulltextSearchChar {
+		fulltextTerm = strings.Replace(fulltextTerm, c, " ", -1)
+	}
+
+	// Prepare the FULLTEXT portion of the query.
+	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+		fulltextTerm = strings.Replace(fulltextTerm, "|", "", -1)
+
+		splitTerm := strings.Fields(fulltextTerm)
+		for i, t := range strings.Fields(fulltextTerm) {
+			if i == len(splitTerm)-1 {
+				splitTerm[i] = t + ":*"
+			} else {
+				splitTerm[i] = t + ":* &"
+			}
+		}
+
+		fulltextTerm = strings.Join(splitTerm, " ")
+
+		fulltextClause = fmt.Sprintf("((%s) @@ to_tsquery(:FulltextTerm))", convertMySQLFullTextColumnsToPostgres(searchColumns))
+	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
+		splitTerm := strings.Fields(fulltextTerm)
+		for i, t := range strings.Fields(fulltextTerm) {
+			splitTerm[i] = "+" + t + "*"
+		}
+
+		fulltextTerm = strings.Join(splitTerm, " ")
+
+		fulltextClause = fmt.Sprintf("MATCH(%s) AGAINST (:FulltextTerm IN BOOLEAN MODE)", searchColumns)
+	}
+
+	return
+}
+
+func (s SqlChannelStoreExperimental) performSearch(searchQuery string, term string, parameters map[string]interface{}) store.StoreResult {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		return s.SqlChannelStore.performSearch(searchQuery, term, parameters)
+	}
+
+	result := store.StoreResult{}
+
+	likeClause, likeTerm := s.buildLIKEClause(term)
+	if likeTerm == "" {
+		// If the likeTerm is empty after preparing, then don't bother searching.
+		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1)
+	} else {
+		parameters["LikeTerm"] = likeTerm
+		fulltextClause, fulltextTerm := s.buildFulltextClause(term)
+		parameters["FulltextTerm"] = fulltextTerm
+		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeClause+" OR "+fulltextClause+")", 1)
+	}
+
+	var channels model.ChannelList
+
+	if _, err := s.GetReplica().Select(&channels, searchQuery, parameters); err != nil {
+		result.Err = model.NewAppError("SqlChannelStore.Search", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError)
+		return result
+	}
+
+	result.Data = &channels
+	return result
+}
+
+func (s SqlChannelStoreExperimental) EnableExperimentalPublicChannelsMaterialization() {
+	if !s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		mlog.Info("Enabling experimental public channels materialization")
+	}
+
+	atomic.StoreUint32(s.experimentalPublicChannelsMaterializationDisabled, 0)
+}
+
+func (s SqlChannelStoreExperimental) DisableExperimentalPublicChannelsMaterialization() {
+	if s.IsExperimentalPublicChannelsMaterializationEnabled() {
+		mlog.Info("Disabling experimental public channels materialization")
+	}
+
+	atomic.StoreUint32(s.experimentalPublicChannelsMaterializationDisabled, 1)
+}
+
+func (s SqlChannelStoreExperimental) IsExperimentalPublicChannelsMaterializationEnabled() bool {
+	return atomic.LoadUint32(s.experimentalPublicChannelsMaterializationDisabled) == 0
+}
diff --git a/store/sqlstore/channel_store_test.go b/store/sqlstore/channel_store_test.go
index 0e8b4191a..5eb84afcd 100644
--- a/store/sqlstore/channel_store_test.go
+++ b/store/sqlstore/channel_store_test.go
@@ -14,7 +14,7 @@ import (
 )
 
 func TestChannelStore(t *testing.T) {
-	StoreTest(t, storetest.TestChannelStore)
+	StoreTestWithSqlSupplier(t, storetest.TestChannelStore)
 }
 
 func TestChannelStoreInternalDataTypes(t *testing.T) {
diff --git a/store/sqlstore/store.go b/store/sqlstore/store.go
index 500f98235..df912028b 100644
--- a/store/sqlstore/store.go
+++ b/store/sqlstore/store.go
@@ -51,6 +51,7 @@ type SqlStore interface {
 	MarkSystemRanUnitTests()
 	DoesTableExist(tablename string) bool
 	DoesColumnExist(tableName string, columName string) bool
+	DoesTriggerExist(triggerName string) bool
 	CreateColumnIfNotExists(tableName string, columnName string, mySqlColType string, postgresColType string, defaultValue string) bool
 	CreateColumnIfNotExistsNoDefault(tableName string, columnName string, mySqlColType string, postgresColType string) bool
 	RemoveColumnIfExists(tableName string, columnName string) bool
diff --git a/store/sqlstore/store_test.go b/store/sqlstore/store_test.go
index 58065d65d..55002aee2 100644
--- a/store/sqlstore/store_test.go
+++ b/store/sqlstore/store_test.go
@@ -16,10 +16,11 @@ import (
 )
 
 var storeTypes = []*struct {
-	Name      string
-	Func      func() (*storetest.RunningContainer, *model.SqlSettings, error)
-	Container *storetest.RunningContainer
-	Store     store.Store
+	Name        string
+	Func        func() (*storetest.RunningContainer, *model.SqlSettings, error)
+	Container   *storetest.RunningContainer
+	SqlSupplier *SqlSupplier
+	Store       store.Store
 }{
 	{
 		Name: "MySQL",
@@ -44,6 +45,19 @@ func StoreTest(t *testing.T, f func(*testing.T, store.Store)) {
 	}
 }
 
+func StoreTestWithSqlSupplier(t *testing.T, f func(*testing.T, store.Store, storetest.SqlSupplier)) {
+	defer func() {
+		if err := recover(); err != nil {
+			tearDownStores()
+			panic(err)
+		}
+	}()
+	for _, st := range storeTypes {
+		st := st
+		t.Run(st.Name, func(t *testing.T) { f(t, st.Store, st.SqlSupplier) })
+	}
+}
+
 func initStores() {
 	defer func() {
 		if err := recover(); err != nil {
@@ -64,7 +78,8 @@ func initStores() {
 				return
 			}
 			st.Container = container
-			st.Store = store.NewLayeredStore(NewSqlSupplier(*settings, nil), nil, nil)
+			st.SqlSupplier = NewSqlSupplier(*settings, nil)
+			st.Store = store.NewLayeredStore(st.SqlSupplier, nil, nil)
 			st.Store.MarkSystemRanUnitTests()
 		}()
 	}
diff --git a/store/sqlstore/supplier.go b/store/sqlstore/supplier.go
index 6c49d91fb..d1d7564f7 100644
--- a/store/sqlstore/supplier.go
+++ b/store/sqlstore/supplier.go
@@ -33,6 +33,7 @@ const (
 )
 
 const (
+	EXIT_GENERIC_FAILURE             = 1
 	EXIT_CREATE_TABLE                = 100
 	EXIT_DB_OPEN                     = 101
 	EXIT_PING                        = 102
@@ -116,8 +117,13 @@ func NewSqlSupplier(settings model.SqlSettings, metrics einterfaces.MetricsInter
 
 	supplier.initConnection()
 
+	enableExperimentalPublicChannelsMaterialization := true
+	if settings.EnablePublicChannelsMaterialization != nil && !*settings.EnablePublicChannelsMaterialization {
+		enableExperimentalPublicChannelsMaterialization = false
+	}
+
 	supplier.oldStores.team = NewSqlTeamStore(supplier)
-	supplier.oldStores.channel = NewSqlChannelStore(supplier, metrics)
+	supplier.oldStores.channel = NewSqlChannelStoreExperimental(supplier, metrics, enableExperimentalPublicChannelsMaterialization)
 	supplier.oldStores.post = NewSqlPostStore(supplier, metrics)
 	supplier.oldStores.user = NewSqlUserStore(supplier, metrics)
 	supplier.oldStores.audit = NewSqlAuditStore(supplier)
@@ -151,10 +157,19 @@ func NewSqlSupplier(settings model.SqlSettings, metrics einterfaces.MetricsInter
 		os.Exit(EXIT_CREATE_TABLE)
 	}
 
+	// This store's triggers should exist before the migration is run to ensure the
+	// corresponding tables stay in sync. Whether or not a trigger should be created before
+	// or after a migration is likely to be decided on a case-by-case basis.
+	if err := supplier.oldStores.channel.(*SqlChannelStoreExperimental).CreateTriggersIfNotExists(); err != nil {
+		mlog.Critical("Error creating triggers", mlog.Err(err))
+		time.Sleep(time.Second)
+		os.Exit(EXIT_GENERIC_FAILURE)
+	}
+
 	UpgradeDatabase(supplier)
 
 	supplier.oldStores.team.(*SqlTeamStore).CreateIndexesIfNotExists()
-	supplier.oldStores.channel.(*SqlChannelStore).CreateIndexesIfNotExists()
+	supplier.oldStores.channel.(*SqlChannelStoreExperimental).CreateIndexesIfNotExists()
 	supplier.oldStores.post.(*SqlPostStore).CreateIndexesIfNotExists()
 	supplier.oldStores.user.(*SqlUserStore).CreateIndexesIfNotExists()
 	supplier.oldStores.audit.(*SqlAuditStore).CreateIndexesIfNotExists()
@@ -461,6 +476,52 @@ func (ss *SqlSupplier) DoesColumnExist(tableName string, columnName string) bool
 	}
 }
 
+func (ss *SqlSupplier) DoesTriggerExist(triggerName string) bool {
+	if ss.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+		count, err := ss.GetMaster().SelectInt(`
+			SELECT
+				COUNT(0)
+			FROM
+				pg_trigger
+			WHERE
+				tgname = $1
+		`, triggerName)
+
+		if err != nil {
+			mlog.Critical(fmt.Sprintf("Failed to check if trigger exists %v", err))
+			time.Sleep(time.Second)
+			os.Exit(EXIT_GENERIC_FAILURE)
+		}
+
+		return count > 0
+
+	} else if ss.DriverName() == model.DATABASE_DRIVER_MYSQL {
+		count, err := ss.GetMaster().SelectInt(`
+			SELECT
+				COUNT(0)
+			FROM
+				information_schema.triggers
+			WHERE
+				trigger_schema = DATABASE()
+			AND	trigger_name = ?
+		`, triggerName)
+
+		if err != nil {
+			mlog.Critical(fmt.Sprintf("Failed to check if trigger exists %v", err))
+			time.Sleep(time.Second)
+			os.Exit(EXIT_GENERIC_FAILURE)
+		}
+
+		return count > 0
+
+	} else {
+		mlog.Critical("Failed to check if column exists because of missing driver")
+		time.Sleep(time.Second)
+		os.Exit(EXIT_GENERIC_FAILURE)
+		return false
+	}
+}
+
 func (ss *SqlSupplier) CreateColumnIfNotExists(tableName string, columnName string, mySqlColType string, postgresColType string, defaultValue string) bool {
 
 	if ss.DoesColumnExist(tableName, columnName) {
diff --git a/store/sqlstore/upgrade.go b/store/sqlstore/upgrade.go
index 5f74dbfb1..a8be96172 100644
--- a/store/sqlstore/upgrade.go
+++ b/store/sqlstore/upgrade.go
@@ -489,7 +489,6 @@ func UpgradeDatabaseToVersion53(sqlStore SqlStore) {
 	if shouldPerformUpgrade(sqlStore, VERSION_5_2_0, VERSION_5_3_0) {
 		saveSchemaVersion(sqlStore, VERSION_5_3_0)
 	}
-
 }
 
 func UpgradeDatabaseToVersion54(sqlStore SqlStore) {
@@ -497,6 +496,11 @@ func UpgradeDatabaseToVersion54(sqlStore SqlStore) {
 	// if shouldPerformUpgrade(sqlStore, VERSION_5_3_0, VERSION_5_4_0) {
 	sqlStore.AlterColumnTypeIfExists("OutgoingWebhooks", "Description", "varchar(500)", "varchar(500)")
 	sqlStore.AlterColumnTypeIfExists("IncomingWebhooks", "Description", "varchar(500)", "varchar(500)")
+
+	if err := sqlStore.Channel().MigratePublicChannels(); err != nil {
+		mlog.Critical("Failed to migrate PublicChannels table", mlog.Err(err))
+		time.Sleep(time.Second)
+		os.Exit(EXIT_GENERIC_FAILURE)
+	}
 	// 	saveSchemaVersion(sqlStore, VERSION_5_4_0)
-	// }
 }
diff --git a/store/store.go b/store/store.go
index 8da70d7ec..8c731f8d5 100644
--- a/store/store.go
+++ b/store/store.go
@@ -174,6 +174,11 @@ type ChannelStore interface {
 	ResetAllChannelSchemes() StoreChannel
 	ClearAllCustomRoleAssignments() StoreChannel
 	ResetLastPostAt() StoreChannel
+	MigratePublicChannels() error
+	DropPublicChannels() error
+	EnableExperimentalPublicChannelsMaterialization()
+	DisableExperimentalPublicChannelsMaterialization()
+	IsExperimentalPublicChannelsMaterializationEnabled() bool
 }
 
 type ChannelMemberHistoryStore interface {
diff --git a/store/storetest/channel_store.go b/store/storetest/channel_store.go
index 54316d1ce..11e058f70 100644
--- a/store/storetest/channel_store.go
+++ b/store/storetest/channel_store.go
@@ -12,52 +12,74 @@ import (
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 
+	"github.com/mattermost/gorp"
 	"github.com/mattermost/mattermost-server/model"
 	"github.com/mattermost/mattermost-server/store"
 )
 
-func TestChannelStore(t *testing.T, ss store.Store) {
+type SqlSupplier interface {
+	GetMaster() *gorp.DbMap
+}
+
+func TestChannelStore(t *testing.T, ss store.Store, s SqlSupplier) {
 	createDefaultRoles(t, ss)
 
-	t.Run("Save", func(t *testing.T) { testChannelStoreSave(t, ss) })
-	t.Run("SaveDirectChannel", func(t *testing.T) { testChannelStoreSaveDirectChannel(t, ss) })
-	t.Run("CreateDirectChannel", func(t *testing.T) { testChannelStoreCreateDirectChannel(t, ss) })
-	t.Run("Update", func(t *testing.T) { testChannelStoreUpdate(t, ss) })
-	t.Run("GetChannelUnread", func(t *testing.T) { testGetChannelUnread(t, ss) })
-	t.Run("Get", func(t *testing.T) { testChannelStoreGet(t, ss) })
-	t.Run("GetForPost", func(t *testing.T) { testChannelStoreGetForPost(t, ss) })
-	t.Run("Restore", func(t *testing.T) { testChannelStoreRestore(t, ss) })
-	t.Run("Delete", func(t *testing.T) { testChannelStoreDelete(t, ss) })
-	t.Run("GetByName", func(t *testing.T) { testChannelStoreGetByName(t, ss) })
-	t.Run("GetByNames", func(t *testing.T) { testChannelStoreGetByNames(t, ss) })
-	t.Run("GetDeletedByName", func(t *testing.T) { testChannelStoreGetDeletedByName(t, ss) })
-	t.Run("GetDeleted", func(t *testing.T) { testChannelStoreGetDeleted(t, ss) })
-	t.Run("ChannelMemberStore", func(t *testing.T) { testChannelMemberStore(t, ss) })
-	t.Run("ChannelDeleteMemberStore", func(t *testing.T) { testChannelDeleteMemberStore(t, ss) })
-	t.Run("GetChannels", func(t *testing.T) { testChannelStoreGetChannels(t, ss) })
-	t.Run("GetMoreChannels", func(t *testing.T) { testChannelStoreGetMoreChannels(t, ss) })
-	t.Run("GetPublicChannelsForTeam", func(t *testing.T) { testChannelStoreGetPublicChannelsForTeam(t, ss) })
-	t.Run("GetPublicChannelsByIdsForTeam", func(t *testing.T) { testChannelStoreGetPublicChannelsByIdsForTeam(t, ss) })
-	t.Run("GetChannelCounts", func(t *testing.T) { testChannelStoreGetChannelCounts(t, ss) })
-	t.Run("GetMembersForUser", func(t *testing.T) { testChannelStoreGetMembersForUser(t, ss) })
-	t.Run("UpdateLastViewedAt", func(t *testing.T) { testChannelStoreUpdateLastViewedAt(t, ss) })
-	t.Run("IncrementMentionCount", func(t *testing.T) { testChannelStoreIncrementMentionCount(t, ss) })
-	t.Run("UpdateChannelMember", func(t *testing.T) { testUpdateChannelMember(t, ss) })
-	t.Run("GetMember", func(t *testing.T) { testGetMember(t, ss) })
-	t.Run("GetMemberForPost", func(t *testing.T) { testChannelStoreGetMemberForPost(t, ss) })
-	t.Run("GetMemberCount", func(t *testing.T) { testGetMemberCount(t, ss) })
-	t.Run("SearchMore", func(t *testing.T) { testChannelStoreSearchMore(t, ss) })
-	t.Run("SearchInTeam", func(t *testing.T) { testChannelStoreSearchInTeam(t, ss) })
-	t.Run("AutocompleteInTeamForSearch", func(t *testing.T) { testChannelStoreAutocompleteInTeamForSearch(t, ss) })
-	t.Run("GetMembersByIds", func(t *testing.T) { testChannelStoreGetMembersByIds(t, ss) })
-	t.Run("AnalyticsDeletedTypeCount", func(t *testing.T) { testChannelStoreAnalyticsDeletedTypeCount(t, ss) })
-	t.Run("GetPinnedPosts", func(t *testing.T) { testChannelStoreGetPinnedPosts(t, ss) })
-	t.Run("MaxChannelsPerTeam", func(t *testing.T) { testChannelStoreMaxChannelsPerTeam(t, ss) })
-	t.Run("GetChannelsByScheme", func(t *testing.T) { testChannelStoreGetChannelsByScheme(t, ss) })
-	t.Run("MigrateChannelMembers", func(t *testing.T) { testChannelStoreMigrateChannelMembers(t, ss) })
-	t.Run("ResetAllChannelSchemes", func(t *testing.T) { testResetAllChannelSchemes(t, ss) })
-	t.Run("ClearAllCustomRoleAssignments", func(t *testing.T) { testChannelStoreClearAllCustomRoleAssignments(t, ss) })
+	for _, enabled := range []bool{true, false} {
+		description := "experimental materialization"
+		if enabled {
+			description += " enabled"
+			ss.Channel().EnableExperimentalPublicChannelsMaterialization()
+		} else {
+			description += " disabled"
+			ss.Channel().DisableExperimentalPublicChannelsMaterialization()
+
+			// Additionally drop the public channels table and all associated triggers
+			// to prove that the experimental store is fully disabled.
+			ss.Channel().DropPublicChannels()
+		}
 
+		t.Run(description, func(t *testing.T) {
+			t.Run("Save", func(t *testing.T) { testChannelStoreSave(t, ss) })
+			t.Run("SaveDirectChannel", func(t *testing.T) { testChannelStoreSaveDirectChannel(t, ss) })
+			t.Run("CreateDirectChannel", func(t *testing.T) { testChannelStoreCreateDirectChannel(t, ss) })
+			t.Run("Update", func(t *testing.T) { testChannelStoreUpdate(t, ss) })
+			t.Run("GetChannelUnread", func(t *testing.T) { testGetChannelUnread(t, ss) })
+			t.Run("Get", func(t *testing.T) { testChannelStoreGet(t, ss) })
+			t.Run("GetForPost", func(t *testing.T) { testChannelStoreGetForPost(t, ss) })
+			t.Run("Restore", func(t *testing.T) { testChannelStoreRestore(t, ss) })
+			t.Run("Delete", func(t *testing.T) { testChannelStoreDelete(t, ss) })
+			t.Run("GetByName", func(t *testing.T) { testChannelStoreGetByName(t, ss) })
+			t.Run("GetByNames", func(t *testing.T) { testChannelStoreGetByNames(t, ss) })
+			t.Run("GetDeletedByName", func(t *testing.T) { testChannelStoreGetDeletedByName(t, ss) })
+			t.Run("GetDeleted", func(t *testing.T) { testChannelStoreGetDeleted(t, ss) })
+			t.Run("ChannelMemberStore", func(t *testing.T) { testChannelMemberStore(t, ss) })
+			t.Run("ChannelDeleteMemberStore", func(t *testing.T) { testChannelDeleteMemberStore(t, ss) })
+			t.Run("GetChannels", func(t *testing.T) { testChannelStoreGetChannels(t, ss) })
+			t.Run("GetMoreChannels", func(t *testing.T) { testChannelStoreGetMoreChannels(t, ss) })
+			t.Run("GetPublicChannelsForTeam", func(t *testing.T) { testChannelStoreGetPublicChannelsForTeam(t, ss) })
+			t.Run("GetPublicChannelsByIdsForTeam", func(t *testing.T) { testChannelStoreGetPublicChannelsByIdsForTeam(t, ss) })
+			t.Run("GetChannelCounts", func(t *testing.T) { testChannelStoreGetChannelCounts(t, ss) })
+			t.Run("GetMembersForUser", func(t *testing.T) { testChannelStoreGetMembersForUser(t, ss) })
+			t.Run("UpdateLastViewedAt", func(t *testing.T) { testChannelStoreUpdateLastViewedAt(t, ss) })
+			t.Run("IncrementMentionCount", func(t *testing.T) { testChannelStoreIncrementMentionCount(t, ss) })
+			t.Run("UpdateChannelMember", func(t *testing.T) { testUpdateChannelMember(t, ss) })
+			t.Run("GetMember", func(t *testing.T) { testGetMember(t, ss) })
+			t.Run("GetMemberForPost", func(t *testing.T) { testChannelStoreGetMemberForPost(t, ss) })
+			t.Run("GetMemberCount", func(t *testing.T) { testGetMemberCount(t, ss) })
+			t.Run("SearchMore", func(t *testing.T) { testChannelStoreSearchMore(t, ss) })
+			t.Run("SearchInTeam", func(t *testing.T) { testChannelStoreSearchInTeam(t, ss) })
+			t.Run("AutocompleteInTeamForSearch", func(t *testing.T) { testChannelStoreAutocompleteInTeamForSearch(t, ss) })
+			t.Run("GetMembersByIds", func(t *testing.T) { testChannelStoreGetMembersByIds(t, ss) })
+			t.Run("AnalyticsDeletedTypeCount", func(t *testing.T) { testChannelStoreAnalyticsDeletedTypeCount(t, ss) })
+			t.Run("GetPinnedPosts", func(t *testing.T) { testChannelStoreGetPinnedPosts(t, ss) })
+			t.Run("MaxChannelsPerTeam", func(t *testing.T) { testChannelStoreMaxChannelsPerTeam(t, ss) })
+			t.Run("GetChannelsByScheme", func(t *testing.T) { testChannelStoreGetChannelsByScheme(t, ss) })
+			t.Run("MigrateChannelMembers", func(t *testing.T) { testChannelStoreMigrateChannelMembers(t, ss) })
+			t.Run("ResetAllChannelSchemes", func(t *testing.T) { testResetAllChannelSchemes(t, ss) })
+			t.Run("ClearAllCustomRoleAssignments", func(t *testing.T) { testChannelStoreClearAllCustomRoleAssignments(t, ss) })
+			t.Run("MaterializedPublicChannels", func(t *testing.T) { testMaterializedPublicChannels(t, ss, s) })
+		})
+	}
 }
 
 func testChannelStoreSave(t *testing.T, ss store.Store) {
@@ -191,8 +213,11 @@ func testChannelStoreCreateDirectChannel(t *testing.T, ss store.Store) {
 	if res.Err != nil {
 		t.Fatal("couldn't create direct channel", res.Err)
 	}
-
 	c1 := res.Data.(*model.Channel)
+	defer func() {
+		<-ss.Channel().PermanentDeleteMembersByChannel(c1.Id)
+		<-ss.Channel().PermanentDelete(c1.Id)
+	}()
 
 	members := (<-ss.Channel().GetMembers(c1.Id, 0, 100)).Data.(*model.ChannelMembers)
 	if len(*members) != 2 {
@@ -501,6 +526,7 @@ func testChannelStoreDelete(t *testing.T, ss store.Store) {
 	}
 
 	cresult := <-ss.Channel().GetChannels(o1.TeamId, m1.UserId, false)
+	require.Nil(t, cresult.Err)
 	list := cresult.Data.(*model.ChannelList)
 
 	if len(*list) != 1 {
@@ -508,18 +534,21 @@ func testChannelStoreDelete(t *testing.T, ss store.Store) {
 	}
 
 	cresult = <-ss.Channel().GetMoreChannels(o1.TeamId, m1.UserId, 0, 100)
+	require.Nil(t, cresult.Err)
 	list = cresult.Data.(*model.ChannelList)
 
 	if len(*list) != 1 {
 		t.Fatal("invalid number of channels")
 	}
 
-	<-ss.Channel().PermanentDelete(o2.Id)
+	cresult = <-ss.Channel().PermanentDelete(o2.Id)
+	require.Nil(t, cresult.Err)
 
 	cresult = <-ss.Channel().GetChannels(o1.TeamId, m1.UserId, false)
-	t.Log(cresult.Err)
-	if cresult.Err.Id != "store.sql_channel.get_channels.not_found.app_error" {
-		t.Fatal("no channels should be found")
+	if assert.NotNil(t, cresult.Err) {
+		require.Equal(t, "store.sql_channel.get_channels.not_found.app_error", cresult.Err.Id)
+	} else {
+		require.Equal(t, &model.ChannelList{}, cresult.Data.(*model.ChannelList))
 	}
 
 	if r := <-ss.Channel().PermanentDeleteByTeam(o1.TeamId); r.Err != nil {
@@ -945,280 +974,298 @@ func testChannelStoreGetChannels(t *testing.T, ss store.Store) {
 }
 
 func testChannelStoreGetMoreChannels(t *testing.T, ss store.Store) {
-	o1 := model.Channel{}
-	o1.TeamId = model.NewId()
-	o1.DisplayName = "Channel1"
-	o1.Name = "zz" + model.NewId() + "b"
-	o1.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o1, -1))
-
-	o2 := model.Channel{}
-	o2.TeamId = model.NewId()
-	o2.DisplayName = "Channel2"
-	o2.Name = "zz" + model.NewId() + "b"
-	o2.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o2, -1))
-
-	m1 := model.ChannelMember{}
-	m1.ChannelId = o1.Id
-	m1.UserId = model.NewId()
-	m1.NotifyProps = model.GetDefaultChannelNotifyProps()
-	store.Must(ss.Channel().SaveMember(&m1))
-
-	m2 := model.ChannelMember{}
-	m2.ChannelId = o1.Id
-	m2.UserId = model.NewId()
-	m2.NotifyProps = model.GetDefaultChannelNotifyProps()
-	store.Must(ss.Channel().SaveMember(&m2))
-
-	m3 := model.ChannelMember{}
-	m3.ChannelId = o2.Id
-	m3.UserId = model.NewId()
-	m3.NotifyProps = model.GetDefaultChannelNotifyProps()
-	store.Must(ss.Channel().SaveMember(&m3))
+	teamId := model.NewId()
+	otherTeamId := model.NewId()
+	userId := model.NewId()
+	otherUserId1 := model.NewId()
+	otherUserId2 := model.NewId()
 
-	o3 := model.Channel{}
-	o3.TeamId = o1.TeamId
-	o3.DisplayName = "ChannelA"
-	o3.Name = "zz" + model.NewId() + "b"
-	o3.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o3, -1))
+	// o1 is a channel on the team to which the user (and the other user 1) belongs
+	o1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Channel1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o1, -1))
 
-	o4 := model.Channel{}
-	o4.TeamId = o1.TeamId
-	o4.DisplayName = "ChannelB"
-	o4.Name = "zz" + model.NewId() + "b"
-	o4.Type = model.CHANNEL_PRIVATE
-	store.Must(ss.Channel().Save(&o4, -1))
+	store.Must(ss.Channel().SaveMember(&model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      userId,
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}))
 
-	o5 := model.Channel{}
-	o5.TeamId = o1.TeamId
-	o5.DisplayName = "ChannelC"
-	o5.Name = "zz" + model.NewId() + "b"
-	o5.Type = model.CHANNEL_PRIVATE
-	store.Must(ss.Channel().Save(&o5, -1))
+	store.Must(ss.Channel().SaveMember(&model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      otherUserId1,
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}))
 
-	cresult := <-ss.Channel().GetMoreChannels(o1.TeamId, m1.UserId, 0, 100)
-	if cresult.Err != nil {
-		t.Fatal(cresult.Err)
+	// o2 is a channel on the other team to which the user belongs
+	o2 := model.Channel{
+		TeamId:      otherTeamId,
+		DisplayName: "Channel2",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
-	list := cresult.Data.(*model.ChannelList)
+	store.Must(ss.Channel().Save(&o2, -1))
 
-	if len(*list) != 1 {
-		t.Fatal("wrong list")
-	}
+	store.Must(ss.Channel().SaveMember(&model.ChannelMember{
+		ChannelId:   o2.Id,
+		UserId:      otherUserId2,
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}))
 
-	if (*list)[0].Name != o3.Name {
-		t.Fatal("missing channel")
+	// o3 is a channel on the team to which the user does not belong, and thus should show up
+	// in "more channels"
+	o3 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o3, -1))
 
-	o6 := model.Channel{}
-	o6.TeamId = o1.TeamId
-	o6.DisplayName = "ChannelA"
-	o6.Name = "zz" + model.NewId() + "b"
-	o6.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o6, -1))
-
-	cresult = <-ss.Channel().GetMoreChannels(o1.TeamId, m1.UserId, 0, 100)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 2 {
-		t.Fatal("wrong list length")
+	// o4 is a private channel on the team to which the user does not belong
+	o4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelB",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
 	}
+	store.Must(ss.Channel().Save(&o4, -1))
 
-	cresult = <-ss.Channel().GetMoreChannels(o1.TeamId, m1.UserId, 0, 1)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 1 {
-		t.Fatal("wrong list length")
+	// o5 is another private channel on the team to which the user does belong
+	o5 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelC",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
 	}
+	store.Must(ss.Channel().Save(&o5, -1))
 
-	cresult = <-ss.Channel().GetMoreChannels(o1.TeamId, m1.UserId, 1, 1)
-	list = cresult.Data.(*model.ChannelList)
+	store.Must(ss.Channel().SaveMember(&model.ChannelMember{
+		ChannelId:   o5.Id,
+		UserId:      userId,
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}))
 
-	if len(*list) != 1 {
-		t.Fatal("wrong list length")
-	}
+	t.Run("only o3 listed in more channels", func(t *testing.T) {
+		result := <-ss.Channel().GetMoreChannels(teamId, userId, 0, 100)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o3}, result.Data.(*model.ChannelList))
+	})
 
-	if r1 := <-ss.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_OPEN); r1.Err != nil {
-		t.Fatal(r1.Err)
-	} else {
-		if r1.Data.(int64) != 3 {
-			t.Log(r1.Data)
-			t.Fatal("wrong value")
-		}
+	// o6 is another channel on the team to which the user does not belong, and would thus
+	// start showing up in "more channels".
+	o6 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelD",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o6, -1))
 
-	if r1 := <-ss.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_PRIVATE); r1.Err != nil {
-		t.Fatal(r1.Err)
-	} else {
-		if r1.Data.(int64) != 2 {
-			t.Log(r1.Data)
-			t.Fatal("wrong value")
-		}
+	// o7 is another channel on the team to which the user does not belong, but is deleted,
+	// and thus would not start showing up in "more channels"
+	o7 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelD",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o7, -1))
+	store.Must(ss.Channel().Delete(o7.Id, model.GetMillis()))
+
+	t.Run("both o3 and o6 listed in more channels", func(t *testing.T) {
+		result := <-ss.Channel().GetMoreChannels(teamId, userId, 0, 100)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o3, &o6}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("only o3 listed in more channels with offset 0, limit 1", func(t *testing.T) {
+		result := <-ss.Channel().GetMoreChannels(teamId, userId, 0, 1)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o3}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("only o6 listed in more channels with offset 1, limit 1", func(t *testing.T) {
+		result := <-ss.Channel().GetMoreChannels(teamId, userId, 1, 1)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o6}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("verify analytics for open channels", func(t *testing.T) {
+		result := <-ss.Channel().AnalyticsTypeCount(teamId, model.CHANNEL_OPEN)
+		require.Nil(t, result.Err)
+		require.EqualValues(t, 4, result.Data.(int64))
+	})
+
+	t.Run("verify analytics for private channels", func(t *testing.T) {
+		result := <-ss.Channel().AnalyticsTypeCount(teamId, model.CHANNEL_PRIVATE)
+		require.Nil(t, result.Err)
+		require.EqualValues(t, 2, result.Data.(int64))
+	})
 }
 
 func testChannelStoreGetPublicChannelsForTeam(t *testing.T, ss store.Store) {
-	o1 := model.Channel{}
-	o1.TeamId = model.NewId()
-	o1.DisplayName = "OpenChannel1Team1"
-	o1.Name = "zz" + model.NewId() + "b"
-	o1.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o1, -1))
-
-	o2 := model.Channel{}
-	o2.TeamId = model.NewId()
-	o2.DisplayName = "OpenChannel1Team2"
-	o2.Name = "zz" + model.NewId() + "b"
-	o2.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o2, -1))
-
-	o3 := model.Channel{}
-	o3.TeamId = o1.TeamId
-	o3.DisplayName = "PrivateChannel1Team1"
-	o3.Name = "zz" + model.NewId() + "b"
-	o3.Type = model.CHANNEL_PRIVATE
-	store.Must(ss.Channel().Save(&o3, -1))
-
-	cresult := <-ss.Channel().GetPublicChannelsForTeam(o1.TeamId, 0, 100)
-	if cresult.Err != nil {
-		t.Fatal(cresult.Err)
-	}
-	list := cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 1 {
-		t.Fatal("wrong list")
-	}
+	teamId := model.NewId()
 
-	if (*list)[0].Name != o1.Name {
-		t.Fatal("missing channel")
+	// o1 is a public channel on the team
+	o1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel1Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o1, -1))
 
-	o4 := model.Channel{}
-	o4.TeamId = o1.TeamId
-	o4.DisplayName = "OpenChannel2Team1"
-	o4.Name = "zz" + model.NewId() + "b"
-	o4.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o4, -1))
-
-	cresult = <-ss.Channel().GetPublicChannelsForTeam(o1.TeamId, 0, 100)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 2 {
-		t.Fatal("wrong list length")
+	// o2 is a public channel on another team
+	o2 := model.Channel{
+		TeamId:      model.NewId(),
+		DisplayName: "OpenChannel1Team2",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o2, -1))
 
-	cresult = <-ss.Channel().GetPublicChannelsForTeam(o1.TeamId, 0, 1)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 1 {
-		t.Fatal("wrong list length")
+	// o3 is a private channel on the team
+	o3 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "PrivateChannel1Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
 	}
+	store.Must(ss.Channel().Save(&o3, -1))
 
-	cresult = <-ss.Channel().GetPublicChannelsForTeam(o1.TeamId, 1, 1)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 1 {
-		t.Fatal("wrong list length")
-	}
+	t.Run("only o1 initially listed in public channels", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsForTeam(teamId, 0, 100)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o1}, result.Data.(*model.ChannelList))
+	})
 
-	if r1 := <-ss.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_OPEN); r1.Err != nil {
-		t.Fatal(r1.Err)
-	} else {
-		if r1.Data.(int64) != 2 {
-			t.Log(r1.Data)
-			t.Fatal("wrong value")
-		}
+	// o4 is another public channel on the team
+	o4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel2Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o4, -1))
 
-	if r1 := <-ss.Channel().AnalyticsTypeCount(o1.TeamId, model.CHANNEL_PRIVATE); r1.Err != nil {
-		t.Fatal(r1.Err)
-	} else {
-		if r1.Data.(int64) != 1 {
-			t.Log(r1.Data)
-			t.Fatal("wrong value")
-		}
+	// o5 is another public, but deleted channel on the team
+	o5 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel3Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o5, -1))
+	store.Must(ss.Channel().Delete(o5.Id, model.GetMillis()))
+
+	t.Run("both o1 and o4 listed in public channels", func(t *testing.T) {
+		cresult := <-ss.Channel().GetPublicChannelsForTeam(teamId, 0, 100)
+		require.Nil(t, cresult.Err)
+		require.Equal(t, &model.ChannelList{&o1, &o4}, cresult.Data.(*model.ChannelList))
+	})
+
+	t.Run("only o1 listed in public channels with offset 0, limit 1", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsForTeam(teamId, 0, 1)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o1}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("only o4 listed in public channels with offset 1, limit 1", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsForTeam(teamId, 1, 1)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o4}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("verify analytics for open channels", func(t *testing.T) {
+		result := <-ss.Channel().AnalyticsTypeCount(teamId, model.CHANNEL_OPEN)
+		require.Nil(t, result.Err)
+		require.EqualValues(t, 3, result.Data.(int64))
+	})
+
+	t.Run("verify analytics for private channels", func(t *testing.T) {
+		result := <-ss.Channel().AnalyticsTypeCount(teamId, model.CHANNEL_PRIVATE)
+		require.Nil(t, result.Err)
+		require.EqualValues(t, 1, result.Data.(int64))
+	})
 }
 
 func testChannelStoreGetPublicChannelsByIdsForTeam(t *testing.T, ss store.Store) {
-	teamId1 := model.NewId()
+	teamId := model.NewId()
 
-	oc1 := model.Channel{}
-	oc1.TeamId = teamId1
-	oc1.DisplayName = "OpenChannel1Team1"
-	oc1.Name = "zz" + model.NewId() + "b"
-	oc1.Type = model.CHANNEL_OPEN
+	// oc1 is a public channel on the team
+	oc1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel1Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&oc1, -1))
 
-	oc2 := model.Channel{}
-	oc2.TeamId = model.NewId()
-	oc2.DisplayName = "OpenChannel2TeamOther"
-	oc2.Name = "zz" + model.NewId() + "b"
-	oc2.Type = model.CHANNEL_OPEN
+	// oc2 is a public channel on another team
+	oc2 := model.Channel{
+		TeamId:      model.NewId(),
+		DisplayName: "OpenChannel2TeamOther",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&oc2, -1))
 
-	pc3 := model.Channel{}
-	pc3.TeamId = teamId1
-	pc3.DisplayName = "PrivateChannel3Team1"
-	pc3.Name = "zz" + model.NewId() + "b"
-	pc3.Type = model.CHANNEL_PRIVATE
-	store.Must(ss.Channel().Save(&pc3, -1))
-
-	cids := []string{oc1.Id}
-	cresult := <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId1, cids)
-	list := cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 1 {
-		t.Fatal("should return 1 channel")
+	// pc3 is a private channel on the team
+	pc3 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "PrivateChannel3Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
 	}
+	store.Must(ss.Channel().Save(&pc3, -1))
 
-	if (*list)[0].Id != oc1.Id {
-		t.Fatal("missing channel")
-	}
+	t.Run("oc1 by itself should be found as a public channel in the team", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId, []string{oc1.Id})
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&oc1}, result.Data.(*model.ChannelList))
+	})
 
-	cids = append(cids, oc2.Id)
-	cids = append(cids, model.NewId())
-	cids = append(cids, pc3.Id)
-	cresult = <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId1, cids)
-	list = cresult.Data.(*model.ChannelList)
+	t.Run("only oc1, among others, should be found as a public channel in the team", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId, []string{oc1.Id, oc2.Id, model.NewId(), pc3.Id})
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&oc1}, result.Data.(*model.ChannelList))
+	})
 
-	if len(*list) != 1 {
-		t.Fatal("should return 1 channel")
+	// oc4 is another public channel on the team
+	oc4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel4Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
-
-	oc4 := model.Channel{}
-	oc4.TeamId = teamId1
-	oc4.DisplayName = "OpenChannel4Team1"
-	oc4.Name = "zz" + model.NewId() + "b"
-	oc4.Type = model.CHANNEL_OPEN
 	store.Must(ss.Channel().Save(&oc4, -1))
 
-	cids = append(cids, oc4.Id)
-	cresult = <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId1, cids)
-	list = cresult.Data.(*model.ChannelList)
-
-	if len(*list) != 2 {
-		t.Fatal("should return 2 channels")
-	}
-
-	if (*list)[0].Id != oc1.Id {
-		t.Fatal("missing channel")
-	}
-
-	if (*list)[1].Id != oc4.Id {
-		t.Fatal("missing channel")
+	// oc4 is another public, but deleted channel on the team
+	oc5 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "OpenChannel4Team1",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&oc5, -1))
+	store.Must(ss.Channel().Delete(oc5.Id, model.GetMillis()))
 
-	cids = cids[:0]
-	cids = append(cids, model.NewId())
-	cresult = <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId1, cids)
-	list = cresult.Data.(*model.ChannelList)
+	t.Run("only oc1 and oc4, among others, should be found as a public channel in the team", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId, []string{oc1.Id, oc2.Id, model.NewId(), pc3.Id, oc4.Id})
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&oc1, &oc4}, result.Data.(*model.ChannelList))
+	})
 
-	if len(*list) != 0 {
-		t.Fatal("should not return a channel")
-	}
+	t.Run("random channel id should not be found as a public channel in the team", func(t *testing.T) {
+		result := <-ss.Channel().GetPublicChannelsByIdsForTeam(teamId, []string{model.NewId()})
+		require.NotNil(t, result.Err)
+		require.Equal(t, result.Err.Id, "store.sql_channel.get_channels_by_ids.not_found.app_error")
+	})
 }
 
 func testChannelStoreGetChannelCounts(t *testing.T, ss store.Store) {
@@ -1644,414 +1691,332 @@ func testGetMemberCount(t *testing.T, ss store.Store) {
 }
 
 func testChannelStoreSearchMore(t *testing.T, ss store.Store) {
-	o1 := model.Channel{}
-	o1.TeamId = model.NewId()
-	o1.DisplayName = "ChannelA"
-	o1.Name = "zz" + model.NewId() + "b"
-	o1.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o1, -1))
+	teamId := model.NewId()
+	otherTeamId := model.NewId()
 
-	o2 := model.Channel{}
-	o2.TeamId = model.NewId()
-	o2.DisplayName = "Channel2"
-	o2.Name = "zz" + model.NewId() + "b"
-	o2.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o2, -1))
+	o1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o1, -1))
 
-	m1 := model.ChannelMember{}
-	m1.ChannelId = o1.Id
-	m1.UserId = model.NewId()
-	m1.NotifyProps = model.GetDefaultChannelNotifyProps()
+	m1 := model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m1))
 
-	m2 := model.ChannelMember{}
-	m2.ChannelId = o1.Id
-	m2.UserId = model.NewId()
-	m2.NotifyProps = model.GetDefaultChannelNotifyProps()
+	m2 := model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m2))
 
-	m3 := model.ChannelMember{}
-	m3.ChannelId = o2.Id
-	m3.UserId = model.NewId()
-	m3.NotifyProps = model.GetDefaultChannelNotifyProps()
+	o2 := model.Channel{
+		TeamId:      otherTeamId,
+		DisplayName: "Channel2",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o2, -1))
+
+	m3 := model.ChannelMember{
+		ChannelId:   o2.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m3))
 
-	o3 := model.Channel{}
-	o3.TeamId = o1.TeamId
-	o3.DisplayName = "ChannelA"
-	o3.Name = "zz" + model.NewId() + "b"
-	o3.Type = model.CHANNEL_OPEN
+	o3 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o3, -1))
 
-	o4 := model.Channel{}
-	o4.TeamId = o1.TeamId
-	o4.DisplayName = "ChannelB"
-	o4.Name = "zz" + model.NewId() + "b"
-	o4.Type = model.CHANNEL_PRIVATE
+	o4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelB",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
+	}
 	store.Must(ss.Channel().Save(&o4, -1))
 
-	o5 := model.Channel{}
-	o5.TeamId = o1.TeamId
-	o5.DisplayName = "ChannelC"
-	o5.Name = "zz" + model.NewId() + "b"
-	o5.Type = model.CHANNEL_PRIVATE
+	o5 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelC",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
+	}
 	store.Must(ss.Channel().Save(&o5, -1))
 
-	o6 := model.Channel{}
-	o6.TeamId = o1.TeamId
-	o6.DisplayName = "Off-Topic"
-	o6.Name = "off-topic"
-	o6.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o6, -1))
-
-	o7 := model.Channel{}
-	o7.TeamId = o1.TeamId
-	o7.DisplayName = "Off-Set"
-	o7.Name = "off-set"
-	o7.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o7, -1))
-
-	o8 := model.Channel{}
-	o8.TeamId = o1.TeamId
-	o8.DisplayName = "Off-Limit"
-	o8.Name = "off-limit"
-	o8.Type = model.CHANNEL_PRIVATE
-	store.Must(ss.Channel().Save(&o8, -1))
-
-	o9 := model.Channel{}
-	o9.TeamId = o1.TeamId
-	o9.DisplayName = "Channel With Purpose"
-	o9.Purpose = "This can now be searchable!"
-	o9.Name = "with-purpose"
-	o9.Type = model.CHANNEL_OPEN
-	store.Must(ss.Channel().Save(&o9, -1))
-
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "ChannelA"); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) == 0 {
-			t.Fatal("should not be empty")
-		}
-
-		if (*channels)[0].Name != o3.Name {
-			t.Fatal("wrong channel returned")
-		}
+	o6 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Topic",
+		Name:        "off-topic",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o6, -1))
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, o4.Name); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 0 {
-			t.Fatal("should be empty")
-		}
+	o7 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Set",
+		Name:        "off-set",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o7, -1))
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, o3.Name); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) == 0 {
-			t.Fatal("should not be empty")
-		}
-
-		if (*channels)[0].Name != o3.Name {
-			t.Fatal("wrong channel returned")
-		}
+	o8 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Limit",
+		Name:        "off-limit",
+		Type:        model.CHANNEL_PRIVATE,
 	}
+	store.Must(ss.Channel().Save(&o8, -1))
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "off-"); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 2 {
-			t.Fatal("should return 2 channels, not including private channel")
-		}
-
-		if (*channels)[0].Name != o7.Name {
-			t.Fatal("wrong channel returned")
-		}
-
-		if (*channels)[1].Name != o6.Name {
-			t.Fatal("wrong channel returned")
-		}
+	o9 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Channel With Purpose",
+		Purpose:     "This can now be searchable!",
+		Name:        "with-purpose",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o9, -1))
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "off-topic"); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 1 {
-			t.Fatal("should return 1 channel")
-		}
-
-		if (*channels)[0].Name != o6.Name {
-			t.Fatal("wrong channel returned")
-		}
+	o10 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA",
+		Name:        "channel-a-deleted",
+		Type:        model.CHANNEL_OPEN,
 	}
+	store.Must(ss.Channel().Save(&o10, -1))
+	o10.DeleteAt = model.GetMillis()
+	o10.UpdateAt = o10.DeleteAt
+	store.Must(ss.Channel().Delete(o10.Id, o10.DeleteAt))
+
+	t.Run("three public channels matching 'ChannelA', but already a member of one and one deleted", func(t *testing.T) {
+		result := <-ss.Channel().SearchMore(m1.UserId, teamId, "ChannelA")
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o3}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("one public channels, but already a member", func(t *testing.T) {
+		result := <-ss.Channel().SearchMore(m1.UserId, teamId, o4.Name)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("three matching channels, but only two public", func(t *testing.T) {
+		result := <-ss.Channel().SearchMore(m1.UserId, teamId, "off-")
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o7, &o6}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("one channel matching 'off-topic'", func(t *testing.T) {
+		result := <-ss.Channel().SearchMore(m1.UserId, teamId, "off-topic")
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o6}, result.Data.(*model.ChannelList))
+	})
+
+	t.Run("search purpose", func(t *testing.T) {
+		result := <-ss.Channel().SearchMore(m1.UserId, teamId, "now searchable")
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o9}, result.Data.(*model.ChannelList))
+	})
+}
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "now searchable"); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 1 {
-			t.Fatal("should return 1 channel")
-		}
+type ByChannelDisplayName model.ChannelList
 
-		if (*channels)[0].Name != o9.Name {
-			t.Fatal("wrong channel returned")
-		}
+func (s ByChannelDisplayName) Len() int { return len(s) }
+func (s ByChannelDisplayName) Swap(i, j int) {
+	s[i], s[j] = s[j], s[i]
+}
+func (s ByChannelDisplayName) Less(i, j int) bool {
+	if s[i].DisplayName != s[j].DisplayName {
+		return s[i].DisplayName < s[j].DisplayName
 	}
 
-	/*
-		// Disabling this check as it will fail on PostgreSQL as we have "liberalised" channel matching to deal with
-		// Full-Text Stemming Limitations.
-		if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "off-topics"); result.Err != nil {
-			t.Fatal(result.Err)
-		} else {
-			channels := result.Data.(*model.ChannelList)
-			if len(*channels) != 0 {
-				t.Logf("%v\n", *channels)
-				t.Fatal("should be empty")
-			}
-		}
-	*/
+	return s[i].Id < s[j].Id
 }
 
 func testChannelStoreSearchInTeam(t *testing.T, ss store.Store) {
-	o1 := model.Channel{}
-	o1.TeamId = model.NewId()
-	o1.DisplayName = "ChannelA"
-	o1.Name = "zz" + model.NewId() + "b"
-	o1.Type = model.CHANNEL_OPEN
+	teamId := model.NewId()
+	otherTeamId := model.NewId()
+
+	o1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o1, -1))
 
-	o2 := model.Channel{}
-	o2.TeamId = model.NewId()
-	o2.DisplayName = "Channel2"
-	o2.Name = "zz" + model.NewId() + "b"
-	o2.Type = model.CHANNEL_OPEN
+	o2 := model.Channel{
+		TeamId:      otherTeamId,
+		DisplayName: "ChannelA",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o2, -1))
 
-	m1 := model.ChannelMember{}
-	m1.ChannelId = o1.Id
-	m1.UserId = model.NewId()
-	m1.NotifyProps = model.GetDefaultChannelNotifyProps()
+	m1 := model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m1))
 
-	m2 := model.ChannelMember{}
-	m2.ChannelId = o1.Id
-	m2.UserId = model.NewId()
-	m2.NotifyProps = model.GetDefaultChannelNotifyProps()
+	m2 := model.ChannelMember{
+		ChannelId:   o1.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m2))
 
-	m3 := model.ChannelMember{}
-	m3.ChannelId = o2.Id
-	m3.UserId = model.NewId()
-	m3.NotifyProps = model.GetDefaultChannelNotifyProps()
+	m3 := model.ChannelMember{
+		ChannelId:   o2.Id,
+		UserId:      model.NewId(),
+		NotifyProps: model.GetDefaultChannelNotifyProps(),
+	}
 	store.Must(ss.Channel().SaveMember(&m3))
 
-	o3 := model.Channel{}
-	o3.TeamId = o1.TeamId
-	o3.DisplayName = "ChannelA"
-	o3.Name = "zz" + model.NewId() + "b"
-	o3.Type = model.CHANNEL_OPEN
+	o3 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA (alternate)",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o3, -1))
 
-	o4 := model.Channel{}
-	o4.TeamId = o1.TeamId
-	o4.DisplayName = "ChannelB"
-	o4.Name = "zz" + model.NewId() + "b"
-	o4.Type = model.CHANNEL_PRIVATE
+	o4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Channel B",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
+	}
 	store.Must(ss.Channel().Save(&o4, -1))
 
-	o5 := model.Channel{}
-	o5.TeamId = o1.TeamId
-	o5.DisplayName = "ChannelC"
-	o5.Name = "zz" + model.NewId() + "b"
-	o5.Type = model.CHANNEL_PRIVATE
+	o5 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Channel C",
+		Name:        "zz" + model.NewId() + "b",
+		Type:        model.CHANNEL_PRIVATE,
+	}
 	store.Must(ss.Channel().Save(&o5, -1))
 
-	o6 := model.Channel{}
-	o6.TeamId = o1.TeamId
-	o6.DisplayName = "Off-Topic"
-	o6.Name = "off-topic"
-	o6.Type = model.CHANNEL_OPEN
+	o6 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Topic",
+		Name:        "off-topic",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o6, -1))
 
-	o7 := model.Channel{}
-	o7.TeamId = o1.TeamId
-	o7.DisplayName = "Off-Set"
-	o7.Name = "off-set"
-	o7.Type = model.CHANNEL_OPEN
+	o7 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Set",
+		Name:        "off-set",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o7, -1))
 
-	o8 := model.Channel{}
-	o8.TeamId = o1.TeamId
-	o8.DisplayName = "Off-Limit"
-	o8.Name = "off-limit"
-	o8.Type = model.CHANNEL_PRIVATE
+	o8 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Off-Limit",
+		Name:        "off-limit",
+		Type:        model.CHANNEL_PRIVATE,
+	}
 	store.Must(ss.Channel().Save(&o8, -1))
 
-	o9 := model.Channel{}
-	o9.TeamId = o1.TeamId
-	o9.DisplayName = "Town Square"
-	o9.Name = "town-square"
-	o9.Type = model.CHANNEL_OPEN
+	o9 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Town Square",
+		Name:        "town-square",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o9, -1))
 
-	o10 := model.Channel{}
-	o10.TeamId = o1.TeamId
-	o10.DisplayName = "The"
-	o10.Name = "the"
-	o10.Type = model.CHANNEL_OPEN
+	o10 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "The",
+		Name:        "the",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o10, -1))
 
-	o11 := model.Channel{}
-	o11.TeamId = o1.TeamId
-	o11.DisplayName = "Native Mobile Apps"
-	o11.Name = "native-mobile-apps"
-	o11.Type = model.CHANNEL_OPEN
+	o11 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Native Mobile Apps",
+		Name:        "native-mobile-apps",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o11, -1))
 
-	o12 := model.Channel{}
-	o12.TeamId = o1.TeamId
-	o12.DisplayName = "Channel With Purpose"
-	o12.Purpose = "This can now be searchable!"
-	o12.Name = "with-purpose"
-	o12.Type = model.CHANNEL_OPEN
+	o12 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelZ",
+		Purpose:     "This can now be searchable!",
+		Name:        "with-purpose",
+		Type:        model.CHANNEL_OPEN,
+	}
 	store.Must(ss.Channel().Save(&o12, -1))
 
+	o13 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "ChannelA (deleted)",
+		Name:        model.NewId(),
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o13, -1))
+	o13.DeleteAt = model.GetMillis()
+	o13.UpdateAt = o13.DeleteAt
+	store.Must(ss.Channel().Delete(o13.Id, o13.DeleteAt))
+
+	testCases := []struct {
+		Description     string
+		TeamId          string
+		Term            string
+		IncludeDeleted  bool
+		ExpectedResults *model.ChannelList
+	}{
+		{"ChannelA", teamId, "ChannelA", false, &model.ChannelList{&o1, &o3}},
+		{"ChannelA, include deleted", teamId, "ChannelA", true, &model.ChannelList{&o1, &o3, &o13}},
+		{"ChannelA, other team", otherTeamId, "ChannelA", false, &model.ChannelList{&o2}},
+		{"empty string", teamId, "", false, &model.ChannelList{&o1, &o3, &o12, &o11, &o7, &o6, &o10, &o9}},
+		{"no matches", teamId, "blargh", false, &model.ChannelList{}},
+		{"prefix", teamId, "off-", false, &model.ChannelList{&o7, &o6}},
+		{"full match with dash", teamId, "off-topic", false, &model.ChannelList{&o6}},
+		{"town square", teamId, "town square", false, &model.ChannelList{&o9}},
+		{"the in name", teamId, "the", false, &model.ChannelList{&o10}},
+		{"Mobile", teamId, "Mobile", false, &model.ChannelList{&o11}},
+		{"search purpose", teamId, "now searchable", false, &model.ChannelList{&o12}},
+		{"pipe ignored", teamId, "town square |", false, &model.ChannelList{&o9}},
+	}
+
 	for name, search := range map[string]func(teamId string, term string, includeDeleted bool) store.StoreChannel{
 		"AutocompleteInTeam": ss.Channel().AutocompleteInTeam,
 		"SearchInTeam":       ss.Channel().SearchInTeam,
 	} {
-		t.Run(name, func(t *testing.T) {
-			if result := <-search(o1.TeamId, "ChannelA", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 2 {
-					t.Fatal("wrong length")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) == 0 {
-					t.Fatal("should not be empty")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "blargh", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 0 {
-					t.Fatal("should be empty")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "off-", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 2 {
-					t.Fatal("should return 2 channels, not including private channel")
-				}
+		for _, testCase := range testCases {
+			t.Run(testCase.Description, func(t *testing.T) {
+				result := <-search(testCase.TeamId, testCase.Term, testCase.IncludeDeleted)
+				require.Nil(t, result.Err)
 
-				if (*channels)[0].Name != o7.Name {
-					t.Fatal("wrong channel returned")
-				}
-
-				if (*channels)[1].Name != o6.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "off-topic", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
 				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
 
-				if (*channels)[0].Name != o6.Name {
-					t.Fatal("wrong channel returned")
+				// AutoCompleteInTeam doesn't currently sort its output results.
+				if name == "AutocompleteInTeam" {
+					sort.Sort(ByChannelDisplayName(*channels))
 				}
-			}
 
-			if result := <-search(o1.TeamId, "town square", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
-
-				if (*channels)[0].Name != o9.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "the", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				t.Log(channels.ToJson())
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
-
-				if (*channels)[0].Name != o10.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "Mobile", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				t.Log(channels.ToJson())
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
-
-				if (*channels)[0].Name != o11.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "now searchable", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
-
-				if (*channels)[0].Name != o12.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-
-			if result := <-search(o1.TeamId, "town square |", false); result.Err != nil {
-				t.Fatal(result.Err)
-			} else {
-				channels := result.Data.(*model.ChannelList)
-				if len(*channels) != 1 {
-					t.Fatal("should return 1 channel")
-				}
-
-				if (*channels)[0].Name != o9.Name {
-					t.Fatal("wrong channel returned")
-				}
-			}
-		})
+				require.Equal(t, testCase.ExpectedResults, channels)
+			})
+		}
 	}
 }
 
@@ -2222,6 +2187,10 @@ func testChannelStoreAnalyticsDeletedTypeCount(t *testing.T, ss store.Store) {
 	} else {
 		d4 = result.Data.(*model.Channel)
 	}
+	defer func() {
+		<-ss.Channel().PermanentDeleteMembersByChannel(d4.Id)
+		<-ss.Channel().PermanentDelete(d4.Id)
+	}()
 
 	var openStartCount int64
 	if result := <-ss.Channel().AnalyticsDeletedTypeCount("", "O"); result.Err != nil {
@@ -2569,3 +2538,158 @@ func testChannelStoreClearAllCustomRoleAssignments(t *testing.T, ss store.Store)
 	require.Nil(t, r4.Err)
 	assert.Equal(t, "", r4.Data.(*model.ChannelMember).Roles)
 }
+
+// testMaterializedPublicChannels tests edge cases involving the triggers and stored procedures
+// that materialize the PublicChannels table.
+func testMaterializedPublicChannels(t *testing.T, ss store.Store, s SqlSupplier) {
+	if !ss.Channel().IsExperimentalPublicChannelsMaterializationEnabled() {
+		return
+	}
+
+	teamId := model.NewId()
+
+	// o1 is a public channel on the team
+	o1 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Open Channel",
+		Name:        model.NewId(),
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o1, -1))
+
+	// o2 is another public channel on the team
+	o2 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Open Channel 2",
+		Name:        model.NewId(),
+		Type:        model.CHANNEL_OPEN,
+	}
+	store.Must(ss.Channel().Save(&o2, -1))
+
+	t.Run("o1 and o2 initially listed in public channels", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o1, &o2}, result.Data.(*model.ChannelList))
+	})
+
+	o1.DeleteAt = model.GetMillis()
+	o1.UpdateAt = model.GetMillis()
+	store.Must(ss.Channel().Delete(o1.Id, o1.DeleteAt))
+
+	t.Run("o1 still listed in public channels when marked as deleted", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o1, &o2}, result.Data.(*model.ChannelList))
+	})
+
+	<-ss.Channel().PermanentDelete(o1.Id)
+
+	t.Run("o1 no longer listed in public channels when permanently deleted", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o2}, result.Data.(*model.ChannelList))
+	})
+
+	o2.Type = model.CHANNEL_PRIVATE
+	require.Nil(t, (<-ss.Channel().Update(&o2)).Err)
+
+	t.Run("o2 no longer listed since now private", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{}, result.Data.(*model.ChannelList))
+	})
+
+	o2.Type = model.CHANNEL_OPEN
+	require.Nil(t, (<-ss.Channel().Update(&o2)).Err)
+
+	t.Run("o2 listed once again since now public", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o2}, result.Data.(*model.ChannelList))
+	})
+
+	// o3 is a public channel on the team that already existed in the PublicChannels table.
+	o3 := model.Channel{
+		Id:          model.NewId(),
+		TeamId:      teamId,
+		DisplayName: "Open Channel 3",
+		Name:        model.NewId(),
+		Type:        model.CHANNEL_OPEN,
+	}
+
+	_, err := s.GetMaster().ExecNoTimeout(`
+		INSERT INTO
+		    PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
+		VALUES
+		    (:Id, :DeleteAt, :TeamId, :DisplayName, :Name, :Header, :Purpose);
+	`, map[string]interface{}{
+		"Id":          o3.Id,
+		"DeleteAt":    o3.DeleteAt,
+		"TeamId":      o3.TeamId,
+		"DisplayName": o3.DisplayName,
+		"Name":        o3.Name,
+		"Header":      o3.Header,
+		"Purpose":     o3.Purpose,
+	})
+	require.Nil(t, err)
+
+	o3.DisplayName = "Open Channel 3 - Modified"
+
+	_, err = s.GetMaster().ExecNoTimeout(`
+		INSERT INTO
+		    Channels(Id, CreateAt, UpdateAt, DeleteAt, TeamId, Type, DisplayName, Name, Header, Purpose, LastPostAt, TotalMsgCount, ExtraUpdateAt, CreatorId)
+		VALUES
+		    (:Id, :CreateAt, :UpdateAt, :DeleteAt, :TeamId, :Type, :DisplayName, :Name, :Header, :Purpose, :LastPostAt, :TotalMsgCount, :ExtraUpdateAt, :CreatorId);
+	`, map[string]interface{}{
+		"Id":            o3.Id,
+		"CreateAt":      o3.CreateAt,
+		"UpdateAt":      o3.UpdateAt,
+		"DeleteAt":      o3.DeleteAt,
+		"TeamId":        o3.TeamId,
+		"Type":          o3.Type,
+		"DisplayName":   o3.DisplayName,
+		"Name":          o3.Name,
+		"Header":        o3.Header,
+		"Purpose":       o3.Purpose,
+		"LastPostAt":    o3.LastPostAt,
+		"TotalMsgCount": o3.TotalMsgCount,
+		"ExtraUpdateAt": o3.ExtraUpdateAt,
+		"CreatorId":     o3.CreatorId,
+	})
+	require.Nil(t, err)
+
+	t.Run("verify o3 INSERT converted to UPDATE", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o2, &o3}, result.Data.(*model.ChannelList))
+	})
+
+	// o4 is a public channel on the team that existed in the Channels table but was omitted from the PublicChannels table.
+	o4 := model.Channel{
+		TeamId:      teamId,
+		DisplayName: "Open Channel 4",
+		Name:        model.NewId(),
+		Type:        model.CHANNEL_OPEN,
+	}
+
+	store.Must(ss.Channel().Save(&o4, -1))
+
+	_, err = s.GetMaster().ExecNoTimeout(`
+		DELETE FROM
+		    PublicChannels
+		WHERE
+		    Id = :Id
+	`, map[string]interface{}{
+		"Id": o4.Id,
+	})
+	require.Nil(t, err)
+
+	o4.DisplayName += " - Modified"
+	require.Nil(t, (<-ss.Channel().Update(&o4)).Err)
+
+	t.Run("verify o4 UPDATE converted to INSERT", func(t *testing.T) {
+		result := <-ss.Channel().SearchInTeam(teamId, "", true)
+		require.Nil(t, result.Err)
+		require.Equal(t, &model.ChannelList{&o2, &o3, &o4}, result.Data.(*model.ChannelList))
+	})
+}
diff --git a/store/storetest/mocks/ChannelStore.go b/store/storetest/mocks/ChannelStore.go
index 63f6bc6a9..c187aae6b 100644
--- a/store/storetest/mocks/ChannelStore.go
+++ b/store/storetest/mocks/ChannelStore.go
@@ -130,6 +130,30 @@ func (_m *ChannelStore) Delete(channelId string, time int64) store.StoreChannel
 	return r0
 }
 
+// DisableExperimentalPublicChannelsMaterialization provides a mock function with given fields:
+func (_m *ChannelStore) DisableExperimentalPublicChannelsMaterialization() {
+	_m.Called()
+}
+
+// DropPublicChannels provides a mock function with given fields:
+func (_m *ChannelStore) DropPublicChannels() error {
+	ret := _m.Called()
+
+	var r0 error
+	if rf, ok := ret.Get(0).(func() error); ok {
+		r0 = rf()
+	} else {
+		r0 = ret.Error(0)
+	}
+
+	return r0
+}
+
+// EnableExperimentalPublicChannelsMaterialization provides a mock function with given fields:
+func (_m *ChannelStore) EnableExperimentalPublicChannelsMaterialization() {
+	_m.Called()
+}
+
 // Get provides a mock function with given fields: id, allowFromCache
 func (_m *ChannelStore) Get(id string, allowFromCache bool) store.StoreChannel {
 	ret := _m.Called(id, allowFromCache)
@@ -601,6 +625,20 @@ func (_m *ChannelStore) InvalidateMemberCount(channelId string) {
 	_m.Called(channelId)
 }
 
+// IsExperimentalPublicChannelsMaterializationEnabled provides a mock function with given fields:
+func (_m *ChannelStore) IsExperimentalPublicChannelsMaterializationEnabled() bool {
+	ret := _m.Called()
+
+	var r0 bool
+	if rf, ok := ret.Get(0).(func() bool); ok {
+		r0 = rf()
+	} else {
+		r0 = ret.Get(0).(bool)
+	}
+
+	return r0
+}
+
 // IsUserInChannelUseCache provides a mock function with given fields: userId, channelId
 func (_m *ChannelStore) IsUserInChannelUseCache(userId string, channelId string) bool {
 	ret := _m.Called(userId, channelId)
@@ -631,6 +669,20 @@ func (_m *ChannelStore) MigrateChannelMembers(fromChannelId string, fromUserId s
 	return r0
 }
 
+// MigratePublicChannels provides a mock function with given fields:
+func (_m *ChannelStore) MigratePublicChannels() error {
+	ret := _m.Called()
+
+	var r0 error
+	if rf, ok := ret.Get(0).(func() error); ok {
+		r0 = rf()
+	} else {
+		r0 = ret.Error(0)
+	}
+
+	return r0
+}
+
 // PermanentDelete provides a mock function with given fields: channelId
 func (_m *ChannelStore) PermanentDelete(channelId string) store.StoreChannel {
 	ret := _m.Called(channelId)
diff --git a/store/storetest/mocks/SqlStore.go b/store/storetest/mocks/SqlStore.go
index a93db78c9..38cdc0a1b 100644
--- a/store/storetest/mocks/SqlStore.go
+++ b/store/storetest/mocks/SqlStore.go
@@ -241,6 +241,20 @@ func (_m *SqlStore) DoesTableExist(tablename string) bool {
 	return r0
 }
 
+// DoesTriggerExist provides a mock function with given fields: triggerName
+func (_m *SqlStore) DoesTriggerExist(triggerName string) bool {
+	ret := _m.Called(triggerName)
+
+	var r0 bool
+	if rf, ok := ret.Get(0).(func(string) bool); ok {
+		r0 = rf(triggerName)
+	} else {
+		r0 = ret.Get(0).(bool)
+	}
+
+	return r0
+}
+
 // DriverName provides a mock function with given fields:
 func (_m *SqlStore) DriverName() string {
 	ret := _m.Called()
diff --git a/store/storetest/mocks/SqlSupplier.go b/store/storetest/mocks/SqlSupplier.go
new file mode 100644
index 000000000..4a844524d
--- /dev/null
+++ b/store/storetest/mocks/SqlSupplier.go
@@ -0,0 +1,29 @@
+// Code generated by mockery v1.0.0. DO NOT EDIT.
+
+// Regenerate this file using `make store-mocks`.
+
+package mocks
+
+import gorp "github.com/mattermost/gorp"
+import mock "github.com/stretchr/testify/mock"
+
+// SqlSupplier is an autogenerated mock type for the SqlSupplier type
+type SqlSupplier struct {
+	mock.Mock
+}
+
+// GetMaster provides a mock function with given fields:
+func (_m *SqlSupplier) GetMaster() *gorp.DbMap {
+	ret := _m.Called()
+
+	var r0 *gorp.DbMap
+	if rf, ok := ret.Get(0).(func() *gorp.DbMap); ok {
+		r0 = rf()
+	} else {
+		if ret.Get(0) != nil {
+			r0 = ret.Get(0).(*gorp.DbMap)
+		}
+	}
+
+	return r0
+}