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:
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.
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
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+.