Tracking progress of an update statement [1101]

Sometimes there is a need to execute a long running update statement. This update statement might be modifying millions of rows as was the case when we went hunting for a way to track the progress of the update. Hunting around took us to In our particular case, we are using postgresql but this should work with any database server that provides sequences. Our original sql was of the form:

update only table1 t1
set amount = t2.price
from table2 t2
where =;

There is of course now way of figuring out how many rows had been updated already. The first step was to create a sequence


We can then use this sequence in the update statement to ensure that each row updated also increments the sequence

update only table1 t1
set amount = t2.price
from table2 t2
where nextval('seq_progress') != 0
and =;

Once the query is running, you can open another connection to the database. To get an indication of how far it has got, you can just run the following

 select nextval('seq_progress');

Bear in mind that this will also increment it by 1 but if you have millions of rows which is really the only case in which this would be useful, a few additional increments is hardly going to make a difference.

Good luck and have fun!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.