Why does filled disk space not release immediatly (only during night) after dropping partitions of a table from Postgres database in Azure server?

Diogo dos Santos 20 Reputation points
2024-04-23T09:04:56.3566667+00:00

I am making some experiments with partitions. I inserted about 20GB of data into a table with partitions, then I dropped the partitions. I wanted to check how fast I could insert data into the table and drop its partitions to release space in the disk to its original point before insert, but after I dropped the partitions, the space in the disk filled with data I inserted was not released. It happens to free only during the night. So I would like to ask, why does it work like that? Why does it seem that data keeps in the disk after its deletion and what mechanism makes them be deleted at night? Does it have anything to do with WAL archive? Or autovacuum? I turned autovacuum off and nothing changed. Thank you for your support.

Azure Database for PostgreSQL
Accepted answer
  1. Sedat SALMAN 13,265 Reputation points
    2024-04-23T10:03:06.03+00:00

    PostgreSQL uses MVCC to manage data consistency. When a row is deleted, PostgreSQL marks the space occupied by the data as available for reuse by the database but does not physically remove it immediately. This space can then be used to store new data as it comes in. Even if you turned autovacuum off, manual vacuuming is necessary to reclaim space or prevent transaction ID wraparound issues.

    To manually reclaim disk space

    VACUUM FULL;

    since this operation can lock the tables while vacuuming you need to be careful with this command

    1 person found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest