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.

  • 1
    Why not drop col4, and recreate it with the default 'someval'?
    – jjanes
    Commented Jun 28, 2021 at 19:36
  • 3
    Disk IOPS is directly related to disk size. Second, your index strategy is killing performance. Commented Jun 28, 2021 at 22:40
  • @JohnHanley what do you think I should do differently with indexes? Right now I'm trying a solution where I INSERT INTO newtable SELECT * FROM oldtable without indexes on newtable (only the PK) and then recreate the indexes afterward. I think it might help a bit. Is that what you had in mind?
    – Stephen
    Commented Jun 29, 2021 at 1:06
  • @jjanes I don't think that dropping and recreating the column will work because while col4 needs to be someval when col2=someotherval, there are other entries in the table where the value of col2 is something different, and those need to be preserved.
    – Stephen
    Commented Jun 29, 2021 at 1:13
  • If my current strategy does not work, I am going to try just partitioning the table by the values of col2 and col4. The "behind the scenes" of the problem I am trying to solve is that col1 is an id, col2 is type_of_value, col3 is the_actual_value, and col4 is curr_or_next_version. So an ID might have 'a' for the type 'person_name' where col4 is 'curr' and 'b' where col4 is 'next'. Then I have a query to delete all the 'curr' values and set 'next' to 'curr', essentially moving to the next version.
    – Stephen
    Commented Jun 29, 2021 at 1:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.