Thanks for the questions. Here are some details: 1) we use this query to get the bloat: https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql But in our load test, we got a empty database with 1 record that gets updated very frequently. Here we just meature the DB size to see how big the DB grows: SELECT pg_total_relation_size('my-table') / 1024/1014;
2) Dead tuples: select n_dead_tup,n_live_tup, n_tup_del, relname,* from pg_stat_all_tables where relname= (select REPLACE((SELECT cast (reltoastrelid::regclass as text) FROM pg_class WHERE relkind = 'r' AND reltoastrelid <> 0 and relname = 'my-table'),'pg_toast.','')); We are only updating the blob so we are mostly interested in the toast 3) In our load test, High Load means constantly updating a single record with a predefined payload (i.e. random bytearray of x MB) for x minutes. We update up to 60MB per second 4) Postgres Version: 14.12-2 5) We are using default autovacuum-settings Best regards, Manuel -----Original Message----- From: Adrian Klaver <adrian.kla...@aklaver.com> Sent: 04 July 2024 17:43 To: Shenavai, Manuel <manuel.shena...@sap.com>; pgsql-general <pgsql-gene...@postgresql.org> Subject: Re: Configure autovacuum On 7/4/24 08:16, Shenavai, Manuel wrote: > We see that our DB keeps increasing under high load (many updates). We see > that our DB has a size of 200GB and we got 199GB bloat, 0 dead tuple. And > when the DB is put on high load (many updates), we still see that the DB size > grows. We try to find parameters to avoid DB growth. Show your work: 1) How did you determine the bloat number? 2) How did you determine there are 0 dead tuples? 3) Define high load. 4) Postgres version? 5) What are your autovacuum settings? > > I think we need to tweak the autovacuum settings and maybe limit the volume > of data that can be written to the DB. That will need to happen on client end. > > Is there any setting in postgres that would allow to write only certain > volume? For example, limit the amount of data that can be written to a table > to 100MB/minute. > > Best regards, > Manuel > > -----Original Message----- > From: Adrian Klaver <adrian.kla...@aklaver.com> > Sent: 14 June 2024 16:32 > To: Shenavai, Manuel <manuel.shena...@sap.com>; pgsql-general > <pgsql-gene...@postgresql.org> > Subject: Re: Configure autovacuum > > On 6/13/24 23:20, Shenavai, Manuel wrote: >> Hi everyone, >> >> I would like to configure the autovacuum in a way that it runs very >> frequently (i.e. after each update-statement). I tried the following > > Why? > > What is the problem you are trying to solve? > >> settings on my table: >> >> alter table mytable set (autovacuum_vacuum_scale_factor = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_delay = 0.0); >> >> alter table mytable set (autovacuum_vacuum_cost_limit = 10000); >> >> alter table mytable set (autovacuum_vacuum_threshold = 1); >> >> I do a lot of updates on a single tuple and I would expect that the >> autovacuum would start basically after each update (due to >> autovacuum_vacuum_threshold=1). But the autovacuum is not running. >> >> Is it possible to configure postgres to autovacuum very aggressively >> (i.e. after each update-statement)? >> >> Thanks in advance & >> >> Best regards, >> >> Manuel >> > -- Adrian Klaver adrian.kla...@aklaver.com
