chore: clean up / compress recent migrations to avoid creating and dropping indices unnecessarily

alnr · ory-bot · commit 5a12526d08cd · 2026-02-03T23:22:00.000Z
GitOrigin-RevId: ff7a8bbdbea7a67e1dbbc9e5080f93fdf51b8300
diff --git a/internal/testhelpers/driver.go b/internal/testhelpers/driver.go
@@ -84,7 +84,7 @@ func NewRegistrySQLFromURL(t testing.TB, dsn string, migrate, initNetwork bool,
 func ConnectToMySQL(t testing.TB) string { return dockertest.RunTestMySQLWithVersion(t, "8.0") }
 func ConnectToPG(t testing.TB) string    { return dockertest.RunTestPostgreSQLWithVersion(t, "16") }
 func ConnectToCRDB(t testing.TB) string {
-	return dockertest.RunTestCockroachDBWithVersion(t, "latest-v24.1")
+	return dockertest.RunTestCockroachDBWithVersion(t, "latest-v25.4")
 }
 
 func ConnectDatabasesURLs(t *testing.T) (pgURL, mysqlURL, crdbURL string) {
diff --git a/internal/testhelpers/sql_schemas/cockroach_dump.sql b/internal/testhelpers/sql_schemas/cockroach_dump.sql
@@ -1,4 +1,4 @@
--- migrations hash: 55b905a5cead652db5b4a5e317d60ad18bd9e6de61d4b43edcdb79244947fbf9b106783f10765f2ffa4d7952c745900c2406f522a101dbed17e5785396484e41
+-- migrations hash: 36ad8390f65c43551e28df0dcf92b0fdeb823b774eecda791f5979deafce2f6cc6ff57fccdfa41cbaa4403fd4ced8a9dfd7b725d1bb51fd6b0fabdccd51338aa
 
 CREATE TABLE public.schema_migration (
 	version VARCHAR(48) NOT NULL,
diff --git a/internal/testhelpers/sql_schemas/mysql_dump.sql b/internal/testhelpers/sql_schemas/mysql_dump.sql
@@ -1,4 +1,4 @@
--- migrations hash: 55b905a5cead652db5b4a5e317d60ad18bd9e6de61d4b43edcdb79244947fbf9b106783f10765f2ffa4d7952c745900c2406f522a101dbed17e5785396484e41
+-- migrations hash: 36ad8390f65c43551e28df0dcf92b0fdeb823b774eecda791f5979deafce2f6cc6ff57fccdfa41cbaa4403fd4ced8a9dfd7b725d1bb51fd6b0fabdccd51338aa
 
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
diff --git a/internal/testhelpers/sql_schemas/postgres_dump.sql b/internal/testhelpers/sql_schemas/postgres_dump.sql
@@ -1,4 +1,4 @@
--- migrations hash: 55b905a5cead652db5b4a5e317d60ad18bd9e6de61d4b43edcdb79244947fbf9b106783f10765f2ffa4d7952c745900c2406f522a101dbed17e5785396484e41
+-- migrations hash: 36ad8390f65c43551e28df0dcf92b0fdeb823b774eecda791f5979deafce2f6cc6ff57fccdfa41cbaa4403fd4ced8a9dfd7b725d1bb51fd6b0fabdccd51338aa
 
 
 
diff --git a/internal/testhelpers/sql_schemas/sqlite3_dump.sql b/internal/testhelpers/sql_schemas/sqlite3_dump.sql
@@ -1,4 +1,4 @@
--- migrations hash: 55b905a5cead652db5b4a5e317d60ad18bd9e6de61d4b43edcdb79244947fbf9b106783f10765f2ffa4d7952c745900c2406f522a101dbed17e5785396484e41
+-- migrations hash: 36ad8390f65c43551e28df0dcf92b0fdeb823b774eecda791f5979deafce2f6cc6ff57fccdfa41cbaa4403fd4ced8a9dfd7b725d1bb51fd6b0fabdccd51338aa
 
 CREATE TABLE "hydra_client"
 (
diff --git a/persistence/sql/migratest/migration_test.go b/persistence/sql/migratest/migration_test.go
@@ -67,7 +67,7 @@ func TestMigrations(t *testing.T) {
 		for db, dsn := range map[string]string{
 			"postgres":  dockertest.RunTestPostgreSQL(t),
 			"mysql":     dockertest.RunTestMySQL(t),
-			"cockroach": dockertest.RunTestCockroachDBWithVersion(t, "latest-v25.1"),
+			"cockroach": dockertest.RunTestCockroachDBWithVersion(t, "latest-v25.4"),
 		} {
 			wg.Add(1)
 			go func() {
@@ -108,7 +108,10 @@ func TestMigrations(t *testing.T) {
 				c, l,
 				popx.WithTestdata(t, os.DirFS("./testdata")))
 			require.NoError(t, err)
-			require.NoError(t, tm.Up(t.Context()))
+			if !assert.NoError(t, tm.Up(t.Context())) {
+				_ = tm.DumpMigrationSchema(t.Context())
+				t.FailNow()
+			}
 
 			t.Run("suite=fixtures", func(t *testing.T) {
 				t.Run("case=hydra_client", func(t *testing.T) {
@@ -288,7 +291,9 @@ func TestMigrations(t *testing.T) {
 					}
 				}
 
-				assert.NoError(t, tm.Down(t.Context(), stepsDown))
+				if !assert.NoError(t, tm.Down(t.Context(), stepsDown)) {
+					_ = tm.DumpMigrationSchema(t.Context())
+				}
 			})
 		})
 	}
diff --git a/persistence/sql/migrations/20240403121110000001_add_expire_columns.mysql.up.sql b/persistence/sql/migrations/20240403121110000001_add_expire_columns.mysql.up.sql
@@ -0,0 +1,12 @@
+ALTER TABLE hydra_oauth2_oidc ADD COLUMN expires_at TIMESTAMP NULL;
+ALTER TABLE hydra_oauth2_access ADD COLUMN expires_at TIMESTAMP NULL;
+ALTER TABLE hydra_oauth2_refresh ADD COLUMN expires_at TIMESTAMP NULL;
+ALTER TABLE hydra_oauth2_code ADD COLUMN expires_at TIMESTAMP NULL;
+ALTER TABLE hydra_oauth2_pkce ADD COLUMN expires_at TIMESTAMP NULL;
+
+-- These turn out to not be necessary, but we need them here for down migrations :(
+CREATE INDEX hydra_oauth2_oidc_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+CREATE INDEX hydra_oauth2_access_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+CREATE INDEX hydra_oauth2_refresh_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+CREATE INDEX hydra_oauth2_code_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+CREATE INDEX hydra_oauth2_pkce_expires_at_idx ON hydra_oauth2_oidc (expires_at);
diff --git a/persistence/sql/migrations/20240403121110000001_add_expire_columns.sqlite.down.sql b/persistence/sql/migrations/20240403121110000001_add_expire_columns.sqlite.down.sql
@@ -1,8 +1,8 @@
-DROP INDEX hydra_oauth2_oidc_expires_at_idx;
-DROP INDEX hydra_oauth2_access_expires_at_idx;
-DROP INDEX hydra_oauth2_refresh_expires_at_idx;
-DROP INDEX hydra_oauth2_code_expires_at_idx;
-DROP INDEX hydra_oauth2_pkce_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_oidc_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_access_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_refresh_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_code_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_pkce_expires_at_idx;
 
 ALTER TABLE hydra_oauth2_oidc DROP COLUMN expires_at;
 ALTER TABLE hydra_oauth2_access DROP COLUMN expires_at;
diff --git a/persistence/sql/migrations/20240403121110000001_add_expire_columns.up.sql b/persistence/sql/migrations/20240403121110000001_add_expire_columns.up.sql
@@ -4,8 +4,9 @@ ALTER TABLE hydra_oauth2_refresh ADD COLUMN expires_at TIMESTAMP NULL;
 ALTER TABLE hydra_oauth2_code ADD COLUMN expires_at TIMESTAMP NULL;
 ALTER TABLE hydra_oauth2_pkce ADD COLUMN expires_at TIMESTAMP NULL;
 
-CREATE INDEX hydra_oauth2_oidc_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX hydra_oauth2_access_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX hydra_oauth2_refresh_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX hydra_oauth2_code_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX hydra_oauth2_pkce_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- These turn out to not be necessary.
+-- CREATE INDEX hydra_oauth2_oidc_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX hydra_oauth2_access_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX hydra_oauth2_refresh_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX hydra_oauth2_code_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX hydra_oauth2_pkce_expires_at_idx ON hydra_oauth2_oidc (expires_at);
diff --git a/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.autocommit.up.sql b/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.autocommit.up.sql
@@ -0,0 +1,10 @@
+-- These turn out to not be necessary.
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_access_expires_at_v2_idx ON hydra_oauth2_access (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_refresh_expires_at_v2_idx ON hydra_oauth2_refresh (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_code_expires_at_v2_idx ON hydra_oauth2_code (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_pkce_expires_at_v2_idx ON hydra_oauth2_pkce (expires_at);
+
+DROP INDEX IF EXISTS hydra_oauth2_access_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_refresh_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_code_expires_at_idx;
+DROP INDEX IF EXISTS hydra_oauth2_pkce_expires_at_idx;
diff --git a/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.down.sql b/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.down.sql
@@ -1,9 +1,10 @@
-CREATE INDEX IF NOT EXISTS hydra_oauth2_access_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX IF NOT EXISTS hydra_oauth2_refresh_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX IF NOT EXISTS hydra_oauth2_code_expires_at_idx ON hydra_oauth2_oidc (expires_at);
-CREATE INDEX IF NOT EXISTS hydra_oauth2_pkce_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- These turn out not to be necessary.
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_access_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_refresh_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_code_expires_at_idx ON hydra_oauth2_oidc (expires_at);
+-- CREATE INDEX IF NOT EXISTS hydra_oauth2_pkce_expires_at_idx ON hydra_oauth2_oidc (expires_at);
 
-DROP INDEX hydra_oauth2_access_expires_at_v2_idx;
-DROP INDEX hydra_oauth2_refresh_expires_at_v2_idx;
-DROP INDEX hydra_oauth2_code_expires_at_v2_idx;
-DROP INDEX hydra_oauth2_pkce_expires_at_v2_idx;
+DROP INDEX IF EXISTS hydra_oauth2_access_expires_at_v2_idx;
+DROP INDEX IF EXISTS hydra_oauth2_refresh_expires_at_v2_idx;
+DROP INDEX IF EXISTS hydra_oauth2_code_expires_at_v2_idx;
+DROP INDEX IF EXISTS hydra_oauth2_pkce_expires_at_v2_idx;
diff --git a/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.mysql.up.sql b/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.mysql.up.sql
@@ -1,3 +1,4 @@
+-- These turn out to not be necessary, but we need them here for down migrations :(
 CREATE INDEX hydra_oauth2_access_expires_at_v2_idx ON hydra_oauth2_access (expires_at);
 CREATE INDEX hydra_oauth2_refresh_expires_at_v2_idx ON hydra_oauth2_refresh (expires_at);
 CREATE INDEX hydra_oauth2_code_expires_at_v2_idx ON hydra_oauth2_code (expires_at);
diff --git a/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.up.sql b/persistence/sql/migrations/20240612222110000001_add_oauth2_expires_at_indices.up.sql
diff --git a/persistence/sql/migrations/20241609000001000000_device_flow.postgres.up.sql b/persistence/sql/migrations/20241609000001000000_device_flow.postgres.up.sql
@@ -38,7 +38,6 @@ ALTER TABLE hydra_oauth2_flow
   ADD COLUMN IF NOT EXISTS device_handled_at TIMESTAMP NULL,
   ADD COLUMN IF NOT EXISTS device_error VARCHAR(2048) NULL;
 
-CREATE UNIQUE INDEX IF NOT EXISTS hydra_oauth2_flow_device_challenge_idx ON hydra_oauth2_flow (device_challenge_id);
 
 ALTER TABLE hydra_client
   ADD COLUMN IF NOT EXISTS device_authorization_grant_id_token_lifespan BIGINT NULL DEFAULT NULL,
diff --git a/persistence/sql/migrations/20241609000001000000_device_flow.sqlite.down.sql b/persistence/sql/migrations/20241609000001000000_device_flow.sqlite.down.sql
@@ -1,7 +1,6 @@
 DROP TABLE hydra_oauth2_device_auth_codes;
 
 DROP INDEX hydra_oauth2_flow_device_challenge_idx;
-
 ALTER TABLE hydra_oauth2_flow DROP COLUMN device_challenge_id;
 ALTER TABLE hydra_oauth2_flow DROP COLUMN device_code_request_id;
 ALTER TABLE hydra_oauth2_flow DROP COLUMN device_verifier;
diff --git a/persistence/sql/migrations/20241609000001000001_device_flow.postgres.autocommit.down.sql b/persistence/sql/migrations/20241609000001000001_device_flow.postgres.autocommit.down.sql
@@ -0,0 +1 @@
+DROP INDEX CONCURRENTLY IF EXISTS hydra_oauth2_flow_device_challenge_idx;
diff --git a/persistence/sql/migrations/20241609000001000001_device_flow.postgres.autocommit.up.sql b/persistence/sql/migrations/20241609000001000001_device_flow.postgres.autocommit.up.sql
@@ -0,0 +1 @@
+CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS hydra_oauth2_flow_device_challenge_idx ON hydra_oauth2_flow (device_challenge_id);
diff --git a/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.autocommit.down.sql b/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.autocommit.down.sql
@@ -1 +1 @@
-DROP INDEX IF EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx;
+DROP INDEX CONCURRENTLY IF EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx;
diff --git a/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.autocommit.up.sql b/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.autocommit.up.sql
@@ -1,4 +1,4 @@
 -- We must avoid having empty fields at the beginning of an index, to avoid queries degenerating into full table scans.
 -- Apart from this consideration, the order of the fields in the index is not really important when the query uses them all.
 -- To avoid performance pitfalls/surprises, we place Ory-controlled fields before user-controlled fields.
-CREATE UNIQUE INDEX IF NOT EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx ON hydra_oauth2_trusted_jwt_bearer_issuer (nid ASC, key_id ASC, issuer ASC, subject ASC);
+CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx ON hydra_oauth2_trusted_jwt_bearer_issuer (nid ASC, key_id ASC, issuer ASC, subject ASC);
diff --git a/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.sqlite.down.sql b/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.sqlite.down.sql
@@ -0,0 +1 @@
+DROP INDEX IF EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx;
diff --git a/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.sqlite.up.sql b/persistence/sql/migrations/20250520000001000000_add_oauth2_trusted_jwt_bearer_issuer_index.sqlite.up.sql
@@ -0,0 +1,4 @@
+-- We must avoid having empty fields at the beginning of an index, to avoid queries degenerating into full table scans.
+-- Apart from this consideration, the order of the fields in the index is not really important when the query uses them all.
+-- To avoid performance pitfalls/surprises, we place Ory-controlled fields before user-controlled fields.
+CREATE UNIQUE INDEX IF NOT EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_nid_uq_idx ON hydra_oauth2_trusted_jwt_bearer_issuer (nid ASC, key_id ASC, issuer ASC, subject ASC);
diff --git a/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.autocommit.down.sql b/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.autocommit.down.sql
@@ -0,0 +1 @@
+CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_issuer_subject_key_id_key ON hydra_oauth2_trusted_jwt_bearer_issuer (issuer ASC, subject ASC, key_id ASC, nid ASC);
diff --git a/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.autocommit.up.sql b/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.autocommit.up.sql
@@ -0,0 +1 @@
+DROP INDEX CONCURRENTLY IF EXISTS hydra_oauth2_trusted_jwt_bearer_issuer_issuer_subject_key_id_key;
diff --git a/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.down.sql b/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.down.sql
diff --git a/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.up.sql b/persistence/sql/migrations/20250523000001000000_rm_oauth2_trusted_jwt_bearer_issuer_unefficient_uq_idx.postgres.up.sql

Original file line number	Diff line number	Diff line change
`@@ -84,7 +84,7 @@ func NewRegistrySQLFromURL(t testing.TB, dsn string, migrate, initNetwork bool,`
`84`	`84`	`func ConnectToMySQL(t testing.TB) string { return dockertest.RunTestMySQLWithVersion(t, "8.0") }`
`85`	`85`	`func ConnectToPG(t testing.TB) string { return dockertest.RunTestPostgreSQLWithVersion(t, "16") }`
`86`	`86`	`func ConnectToCRDB(t testing.TB) string {`
`87`		`- return dockertest.RunTestCockroachDBWithVersion(t, "latest-v24.1")`
	`87`	`+ return dockertest.RunTestCockroachDBWithVersion(t, "latest-v25.4")`
`88`	`88`	`}`
`89`	`89`
`90`	`90`	`func ConnectDatabasesURLs(t *testing.T) (pgURL, mysqlURL, crdbURL string) {`
Original file line number	Diff line number	Diff line change
`@@ -1,4 +1,4 @@`
`1`		`--- migrations hash: 55b905a5cead652db5b4a5e317d60ad18bd9e6de61d4b43edcdb79244947fbf9b106783f10765f2ffa4d7952c745900c2406f522a101dbed17e5785396484e41`
	`1`	`+-- migrations hash: 36ad8390f65c43551e28df0dcf92b0fdeb823b774eecda791f5979deafce2f6cc6ff57fccdfa41cbaa4403fd4ced8a9dfd7b725d1bb51fd6b0fabdccd51338aa`
`2`	`2`
`3`	`3`	`CREATE TABLE public.schema_migration (`
`4`	`4`	`version VARCHAR(48) NOT NULL,`