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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
