vacuum and autovacuum - is it good to configure the threshold at TABLE LEVEL?

Hopefully, this[1] will help you.

1] https://www.percona.com/blog/2018/08/10/tuning-autovacuum-in-postgresql-and-autovacuum-internals/

regards,
Amul

Thanks, amul. I have already gone through this. What I would like to
understand is the performance impact on autovacuum launcher and worker
process when autovacuum is running from configurations done by
*ALTER TABLE autvac_test SET (autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 100);*
at table level.

-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Wed, Nov 28, 2018 at 9:11 AM rajan <vgmonnet@gmail.com> wrote:

Thanks, amul. I have already gone through this. What I would like to
understand is the performance impact on autovacuum launcher and worker
process when autovacuum is running from configurations done by
*ALTER TABLE autvac_test SET (autovacuum_vacuum_scale_factor = 0,
autovacuum_vacuum_threshold = 100);*
at table level.

An answer could be yes or no, something work for me that not necessarily work
for you.

The aforesaid configuration will trigger vacuum at every 150 row update/delete.
It depends on your server load, how frequent 150 row count reaches. Also,
triggering vacuum too frequently is also not that much beneficial, IMO.

Regards,
Amul

thanks for the reply, amul.

-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Hello Amul,

I have a doubt. Please find below the details,

1. autovacuum_vacuum_threshold and autovacuum_analyse_threshold are set at
300
2. autovacuum_vacuum_scale_factor and autovacuum_analyse_scale_face are set
at 300
3. there is table which has only 6 records
4. and the number of updates happened in the table is 944
5. now when I fetch data from pg_stat_user_table, i find that only
last_autoanalyse column is updated with datetime and last_autovacuum column
is not having a value

does this mean only autoanalyse was executed for this table? But when I have
both vacuum_threshold and analyse_threshold set at 300, how can only analyse
run and vacuum does not?

This may be a stupid question. But any answer can help me understand.

-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

Apologize for delayed response.

On Mon, Dec 3, 2018 at 12:30 PM rajan <vgmonnet@gmail.com> wrote:

Hello Amul,

I have a doubt. Please find below the details,

1. autovacuum_vacuum_threshold and autovacuum_analyse_threshold are set at
300
2. autovacuum_vacuum_scale_factor and autovacuum_analyse_scale_face are set
at 300
3. there is table which has only 6 records
4. and the number of updates happened in the table is 944
5. now when I fetch data from pg_stat_user_table, i find that only
last_autoanalyse column is updated with datetime and last_autovacuum column
is not having a value

does this mean only autoanalyse was executed for this table? But when I have
both vacuum_threshold and analyse_threshold set at 300, how can only analyse
run and vacuum does not?

That's right auto-vacuumed might not have triggered -- I think, that because of
autovacuum_vacuum_scale_factor setting and most of the time update does not
increase the table size.

Following question might not directly be related to your query but
just curious to know:
Does your table have indexes and by any chance your experiment
updating non-index column?

Regards,
Amul

Thanks for the reply Amul.

Btw, No my table does not has any index.

-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Mon, Dec 10, 2018 at 2:51 PM rajan <vgmonnet@gmail.com> wrote:

Thanks for the reply Amul.

Btw, No my table does not has any index.

Thanks for the information.

Regards,
Amul