Aurora PostgreSQL) RPMインストールとのパフォーマンス系パラメータのデフォルト値比較

TL;DR
前提
一覧の見方
一覧(カテゴリごと)
値の異なるもののみ抜粋した一覧
全パフォーマンス系パラメータ比較一覧
ついでにAuroraで変更不可能なパフォーマンス系パラメータ一覧
備考
1. 一覧の作成方法

TL;DR

PostgreSQLのパフォーマンス系パラメータについて、EC2にPostgreSQLをRPMインストールしたときのデフォルト値と、Aurora PostgreSQLの特定インスタンスタイプでのデフォルト値を比較
パフォーマンス系パラメータ203個のうち、RPMインストールとAuroraで差異のあるパラメータは37個あった
- ただし特にAuroraで、特にメモリ関連のパラメータなどは、インスタンスタイプに応じて値が動的に変更されるので、あくまで参考まで
- ちなみにパフォーマンス系パラメータ203個のうち、79個はAuroraで変更できないパラメータ。

前提

PostgreSQL15.3(RPMインストール/Aurora PostgreSQLとも)
比較対象のパラメータは、以下記事のパラメータ(203個)

一覧の見方

4列あって、左から
- パラメータ名、RPMインストール、Aurora、Auroraで変更可能
パラメータ名昇順
各列の説明
- RPMインストール列
  - EC2+RHEL(x64)+PostgreSQL15.3(RPMインストール)
  - パラメータはインストールデフォルトの値
- Aurora列
  - Aurora PostgreSQL15.3+db.r6i.large
  - パラメータは起動後デフォルトの値
    - メモリ関連のパラメータなどは特に、インスタンスタイプから動的に設定されてるはずなので参考まで
  - 青い下線を引いた値は、RPMインストールと異なる値
- Auroraで変更可能列
  - AuroraDBクラスターまたはAuroraDBインスタンスで変更可能かどうか
  - クラスターとインスタンスで比較してみたら、インスタンスで変更可能なものはすべてクラスタで変更可能で、逆にクラスタで変更可能だがインスタンスで変更できないものがあったので、AuroraDBクラスターでの変更可能かどうかの値を記載(これで、クラスタで変更可能またはクラスタとインスタンスの両方で変更可能、の一覧になる)
  - 空白の値について
    - PostgreSQL内部の値ではないので、AWSCLIで取得するのだが、PostgreSQLのパラメータで値が取れないものがある(AWSCLIでdescribe結果のJSONの中にパラメータが存在しない)。仕方ないのでそういうものは空白

一覧(カテゴリごと)

Autovacuum

name	RPMインストール	Aurora	Auroraで変更可能
autovacuum	on	on	true
autovacuum_analyze_scale_factor	0.1	0.05	true
autovacuum_analyze_threshold	50	50	true
autovacuum_freeze_max_age	200000000	200000000	true
autovacuum_max_workers	3	3	true
autovacuum_multixact_freeze_max_age	400000000	400000000	true
autovacuum_naptime	1min	5s	true
autovacuum_vacuum_cost_delay	2ms	5ms	true
autovacuum_vacuum_cost_limit	-1	200	true
autovacuum_vacuum_insert_scale_factor	0.2	0.2
autovacuum_vacuum_insert_threshold	1000	1000
autovacuum_vacuum_scale_factor	0.2	0.1	true
autovacuum_vacuum_threshold	50	50	true

Client Connection Defaults / Other Defaults

gin_fuzzy_search_limit

true

Client Connection Defaults / Shared Library Preloading

jit_provider	llvmjit	llvmjit
local_preload_libraries
session_preload_libraries
shared_preload_libraries	環境依存	環境依存	true

Client Connection Defaults / Statement Behavior

default_table_access_method	heap	heap
default_toast_compression	pglz	pglz
default_transaction_deferrable	off	off	true
default_transaction_isolation	read committed	read committed	true
default_transaction_read_only	off	off	true
gin_pending_list_limit	4MB	4MB	true
idle_in_transaction_session_timeout	0	1d	true
idle_session_timeout	0	0
lock_timeout	0	0
row_security	on	on	true
statement_timeout	0	0	true
transaction_deferrable	off	off
transaction_isolation	read committed	read committed
transaction_read_only	off	off
vacuum_failsafe_age	1600000000	1200000000
vacuum_freeze_min_age	50000000	50000000	true
vacuum_freeze_table_age	150000000	150000000	true
vacuum_multixact_failsafe_age	1600000000	1200000000
vacuum_multixact_freeze_min_age	5000000	5000000	true
vacuum_multixact_freeze_table_age	150000000	150000000	true

Connections and Authentication / Authentication

