Which PostgreSQL configuration settings does OtterTune optimize?

OtterTune automatic configuration tuning helps Amazon RDS PostgreSQL and Amazon Aurora PostgreSQL database users get better database performance at a lower cost, and with less DBA effort:

OtterTune works by analyzing a PostgreSQL database’s workload, and then using machine learning algorithms to optimize each of 100+ PostgreSQL configuration settings to support the database’s specific workload. There are 3 questions people commonly ask about OtterTune:

1. Does OtterTune need to access my PostgreSQL data or queries? No (“How OtterTune Securely Collects Metrics“).

2. Can I control how OtterTune makes changes to database settings? Yes (“Human-in-the-Loop Control Over Auto-tuning“).

3. What PostgreSQL configuration settings does OtterTune optimize? Read on to learn more about what knobs OtterTune optimizes.

Which PostgreSQL configuration settings does OtterTune auto-tune?

OtterTune analyzes and auto-tunes over 100 different PostgreSQL knobs. The exact list of knobs depends on two things:

The edition of OtterTune you run:

• OtterTune Starter edition – free version, tunes a core set of 20 knobs

• OtterTune Standard and Enterprise edition – paid versions, which tune 100+ knobs

The version of PostgreSQL you run

• As new, performance-related knobs are added to PostgreSQL, OtterTune adds support for them.

• Amazon Aurora PostgreSQL implements a different storage layer than Amazon RDS and thus has fewer adjustable knobs. Therefore, Aurora users should note that OtterTune optimizes a slightly smaller subset of the knobs it tunes for Amazon RDS PostgreSQL.

Below, we list the Amazon RDS PostgreSQL knobs that OtterTune automatically optimizes.

“Core” PostgreSQL knobs tuned by the free OtterTune starter edition

The free OtterTune Starter Edition tunes the following knobs for Amazon RDS PostgreSQL versions 9.6, 10, 11, 12, 13, and 14:

• autovacuum_vacuum_cost_delay

• autovacuum_vacuum_cost_limit

• autovacuum_vacuum_scale_factor

• autovacuum_vacuum_threshold

• bgwriter_delay

• bgwriter_lru_maxpages

• bgwriter_lru_multiplier

• checkpoint_completion_target

• checkpoint_timeout

• default_statistics_target

• effective_cache_size

• effective_io_concurrency

• maintenance_work_mem

• max_wal_size

• max_worker_processes

• random_page_cost

• shared_buffers

• temp_buffers

• wal_buffers

• wal_writer_delay

• work_mem

100+ PostgreSQL knobs tuned by OtterTune standard and enterprise editions

The paid OtterTune Standard and Enterprise Editions tune the same knobs as OtterTune Starter, above, plus these additional 90+ knobs, depending on which version of PostgreSQL you run:

Knobs tuned in PostgreSQL 9.6 and higher (except where noted)

• autovacuum_analyze_scale_factor

• autovacuum_analyze_threshold

• autovacuum_freeze_max_age

• autovacuum_max_workers

• autovacuum_multixact_freeze_max_age

• autovacuum_naptime

• autovacuum_work_mem

• backend_flush_after

• bgwriter_flush_after

• checkpoint_flush_after

• commit_delay

• commit_siblings

• cpu_index_tuple_cost

• cpu_operator_cost

• cpu_tuple_cost

• cursor_tuple_fraction

• deadlock_timeout

• from_collapse_limit

• geqo_effort

• geqo_generations

• geqo_pool_size

• geqo_selection_bias

• geqo_threshold

• gin_fuzzy_search_limit

• gin_pending_list_limit

• join_collapse_limit

• max_locks_per_transaction

• max_parallel_workers_per_gather

• max_pred_locks_per_transaction

• max_standby_archive_delay

• max_standby_streaming_delay

• max_wal_senders

• min_parallel_relation_size (version 9.6 only)

• min_wal_size

• parallel_setup_cost

• parallel_tuple_cost

• replacement_sort_tuples (versions 9.6 and 10 only)

• seq_page_cost

• sql_inheritance (version 9.6 only)

• temp_file_limit

• vacuum_cost_delay

• vacuum_cost_limit

• vacuum_cost_page_dirty

• vacuum_cost_page_hit

• vacuum_cost_page_miss

• vacuum_defer_cleanup_age

• vacuum_freeze_min_age

• vacuum_freeze_table_age

• vacuum_multixact_freeze_min_age

• vacuum_multixact_freeze_table_age

• wal_compression

• wal_keep_segments (versions 9.6, 10, 11, and 12 only)

• wal_receiver_timeout

• wal_sender_timeout

• wal_sync_method

• wal_writer_flush_after

Knobs tuned in PostgreSQL 10 and higher (except where noted)

In PostgreSQL 10, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• max_logical_replication_workers

• max_parallel_workers

• max_pred_locks_per_relation

• max_pred_locks_per_transaction

• max_sync_workers_per_subscription

• min_parallel_index_scan_size

• min_parallel_table_scan_size

Knobs tuned in PostgreSQL 11 and higher

In PostgreSQL 11, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• jit_above_cost

• jit_inline_above_cost

• jit_optimize_above_cost

• parallel_leader_participation

• vacuum_cleanup_index_scale_factor

PostgreSQL 12

There weren’t any new knobs introduced in PostgreSQL 12 worth auto-tuning.

Knobs tuned in PostgreSQL 13 and higher

In PostgreSQL 13, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• autovacuum_vacuum_insert_scale_factor

• autovacuum_vacuum_insert_threshold

• hash_mem_multiplier

• logical_decoding_work_mem

• maintenance_io_concurrency

• max_slot_wal_keep_size

Knobs tuned in PostgreSQL 14

In PostgreSQL 14, OtterTune tunes all of the knobs listed above (except where noted), plus these knobs:

• client_connection_check_interval

• min_dynamic_shared_memory

• vacuum_failsafe_age

• vacuum_multixact_failsafe_age

Try it yourself

If you’d like to learn more about how OtterTune automates PostgreSQL database configuration tuning, watch this short video.