Re: Config for fast huge cascaded updates

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