Hello.
--
I have postgresql 12 with a 3.0 GB database with a table containing 10 millions of rows, this table also has 4 indexes.
I have an AWS EC2 server with two AMD EPYC 7571 cores, 2 GB of RAM and SSD disk.
Because of index bloating I use pg_repack.
When I use default postgresql.conf file I have no issues, but when I tune postgresql.conf and call pg_repack --only-indexes for my table, my server hangs and I can not connect to the server until reboot from AWS console.
What is wrong in my postgresql.conf?
My tuned parameters are:
max_stack_depth = 5MB
random_page_cost = 1.1
# Connectivity
max_connections = 100
superuser_reserved_connections = 3
# Memory Settings
shared_buffers = 350MB
effective_cache_size = 500MB
maintenance_work_mem = 384MB
work_mem = 2MB
# Monitoring
shared_preload_libraries = 'pg_stat_statements,pg_repack'
# Checkpointing:
checkpoint_timeout = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB
# WAL writing
wal_buffers = -1
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB
# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
random_page_cost = 1.1
# Connectivity
max_connections = 100
superuser_reserved_connections = 3
# Memory Settings
shared_buffers = 350MB
effective_cache_size = 500MB
maintenance_work_mem = 384MB
work_mem = 2MB
# Monitoring
shared_preload_libraries = 'pg_stat_statements,pg_repack'
# Checkpointing:
checkpoint_timeout = '15 min'
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB
# WAL writing
wal_buffers = -1
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB
# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
Faithfully yours, Roman I. Liverovskiy