authentication_timeout

1min

true

Connections and Authentication / Connection Settings

client_connection_check_interval	0	0
max_connections	100	1707	true
tcp_keepalives_count	9	2	true
tcp_keepalives_idle	7200	300	true
tcp_keepalives_interval	75	30	true
tcp_user_timeout	0	0

Connections and Authentication / SSL

ssl

off

true

Developer Options

debug_discard_caches	0	0
force_parallel_mode	off	off	true
ignore_system_indexes	off	off
jit_tuple_deforming	on	on
trace_sort	off	off
wal_consistency_checking

Lock Management

deadlock_timeout	1s	1s	true
max_locks_per_transaction	64	64	true
max_pred_locks_per_page	2	2	true
max_pred_locks_per_relation	-2	-2	true
max_pred_locks_per_transaction	64	64	true
block_size	8192	8192

Preset Options

block_size	8192	8192
data_checksums	off	off
debug_assertions	off	off
segment_size	1GB	1GB
shared_memory_size	148MB	10516MB
shared_memory_size_in_huge_pages	74	5259
wal_block_size	8192	8192
wal_segment_size	16MB	64MB

Query Tuning / Genetic Query Optimizer

geqo	on	on	true
geqo_effort	5	5	true
geqo_generations	0	0	true
geqo_pool_size	0	0	true
geqo_seed	0	0	true
geqo_selection_bias	2	2	true
geqo_threshold	12	12	true

Query Tuning / Other Planner Options

constraint_exclusion	partition	partition	true
cursor_tuple_fraction	0.1	0.1	true
default_statistics_target	100	100	true
jit	on	off
plan_cache_mode	auto	auto
recursive_worktable_factor	10	10

Query Tuning / Planner Cost Constants

cpu_index_tuple_cost	0.005	0.005	true
cpu_operator_cost	0.0025	0.0025	true
cpu_tuple_cost	0.01	0.01	true
effective_cache_size	4GB	10415664kB	true
jit_above_cost	100000	100000
jit_inline_above_cost	500000	500000
jit_optimize_above_cost	500000	500000
min_parallel_index_scan_size	512kB	512kB	true
min_parallel_table_scan_size	8MB	8MB	true
parallel_setup_cost	1000	1000	true
parallel_tuple_cost	0.1	0.1	true
random_page_cost	4	4	true
seq_page_cost	1	1	true

Query Tuning / Planner Method Configuration

enable_async_append	on	on
enable_bitmapscan	on	on	true
enable_gathermerge	on	on	true
enable_hashagg	on	on	true
enable_hashjoin	on	on	true
enable_incremental_sort	on	on
enable_indexonlyscan	on	on	true
enable_indexscan	on	on	true
enable_material	on	on	true
enable_memoize	on	on
enable_mergejoin	on	on	true
enable_nestloop	on	on	true
enable_parallel_append	on	on	true
enable_parallel_hash	on	on	true
enable_partition_pruning	on	on	true
enable_partitionwise_aggregate	off	off	true
enable_partitionwise_join	off	off	true
enable_seqscan	on	on	true
enable_sort	on	on	true
enable_tidscan	on	on	true

Reporting and Logging / Process Title

update_process_title

true

Reporting and Logging / What to Log

debug_pretty_print	on	on	true
debug_print_parse	off	off	true
debug_print_plan	off	off	true
debug_print_rewritten	off	off	true
log_autovacuum_min_duration	0	10s	true
log_checkpoints	on	on
log_connections	off	off	true
log_disconnections	off	off	true
log_duration	off	off	true
log_error_verbosity	default	default	true
log_lock_waits	off	off	true
log_parameter_max_length	-1	-1
log_parameter_max_length_on_error	0	0
log_statement	none	none	true
log_temp_files	-1	-1	true

Reporting and Logging / When to Log

log_min_duration_sample	-1	-1
log_min_duration_statement	-1	-1	true
log_min_error_statement	error	error	true
log_min_messages	warning	warning	true
log_startup_progress_interval	10s	10s
log_statement_sample_rate	1	1
log_transaction_sample_rate	0	0

Resource Usage / Asynchronous Behavior

backend_flush_after	0	0	true
effective_io_concurrency	1	256	false
maintenance_io_concurrency	10	1
max_parallel_maintenance_workers	2	2	true
max_parallel_workers	8	8	true
max_parallel_workers_per_gather	2	2	true
max_worker_processes	8	8	true
old_snapshot_threshold	-1	-1	true
parallel_leader_participation	on	on	true

Resource Usage / Background Writer

bgwriter_delay	200ms	200ms
bgwriter_flush_after	512kB	512kB	true
bgwriter_lru_maxpages	100	100
bgwriter_lru_multiplier	2	2

