Vaccum and Autovaccum

anshul modi 21 Reputation points
2022-05-04T19:44:44.467+00:00

We use PostgreSQL on Azure.
So ,We have big tables around 100GB in size each ,Autovaccum never gets completed on those tables. Since I believe Update and delete operations take over the priority and never let autovaccum to complete.

****So I want to create a job to run manual vacuum and analyze every night on those tables to improve query performances. IN Linux we can create those jobs using crontab scripts.
However we use Azure on PostgreSQL.
Do we have any such inbuilt setting or feature in Azure ,which allows to schedule vacuum and analyze everyday like cronjobs.
Pls advise.****

Here are the current settings in our DB.

"autovacuum" "on"
"autovacuum_analyze_scale_factor" "0.05"
"autovacuum_analyze_threshold" "50"
"autovacuum_freeze_max_age" "200000000"
"autovacuum_max_workers" "3"
"autovacuum_multixact_freeze_max_age" "400000000"
"autovacuum_naptime" "15"
"autovacuum_vacuum_cost_delay" "20"
"autovacuum_vacuum_cost_limit" "-1"
"autovacuum_vacuum_scale_factor" "0.05"
"autovacuum_vacuum_threshold" "50"
"autovacuum_work_mem" "-1"
"log_autovacuum_min_duration" "-1"

Azure Database for PostgreSQL
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 29,017 Reputation points Microsoft Employee
    2022-05-04T21:28:23.88+00:00

    Hi, @anshul modi Welcome to the Microsoft Q&A forum and thanks for reaching out.
    Is your Postgres single server or flexible server?
    For a single server, this is the document that you can follow.
    For a flexible server, there is an extension pg_cron so you can schedule a job using that extension.

    Please let me know if you need any additional information.

    Regards
    Geetha


0 additional answers

Sort by: Most helpful