5

I have a ~320 million row table on which I run the following query:

UPDATE my_table SET state = TRIM(state)

Needless to say this has been running for 90+ hours (since thursday afternoon) and has not finished yet.

I'd like to know, more or less, how many rows it has modified until now. Is there any way to obtain this information from the running transaction?

Trying to search I have found how to determine a rough estimate of how many new rows are being added to a table using the pgstattuple function:

select dead_tuple_count from pgstattuple('my_table');

But this does not seem to show any interesting information for how many rows have been modified.

  • 1
    pg_table_size('my_table') might give you a rough idea, provided that you've got something to compare against... By the way, how many records actually need trimming? If it's only a fraction of the table, then adding a WHERE state <> TRIM(state) should be a huge improvement. – Nick Barnes Feb 11 at 12:16
  • @NickBarnes Does the size reported by pg_table_size include the modified but uncommitted rows? I did not know that. I don't know the initial table size but looking at how much it increases in 1 hour I might be able to guess it... – Giacomo Alzetta Feb 11 at 13:33
  • It just counts the size of the underlying files, so it doesn't (and can't) discriminate between committed and uncommitted data. Since Postgres uses row versioning, updating every record in a table should (roughly) double the size on disk. – Nick Barnes Feb 11 at 13:46
  • @NickBarnes Thanks! I believe I achieved a rough estimate good enough for my purpose. I wrote an answer describing how I estimated the initial size from the growth rate. In my case I will probably kill the transaction because apparently it's at roughly 30-35% of the progress after 100 hours or so. – Giacomo Alzetta Feb 11 at 17:08
2

There is at least one way to peek at the progress of an uncommitted UPDATE, though it's a bit heavy-handed.

Postgres handles transaction isolation through row versioning. Their implementation involves tagging every record version with the smallest and largest transaction IDs which are allowed to see it (xmin and xmax, respectively).

Under this scheme, an UPDATE works by setting the xmax of the target record to the current transaction ID (equivalent to a DELETE) and creating an updated copy with the transaction ID in xmin (equivalent to an INSERT).

These system columns can be queried, so given the transaction ID of the UPDATE (which you can get from pg_stat_activity.backend_xid), you can find out how many rows it's processed with e.g.:

SELECT COUNT(*)
FROM my_table
WHERE xmax = 2357

Things get a bit messier if the transaction has set any savepoints, in which case the xmax will be a subtransaction ID, which doesn't appear in pg_stat_activity (or anywhere else, as far as I'm aware). In that case, you can inspect all rows which have been marked for update/deletion, by either in-progress or rolled-back transactions, with:

SELECT xmax, COUNT(*)
FROM my_table
WHERE xmax <> 0
GROUP BY xmax

... and from there, it shouldn't be too hard to figure out which ID is the one you're interested in.

  • Damn nice! Unfortunately I already interrupted the query, but this is certainly the way to go the next time I end up with a long running query. – Giacomo Alzetta Feb 12 at 10:42
  • @Giacomo: Yeah, sorry I didn't think of this in the first place, I was a bit fixated on digging the info out of the table stats somehow :). The pg_table_size() approach would still be useful for a long-running INSERT, where the pending rows aren't visible to any concurrent query. – Nick Barnes Feb 12 at 10:50
3

Following @Nick Barnes suggestion to use pg_table_size I was able to obtain a rough estimate of progress. Note however that this can only work when only one transaction is modifying the table. If you have multiple transactions modifying the table you will not be able to tell how much a specific transaction has completed.

Moreover in order for you to be able to get a progress estimate you should know how many rows are affected by the query.

So using the command:

SELECT pg_table_size('my_table');

You can obtain the size of the table (this contains both the commited and uncommitted data).

If you know the initial size of the table initial_size, the initial number of rows N, and the number K of rows affected by the query you can get an estimated of the data that your query will write:

delta_size = (initial_size/N)*K

Now if the current table size is current_size your estimate for progress will be:

progress_perc = 100*(current_size - initial_size)/delta_size

If you do not know the initial size of the table you can estimate it by checking the growth of the table.

Using pg_table_size check how much the table grows in a certain interval of time delta_t, say 1 hour. Then you can estimate the initial size from the equation:

initial_size = current_size - growth*num_delta_t_passed

You can obtain the time since when the transaction started using the query:

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

(taken from this gist page)

For Postgresql 9.6 it does not seem possible to obtain an accurate response. I don't know whether a better option exists for Postgresql 10+.

3

With postgres implementation of MVCC, you may query the xmax metafield that will tell you exactly how many rows are currently being updated by the other transaction (assuming, there is no other transaction performing write operations on that table at the same time).

So something like :

select count(1) from my_table where xmax <> 0;

should give you the number of rows updated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.