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 = 300MBmaintenance_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
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
