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