GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Online Index Creation and Schema Changes

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Online Index Creation and Schema Changes

    We have some schema and index tweaks and some schema changes (mostly column adds/mods) that we'd like to do on a couple largish tables. I'm estimating that some of these will take an hour to complete.
    Unfortunately, the tables in question get a fair amount of writes, so it would effectively take our site down for the entire operation.
    My thought was to create a new table (CREATE TABLE LIKE..), ALTER it, and then insert all the rows from the old (and online) table. After some iteration, the tables should 'converge', at which point we use RENAME to swap the two and then run a final pass to sync the two tables.

    The online table will need to be locked during the swap and final pass to make sure that no updates/deletes sneak through. Unfortunately, RENAME doesn't like to be run on locked tables.

    Any ideas here? Is there another way? Heck, I'd be fine with acquiring a global lock.

    Thanks!
Working...
X