Re: Autovacuum------Doubts

Hi Laurenz,

Thanks for your update. Kindly check the below update.

I have changed the below 3 parameters.

DatabaseName=# show autovacuum_vacuum_threshold;
autovacuum_vacuum_threshold
-----------------------------
20
(1 row)

DatabaseName=# show autovacuum_analyze_threshold;
autovacuum_analyze_threshold
------------------------------
20
(1 row)

DatabaseName=# show autovacuum_vacuum_scale_factor;
autovacuum_vacuum_scale_factor
--------------------------------
0.1
(1 row)

DatabaseName=#

As per my understanding.

Table total count=100

If I deleted 20 records. Remaining 80 rows but auto vacuum is not
happening. While I deleted 35 rows then only Autovacuuum are happen.

20% tuples value came. As I defined the current setting in the parameter
file. Autovacuum should start automatically. Am I correct?

May I know how you are telling the below method calculation. Can you
explain please.

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of
table rows

I checked the link below.

https://www.postgresql.org/docs/14/runtime-config-autovacuum.html

On Mon, Apr 8, 2024 at 8:08 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Mon, 2024-04-08 at 16:25 +0530, jaya kumar wrote:
> > I am not clear.
>
> I can confirm that.
>
> > Could you explain again. If I delete 20 records out of 100. It means
> Autocacumm should run
> > but not run automatically. As you provide a solution, the method should
> be deleted but does not happen.
>
> If you delete 20 rows, autovacuum should not run.
>
> > Delete record=30
> >
> > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number
> of table rows ?
> > o/p: 20+0.1*100=2010?
>
> I don't know what an o/p is, but that calculation is wrong.
>
> > DatabaseName=# show autovacuum_vacuum_threshold;
> > autovacuum_vacuum_threshold
> > -----------------------------
> > 20
> > (1 row)
> >
> > DatabaseName=# show autovacuum_vacuum_scale_factor;
> > autovacuum_vacuum_scale_factor
> > --------------------------------
> > 0.1
> > (1 row)
> >
> > DatabaseName=# select count(*) from emp9;
> > count
> > -------
> > 100
> > (1 row)
> >
> > DatabaseName=# delete from emp9 where eno between 1 and 30;
>
> Try deleting 31 or 32 rows.
> Statistics are not necessarily 100% accurate.
>
> > On Mon, Apr 8, 2024 at 3:15 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
> > > On Mon, 2024-04-08 at 14:57 +0530, jaya kumar wrote:
> > > > autovacuum_analyze_threshold and autovacuum_vacuum_threshold both
> parameter
> > > > values set from 50 to 20.
> > > >
> > > > Created one table with 100 records. First, we deleted 21 records.
> after an
> > > > autovacuum does not happen automatically. After again I deleted
> another 20.
> > > > Out of 100 we delectated 40 records then the autovacuum happened
> automatically.
> > > > As per the configuration Autocacuum will automatically delete 21
> records
> > > > in the table but my case does not happen.
> > > >
> > > > Can someone check and explain this.
> > >
> > > The threshold is
> > >
> > > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor *
> number of table rows
> > >
> > > So since you have set autovacuum_vacuum_scale_factor to 0.1 and there
> are 100 rows,
> > > you need to delete 20 + 100 * 0.1 = 30 rows before autovacuum kicks in.
>
> Please bottom-post.
>
> Yours,
> Laurenz Albe
>

--
Thanks & Regards,
Jayakumar.S
+91-9840864439.