Hi Carson,
Adding a column is quick
Renaming a column is quick too.
Updating… not so much.
I would
1. create/run a migration that adds the column.
do not add any indexes on the column yet
2. update the columns:
start_id = connection.select_value "select min(id) from big_table where new_col
is null" #or 0 if you set not null/default to 0
end_id = connection.select_value "select max(id) from big_table"
BATCH_SIZE=10000
while start_id < end_id
connection.execute "update big_table set new_col = old_col where id >=
#{start_id} and id < #{start_id + BATCH_SIZE}"
start_id += BATCH_SIZE
end
3. drop the old column and rename the new column to the name of the old column.
add indexes/foreign keys
this migration will require a rails restart
Play around with the batch size.
There will be a sweet spot. doubling the number of rows will only increase the
time by a few seconds. Then all of a sudden it will take longer.
Also, while you are playing with batch size, keep increasing the start_id. so
you are getting the work done while you are playing/experimenting.
Time the execution to give you an idea of just how long it will take to update
every row.
—Keenan
On Wednesday, March 14, 2012 at 4:57 AM, Peter van Hardenberg wrote:
> Hi Carson,
>
> Unfortunately, followers are exact replicas of the master database and do not
> support having changes made to them, so unfortunately your proposed plan
> won't work.
>
> That said, if the on-disk storage type is compatible, it should be basically
> instantaneous. Try it on a follower and see if it happens instantly.
> Otherwise, I recommend you create the new column and migrate the data with a
> worker process gradually. Gigantic transactions can cause pain for running
> databases due to a large number of non-visible rows polluting things like
> indices. Someone else may have more specific advice for your case.
>
> Let us know what you discover,
> -pvh
>
> On Tue, Mar 13, 2012 at 9:11 PM, Carson Gross <[email protected]
> (mailto:[email protected])> wrote:
> > Hi Guys,
> >
> > So, we've got ourselves into a bit of a pickle, and have an integer
> > column in a (100 million plus rows) table that really needs to be
> > bigint. Not this year, but certainly before we die. I'd like to make
> > the change sooner rather than later, and I *think* that heroku is
> > gonna save our bacon here if the following technique could work:
> >
> > * Create a database that follows our current db
> > * In the follower run 'ALTER TABLE my_table ALTER COLUMN id TYPE
> > bigint'
> > * Wait a long time
> > * Wait for the follower to catch up (I assume that given that we are
> > widening the datatype the follower will work again after the upgrade)
> > * Move the app over to the new db.
> >
> > Does this sound plausible?
> >
> > Thanks,
> > Carson
> >
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Heroku" group.
> > To post to this group, send email to [email protected]
> > (mailto:[email protected]).
> > To unsubscribe from this group, send email to
> > [email protected]
> > (mailto:heroku%[email protected]).
> > For more options, visit this group at
> > http://groups.google.com/group/heroku?hl=en.
> >
>
> --
> You received this message because you are subscribed to the Google Groups
> "Heroku" group.
> To post to this group, send email to [email protected]
> (mailto:[email protected]).
> To unsubscribe from this group, send email to
> [email protected]
> (mailto:[email protected]).
> For more options, visit this group at
> http://groups.google.com/group/heroku?hl=en.
--
You received this message because you are subscribed to the Google Groups
"Heroku" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/heroku?hl=en.
