Could a slow update query be faster if broken down into multiple update statements in Postgres?

Question

Context

I have an UPDATE statement in Postgres which essentially restructures a jsonb column.Here's what it's doing if you're curious:

metadata = (
          jsonb_build_object(
            'input_file_url', metadata -> 'input_file_url',
            'standard_encoding_state', metadata -> 'standard_encoding_state',
            'state', metadata -> 'state',
            'error', metadata -> 'error',
            'io_file_and_run_strategy_response', (
              jsonb_build_object(
                'input_file_url', metadata -> 'input_file_url',
                'output_file_url', metadata -> 'output_file_url',
                'download_encode_upload_duration', metadata -> 'download_encode_upload_duration',
                'output_video_size', metadata -> 'output_video_size',
                'input_video_size', metadata -> 'input_video_size',
                'download_source_start_time', metadata -> 'download_source_start_time',
                'download_source_end_time', metadata -> 'download_source_end_time',
                'encode_local_file_start_time', metadata -> 'encode_local_file_start_time',
                'encode_local_file_end_time', metadata -> 'encode_local_file_end_time',
                'upload_encoded_start_time', metadata -> 'upload_encoded_start_time',
                'upload_encoded_end_time', metadata -> 'upload_encoded_end_time'
              )
            )
          )
        )

As you can see it's just changing the structure of the json object.

This table has tens of millions of records. When I run the query it usually it takes about 20 mins (I've been doing some testing on a local copy of the db).

However, earlier today it took about 2 hours. When I looked in the pg activities view, I saw that it was blocked by an IO DataFileRead event.. and in the activities it also looked like autovaccum was running (but according to pg_blocks nothing was blocked, but my theory is Autovacumm was slowing down disk read).

Actual Question

My theory on why it took so long was because autovaccum ran because so many records got updated.

Could there be any benefits if I introduced a date filter to the update query, and instead of running ONE BIG update, I run many on a loop using the date filter.

Or is it always better to just give PG a big query and let it do the most optimized thing?

I did! And I will... the issue is, the 2 hour thing only happened once... and I don't know why, so it's hard to recreate. Every other time it only took 20 mins.. so I was just looking for some general PG knowledge to see if there's anything to my theory. But will defo test it again — andy
– andy, Commented Feb 14, 2023 at 14:25
You are talking about a single update changing all rows of the table, right? Can you get down time, or do you need the database available? — Laurenz Albe
– Laurenz Albe, Commented Feb 14, 2023 at 15:11
if anyone is interested, reddit has yielded some interesting points: reddit.com/r/PostgreSQL/comments/1126kqe/… — andy
– andy, Commented Feb 14, 2023 at 15:54
great question laurenz. I think my priority is availability, and then time. Though if I could avoid an IO arms race with vacumm that would hopefully speed things up.. though I'm not a DBA so I'm not sure — andy
– andy, Commented Feb 14, 2023 at 15:55

jjanes · Accepted Answer · 2023-02-14 19:50:57Z

Could it? Sure. Is there any reason to think it will systematically be better based on the data offered here? No.

It could be better because you can throttle it (by delaying between iterations) so it doesn't interfere with other things, either because it uses too many resources or because it holds locks for too long at a time. But that seems like the opposite of your concern here.

It could be better by allowing vacuum to run between batches, so that space "disused" in one batch can be used in another one, minimizing bloat. But that also doesn't seem like your concern here.

It could also force a better execution plan, as sometimes the planner makes horrible choices for massive bulk updates. But that mostly applies to updates with a join, which doesn't seem to be the case here.

Finally, it could be paused part way through without losing all of your work, so you can access the situation and decide if you should continue or change approaches. This might be the best reason to divide it into batches.

Yes, autovacuum could slow down your update by consuming too much of the available IO resources. But since autovacuum throttles itself by default, this usually shouldn't be a problem on any capable server unless you have mucked up the throttle tuning values. Your theory about autovacuum doesn't make a lot of sense for a different reason--the changes made by a bulk update won't get reported until that update commits, meaning the update itself can't be the thing that caused autovacuum to decide to vacuum your table during the update. It must be scheduled due to other things that were going on before you started the update, (or things that started and then finished and committed during the update).

You should configure auto_explain to automatically analyze all statements, and log the slow ones. You would want auto_explain.log_analyze=on, auto_explain.log_buffers=on, auto_explain.log_min_duration set to some suitable time, and also have track_io_timing turned on. This can impose a high overhead on old systems with slow clock access, so if that is the case for you you could also set auto_explain.log_timing=off, which eliminates almost all of the overhead, but also produces less detailed output.

Wow great answer, thank you. I just learned so much reading that. You're right about autovaccum. Here's something that didn't occur to me until I read your message (because I just didn't know). All the other times I ran the update on my test database, I ran it in isolation. However, this is only one of three updates (2 on this table, and 1 on another similarly sized table) and the time it took 2 hours coincides when I ran all 3 updates in immediate succession. So I guess the other two set off autovaccum which then used up IO resources? 🤔 This was all in WSL on my 16gb Surface Book 2 — andy
– andy, Commented Feb 14, 2023 at 20:58

Stack Exchange Network

Could a slow update query be faster if broken down into multiple update statements in Postgres?

Context

Actual Question

1 Answer 1

Your Answer

Hot Network Questions

Could a slow update query be faster if broken down into multiple update statements in Postgres?

Context

Actual Question

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions