postgresql11 space reuse under high delete/update rate

From: Aliza Abulafia <Aliza(dot)Abulafia(at)Amdocs(dot)com>
To: "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org>
Cc: "Eli Cohen (ELCOHEN)" <Eli(dot)Cohen(at)amdocs(dot)com>
Subject: postgresql11 space reuse under high delete/update rate
Date: 2019-03-04 10:53:51
Message-ID: AM6PR06MB4567867B0AF45B47BD2785E1E6710@AM6PR06MB4567.eurprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

thanks in advance, Aliza.
This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service <https://www.amdocs.com/about/email-terms-of-service>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Aliza Abulafia 2019-03-04 13:24:01 postgresql11.1 - stabilize partition pruning at execution time
Previous Message Ashok Kumar Tiwari 2019-03-04 09:14:59 Re: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.