3

I need to delete around 80% of my 500Gb Postgresql DB. I have successfully run a delete command for around 50Gb of rows so far, and paused before proceeding. (This took a long time, perhaps one hour)

I notice that after deleting around 50Gb of data, no extra disk space is freed up, but some memory intensive postgres processes can be observed when I run 'htop'. Am I correct in assuming this is down to dead rows, which need to be vacuumed before the disk space is released?

Second part of this question is, if I am not mistaken about the first part, am I better off deleting all the rows and then allowing auto-vacuum to take place? It appears auto-vacuum (or some other intensive background process) has started by itself before I had a chance to continue my row deletion command list. Do I just proceed or should I gracefully tell it to stop first?

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
jsstuball
  • 4,104
  • 7
  • 33
  • 63
  • 1
    autovacuum does not perform `vacuum full` - so it won't necessarily release deleted tuples disk space, but it will mark them as reusable instead. yes- its normal. no - you should not care about it - continue your job, leaving autovacuum to perform its – Vao Tsun Apr 06 '18 at 08:30

2 Answers2

3

After a big delete, autovacuum is sure to run. That is as designed and should not interfere with you deleting still more rows.

While autovacuum frees the dead space in the tables, it does not return the space to the operating system. Rather, it remains as free space in the table and can be reused for future inserts.

If you want to shrink the tables, run VACUUM (FULL) on them, but be warned that this rewrites the table, so it temporarily uses additional storage space and blocks all concurrent activity on the table.

If you have to do mass deletes like that regularly, consider partitioning the table. It makes bulk deletes painless.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
1

A better solution would be to TRUNCATE the table.

In my scenario i deleted a specific row that was taking up a lot of disk space but it was too much data for VACUUM to clear in a reasonable amount of time.

I ended up duplicating the table:

CREATE table dupe_table AS (SELECT * FROM table);

Truncating the original table:

TRUNCATE table

Finally moving the data back:

INSERT INTO table(column1, column2, column3)
SELECT column1, column2, column3
FROM dupe_table

NOTE: that you could lose data if you transaction happening between the creation of the duplicate table and the truncating of the original table

ricks
  • 3,154
  • 31
  • 51