Keenan, Thanks, that makes a lot of sense. This is a leaf table in our model, so nothing points to it except the UI (where you can update stuff from), so I think we could do something like this:
* As you suggest, create a new column of type bigint with an auto- increment/sequence set up via a migration * Do batch updates to the table, setting values from this new sequence, as you suggest, until we hit rows that already have values for it * Do the rename Thanks for the suggestion: postgres not giving me any feedback on progress was making me sick to my stomach and this is a nice, incremental, bail-out-able way to unfsck our db. Beers on me next time you are in Sacramento, California. Cheers, Carson On Mar 14, 4:12 am, Keenan Brock <[email protected]> wrote: > 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 > > > athttp://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 > > athttp://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.
