RDS for PostgreSQL Table Bloat Remediation and Space Reclamation

How It Works

1. autovacuum: automatic space reuse (logical reclamation)

• Working principle: A background daemon periodically scans tables, identifies dead tuples, and marks them as reusable space. This reclaimed space is registered in the free space map (FSM) for future INSERT or UPDATE reuse.
• Space: This mechanism achieves only logical space reclamation and does not shrink the physical file size. The table file size remains unchanged at the OS level but internal fragments are reused.
• Locking and performance: It does not block reads or writes and consumes minimal resources.
• Ideal scenario: This mechanism is ideal for routine automatic maintenance and serves as the baseline defense against uncontrolled table bloat.

2. VACUUM FULL: physical space reclamation (blocking)

• Working principle: This mechanism rewrites the entire table by copying valid rows into new compact pages. Once the rewrite is complete, it discards the original table.
• Space: It achieves absolute physical space reclamation. Table files shrink to the minimum size required for valid data. The released space is returned to the OS.
• Locking and performance: An ACCESS EXCLUSIVE lock is required on the target table and reads and writes for that table are completely blocked. The process requires temporary disk space of about twice the size of the table.
• Ideal scenario: This mechanism is used within downtime maintenance windows or when a large amount of disk space must be reclaimed and online tools cannot be used.

3. pg_repack: online, lossless space reclamation (recommended for production environments)

• Working principle: This mechanism creates a new table to contain valid data, synchronizes incremental changes using triggers or logs, and then atomically swaps the metadata of the new and old tables.
• Space: It achieves physical space reclamation. Data pages are reorganized and excess space is released to the OS.
• Locking and performance: No reads or writes are blocked. Only a brief, transient lock (milliseconds) is required during the final metadata swap. The process requires additional temporary space approximately equal to the size of the target table.
• Ideal scenario: This mechanism is the preferred space reclamation solution for production environments requiring high availability.

Comparison of Space Reclamation Mechanisms