Re: postgresql11 space reuse under high delete/update rate

On 3/4/19 4:53 AM, Aliza Abulafia wrote:

Hi

 

we are evaluating postgresql 11.1 for our productions.

Having a system with 4251 updates per second, ~1000 delete per second and   ~3221 inserts per second and 1billion transaction per day.

we face a challenge where PostgreSQL does not reuse its (delete/update) space , and tables constantly increase size.

we configured aggressive Autovacuum settings to avoid the wraparound situation. also tried adding periodic execution of vacuum analyze and vaccum –

and still there is no space reuse. (only vacuum full or pg_repack release space to operating system – but this is not a reuse)

 

following are our vacuum settings :

autovacuum                          | on

vacuum_cost_limit                   | 6000

autovacuum_analyze_threshold        | 50

autovacuum_vacuum_threshold         | 50

autovacuum_vacuum_cost_delay        | 5

autovacuum_max_workers              | 32

autovacuum_freeze_max_age           | 2000000

autovacuum_multixact_freeze_max_age | 2000000

vacuum_freeze_table_age             | 20000

vacuum_multixact_freeze_table_age   | 20000

vacuum_cost_page_dirty              | 20

vacuum_freeze_min_age               | 10000

vacuum_multixact_freeze_min_age     | 10000

log_autovacuum_min_duration         | 1000

autovacuum_naptime                  | 10

autovacuum_analyze_scale_factor     | 0

autovacuum_vacuum_scale_factor      | 0

vacuum_cleanup_index_scale_factor   | 0

vacuum_cost_delay                   | 0

vacuum_defer_cleanup_age            | 0

autovacuum_vacuum_cost_limit        | -1

autovacuum_work_mem                 | -1

How frequently did you manually vacuum?

For example, generate a list of tables with a "sufficient" number of dead tuples, and then manually vacuum them in parallel:
TABLES=`mktemp`
psql $DB -c "SELECT '-t', schemaname||'.'||relname
             FROM pg_stat_all_tables
             WHERE n_dead_tuples > 500 -- or whatever number you think best
             ORDER BY 2;" > $TABLES
vacuumdb --jobs=6 --dbname=$DB `cat $TABLES`
psql -c "CHECKPOINT;"