Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while the update is working. The update operation is pretty simple, because the value of the new column is basically computed from another column, kind of like this:

UPDATE table
    SET a_col = array[col];

However, I have to leave this running for a long time, and I would like to not bog down the usual DB activity while the update happens. Is there a better method to do this than running a process that does everything in one go?

Thanks very much!

share|improve this question
Well,postgresql is reliable, and even when crashing does not corrupt your data. You might look for a faster ways of updating all these data, but I cannot understand your reference to "reliable": if anything goes wrong, then your transaction is just rolled back. – eppesuig Apr 2 at 16:48
My phrasing was incorrect. I've modified it to make it clearer. Basically, I want the update to run while the DB is in operation. I ran the query like I typed it above, and it basically locked everything up. – Juan Carlos Coto Apr 2 at 17:39
You may use LIMIT as shown in this other answer stackoverflow.com/questions/112249/… – eppesuig Apr 2 at 19:28
1  
Cross-posted to SO where I answered before I saw this one. Would be dba.SE stuff ... Juan, please don't cross-post, even less without declaring it. – Erwin Brandstetter Apr 2 at 20:26
@ErwinBrandstetter Right. I thought I had asked them in a way as to allow for some leeway (one is more concentrated on "How do I do this?" and the other on "What should I do?"), but I'll make sure not to do that again. Thanks very much, sorry if it caused you trouble. – Juan Carlos Coto Apr 2 at 20:30
show 2 more comments

closed as not a real question by JNK♦ Apr 3 at 17:22

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.