0

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.

Improve this question
2
  • 1
    Trying to run a database with only 20 GB of spare space when you have an 85 GB table will be constant torture. Get more space, or you will be beating your head against this wall endlessly.
    – jjanes
    Dec 31, 2022 at 4:15
  • Completely agreeing with you on this, but that's unfortunately something beyond my decision and control
    – Sam
    Jan 2 at 14:33

1 Answer 1

Reset to default
0

Updating the table in batches using a WHERE clause should work, but you will need to VACUUM the table between each batch. That way the space obsoleted by one update can get reused by the next one. You should make sure you don't have any long-lived snapshots (transactions) as those will force the obsolete tuples to be retained in case the old snapshot should wish to see them.

If you run the batches back to back with no pause in between, then you will be at the mercy of when autovacuum kicks in and how long it takes to run. By doing a manual vacuum between batches, you can be sure of when it ran and when it finished.

Improve this answer
3
  • Hi thanks for the answer, yes updating it by using where clause seems to be the best method. However, plain vacuum does not help to reclaim the space. Only full vacuum seems to be working
    – Sam
    Jan 2 at 14:31
  • Plain vacuum doesn't reclaim space as in making it available to the OS, but it does reclaim space by making it reusable to PostgreSQL. You can use pg_freespacemap to verify it was made available. It it wasn't, then look into that long-lived snapshots.
    – jjanes
    Jan 2 at 18:07
  • Hi thanks, but does this mean that I'd still be in trouble if I end up going above disk space by these updates and vacuum's, even though the space is available for PostgreSQL to reuse? Because I can see that after each round of update and vacuum the size of the table keeps going up. i.e. the space vacuumed in the previous step is not directly used in the next update operation
    – Sam
    Jan 3 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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