By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

After Vacuum Full failed, the database was left with 100% of space used

0

After a failure in Vacuum Full, the numb3rs-data database had 100% of its space used. The server was rebooted and an attempt was made to run Vacuum Full again. This comand was run after deleting 1 million records from a table (dbsaude.ambulatorio_resumo).

Topics
DatabaseAnalytics
Tags
DatabaseAmazon Redshift
Language
English
Andre Almeida
asked a month ago61 views
1 Answer
0

I apologize, but there seems to be a misunderstanding in your statement. VACUUM FULL does not typically fail in a way that would leave the database with 100% space used. In fact, VACUUM FULL is designed to reclaim space and reduce bloat in the database.

However, if you're experiencing issues after deleting a large number of records and attempting to run VACUUM FULL, here are some points to consider:

  1. VACUUM FULL requires significant free space to operate, as it creates a new copy of the table. If your database was already near capacity, this could cause issues.

  2. VACUUM FULL requires an ACCESS EXCLUSIVE lock on the table, which means no other operations can access the table while it's running. This can lead to timeouts or apparent "failures" if other processes are trying to access the table.

  3. After deleting 1 million records, running a regular VACUUM (without FULL) might be sufficient and less risky. Regular VACUUM reclaims space for reuse within the same table but doesn't require as much free space or locking.

  4. If you're still having issues, you might want to consider using the pg_repack extension instead of VACUUM FULL. pg_repack can achieve similar results with minimal locking and is supported by both Amazon RDS for PostgreSQL and Aurora PostgreSQL-Compatible.

  5. Make sure you have enough free space in your database before attempting VACUUM FULL again. You might need to increase your storage capacity if you're consistently running out of space.

  6. Check your PostgreSQL logs for any specific error messages related to the VACUUM FULL operation. This can provide more insight into why it might have failed.

If you continue to experience issues, it would be advisable to contact AWS support for more specific troubleshooting based on your exact database configuration and the specific error messages you're encountering.
Sources
Vacuuming and analyzing tables manually - AWS Prescriptive Guidance
Example: Reclaiming space by using autovacuum and VACUUM FULL - AWS Prescriptive Guidance

profile picture
re:Post Agent
answered a month ago
AWS
SUPPORT ENGINEER
Deepam_G
reviewed a month ago
  • Deepam_G SUPPORT ENGINEER
    a month ago

    Hi Andre, as suggested in the above response, the VACUUM FULL works by rewriting the content of the original table to a new disk file and hence, it requires enough free storage to be available to accommodate the live data on the table. Once all the live data is copied to the new table, VACUUM FULL reclaims the storage back by dropping the original table and replacing it with the new one without without extra space. As mentioned, if your database does not have sufficient free storage, it can cause issues. To troubleshoot the Disk Full related issues, kindly refer the AWS article referenced below. Please feel free to raise a support case with AWS team if you may require further assistance.

    [+] https://repost.aws/knowledge-center/diskfull-error-rds-postgresql

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content