question

anshulmodi-6401 avatar image
0 Votes"
anshulmodi-6401 asked hamandaxen-9336 edited

Vaccum and Autovaccum

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-postgresql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pls share the link how to file a support ticket for immediate assistance.

0 Votes 0 ·

@anshulmodi-6401 Do you have a support plan?

0 Votes 0 ·

Gratitude for speedy reaction ,So this report simply tells wash a car without a hose about elements of autovaccum.
We have single server and not adaptable waiter.


0 Votes 0 ·

1 Answer

GeethaThatipatri-MSFT avatar image
0 Votes"
GeethaThatipatri-MSFT answered

Hi, @anshulmodi-6401 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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello Geetha ,

Thanks for quick response ,So this document just tells about features of autovaccum.
We have single server and not flexible server.
My question was particularly .how can we do schedule it manually using any inbuilt azure feature for every night for single server db, because we dont want auto one to run in office hours.
Somehow autovaccum on our 100 gb tables is not getting completed and jobs get killed everyday self only.

The way you said pg_cron in flexible server ,do we have any such thing for single server.

Thanks
Anshul

0 Votes 0 ·

@anshulmodi-6401on a single server, there is a challenge I believe using Azure Functions with a Timmer trigger would be an option for a single server, however, I would recommend you to please file a support ticket for immediate assistance. And in case you don't have a support plan, please let us know here.

Regards
Geetha

0 Votes 0 ·