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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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. *****

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks everyone. Sorry for the late reply.

Do you have indexes on all the referencing columns?

I had thought so, but it turns out no, and this appears to be the main
cause of the slowness. After adding a couple of extra indexes in the bigger
tables, things are going much more smoothly.

write the whole thing into a new SQL schema

This is a really interesting approach I hadn't thought of! We can currently
afford a little bit of downtime, but it's helpful to keep this in mind if
we ever do this kind of thing again in future.

The two changes we've made are:

- Add a few indexes so that the cascades operate more efficiently
- Move some of the tables (whose ID values don't matter so much to our
app) into a separate migration, which can be run before we take down the
site. Then only the tables whose IDs matter to the app/user are done while
the site is down.

With those changes it looks like we can fit the downtime into the window we
have. Thanks for all the advice, much appreciated!

On 28 June 2017 at 01:28, Andrew Sullivan <ajs@crankycanuck.ca> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Regards,
Craig

Developer
Koordinates

+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates
<https://twitter.com/koordinates&gt;