I have a single table with an approximate size of 85gb and roughly around 120 million of rows (in a PostgreSQL (12.8) database).
I need to update all 120 something million rows with an update query to get rid of certain characters existing in all rows of a column by using the function of regexp_replace. And another constraint I have is that I don't have much disk space (around 20 gb), considering the necessity of duplication of the table while running the update operation. But this is a secondary issue for me. For the time being, the main problem is the slowness of this operation during the run and more importantly increasing size of table once the run has been completed. This increase in the size leaves no other option for me other than running a full vacuum on the table, which is something I cannot do due to the aforementioned disk space limitations.
I should also add that this table is indexed (with different methods including btree) and also has a primary key. I also tried to create cluster on the index but nothing has changed. In order to reduce the run time I selected only 20 million rows from the main table and tried to work on that one, however that also didn't help. Also related to the full vacuum necessity I mentioned above, even if I try to update the table by applying a where condition to update only a smaller portion at each run, that also causes an growth in size, which means that I have to run full vacuum afterwards, as normal vacuum does not help reducing the size back to what it was before the run.