1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
|
# What requires downtime?
When working with a database certain operations can be performed without taking
GitLab offline, others do require a downtime period. This guide describes
various operations, their impact, and how to perform them without requiring
downtime.
## Adding Columns
On PostgreSQL you can safely add a new column to an existing table as long as it
does **not** have a default value. For example, this query would not require
downtime:
```sql
ALTER TABLE projects ADD COLUMN random_value int;
```
Add a column _with_ a default however does require downtime. For example,
consider this query:
```sql
ALTER TABLE projects ADD COLUMN random_value int DEFAULT 42;
```
This requires updating every single row in the `projects` table so that
`random_value` is set to `42` by default. This requires updating all rows and
indexes in a table. This in turn acquires enough locks on the table for it to
effectively block any other queries.
As of MySQL 5.6 adding a column to a table is still quite an expensive
operation, even when using `ALGORITHM=INPLACE` and `LOCK=NONE`. This means
downtime _may_ be required when modifying large tables as otherwise the
operation could potentially take hours to complete.
Adding a column with a default value _can_ be done without requiring downtime
when using the migration helper method
`Gitlab::Database::MigrationHelpers#add_column_with_default`. This method works
similar to `add_column` except it updates existing rows in batches without
blocking access to the table being modified. See ["Adding Columns With Default
Values"](migration_style_guide.html#adding-columns-with-default-values) for more
information on how to use this method.
## Dropping Columns
Removing columns is tricky because running GitLab processes may still be using
the columns. To work around this you will need two separate merge requests and
releases: one to ignore and then remove the column, and one to remove the ignore
rule.
### Step 1: Ignoring The Column
The first step is to ignore the column in the application code. This is
necessary because Rails caches the columns and re-uses this cache in various
places. This can be done by including the `IgnorableColumn` module into the
model, followed by defining the columns to ignore. For example, to ignore
`updated_at` in the User model you'd use the following:
```ruby
class User < ActiveRecord::Base
include IgnorableColumn
ignore_column :updated_at
end
```
Once added you should create a _post-deployment_ migration that removes the
column. Both these changes should be submitted in the same merge request.
### Step 2: Removing The Ignore Rule
Once the changes from step 1 have been released & deployed you can set up a
separate merge request that removes the ignore rule. This merge request can
simply remove the `ignore_column` line, and the `include IgnorableColumn` line
if no other `ignore_column` calls remain.
## Renaming Columns
Renaming columns the normal way requires downtime as an application may continue
using the old column name during/after a database migration. To rename a column
without requiring downtime we need two migrations: a regular migration, and a
post-deployment migration. Both these migration can go in the same release.
### Step 1: Add The Regular Migration
First we need to create the regular migration. This migration should use
`Gitlab::Database::MigrationHelpers#rename_column_concurrently` to perform the
renaming. For example
```ruby
# A regular migration in db/migrate
class RenameUsersUpdatedAtToUpdatedAtTimestamp < ActiveRecord::Migration
include Gitlab::Database::MigrationHelpers
disable_ddl_transaction!
def up
rename_column_concurrently :users, :updated_at, :updated_at_timestamp
end
def down
cleanup_concurrent_column_rename :users, :updated_at_timestamp, :updated_at
end
end
```
This will take care of renaming the column, ensuring data stays in sync, copying
over indexes and foreign keys, etc.
**NOTE:** if a column contains 1 or more indexes that do not contain the name of
the original column, the above procedure will fail. In this case you will first
need to rename these indexes.
### Step 2: Add A Post-Deployment Migration
The renaming procedure requires some cleaning up in a post-deployment migration.
We can perform this cleanup using
`Gitlab::Database::MigrationHelpers#cleanup_concurrent_column_rename`:
```ruby
# A post-deployment migration in db/post_migrate
class CleanupUsersUpdatedAtRename < ActiveRecord::Migration
include Gitlab::Database::MigrationHelpers
disable_ddl_transaction!
def up
cleanup_concurrent_column_rename :users, :updated_at, :updated_at_timestamp
end
def down
rename_column_concurrently :users, :updated_at_timestamp, :updated_at
end
end
```
## Changing Column Constraints
Adding or removing a NOT NULL clause (or another constraint) can typically be
done without requiring downtime. However, this does require that any application
changes are deployed _first_. Thus, changing the constraints of a column should
happen in a post-deployment migration.
NOTE: Avoid using `change_column` as it produces inefficient query because it re-defines
the whole column type. For example, to add a NOT NULL constraint, prefer `change_column_null `
## Changing Column Types
Changing the type of a column can be done using
`Gitlab::Database::MigrationHelpers#change_column_type_concurrently`. This
method works similarly to `rename_column_concurrently`. For example, let's say
we want to change the type of `users.username` from `string` to `text`.
### Step 1: Create A Regular Migration
A regular migration is used to create a new column with a temporary name along
with setting up some triggers to keep data in sync. Such a migration would look
as follows:
```ruby
# A regular migration in db/migrate
class ChangeUsersUsernameStringToText < ActiveRecord::Migration
include Gitlab::Database::MigrationHelpers
disable_ddl_transaction!
def up
change_column_type_concurrently :users, :username, :text
end
def down
cleanup_concurrent_column_type_change :users, :username
end
end
```
### Step 2: Create A Post Deployment Migration
Next we need to clean up our changes using a post-deployment migration:
```ruby
# A post-deployment migration in db/post_migrate
class ChangeUsersUsernameStringToTextCleanup < ActiveRecord::Migration
include Gitlab::Database::MigrationHelpers
disable_ddl_transaction!
def up
cleanup_concurrent_column_type_change :users
end
def down
change_column_type_concurrently :users, :username, :string
end
end
```
And that's it, we're done!
## Adding Indexes
Adding indexes is an expensive process that blocks INSERT and UPDATE queries for
the duration. When using PostgreSQL one can work arounds this by using the
`CONCURRENTLY` option:
```sql
CREATE INDEX CONCURRENTLY index_name ON projects (column_name);
```
Migrations can take advantage of this by using the method
`add_concurrent_index`. For example:
```ruby
class MyMigration < ActiveRecord::Migration
def up
add_concurrent_index :projects, :column_name
end
def down
remove_index(:projects, :column_name) if index_exists?(:projects, :column_name)
end
end
```
Note that `add_concurrent_index` can not be reversed automatically, thus you
need to manually define `up` and `down`.
When running this on PostgreSQL the `CONCURRENTLY` option mentioned above is
used. On MySQL this method produces a regular `CREATE INDEX` query.
MySQL doesn't really have a workaround for this. Supposedly it _can_ create
indexes without the need for downtime but only for variable width columns. The
details on this are a bit sketchy. Since it's better to be safe than sorry one
should assume that adding indexes requires downtime on MySQL.
## Dropping Indexes
Dropping an index does not require downtime on both PostgreSQL and MySQL.
## Adding Tables
This operation is safe as there's no code using the table just yet.
## Dropping Tables
Dropping tables can be done safely using a post-deployment migration, but only
if the application no longer uses the table.
## Adding Foreign Keys
Adding foreign keys usually works in 3 steps:
1. Start a transaction
1. Run `ALTER TABLE` to add the constraint(s)
1. Check all existing data
Because `ALTER TABLE` typically acquires an exclusive lock until the end of a
transaction this means this approach would require downtime.
GitLab allows you to work around this by using
`Gitlab::Database::MigrationHelpers#add_concurrent_foreign_key`. This method
ensures that when PostgreSQL is used no downtime is needed.
## Removing Foreign Keys
This operation does not require downtime.
## Data Migrations
Data migrations can be tricky. The usual approach to migrate data is to take a 3
step approach:
1. Migrate the initial batch of data
1. Deploy the application code
1. Migrate any remaining data
Usually this works, but not always. For example, if a field's format is to be
changed from JSON to something else we have a bit of a problem. If we were to
change existing data before deploying application code we'll most likely run
into errors. On the other hand, if we were to migrate after deploying the
application code we could run into the same problems.
If you merely need to correct some invalid data, then a post-deployment
migration is usually enough. If you need to change the format of data (e.g. from
JSON to something else) it's typically best to add a new column for the new data
format, and have the application use that. In such a case the procedure would
be:
1. Add a new column in the new format
1. Copy over existing data to this new column
1. Deploy the application code
1. In a post-deployment migration, copy over any remaining data
In general there is no one-size-fits-all solution, therefore it's best to
discuss these kind of migrations in a merge request to make sure they are
implemented in the best way possible.
|