# -----------------------------
# PostgreSQL 18 configuration file
# Optimized for: 8GB RAM, 6 vCPU, KVM Virtual Machine
# -----------------------------

# CONNECTIONS AND AUTHENTICATION
listen_addresses = '*'
max_connections = 100
superuser_reserved_connections = 3

# MEMORY SETTINGS
shared_buffers = 2GB
huge_pages = try
effective_cache_size = 6GB
maintenance_work_mem = 512MB
work_mem = 20MB

# QUERY TUNING
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100

# WRITE AHEAD LOG (WAL)
wal_level = replica
wal_buffers = 16MB
min_wal_size = 1GB
max_wal_size = 4GB                 
wal_compression = on
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# BACKGROUND WRITER
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0

# AUTOVACUUM
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 200

# MONITORING AND STATISTICS
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
track_wal_io_timing = on
compute_query_id = on

# pg_stat_statements configuration
pg_stat_statements.track = all
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

# LOGGING
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# What to log
log_min_duration_statement = 1000  
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_lock_waits = on
log_statement = 'none'             
log_temp_files = 0
log_timezone = 'UTC'

# LOCALE AND FORMATTING
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'

# LOCK MANAGEMENT
deadlock_timeout = 1s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64

# CLIENT CONNECTION DEFAULTS
search_path = '"$user", public'
idle_in_transaction_session_timeout = 600000 
statement_timeout = 0

# PARALLEL QUERY EXECUTION
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6
max_parallel_maintenance_workers = 3
parallel_leader_participation = on

# INCREMENTAL BACKUP
summarize_wal = on

# VACUUM OPTIMIZATION
vacuum_buffer_usage_limit = 256kB

# VECTOR EXTENSION SETTINGS
maintenance_work_mem = 512MB
max_parallel_maintenance_workers = 3
work_mem = 32MB

# EXTENSIONS
shared_preload_libraries = 'pg_stat_statements'