How to reclaim the storage in PostgreSQL after Vacuum?

PostgreSQL Vacuum

When you run a standard VACUUM command in PostgreSQL, you might notice that it doesn’t free up space at the operating system level, even though it cleans up dead tuples within the database. This is because VACUUM is designed to work efficiently without disrupting normal database operations. However, there are several effective methods available when you need to reclaim and return storage space to your operating system properly. Here’s a comprehensive guide on how to accomplish this:

• Use VACUUM FULL:

VACUUM FULL table_name;

This command rewrites the entire table to a new disk file, removing dead rows and returning space to the operating system. Note that it requires an exclusive lock on the table, blocking all operations during execution.

• Use pg_repack extension:

CREATE EXTENSION pg_repack;
pg_repack -t table_name database_name;

This is a more production-friendly approach since it doesn’t require long locks.

• For indexes, use:

REINDEX TABLE table_name;

or

REINDEX INDEX index_name;

• Use CLUSTER command to reorganize tables:

CLUSTER table_name USING index_name;

• Adjust autovacuum settings to be more aggressive:

ALTER TABLE table_name SET (
  autovacuum_vacuum_threshold = 50,
  autovacuum_vacuum_scale_factor = 0.1
);

Important considerations:

• VACUUM FULL is highly I/O intensive and requires an exclusive table lock, which means no other operations can access the table during the process. This can significantly impact database availability during execution.
• Regular VACUUM operations help maintain table health by preventing table bloat through dead tuple cleanup, but they are designed to work without disrupting operations and therefore cannot return disk space directly to the operating system. This behavior is intentional to maintain performance.
• For large tables in production environments, pg_repack is typically the most effective solution as it provides a balance between space reclamation and operational impact. It achieves this by creating a new copy of the table while maintaining access to the original.
• Regularly monitor table bloat by checking the ratio of actual disk space used versus the space needed for active data. This helps identify when storage reclamation operations are necessary and prevents performance degradation from excessive bloat.

Optimizing PostgreSQL Performance: Reclaiming Space with Minimal Downtime Using pg_repack

How to use pg_stat_statements to monitor the performance of VACUUM and ANALYZE operations?

PostgreSQL Vacuum and Vacuum full are not two different processes