Resource Usage / Cost-Based Vacuum Delay

vacuum_cost_delay	0	0	true
vacuum_cost_limit	200	200	true
vacuum_cost_page_dirty	20	20
vacuum_cost_page_hit	1	1	true
vacuum_cost_page_miss	2	0	true

Resource Usage / Disk

temp_file_limit

-1

true

Resource Usage / Kernel Resources

max_files_per_process

1000

true

Resource Usage / Memory

autovacuum_work_mem	-1	496670kB	true
dynamic_shared_memory_type	posix	posix
hash_mem_multiplier	2	2
huge_pages	try	on	false
huge_page_size	0	0
logical_decoding_work_mem	64MB	64MB	true
maintenance_work_mem	64MB	254MB	true
max_prepared_transactions	0	0	true
max_stack_depth	2MB	6MB	true
min_dynamic_shared_memory	0	0
shared_buffers	128MB	1301958	true
shared_memory_type	mmap	mmap
temp_buffers	8MB	8MB	true
work_mem	4MB	4MB	true

Statistics / Cumulative Query and Index Statistics

stats_fetch_consistency	cache	cache
track_activities	on	on	true
track_activity_query_size	1kB	4kB	true
track_counts	on	on	true
track_functions	all	pl	true
track_io_timing	off	on	true
track_wal_io_timing	off	off

Statistics / Monitoring

compute_query_id	auto	auto
log_executor_stats	off	off	true
log_parser_stats	off	off	true
log_planner_stats	off	off	true
log_statement_stats	off	off	true

Version and Platform Compatibility / Previous PostgreSQL Versions

synchronize_seqscans

true

Write-Ahead Log / Archiving

archive_timeout

5min

false

Write-Ahead Log / Checkpoints

checkpoint_completion_target	0.9	0.9
checkpoint_flush_after	256kB	256kB	true
checkpoint_timeout	5min	1min	false
checkpoint_warning	30s	30s
max_wal_size	1GB	1GB
min_wal_size	80MB	512MB	false

Write-Ahead Log / Recovery

recovery_prefetch	try	off
wal_decode_buffer_size	512kB	512kB

Write-Ahead Log / Settings

commit_delay	0	0
commit_siblings	5	5
fsync	on	on
full_page_writes	on	off
synchronous_commit	on	on	true
wal_buffers	4MB	-1	true
wal_compression	off	off
wal_level	replica	replica
wal_log_hints	off	off
wal_skip_threshold	2MB	2MB
wal_sync_method	fdatasync	fdatasync
wal_writer_delay	200ms	200ms
wal_writer_flush_after	1MB	1MB

値の異なるもののみ抜粋した一覧

一番左にカテゴリ列を追加
上記一覧でAurora列に青い下線が引いてある値を網羅(37個)

クリックして表示

category	name	RPMインストール	Aurora	Auroraで変更可能
Autovacuum	autovacuum_analyze_scale_factor	0.1	0.05	true
Autovacuum	autovacuum_naptime	1min	5s	true
Autovacuum	autovacuum_vacuum_cost_delay	2ms	5ms	true
Autovacuum	autovacuum_vacuum_cost_limit	-1	200	true
Autovacuum	autovacuum_vacuum_scale_factor	0.2	0.1	true
Client Connection Defaults / Statement Behavior	idle_in_transaction_session_timeout	0	1d	true
Client Connection Defaults / Statement Behavior	vacuum_failsafe_age	1600000000	1200000000
Client Connection Defaults / Statement Behavior	vacuum_multixact_failsafe_age	1600000000	1200000000
Connections and Authentication / Connection Settings	max_connections	100	1707	true
Connections and Authentication / Connection Settings	tcp_keepalives_count	9	2	true
Connections and Authentication / Connection Settings	tcp_keepalives_idle	7200	300	true
Connections and Authentication / Connection Settings	tcp_keepalives_interval	75	30	true
Connections and Authentication / SSL	ssl	off	on	true
Preset Options	shared_memory_size	148MB	10516MB
Preset Options	shared_memory_size_in_huge_pages	74	5259
Preset Options	wal_segment_size	16MB	64MB
Query Tuning / Other Planner Options	jit	on	off
Query Tuning / Planner Cost Constants	effective_cache_size	4GB	10415664kB	true
Reporting and Logging / What to Log	log_autovacuum_min_duration	0	10s	true
Resource Usage / Asynchronous Behavior	effective_io_concurrency	1	256	false
Resource Usage / Asynchronous Behavior	maintenance_io_concurrency	10	1
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_page_miss	2	0	true
Resource Usage / Memory	autovacuum_work_mem	-1	496670kB	true
Resource Usage / Memory	huge_pages	try	on	false
Resource Usage / Memory	maintenance_work_mem	64MB	254MB	true
Resource Usage / Memory	max_stack_depth	2MB	6MB	true
Resource Usage / Memory	shared_buffers	128MB	1301958	true
Statistics / Cumulative Query and Index Statistics	track_activity_query_size	1kB	4kB	true
Statistics / Cumulative Query and Index Statistics	track_functions	all	pl	true
Statistics / Cumulative Query and Index Statistics	track_io_timing	off	on	true
Write-Ahead Log / Archiving	archive_timeout	0	5min	false
Write-Ahead Log / Checkpoints	checkpoint_timeout	5min	1min	false
Write-Ahead Log / Checkpoints	min_wal_size	80MB	512MB	false
Write-Ahead Log / Recovery	recovery_prefetch	try	off
Write-Ahead Log / Settings	full_page_writes	on	off
Write-Ahead Log / Settings	wal_buffers	4MB	-1	true

