faster way to update very large table (150 million rows) in Postgres

Question

I have a very large table (about 150 million rows) in Postgres 12. It has a simple structure:

• col1 (VARCHAR)
• col2 (VARCHAR)
• col3 (VARCHAR)
• col4 (VARCHAR)

The PK is (col1, col2, col3, col4), so as a result that has a unique index. I also separately have one index on col2, one index on col3, and one index on col4.

I want to run a query like: UPDATE mytable SET col4 = 'someval' WHERE col2 = 'someotherval'

As it turns out, this query needs to hit about 150 million rows. When I run it, it seems to run forever.

That is the total table size because right now col2 will always equal 'someotherval'. However in the future there will be other values in that column other than 'someotherval' as well. The table is probably eventually going to be about 300 million rows where most of the time col2 is either 'someotherval' or a single other value. Maybe in a few rare instances it would be a third value.

Anyway, I'm wondering if there is any faster way to perform an update like this. I am running it right now, in Cloud SQL on Google Cloud Platform, and so far it has taken 3.5 hours and hasn't finished yet. It's OK for it to take a while but I don't want it to take all day.

Looking at the performance logs for my instance (on the Cloud SQL instance page in Google's Cloud Console), the main thing is that write operations are flatlining at about 4k/second, and read operations at 2k/second. Do you think that is equal to the number of rows? If so then this is going to be way too slow. Meanwhile there is not much CPU being consumed at all, so I don't think that the operation is benefitting from any parallelism.

Anyway I was hoping since the operation is simple that there might be a trick to speed this up.