postgresql.conf

# Basic PostgreSQL configuration for production
listen_addresses = '*'
max_connections = 300
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

# Memory Configuration
shared_buffers = '256MB'
effective_cache_size = '1GB'
maintenance_work_mem = '128MB'
work_mem = '16MB'
temp_buffers = '16MB'

# Write Ahead Log
wal_level = 'replica'
wal_buffers = '8MB'
min_wal_size = '1GB'
max_wal_size = '4GB'
checkpoint_timeout = '15min'
checkpoint_completion_target = 0.9
checkpoint_warning = '30s'

# Query Planner
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100

# Parallel Query Settings
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
parallel_leader_participation = on

# Logging Configuration
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/lib/postgresql/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = '100MB'
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 250
log_line_prefix = '%m [%p] %q%u@%d '

# Autovacuum Settings
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = '1min'
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

# Statistics and Monitoring
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 5000
track_activity_query_size = 2048
track_io_timing = on

# Security Settings
ssl = on
ssl_cert_file = '/etc/postgresql/ssl/fullchain.pem'
ssl_key_file = '/etc/postgresql/ssl/privkey.pem'
ssl_prefer_server_ciphers = on
password_encryption = scram-sha-256

# Locale and Timezone Settings
timezone = 'UTC'
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'