postgres-optimization.conf

# PostgreSQL Performance Optimization Configuration
# For PyAirtable authentication and user data optimization
# Created: 2025-08-11

# =============================================================================
# CONNECTION SETTINGS
# =============================================================================

# Listen on all interfaces for Docker container networking
listen_addresses = '*'                  # Accept connections from all IPs
port = 5432

# Connection limits and pooling
max_connections = 100                    # Reduced from default 100 for container
superuser_reserved_connections = 3

# Connection pooling settings
shared_preload_libraries = 'pg_stat_statements,auto_explain'

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

# Shared memory settings (optimized for container with 2-4GB RAM)
shared_buffers = 256MB                   # 25% of available RAM
effective_cache_size = 1GB               # 75% of available RAM
work_mem = 4MB                          # Per-operation memory
maintenance_work_mem = 64MB             # For maintenance operations
max_wal_size = 1GB
min_wal_size = 80MB

# Memory for sorting and hashing
hash_mem_multiplier = 1.0
temp_file_limit = -1

# =============================================================================
# QUERY PLANNING
# =============================================================================

# Cost-based optimizer settings
random_page_cost = 1.1                  # SSD-optimized (default 4.0 for HDD)
seq_page_cost = 1.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

# Query planning
default_statistics_target = 100         # Statistics for better query plans
constraint_exclusion = partition        # Enable constraint exclusion
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

# =============================================================================
# WRITE PERFORMANCE
# =============================================================================

# Write-ahead logging
wal_level = replica
wal_compression = on
wal_buffers = 16MB
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
max_wal_senders = 3

# Asynchronous commit for better performance (with minimal data loss risk)
synchronous_commit = off                # Better performance for auth operations
wal_writer_delay = 200ms
commit_delay = 0

# =============================================================================
# AUTHENTICATION & SECURITY OPTIMIZATION
# =============================================================================

# Authentication settings
password_encryption = scram-sha-256
ssl = off                               # Disabled for internal docker network
log_connections = on
log_disconnections = on

# Connection security
tcp_keepalives_idle = 600               # 10 minutes
tcp_keepalives_interval = 30
tcp_keepalives_count = 3

# =============================================================================
# LOGGING & MONITORING
# =============================================================================

# Logging configuration for performance monitoring
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# Performance logging
log_min_duration_statement = 250        # Log slow queries (>250ms)
log_checkpoints = on
log_lock_waits = on                     # Log lock waits
log_temp_files = 0                      # Log all temporary files
log_autovacuum_min_duration = 0         # Log autovacuum activity

# Statement statistics
pg_stat_statements.max = 1000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

# Auto-explain for slow queries
auto_explain.log_min_duration = 500     # Auto-explain queries >500ms
auto_explain.log_analyze = on
auto_explain.log_verbose = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

# =============================================================================
# MAINTENANCE & VACUUMING
# =============================================================================

# Autovacuum settings (optimized for high-frequency auth operations)
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s                # More frequent cleanup
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1    # Vacuum when 10% of table is dead
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05  # Analyze when 5% of table changes

# Vacuum cost settings
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 500

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

# Background writer (optimized for write-heavy auth operations)
bgwriter_delay = 100ms                  # More frequent writes
bgwriter_lru_maxpages = 200            # More pages per round
bgwriter_lru_multiplier = 4.0          # Aggressive background writing
bgwriter_flush_after = 512kB

# =============================================================================
# PARALLEL QUERIES
# =============================================================================

# Parallel query settings
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_parallel_workers = 4
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

# =============================================================================
# LOCALE & ENCODING
# =============================================================================

# Character sets and localization
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'

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

# =============================================================================
# MISCELLANEOUS
# =============================================================================

# Various settings
default_tablespace = ''
temp_tablespaces = ''
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
default_transaction_deferrable = off
session_replication_role = 'origin'

# Lock settings
deadlock_timeout = 1s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64

# Statement timeout (prevent runaway queries)
statement_timeout = 30s                 # 30 second timeout for statements
lock_timeout = 10s                      # 10 second timeout for locks
idle_in_transaction_session_timeout = 30min