全パフォーマンス系パラメータ比較一覧

クリックして表示

category	name	RPMインストール	Aurora	Auroraで変更可能
Autovacuum	autovacuum	on	on	true
Autovacuum	autovacuum_analyze_scale_factor	0.1	0.05	true
Autovacuum	autovacuum_analyze_threshold	50	50	true
Autovacuum	autovacuum_freeze_max_age	200000000	200000000	true
Autovacuum	autovacuum_max_workers	3	3	true
Autovacuum	autovacuum_multixact_freeze_max_age	400000000	400000000	true
Autovacuum	autovacuum_naptime	1min	5s	true
Autovacuum	autovacuum_vacuum_cost_delay	2ms	5ms	true
Autovacuum	autovacuum_vacuum_cost_limit	-1	200	true
Autovacuum	autovacuum_vacuum_insert_scale_factor	0.2	0.2
Autovacuum	autovacuum_vacuum_insert_threshold	1000	1000
Autovacuum	autovacuum_vacuum_scale_factor	0.2	0.1	true
Autovacuum	autovacuum_vacuum_threshold	50	50	true
Client Connection Defaults / Other Defaults	gin_fuzzy_search_limit	0	0	true
Client Connection Defaults / Shared Library Preloading	jit_provider	llvmjit	llvmjit
Client Connection Defaults / Shared Library Preloading	local_preload_libraries
Client Connection Defaults / Shared Library Preloading	session_preload_libraries
Client Connection Defaults / Shared Library Preloading	shared_preload_libraries	環境依存	環境依存	true
Client Connection Defaults / Statement Behavior	default_table_access_method	heap	heap
Client Connection Defaults / Statement Behavior	default_toast_compression	pglz	pglz
Client Connection Defaults / Statement Behavior	default_transaction_deferrable	off	off	true
Client Connection Defaults / Statement Behavior	default_transaction_isolation	read committed	read committed	true
Client Connection Defaults / Statement Behavior	default_transaction_read_only	off	off	true
Client Connection Defaults / Statement Behavior	gin_pending_list_limit	4MB	4MB	true
Client Connection Defaults / Statement Behavior	idle_in_transaction_session_timeout	0	1d	true
Client Connection Defaults / Statement Behavior	idle_session_timeout	0	0
Client Connection Defaults / Statement Behavior	lock_timeout	0	0
Client Connection Defaults / Statement Behavior	row_security	on	on	true
Client Connection Defaults / Statement Behavior	statement_timeout	0	0	true
Client Connection Defaults / Statement Behavior	transaction_deferrable	off	off
Client Connection Defaults / Statement Behavior	transaction_isolation	read committed	read committed
Client Connection Defaults / Statement Behavior	transaction_read_only	off	off
Client Connection Defaults / Statement Behavior	vacuum_failsafe_age	1600000000	1200000000
Client Connection Defaults / Statement Behavior	vacuum_freeze_min_age	50000000	50000000	true
Client Connection Defaults / Statement Behavior	vacuum_freeze_table_age	150000000	150000000	true
Client Connection Defaults / Statement Behavior	vacuum_multixact_failsafe_age	1600000000	1200000000
Client Connection Defaults / Statement Behavior	vacuum_multixact_freeze_min_age	5000000	5000000	true
Client Connection Defaults / Statement Behavior	vacuum_multixact_freeze_table_age	150000000	150000000	true
Connections and Authentication / Authentication	authentication_timeout	1min	1min	true
Connections and Authentication / Connection Settings	client_connection_check_interval	0	0
Connections and Authentication / Connection Settings	max_connections	100	1707	true
Connections and Authentication / Connection Settings	tcp_keepalives_count	9	2	true
Connections and Authentication / Connection Settings	tcp_keepalives_idle	7200	300	true
Connections and Authentication / Connection Settings	tcp_keepalives_interval	75	30	true
Connections and Authentication / Connection Settings	tcp_user_timeout	0	0
Connections and Authentication / SSL	ssl	off	on	true
Developer Options	debug_discard_caches	0	0
Developer Options	force_parallel_mode	off	off	true
Developer Options	ignore_system_indexes	off	off
Developer Options	jit_tuple_deforming	on	on
Developer Options	trace_sort	off	off
Developer Options	wal_consistency_checking
Lock Management	deadlock_timeout	1s	1s	true
Lock Management	max_locks_per_transaction	64	64	true
Lock Management	max_pred_locks_per_page	2	2	true
Lock Management	max_pred_locks_per_relation	-2	-2	true
Lock Management	max_pred_locks_per_transaction	64	64	true
Preset Options	block_size	8192	8192
Preset Options	data_checksums	off	off
Preset Options	debug_assertions	off	off
Preset Options	segment_size	1GB	1GB
Preset Options	shared_memory_size	148MB	10516MB
Preset Options	shared_memory_size_in_huge_pages	74	5259
Preset Options	wal_block_size	8192	8192
Preset Options	wal_segment_size	16MB	64MB
Query Tuning / Genetic Query Optimizer	geqo	on	on	true
Query Tuning / Genetic Query Optimizer	geqo_effort	5	5	true
Query Tuning / Genetic Query Optimizer	geqo_generations	0	0	true
Query Tuning / Genetic Query Optimizer	geqo_pool_size	0	0	true
Query Tuning / Genetic Query Optimizer	geqo_seed	0	0	true
Query Tuning / Genetic Query Optimizer	geqo_selection_bias	2	2	true
Query Tuning / Genetic Query Optimizer	geqo_threshold	12	12	true
Query Tuning / Other Planner Options	constraint_exclusion	partition	partition	true
Query Tuning / Other Planner Options	cursor_tuple_fraction	0.1	0.1	true
Query Tuning / Other Planner Options	default_statistics_target	100	100	true
Query Tuning / Other Planner Options	jit	on	off
Query Tuning / Other Planner Options	plan_cache_mode	auto	auto
Query Tuning / Other Planner Options	recursive_worktable_factor	10	10
Query Tuning / Planner Cost Constants	cpu_index_tuple_cost	0.005	0.005	true
Query Tuning / Planner Cost Constants	cpu_operator_cost	0.0025	0.0025	true
Query Tuning / Planner Cost Constants	cpu_tuple_cost	0.01	0.01	true
Query Tuning / Planner Cost Constants	effective_cache_size	4GB	10415664kB	true
Query Tuning / Planner Cost Constants	jit_above_cost	100000	100000
Query Tuning / Planner Cost Constants	jit_inline_above_cost	500000	500000
Query Tuning / Planner Cost Constants	jit_optimize_above_cost	500000	500000
Query Tuning / Planner Cost Constants	min_parallel_index_scan_size	512kB	512kB	true
Query Tuning / Planner Cost Constants	min_parallel_table_scan_size	8MB	8MB	true
Query Tuning / Planner Cost Constants	parallel_setup_cost	1000	1000	true
Query Tuning / Planner Cost Constants	parallel_tuple_cost	0.1	0.1	true
Query Tuning / Planner Cost Constants	random_page_cost	4	4	true
Query Tuning / Planner Cost Constants	seq_page_cost	1	1	true
Query Tuning / Planner Method Configuration	enable_async_append	on	on
Query Tuning / Planner Method Configuration	enable_bitmapscan	on	on	true
Query Tuning / Planner Method Configuration	enable_gathermerge	on	on	true
Query Tuning / Planner Method Configuration	enable_hashagg	on	on	true
Query Tuning / Planner Method Configuration	enable_hashjoin	on	on	true
Query Tuning / Planner Method Configuration	enable_incremental_sort	on	on
Query Tuning / Planner Method Configuration	enable_indexonlyscan	on	on	true
Query Tuning / Planner Method Configuration	enable_indexscan	on	on	true
Query Tuning / Planner Method Configuration	enable_material	on	on	true
Query Tuning / Planner Method Configuration	enable_memoize	on	on
Query Tuning / Planner Method Configuration	enable_mergejoin	on	on	true
Query Tuning / Planner Method Configuration	enable_nestloop	on	on	true
Query Tuning / Planner Method Configuration	enable_parallel_append	on	on	true
Query Tuning / Planner Method Configuration	enable_parallel_hash	on	on	true
Query Tuning / Planner Method Configuration	enable_partition_pruning	on	on	true
Query Tuning / Planner Method Configuration	enable_partitionwise_aggregate	off	off	true
Query Tuning / Planner Method Configuration	enable_partitionwise_join	off	off	true
Query Tuning / Planner Method Configuration	enable_seqscan	on	on	true
Query Tuning / Planner Method Configuration	enable_sort	on	on	true
Query Tuning / Planner Method Configuration	enable_tidscan	on	on	true
Reporting and Logging / Process Title	update_process_title	on	on	true
Reporting and Logging / What to Log	debug_pretty_print	on	on	true
Reporting and Logging / What to Log	debug_print_parse	off	off	true
Reporting and Logging / What to Log	debug_print_plan	off	off	true
Reporting and Logging / What to Log	debug_print_rewritten	off	off	true
Reporting and Logging / What to Log	log_autovacuum_min_duration	0	10s	true
Reporting and Logging / What to Log	log_checkpoints	on	on
Reporting and Logging / What to Log	log_connections	off	off	true
Reporting and Logging / What to Log	log_disconnections	off	off	true
Reporting and Logging / What to Log	log_duration	off	off	true
Reporting and Logging / What to Log	log_error_verbosity	default	default	true
Reporting and Logging / What to Log	log_lock_waits	off	off	true
Reporting and Logging / What to Log	log_parameter_max_length	-1	-1
Reporting and Logging / What to Log	log_parameter_max_length_on_error	0	0
Reporting and Logging / What to Log	log_statement	none	none	true
Reporting and Logging / What to Log	log_temp_files	-1	-1	true
Reporting and Logging / When to Log	log_min_duration_sample	-1	-1
Reporting and Logging / When to Log	log_min_duration_statement	-1	-1	true
Reporting and Logging / When to Log	log_min_error_statement	error	error	true
Reporting and Logging / When to Log	log_min_messages	warning	warning	true
Reporting and Logging / When to Log	log_startup_progress_interval	10s	10s
Reporting and Logging / When to Log	log_statement_sample_rate	1	1
Reporting and Logging / When to Log	log_transaction_sample_rate	0	0
Resource Usage / Asynchronous Behavior	backend_flush_after	0	0	true
Resource Usage / Asynchronous Behavior	effective_io_concurrency	1	256	false
Resource Usage / Asynchronous Behavior	maintenance_io_concurrency	10	1
Resource Usage / Asynchronous Behavior	max_parallel_maintenance_workers	2	2	true
Resource Usage / Asynchronous Behavior	max_parallel_workers	8	8	true
Resource Usage / Asynchronous Behavior	max_parallel_workers_per_gather	2	2	true
Resource Usage / Asynchronous Behavior	max_worker_processes	8	8	true
Resource Usage / Asynchronous Behavior	old_snapshot_threshold	-1	-1	true
Resource Usage / Asynchronous Behavior	parallel_leader_participation	on	on	true
Resource Usage / Background Writer	bgwriter_delay	200ms	200ms
Resource Usage / Background Writer	bgwriter_flush_after	512kB	512kB	true
Resource Usage / Background Writer	bgwriter_lru_maxpages	100	100
Resource Usage / Background Writer	bgwriter_lru_multiplier	2	2
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_delay	0	0	true
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_limit	200	200	true
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_page_dirty	20	20
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_page_hit	1	1	true
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_page_miss	2	0	true
Resource Usage / Disk	temp_file_limit	-1	-1	true
Resource Usage / Kernel Resources	max_files_per_process	1000	1000	true
Resource Usage / Memory	autovacuum_work_mem	-1	496670kB	true
Resource Usage / Memory	dynamic_shared_memory_type	posix	posix
Resource Usage / Memory	hash_mem_multiplier	2	2
Resource Usage / Memory	huge_pages	try	on	false
Resource Usage / Memory	huge_page_size	0	0
Resource Usage / Memory	logical_decoding_work_mem	64MB	64MB	true
Resource Usage / Memory	maintenance_work_mem	64MB	254MB	true
Resource Usage / Memory	max_prepared_transactions	0	0	true
Resource Usage / Memory	max_stack_depth	2MB	6MB	true
Resource Usage / Memory	min_dynamic_shared_memory	0	0
Resource Usage / Memory	shared_buffers	128MB	1301958	true
Resource Usage / Memory	shared_memory_type	mmap	mmap
Resource Usage / Memory	temp_buffers	8MB	8MB	true
Resource Usage / Memory	work_mem	4MB	4MB	true
Statistics / Cumulative Query and Index Statistics	stats_fetch_consistency	cache	cache
Statistics / Cumulative Query and Index Statistics	track_activities	on	on	true
Statistics / Cumulative Query and Index Statistics	track_activity_query_size	1kB	4kB	true
Statistics / Cumulative Query and Index Statistics	track_counts	on	on	true
Statistics / Cumulative Query and Index Statistics	track_functions	all	pl	true
Statistics / Cumulative Query and Index Statistics	track_io_timing	off	on	true
Statistics / Cumulative Query and Index Statistics	track_wal_io_timing	off	off
Statistics / Monitoring	compute_query_id	auto	auto
Statistics / Monitoring	log_executor_stats	off	off	true
Statistics / Monitoring	log_parser_stats	off	off	true
Statistics / Monitoring	log_planner_stats	off	off	true
Statistics / Monitoring	log_statement_stats	off	off	true
Version and Platform Compatibility / Previous PostgreSQL Versions	synchronize_seqscans	on	on	true
Write-Ahead Log / Archiving	archive_timeout	0	5min	false
Write-Ahead Log / Checkpoints	checkpoint_completion_target	0.9	0.9
Write-Ahead Log / Checkpoints	checkpoint_flush_after	256kB	256kB	true
Write-Ahead Log / Checkpoints	checkpoint_timeout	5min	1min	false
Write-Ahead Log / Checkpoints	checkpoint_warning	30s	30s
Write-Ahead Log / Checkpoints	max_wal_size	1GB	1GB
Write-Ahead Log / Checkpoints	min_wal_size	80MB	512MB	false
Write-Ahead Log / Recovery	recovery_prefetch	try	off
Write-Ahead Log / Recovery	wal_decode_buffer_size	512kB	512kB
Write-Ahead Log / Settings	commit_delay	0	0
Write-Ahead Log / Settings	commit_siblings	5	5
Write-Ahead Log / Settings	fsync	on	on
Write-Ahead Log / Settings	full_page_writes	on	off
Write-Ahead Log / Settings	synchronous_commit	on	on	true
Write-Ahead Log / Settings	wal_buffers	4MB	-1	true
Write-Ahead Log / Settings	wal_compression	off	off
Write-Ahead Log / Settings	wal_level	replica	replica
Write-Ahead Log / Settings	wal_log_hints	off	off
Write-Ahead Log / Settings	wal_skip_threshold	2MB	2MB
Write-Ahead Log / Settings	wal_sync_method	fdatasync	fdatasync
Write-Ahead Log / Settings	wal_writer_delay	200ms	200ms
Write-Ahead Log / Settings	wal_writer_flush_after	1MB	1MB

