Re: Inquiry Regarding Automatic Tuning Features in PostgreSQL

On Sat, Dec 9, 2023 at 3:17 AM Md. Ezhar Ansari <ezhar(dot)ansari(at)alumnux(dot)com>
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