Thread: [GENERAL] Config for fast huge cascaded updates

Craig de Stigter <craig.destigter@koordinates.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

On Tue, Jun 27, 2017 at 10:17:49AM +1200, Craig de Stigter wrote:
> 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.

Indeed.

Does the database need to be online when this is happening?

If it were me, I'd try to find a way to dump it, modify the data in a
dump file, and then reload it.  I think that'd be faster.

Another way you might try, if you need to be online while doing this,
is to write the whole thing into a new SQL schema.  Make the mods you
need.  When you think you're close to done, put a trigger in the "old
schema" to update data in the new schema, then do a last pass to catch
anything you missed in the interim, then cut your application over to
the new schema (update the search_path, force everything to disconnect
and reconnect, and when they reconnect they have the new data in
place).  A variation on this technique is also useful for gradual
roll-out of new features -- you don't have to upgrade everything at
once and you have a natural rollback strategy (but you need a more
complicated set of triggers that keeps the two schemas in sync during
cutover period).

This second approach isn't faster, it's hard on I/O and disk space,
but it keeps you up and you can do the changes at a leisurely pace.
Just make sure you have the I/O and space before you do it :)

Hope that helps,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 06/26/2017 06:29 PM, Andrew Sullivan wrote:
> On Tue, Jun 27, 2017 at 10:17:49AM +1200, Craig de Stigter wrote:
>> 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.
>

You can make it faster through a number of simple changes:

1. make sure synchronous_commit is off
2. make sure you have lots of checkpoint_segments (or a very large
max_wal_size)
3. make sure you checkpoint_timeout is some ridiculously high value (2
hours)

Alternatively, and ONLY do this if you take a backup right before hand,
you can set the table unlogged, make the changes and assuming success,
make the table logged again. That will great increase the write speed
and reduce wal segment churn.

However, if that fails, the table is dead. You will have to reload it
from backup.

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****     Unless otherwise stated, opinions are my own.   *****

On Mon, Jun 26, 2017 at 07:26:08PM -0700, Joshua D. Drake wrote:

> Alternatively, and ONLY do this if you take a backup right before hand, you
> can set the table unlogged, make the changes and assuming success, make the
> table logged again. That will great increase the write speed and reduce wal
> segment churn.

Note that this is not for just that table, but for all of the
implicated ones because of the CASCADE statements.  It sounds like the
OP is basically rewriting a significant chunk of the entire database,
so nothing is going to be super fast: all those CASCADEs have to fire
and all those other tables need to be updated too.

> However, if that fails, the table is dead. You will have to reload it from
> backup.

Right, and that goes for all the affected tables.

Best regards,

A

--
Andrew Sullivan
ajs@crankycanuck.ca