Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL

["Md. Ezhar Ansari" <ezhar.ansari@xxxxxxxxxxx>]

Dear Ron Johnson,

Thanks for your quick response and recommendations! I'll adjust auto-analyze thresholds and buffer values based on your insights. Your specific settings are particularly helpful. 

It seems there aren't specific automatic tuning features or extensions in PostgreSQL.

If you have any additional tips, I'd appreciate your guidance.

Best,

Ezhar

Get Outlook for Android

---

From: Ron Johnson <ronljohnsonjr@xxxxxxxxx>
Sent: Saturday, December 9, 2023 9:27:33 PM
To: Md. Ezhar Ansari <ezhar.ansari@xxxxxxxxxxx>
Cc: Pgsql-admin <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL

 

You don't often get email from ronljohnsonjr@xxxxxxxxx. Learn why this is important

On Sat, Dec 9, 2023 at 3:17 AM Md. Ezhar Ansari <ezhar.ansari@xxxxxxxxxxx> wrote:

Dear Pgsql-admin,

I hope this email finds you well. My name is Md Ezhar Ansari, and I am currently exploring options to optimize the performance of our PostgreSQL database. In my research, I came across the automatic tuning features available in SQL Server, and I was wondering if PostgreSQL has a similar capability or if there are any recommended extensions for automatic tuning.

I am particularly interested in functionalities that can automatically analyze and adjust configuration parameters, query plans, or other aspects to enhance the overall performance of our PostgreSQL database. If there are built-in features or third-party extensions that offer such capabilities, I would appreciate any information or guidance you could provide.

Additionally, if there are any best practices or recommended approaches for performance tuning in PostgreSQL, I would be grateful for your insights.

PG has auto-analyze, but its default thresholds are pretty archaic, seeing as how PG might still run on small hardware.

PG does not cache query plans, so there's no need to drop out-of-date query plans.

Adjusting the auto-analyze thresholds, and buffer values, will get you where you need.

Here's what I set them at:

shared_buffers = $SHB  # I set this at 25% of RAM

work_mem = 300MB
maintenance_work_mem = $MWM  # I set this at 10% of RAM
effective_cache_size = $ECS  # Should be most of RAM on a dedicated DB server (leave room for the OS!)

autovacuum = on
autovacuum_vacuum_threshold =  250  
autovacuum_vacuum_cost_delay = 4ms
autovacuum_vacuum_scale_factor = 0.03 # default 10% is too low for big tables
autovacuum_max_workers = 6
autovacuum_analyze_threshold = 250
autovacuum_analyze_scale_factor = 0.03