3

I need to add thinking-sphinx delta column in my table that is having 17Million records, then i need to index that field.

The DB is postgres and adding the column will be resulted in a down time for an hour which is not bare-able, so any suggestion/idea that how can table altered so there is no lock on whole table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
Gul
  • 1,757
  • 2
  • 15
  • 26
  • Why do you think that it will take an hour to update? I just made a test on a production table with ~ 3,400,000 rows. Adding a new column took 4.366 ms, index creation - 8725.777 ms. – strkol Mar 28 '12 at 14:58

1 Answers1

1

The only solution I can think of to avoid a long table lock, is to create a new table with the desired structure. Then use INSERT INTO ... SELECT to populate that table, re-create all indexes (including the new one) and then rename the new table to the old table.

Renaming the table can be done in a transaction so that no client will notice this (although this might take some time, because I think the RENAME will wait until it gets an exclusive lock).

You will have to recreate foreign keys referencing that table as well (not sure about the level of locking involved in that).

This of course is only possible if you have enough space so that you can keep a complete copy of the table around.