# ==============================================================================
# POSTGRESQL CONFIGURATION FOR ADTLAS
# ==============================================================================
# 
# Optimized PostgreSQL configuration for the Adtlas TV advertising platform.
# This configuration balances performance, reliability, and resource usage
# for a containerized Django application.
#
# Configuration areas covered:
# - Connection settings
# - Memory allocation
# - Query optimization
# - Logging and monitoring
# - Security settings
#
# Author: Adtlas Development Team
# Version: 1.0.0
# PostgreSQL Version: 15+
# Last Updated: 2025-07-08
# ==============================================================================

# ==============================================================================
# CONNECTION AND AUTHENTICATION
# ==============================================================================

# Maximum number of concurrent connections
# Adjusted for containerized environment with limited resources
max_connections = 100

# Authentication settings
# Use md5 for secure password authentication
password_encryption = md5

# SSL configuration (can be enabled in production)
ssl = off

# ==============================================================================
# MEMORY ALLOCATION
# ==============================================================================

# Shared memory settings
# Set to ~25% of available container memory (assuming 1GB container)
shared_buffers = 256MB

# Working memory for query operations
# Set per connection, so be conservative
work_mem = 4MB

# Maintenance operations memory
# Used for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 64MB

# Effective cache size hint
# Set to ~75% of available system memory for query planner
effective_cache_size = 768MB

# ==============================================================================
# QUERY PLANNING AND EXECUTION
# ==============================================================================

# Cost-based optimizer settings
random_page_cost = 1.1          # Lower for SSD storage
effective_io_concurrency = 200   # Higher for SSD storage

# Enable parallel query execution
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_worker_processes = 8

# Query planning settings
default_statistics_target = 100
constraint_exclusion = partition

# ==============================================================================
# WRITE-AHEAD LOGGING (WAL)
# ==============================================================================

# WAL configuration for data durability
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB

# Checkpoint settings for performance
checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

# Archive settings (disabled for development)
archive_mode = off

# ==============================================================================
# REPLICATION (for future scaling)
# ==============================================================================

# Hot standby settings (for read replicas)
hot_standby = on
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s

# Replication settings
max_replication_slots = 10
max_wal_senders = 10

# ==============================================================================
# LOGGING AND MONITORING
# ==============================================================================

# Logging destination
log_destination = 'stderr'
logging_collector = off

# What to log
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1000  # Log slow queries (>1s)

# Log content settings
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_lock_waits = on
log_temp_files = 10MB

# Statement logging
log_statement = 'none'  # Change to 'all' for debugging
log_duration = off

# ==============================================================================
# PERFORMANCE MONITORING
# ==============================================================================

# Track query statistics
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

# Track I/O timing
track_io_timing = on
track_functions = all

# ==============================================================================
# AUTOVACUUM SETTINGS
# ==============================================================================

# Enable automatic maintenance
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

# Vacuum thresholds
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

# Vacuum cost settings
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 200

# ==============================================================================
# LOCALE AND FORMATTING
# ==============================================================================

# Default locale settings
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'

# Default text search configuration
default_text_search_config = 'pg_catalog.english'

# Timezone
timezone = 'UTC'
log_timezone = 'UTC'

# ==============================================================================
# SECURITY SETTINGS
# ==============================================================================

# Row level security
row_security = on

# Enable data checksums for corruption detection
data_checksums = on

# ==============================================================================
# DEVELOPMENT AND DEBUGGING
# ==============================================================================

# Debug settings (disable in production)
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off

# Error handling
exit_on_error = off
restart_after_crash = on

# ==============================================================================
# CUSTOM SETTINGS FOR DJANGO
# ==============================================================================

# Django-specific optimizations
synchronous_commit = on          # Ensure ACID compliance for Django ORM
commit_delay = 0                 # No artificial delay
commit_siblings = 5              # Commit when 5 transactions are waiting

# Full-text search settings for Django's search functionality
default_text_search_config = 'pg_catalog.english'

# JSON support settings (for Django JSONField)
gin_pending_list_limit = 4MB

# ==============================================================================
# NOTES FOR PRODUCTION DEPLOYMENT
# ==============================================================================
#
# For production deployment, consider adjusting:
# 1. max_connections based on expected load
# 2. shared_buffers to 25-40% of available RAM
# 3. effective_cache_size to 75% of available RAM
# 4. work_mem based on connection count and complexity
# 5. Enable SSL and configure certificates
# 6. Set up WAL archiving for backup strategy
# 7. Configure connection pooling (pgBouncer)
# 8. Enable log_statement for audit trails
# 9. Set up monitoring with pg_stat_statements
# 10. Configure auto-explain for slow query analysis
#
# ==============================================================================