ついでにAuroraで変更不可能なパフォーマンス系パラメータ一覧

AWSCLIでAururaDBクラスターのデフォルトパラメータグループに項目のないパラメータ(一覧上では空白)と、パラメータが取得できて「Auroraで変更可能」列がfalseのもの(79個)

クリックして表示

category	name	RPMインストール	Aurora	Auroraで変更可能
Autovacuum	autovacuum_vacuum_insert_scale_factor	0.2	0.2
Autovacuum	autovacuum_vacuum_insert_threshold	1000	1000
Client Connection Defaults / Shared Library Preloading	jit_provider	llvmjit	llvmjit
Client Connection Defaults / Shared Library Preloading	local_preload_libraries
Client Connection Defaults / Shared Library Preloading	session_preload_libraries
Client Connection Defaults / Statement Behavior	default_table_access_method	heap	heap
Client Connection Defaults / Statement Behavior	default_toast_compression	pglz	pglz
Client Connection Defaults / Statement Behavior	idle_session_timeout	0	0
Client Connection Defaults / Statement Behavior	lock_timeout	0	0
Client Connection Defaults / Statement Behavior	transaction_deferrable	off	off
Client Connection Defaults / Statement Behavior	transaction_isolation	read committed	read committed
Client Connection Defaults / Statement Behavior	transaction_read_only	off	off
Client Connection Defaults / Statement Behavior	vacuum_failsafe_age	1600000000	1200000000
Client Connection Defaults / Statement Behavior	vacuum_multixact_failsafe_age	1600000000	1200000000
Connections and Authentication / Connection Settings	client_connection_check_interval	0	0
Connections and Authentication / Connection Settings	tcp_user_timeout	0	0
Developer Options	debug_discard_caches	0	0
Developer Options	ignore_system_indexes	off	off
Developer Options	jit_tuple_deforming	on	on
Developer Options	trace_sort	off	off
Developer Options	wal_consistency_checking
Preset Options	block_size	8192	8192
Preset Options	data_checksums	off	off
Preset Options	debug_assertions	off	off
Preset Options	segment_size	1GB	1GB
Preset Options	shared_memory_size	148MB	10516MB
Preset Options	shared_memory_size_in_huge_pages	74	5259
Preset Options	wal_block_size	8192	8192
Preset Options	wal_segment_size	16MB	64MB
Query Tuning / Other Planner Options	jit	on	off
Query Tuning / Other Planner Options	plan_cache_mode	auto	auto
Query Tuning / Other Planner Options	recursive_worktable_factor	10	10
Query Tuning / Planner Cost Constants	jit_above_cost	100000	100000
Query Tuning / Planner Cost Constants	jit_inline_above_cost	500000	500000
Query Tuning / Planner Cost Constants	jit_optimize_above_cost	500000	500000
Query Tuning / Planner Method Configuration	enable_async_append	on	on
Query Tuning / Planner Method Configuration	enable_incremental_sort	on	on
Query Tuning / Planner Method Configuration	enable_memoize	on	on
Reporting and Logging / What to Log	log_checkpoints	on	on
Reporting and Logging / What to Log	log_parameter_max_length	-1	-1
Reporting and Logging / What to Log	log_parameter_max_length_on_error	0	0
Reporting and Logging / When to Log	log_min_duration_sample	-1	-1
Reporting and Logging / When to Log	log_startup_progress_interval	10s	10s
Reporting and Logging / When to Log	log_statement_sample_rate	1	1
Reporting and Logging / When to Log	log_transaction_sample_rate	0	0
Resource Usage / Asynchronous Behavior	effective_io_concurrency	1	256	false
Resource Usage / Asynchronous Behavior	maintenance_io_concurrency	10	1
Resource Usage / Background Writer	bgwriter_delay	200ms	200ms
Resource Usage / Background Writer	bgwriter_lru_maxpages	100	100
Resource Usage / Background Writer	bgwriter_lru_multiplier	2	2
Resource Usage / Cost-Based Vacuum Delay	vacuum_cost_page_dirty	20	20
Resource Usage / Memory	dynamic_shared_memory_type	posix	posix
Resource Usage / Memory	hash_mem_multiplier	2	2
Resource Usage / Memory	huge_pages	try	on	false
Resource Usage / Memory	huge_page_size	0	0
Resource Usage / Memory	min_dynamic_shared_memory	0	0
Resource Usage / Memory	shared_memory_type	mmap	mmap
Statistics / Cumulative Query and Index Statistics	stats_fetch_consistency	cache	cache
Statistics / Cumulative Query and Index Statistics	track_wal_io_timing	off	off
Statistics / Monitoring	compute_query_id	auto	auto
Write-Ahead Log / Archiving	archive_timeout	0	5min	false
Write-Ahead Log / Checkpoints	checkpoint_completion_target	0.9	0.9
Write-Ahead Log / Checkpoints	checkpoint_timeout	5min	1min	false
Write-Ahead Log / Checkpoints	checkpoint_warning	30s	30s
Write-Ahead Log / Checkpoints	max_wal_size	1GB	1GB
Write-Ahead Log / Checkpoints	min_wal_size	80MB	512MB	false
Write-Ahead Log / Recovery	recovery_prefetch	try	off
Write-Ahead Log / Recovery	wal_decode_buffer_size	512kB	512kB
Write-Ahead Log / Settings	commit_delay	0	0
Write-Ahead Log / Settings	commit_siblings	5	5
Write-Ahead Log / Settings	fsync	on	on
Write-Ahead Log / Settings	full_page_writes	on	off
Write-Ahead Log / Settings	wal_compression	off	off
Write-Ahead Log / Settings	wal_level	replica	replica
Write-Ahead Log / Settings	wal_log_hints	off	off
Write-Ahead Log / Settings	wal_skip_threshold	2MB	2MB
Write-Ahead Log / Settings	wal_sync_method	fdatasync	fdatasync
Write-Ahead Log / Settings	wal_writer_delay	200ms	200ms
Write-Ahead Log / Settings	wal_writer_flush_after	1MB	1MB

備考

一覧の作成方法

EC2+PostgreSQLにpsqlでつないで、show_all結果をCSVに出力(RPMインストール列用)
Auroraにpsqlでつないで、show_all結果をCSVに出力(Aurora列用)
AWSCLIでAuroraDBクラスターのデフォルトパラメータグループをdescribeして、JSONをCSVに出力(Auroraで変更可能列用)
上記3つのCSVをCOPYコマンドで個別のテーブルに投入
psqlで以下を抽出
- RPMインストール列用テーブルに対して、パラメータ名をキーにして、Aurora列用テーブルとAuroraで変更可能列をleft join