Re: [GENERAL] Config for fast huge cascaded updates

2017-07-02 Thread Craig de Stigter
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

Re: [GENERAL] Config for fast huge cascaded updates

2017-06-27 Thread Andrew Sullivan
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

Re: [GENERAL] Config for fast huge cascaded updates

2017-06-26 Thread Joshua D. Drake
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 num

Re: [GENERAL] Config for fast huge cascaded updates

2017-06-26 Thread Andrew Sullivan
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,

Re: [GENERAL] Config for fast huge cascaded updates

2017-06-26 Thread Tom Lane
Craig de Stigter 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: > UPDA