Index repo id for storage_repositories and repository_assignments

With repository ID present and backfilled in both 'storage_repositories'
and 'repository_assignments' tables, Praefect can now start joining the
records using the repository_id instead of (virtual_storage, relative_path).
To prepare for that, this commit indexes both tables using (repository_id,
storage). In a later release, (virtual_storage, relative_path) can be dropped
from both tables and the primary key can be changed to use the new indexes.

Changelog: performance

1 files changed, 19 insertions, 0 deletions

diff --git a/internal/praefect/datastore/migrations/20210922091614_repository_id_primary_key_indexes.go b/internal/praefect/datastore/migrations/20210922091614_repository_id_primary_key_indexes.go
new file mode 100644
index 000000000..e5441ff94
--- /dev/null
+++ b/internal/praefect/datastore/migrations/20210922091614_repository_id_primary_key_indexes.go
@@ -0,0 +1,19 @@
+package migrations
+
+import migrate "github.com/rubenv/sql-migrate"
+
+func init() {
+	m := &migrate.Migration{
+		Id: "20210922091614_repository_id_primary_key_indexes",
+		Up: []string{
+			"CREATE UNIQUE INDEX repository_assignments_new_pkey ON repository_assignments (repository_id, storage)",
+			"CREATE UNIQUE INDEX storage_repositories_new_pkey ON storage_repositories (repository_id, storage)",
+		},
+		Down: []string{
+			"DROP INDEX repository_assignments_new_pkey",
+			"DROP INDEX storage_repositories_new_pkey",
+		},
+	}
+
+	allMigrations = append(allMigrations, m)
+}