Skip to content

perf: optimize migration 371 to run faster on large deployments (#20906) #21041

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
david-fraley merged 1 commit into from
Dec 1, 2025
+65 −2
Merged

perf: optimize migration 371 to run faster on large deployments (#20906) #21041

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
10 changes: 8 additions & 2 deletions coderd/database/migrations/000371_api_key_scopes_array_allow_list.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -141,13 +141,19 @@ ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:read';
ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:update';
-- End enum extensions

-- Purge old API keys to speed up the migration for large deployments.
-- Note: that problem should be solved in coderd once PR 20863 is released:
-- https://github.com/coder/coder/blob/main/coderd/database/dbpurge/dbpurge.go#L85
DELETE FROM api_keys WHERE expires_at < NOW() - INTERVAL '7 days';

-- Add new columns without defaults; backfill; then enforce NOT NULL
ALTER TABLE api_keys ADD COLUMN scopes api_key_scope[];
ALTER TABLE api_keys ADD COLUMN allow_list text[];

-- Backfill existing rows for compatibility
UPDATE api_keys SET scopes = ARRAY[scope::api_key_scope];
UPDATE api_keys SET allow_list = ARRAY['*:*'];
UPDATE api_keys SET
scopes = ARRAY[scope::api_key_scope],
allow_list = ARRAY['*:*'];

-- Enforce NOT NULL
ALTER TABLE api_keys ALTER COLUMN scopes SET NOT NULL;
Expand Down
57 changes: 57 additions & 0 deletions ...base/migrations/testdata/fixtures/000371_add_api_key_and_oauth2_provider_app_token.up.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,57 @@
-- Ensure api_keys and oauth2_provider_app_tokens have live data after
-- migration 000371 deletes expired rows.
INSERT INTO api_keys (
id,
hashed_secret,
user_id,
last_used,
expires_at,
created_at,
updated_at,
login_type,
lifetime_seconds,
ip_address,
token_name,
scopes,
allow_list
)
VALUES (
'fixture-api-key',
'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
'30095c71-380b-457a-8995-97b8ee6e5307',
NOW() - INTERVAL '1 hour',
NOW() + INTERVAL '30 days',
NOW() - INTERVAL '1 day',
NOW() - INTERVAL '1 day',
'password',
86400,
'0.0.0.0',
'fixture-api-key',
ARRAY['workspace:read']::api_key_scope[],
ARRAY['*:*']
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO oauth2_provider_app_tokens (
id,
created_at,
expires_at,
hash_prefix,
refresh_hash,
app_secret_id,
api_key_id,
audience,
user_id
)
VALUES (
'9f92f3c9-811f-4f6f-9a1c-3f2eed1f9f15',
NOW() - INTERVAL '30 minutes',
NOW() + INTERVAL '30 days',
CAST('fixture-hash-prefix' AS bytea),
CAST('fixture-refresh-hash' AS bytea),
'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
'fixture-api-key',
'https://coder.example.com',
'30095c71-380b-457a-8995-97b8ee6e5307'
)
ON CONFLICT (id) DO NOTHING;
Loading