From: | Craig de Stigter <craig(dot)destigter(at)koordinates(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Config for fast huge cascaded updates |
Date: | 2017-06-26 22:17:49 |
Message-ID: | CAF1M8pe8_FXTyjhDd4Dq+O+pxdMAFRBGDxQTNgqyo4cZciD7XA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi folks
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.
This is understandable (it's rewriting most of the database) but what
settings can we tweak to make this process faster?
So far we have experimented with the following:
- checkpoint_timeout : 3600
- autovacuum: 0
- max_wal_size: 128 (2GB)
- synchronous_commit: off
What other things would you recommend to improve performance of this sort
of thing?
--
Regards,
Craig
Developer
Koordinates
+64 21 256 9488 <+64%2021%20256%209488> / koordinates.com / @koordinates
<https://twitter.com/koordinates>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-06-26 22:36:56 | Re: Config for fast huge cascaded updates |
Previous Message | Adrian Klaver | 2017-06-26 21:51:15 | Re: ERROR: query returned no rows |