# ==============================================================================
# docker/postgres/postgresql.conf — PostgreSQL Production Tuning
# ==============================================================================
# Tuned for a 2GB RAM server running SMM Panel.
# Scale values proportionally for larger servers.
#
# Rule of thumb: shared_buffers = 25% of RAM
# ==============================================================================

# ── Connection ─────────────────────────────────────────────────────────────────
max_connections = 100           # Limit connections (use PgBouncer for pooling at scale)

# ── Memory ────────────────────────────────────────────────────────────────────
shared_buffers = 512MB          # 25% of RAM — primary DB cache
effective_cache_size = 1536MB   # Estimate of OS + DB cache (75% of RAM)
work_mem = 4MB                  # Per-sort / per-hash operation — multiply by max_connections
maintenance_work_mem = 128MB    # For VACUUM, CREATE INDEX, etc.
wal_buffers = 16MB

# ── Checkpoints ───────────────────────────────────────────────────────────────
checkpoint_completion_target = 0.9
wal_level = replica             # Required for streaming replication if needed

# ── Query Planner ─────────────────────────────────────────────────────────────
random_page_cost = 1.1          # SSDs: set to 1.1 (default 4.0 is for spinning disk)
effective_io_concurrency = 200  # SSDs: set to 200
default_statistics_target = 100

# ── Logging ───────────────────────────────────────────────────────────────────
log_destination = 'stderr'
logging_collector = off         # Docker captures stderr natively
log_min_duration_statement = 1000  # Log queries slower than 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0              # Log all temp file usage (indicates work_mem too low)
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# ── Autovacuum ────────────────────────────────────────────────────────────────
# More aggressive autovacuum for high-write tables (orders, transactions)
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 20s
autovacuum_vacuum_scale_factor = 0.05   # Vacuum after 5% of rows change (default 20%)
autovacuum_analyze_scale_factor = 0.025 # Analyze after 2.5% of rows change

# ── Performance ───────────────────────────────────────────────────────────────
synchronous_commit = on         # Don't disable — data safety > 10% write speed