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;"

--
Angular momentum makes the world go 'round.