Introduction

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.

SELECT schemaname || '.' || relname as tblnam,
    n_dead_tup,
    (n_dead_tup::float / n_live_tup::float) * 100 as pfrag
FROM pg_stat_user_tables
WHERE schemaname = 'xml' and n_dead_tup > 0 and n_live_tup > 0 order by pfrag desc;

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

The 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 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 pfrag column 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>;

The 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 xml.t_keywords:

1. Determine the current size of the table.

SELECT pg_size_pretty( pg_total_relation_size('xml.t_keywords') );

2. Defragment and reclaim all disk space.

VACUUM FULL xml.t_keywords;

3. Determine the new size of the table after vacuum.

SELECT pg_size_pretty( pg_total_relation_size('xml.t_keywords') );

Alternative Options

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:

pg_dump -Ualexandria -hOLD-IP [ other options ] | psql -Ualexandria -hNEW-IP postgres