Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita926157

Browse files
authored
perf: optimize migration 371 to run faster on large deployments (#20906)
closes#20899This is in response to a migration in v2.27 that takes very long ondeployments with large `api_keys` tables.NOTE: The optimization causes the _up_ migration to delete old data(keys that expired more than 7 days ago). The _down_ migration won'tresurrect the deleted data.
1 parent9c2f94b commita926157

File tree

2 files changed

+65
-2
lines changed

2 files changed

+65
-2
lines changed

‎coderd/database/migrations/000371_api_key_scopes_array_allow_list.up.sql‎

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -141,13 +141,19 @@ ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:read';
141141
ALTERTYPE api_key_scope ADD VALUE IF NOT EXISTS'workspace_proxy:update';
142142
-- End enum extensions
143143

144+
-- Purge old API keys to speed up the migration for large deployments.
145+
-- Note: that problem should be solved in coderd once PR 20863 is released:
146+
-- https://github.com/coder/coder/blob/main/coderd/database/dbpurge/dbpurge.go#L85
147+
DELETEFROM api_keysWHERE expires_at< NOW()- INTERVAL'7 days';
148+
144149
-- Add new columns without defaults; backfill; then enforce NOT NULL
145150
ALTERTABLE api_keys ADD COLUMN scopes api_key_scope[];
146151
ALTERTABLE api_keys ADD COLUMN allow_listtext[];
147152

148153
-- Backfill existing rows for compatibility
149-
UPDATE api_keysSET scopes= ARRAY[scope::api_key_scope];
150-
UPDATE api_keysSET allow_list= ARRAY['*:*'];
154+
UPDATE api_keysSET
155+
scopes= ARRAY[scope::api_key_scope],
156+
allow_list= ARRAY['*:*'];
151157

152158
-- Enforce NOT NULL
153159
ALTERTABLE api_keys ALTER COLUMN scopesSETNOT NULL;
Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
-- Ensure api_keys and oauth2_provider_app_tokens have live data after
2+
-- migration 000371 deletes expired rows.
3+
INSERT INTO api_keys (
4+
id,
5+
hashed_secret,
6+
user_id,
7+
last_used,
8+
expires_at,
9+
created_at,
10+
updated_at,
11+
login_type,
12+
lifetime_seconds,
13+
ip_address,
14+
token_name,
15+
scopes,
16+
allow_list
17+
)
18+
VALUES (
19+
'fixture-api-key',
20+
'\xaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
21+
'30095c71-380b-457a-8995-97b8ee6e5307',
22+
NOW()- INTERVAL'1 hour',
23+
NOW()+ INTERVAL'30 days',
24+
NOW()- INTERVAL'1 day',
25+
NOW()- INTERVAL'1 day',
26+
'password',
27+
86400,
28+
'0.0.0.0',
29+
'fixture-api-key',
30+
ARRAY['workspace:read']::api_key_scope[],
31+
ARRAY['*:*']
32+
)
33+
ON CONFLICT (id) DO NOTHING;
34+
35+
INSERT INTO oauth2_provider_app_tokens (
36+
id,
37+
created_at,
38+
expires_at,
39+
hash_prefix,
40+
refresh_hash,
41+
app_secret_id,
42+
api_key_id,
43+
audience,
44+
user_id
45+
)
46+
VALUES (
47+
'9f92f3c9-811f-4f6f-9a1c-3f2eed1f9f15',
48+
NOW()- INTERVAL'30 minutes',
49+
NOW()+ INTERVAL'30 days',
50+
CAST('fixture-hash-prefix'ASbytea),
51+
CAST('fixture-refresh-hash'ASbytea),
52+
'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
53+
'fixture-api-key',
54+
'https://coder.example.com',
55+
'30095c71-380b-457a-8995-97b8ee6e5307'
56+
)
57+
ON CONFLICT (id) DO NOTHING;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp