CODEWITHSUNDEEP
postgresqlthinking-sphinx
Gul
Gul

Reputation: 1767

Add column without down time

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.

Upvotes: 3

Views: 500

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions