Postgres improve large update performance?

Question

I'm migrating my Postgres database and am attempting to update a string value to a numeric value, like this:

UPDATE table SET column = 1 WHERE LENGTH(column) = 1;

This table contains around 20 million rows, and the update has been taking forever to run. I have an index on LENGTH(column) as well as 4 other indexes on different columns, one of which is a UNIQUE index on 2 columns. There's also a foreign key constraint on this table.

What could I do to speed this query up? If more information is needed, I'd be happy to provide it.

Answer 1

Dropping constraints that affect the column and indexes (except the one that supports the WHERE condition) will speed up such an UPDATE.

You can also get a small performance gain from increasing max_wal_size.

Other than that, you just have to wait it out.