When you delete records from a PostgreSQL table, the table size may not immediately reduce. This is because PostgreSQL employs a technique called MVCC (Multi-Version Concurrency Control), which keeps old versions of rows for concurrent transaction consistency and isolation.
To reclaim the disk space occupied by the deleted rows and reduce the table size, you can perform a VACUUM operation. VACUUM is responsible for cleaning up the dead rows and updating the visibility map to reflect the changes. The command to run VACUUM on a table is as follows:
VACUUM table_name;
Replace table_name with the name of your table.
Additionally, if you want to reclaim disk space at the table level, you can use the VACUUM FULL command. However, please note that VACUUM FULL requires an exclusive lock on the table and can be more resource-intensive.
VACUUM FULL table_name;
Running VACUUM or VACUUM FULL on the table should free up the disk space occupied by the deleted rows and reduce the table size accordingly. Keep in mind that it may take some time for the operation to complete, depending on the size of the table and the amount of data being processed.
“VACUUM” it is not claim the space, but “VACUUM FULL” did?
Here are a few possible reasons why regular VACUUM might not have reclaimed the space:
- Transaction ID Wraparound: If your table has reached the transaction ID wraparound point (2 billion transactions), regular VACUUM may not be able to remove dead rows until you perform a maintenance procedure called a “vacuum freeze” or “autovacuum” runs on the table. This situation is known as “frozen” transactions.
- Long-running Transactions: If there are long-running transactions holding open transactions or preventing the reclamation of dead rows, regular VACUUM may not be able to free up the space until those transactions are completed or released.
- Concurrent Activity: If there is heavy concurrent activity on the table, regular VACUUM may not be able to keep up with the rate of row deletion, which can result in the space not being reclaimed immediately.
In such cases, using VACUUM FULL can be an effective solution.
