I have to update cca 2.5 milion rows in a table with cca 20 milion rows.
The updated column value is based on value of another column, but another select is required to determine it's value. The column being updated is also a part of an index.
Currently I generated sql script with one UPDATE statement per row, but I imagine this is not very efficient. What's the most efficient way and are there any post update actions to perform to keep the table performing well? The table is being constantly used (selected/updated/inserted to), I don't want this to cause any downtime. The version of postgresql is 9.1.
EDIT
The updates in the script are of form
UPDATE table1 SET fid=123 WHERE id=345;
Where id column is integer and the primary key of the table.
The schema looks like this:
CREATE TABLE table1 (
id serial PRIMARY KEY,
date timestamp,
rname varchar(50),
fid integer references table2(id)
);
CREATE INDEX ON table1 (fid, date);
CREATE TABLE table2 (
id serial PRIMARY KEY,
rname varchar(50)
);
The fid
column was recently added and I need to update some records in table1 so the records are properly linked by record id and not a rname
column which causes data redundancy and other problems.
I've written perl script that generates set of update statements to do so based on the current state of the tables. The new inserted records in table have fid
properly filled, I need to update the old.