Re: Config for fast huge cascaded updates

Craig de Stigter <craig(dot)destigter(at)koordinates(dot)com> writes:
> We're doing a large migration on our site which involves changing most of
> the primary key values. We've noticed this is a *very* slow process.

> Firstly we've set up all the foreign keys to use `on update cascade`. Then
> we essentially do this on every table:

> UPDATE TABLE users SET id = id + 1000000;

> Since this cascades via about 40 foreign keys to most of the other tables
> in the database, this update on our fairly small table takes about five
> hours.

Do you have indexes on all the referencing columns?

The core problem here is that the updates will be cascaded one row at a
time. As long as the referencing rows can be found by an indexscan,
that might be tolerable, but it's certainly not as fast as a bulk
update.

If you can guarantee no other updates while you're doing the migration,
it might be practical to drop the foreign key constraints, run all the
bulk updates by hand (on referencing tables too!), and then re-establish
the constraints. Of course there's a lot of potential for errors of
omission here, but if you can script it and test the script in advance,
it's worth considering.

regards, tom lane