Reclaiming Disk Space in Your PostgreSQL Database
IFI recommends 6TB of disk space for an on-site PostgreSQL instance. Normally, this will accommodate approximately 3 years of database growth (depending on your subscription level). If you are running out of disk space more quickly than anticipated, the reason may be an increased number of deletes occurring during the update process. This is especially likely to occur when IFI replaces content at a more aggressive rate than usual, as we have been doing since October 2019 in order to replace our old translations with higher-quality translations from Google. The blog Understanding of Bloat and VACUUM in PostgreSQL does a good job of explaining why your database may be growing disproportionately to actual pipeline data flow. This blog will help diagnose whether bloat is the cause of your problem and provide some potential solutions.
Checking for Bloat
The following SQL query will examine each table in the XML schema and identify dead rows (tuples) that are wasting disk space.
If this query returns a high percentage (
pfrag ) of dead tuples, the
VACUUM command may be used to reclaim space.
Using VACUUM to Reclaim Storage
1) VACUUM ANALYZE
apgupd before running this command.
VACUUM ANALYZE command marks the dead tuples in all tables of the database as available for reuse, which should slow database growth considerably.
After you run this, you may want to adjust the aggressiveness of your autovacuum settings to avoid accumulating more bloat in the future. The following settings should be considered:
autovacuum_max_workers= 5 - This is is the recommended setting for a 16-core machine. If you have, for example, a 4-core, a setting of 2 would be the best available option.
autovacuum_vacuum_scale_factor= 0.02 - This setting indicates the threshold which determines when autovacuum needs to run per table. It refers to the fraction of the table size. So if the percentage of dead tuples (the
pfragcolumn in the SQL query above) is greater than 2%, this spawns an autovacuum process. The default value for this setting is 20%, which will create massive bloat in your database before the autovacuum process even starts.
autovacuum_analyze_scale_factor= 0.01 - Although not specific to table size and vacuum cleanup, this setting, which is also a percentage of fragmentation of the table, tells the autovacuum process to analyze the table (i.e., update query planner statistics) when the fragmentation percentage reaches 1% (the default is 10%).
2) VACUUM FULL <table>;
apgupd before running this command.
FULL vacuum command physically re-writes the table, removing the dead tuples and reducing the size of the table, whereas without the FULL modifier, the dead tuples are only made available for reuse. This is a processor- and disk-intensive operation but given appropriate planning, can reduce the size of the table by upwards of 25%. If you want to pursue this avenue, pick a highly fragmented table and test the amount of disk space recovered in order to assess whether it is worth the effort. For example, to test the process on
1. Determine the current size of the table.
2. Defragment and reclaim all disk space.
3. Determine the new size of the table after vacuum.
You will need at least twice the current table size available as free disk space in order to run the
VACUUM FULL command because it re-writes the entire table. You can use both strategies above -
VACUUM (to slow growth) and
VACUUM FULL (to reduce data footprint). However, it is recommended to complete
VACUUM first if you want to pursue the more aggressive
If you are really out of disk space, you may want to consider the following options:
1. Create a hot standby on a separate machine with +2TB disk, i.e., 8TB instead of 6TB. See Hot Standby for more information.
This is probably the easiest method and is least intrusive to normal workflow as nothing needs to be stopped or paused. Once the primary and standby instances are in sync, simply make the standby the primary.
2. If you can tolerate downtime, create a parallel machine as in #1 but use
pg_dump to recreate the database. You will need to pause
apgupd for the duration of the transfer: