# ==============================================================================
# POSTGRESQL CONFIGURATION FOR STREAM PROCESSOR
# ==============================================================================
# 
# Optimized PostgreSQL configuration for the Stream Processor application.
# This configuration is tuned for high-frequency stream processing operations,
# monitoring data collection, and Django ORM usage patterns.
#
# Configuration areas covered:
# - Memory management and buffer optimization
# - Connection pooling and concurrency
# - Write-ahead logging (WAL) optimization
# - Query performance tuning
# - Monitoring and logging
# - Autovacuum optimization
#
# Author: Stream Processor Development Team
# Version: 1.0.0
# PostgreSQL Version: 17
# Last Updated: 2025-01-27
# ==============================================================================

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

# Listen on all addresses (safe in Docker container)
listen_addresses = '*'
port = 5432

# Connection limits optimized for Django + Celery workers
max_connections = 200
superuser_reserved_connections = 3

# Connection pooling settings
tcp_keepalives_idle = 600
tcp_keepalives_interval = 30
tcp_keepalives_count = 3

# ==============================================================================
# MEMORY CONFIGURATION
# ==============================================================================

# Shared memory settings (25% of available RAM for container)
shared_buffers = 256MB

# Effective cache size (estimate of OS cache + shared_buffers)
# Set to ~75% of total available memory
effective_cache_size = 768MB

# Work memory for sorting and hash operations
# Optimized for stream processing queries
work_mem = 8MB

# Maintenance work memory for VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 128MB

# Memory for autovacuum workers
autovacuum_work_mem = 64MB

# Temporary file limit per process
temp_file_limit = 2GB

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

# WAL configuration for high-frequency writes
wal_level = replica
wal_buffers = 16MB
wal_writer_delay = 200ms

# Checkpoint configuration for stream processing workload
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 512MB

# WAL archiving (disabled for single instance)
archive_mode = off

# ==============================================================================
# QUERY TUNING
# ==============================================================================

# Cost-based optimizer settings
random_page_cost = 1.1
seq_page_cost = 1.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

# Parallel query settings
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_worker_processes = 8
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0

# Query planning
default_statistics_target = 100
constraint_exclusion = partition
from_collapse_limit = 8
join_collapse_limit = 8

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

# Logging configuration
logging_collector = on
log_destination = 'stderr'
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_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = off
log_disconnections = off
log_lock_waits = on
log_temp_files = 10MB

# Statement logging for performance monitoring
log_statement = 'none'
log_duration = off
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# ==============================================================================
# AUTOVACUUM CONFIGURATION
# ==============================================================================

# Enable autovacuum for high-frequency tables
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s

# Aggressive autovacuum for stream processing tables
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05

# Vacuum cost settings
vacuum_cost_delay = 10ms
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 200

# ==============================================================================
# BACKGROUND WRITER
# ==============================================================================

# Background writer settings for high write workload
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 512kB

# ==============================================================================
# LOCK MANAGEMENT
# ==============================================================================

# Lock settings for concurrent stream processing
deadlock_timeout = 1s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64

# ==============================================================================
# CLIENT CONNECTION DEFAULTS
# ==============================================================================

# Timezone and locale
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

# Default transaction isolation (matches Django settings)
default_transaction_isolation = 'read committed'

# Statement timeout for long-running queries
statement_timeout = 300s
lock_timeout = 30s
idle_in_transaction_session_timeout = 60s

# ==============================================================================
# PERFORMANCE EXTENSIONS
# ==============================================================================

# Shared preload libraries for performance monitoring
shared_preload_libraries = 'pg_stat_statements'

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

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

# Replication settings (currently disabled)
max_wal_senders = 3
wal_keep_size = 1GB
hot_standby = on
hot_standby_feedback = off

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

# SSL configuration (disabled in Docker environment)
ssl = off

# Authentication
password_encryption = scram-sha-256

# ==============================================================================
# STREAM PROCESSOR SPECIFIC OPTIMIZATIONS
# ==============================================================================

# Optimizations for Django ORM patterns
track_activities = on
track_counts = on
track_io_timing = on
track_functions = none

# Optimizations for time-series data (monitoring metrics)
effective_io_concurrency = 2
maintenance_io_concurrency = 2

# Optimizations for frequent INSERT/UPDATE operations
wal_compression = on
full_page_writes = on

# ==============================================================================
# NOTES FOR PRODUCTION DEPLOYMENT
# ==============================================================================
#
# For production deployment, consider:
# 1. Increase shared_buffers based on available RAM
# 2. Enable SSL/TLS for secure connections
# 3. Configure streaming replication for high availability
# 4. Set up connection pooling with PgBouncer
# 5. Monitor slow queries and optimize indexes
# 6. Implement regular backup strategy
# 7. Configure log rotation and monitoring
# 8. Tune autovacuum based on actual workload
# 9. Set up monitoring with pg_stat_statements
# 10. Consider partitioning for large time-series tables
#
# ==============================================================================