############ CONFIGURAÇÃO OTIMIZADA POSTGRESQL 16 + TIMESCALEDB ############
# Otimizado para: 4 núcleos CPU, 8GB RAM
# Ambiente: Zabbix 7.0 LTS com ~1000 equipamentos
# Features: TimescaleDB Hypertables + Compressão nativa
# Objetivo: Máxima performance para time-series e retenção longa
############################################################################

# Adicione ao arquivo: /etc/postgresql/16/main/postgresql.conf

############################################################################
# CONEXÕES
############################################################################

listen_addresses = '*'
max_connections = 300
superuser_reserved_connections = 3

############################################################################
# MEMÓRIA - OTIMIZADO PARA 8GB RAM (3GB para PostgreSQL)
############################################################################

# Buffer compartilhado (25-30% da RAM disponível para PostgreSQL)
shared_buffers = 2GB

# Cache efetivo (estimativa de memória total disponível para cache)
# Inclui cache do SO + shared_buffers
effective_cache_size = 4GB

# Memória para operações de manutenção (VACUUM, CREATE INDEX)
maintenance_work_mem = 512MB

# Memória por operação de sort/hash (conexão individual)
work_mem = 32MB

# Memória para operações de autovacuum
autovacuum_work_mem = 256MB

############################################################################
# WAL (Write-Ahead Log) - OTIMIZADO PARA ALTA ESCRITA
############################################################################

# Tamanho do buffer WAL em memória
wal_buffers = 16MB

# Nível mínimo de WAL (minimal = máxima performance sem replicação)
wal_level = minimal

# Tamanhos máximo e mínimo de WAL em disco
max_wal_size = 4GB
min_wal_size = 1GB

# Compressão de WAL (reduz I/O de disco)
wal_compression = on

# Método de gravação no disco
wal_writer_delay = 200ms

############################################################################
# CHECKPOINTS - REDUZ I/O E AUMENTA PERFORMANCE
############################################################################

# Target de conclusão do checkpoint (0.9 = 90% do intervalo)
checkpoint_completion_target = 0.9

# Intervalo entre checkpoints automáticos
checkpoint_timeout = 15min

# Log de checkpoints (útil para tuning)
log_checkpoints = on

############################################################################
# PLANNER E QUERY OPTIMIZATION
############################################################################

# Custo estimado de leitura aleatória em disco
# SSD = 1.1, HDD = 4.0
random_page_cost = 1.1

# Custo de leitura sequencial
seq_page_cost = 1.0

# Capacidade de I/O paralelo (SSD)
effective_io_concurrency = 200

# Limite de custo para uso de índice
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025

############################################################################
# PARALELISMO - APROVEITA 4 NÚCLEOS
############################################################################

# Máximo de workers para processos em background
max_worker_processes = 8

# Workers por query paralela
max_parallel_workers_per_gather = 2

# Total de workers paralelos simultâneos
max_parallel_workers = 4

# Mínimo de linhas para considerar paralelismo
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

############################################################################
# AUTOVACUUM - CRÍTICO PARA ZABBIX COM TIME-SERIES
############################################################################

# Habilitar autovacuum (OBRIGATÓRIO)
autovacuum = on

# Número de processos autovacuum simultâneos
autovacuum_max_workers = 4

# Intervalo entre varreduras de autovacuum
autovacuum_naptime = 10s

# Thresholds agressivos para Zabbix (muitas escritas)
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# Scale factors reduzidos (mais agressivo)
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.005

# Custo e delay do autovacuum
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

# Prevenir wraparound
autovacuum_freeze_max_age = 200000000

############################################################################
# VACUUM E MANUTENÇÃO
############################################################################

# Age máximo para vacuum de prevenção
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000

# Custo de vacuum manual
vacuum_cost_delay = 0
vacuum_cost_limit = 200

############################################################################
# OTIMIZAÇÕES ESPECÍFICAS PARA ZABBIX/TIME-SERIES
############################################################################

# Commit assíncrono (aceita pequeno risco para MÁXIMA performance)
# ATENÇÃO: Em caso de crash, pode perder até 1 segundo de dados
synchronous_commit = off

# Desabilita full page writes (maior risco, muito mais performance)
# RECOMENDADO apenas com filesystem confiável (ext4, xfs, zfs)
full_page_writes = off

# Compressão de TOAST
default_toast_compression = lz4

############################################################################
# TIMESCALEDB - EXTENSÃO PARA TIME-SERIES
############################################################################

# Preload da extensão TimescaleDB
shared_preload_libraries = 'timescaledb,pg_stat_statements'

# Configurações TimescaleDB
timescaledb.max_background_workers = 8

############################################################################
# LOGGING - ANÁLISE E TROUBLESHOOTING
############################################################################

# Destino dos logs
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

# Formato e informações dos logs
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'America/Sao_Paulo'

# Logs de queries lentas (> 1 segundo)
log_min_duration_statement = 1000

# Logs de conexões e locks
log_connections = on
log_disconnections = on
log_lock_waits = on

# Log de tabelas temporárias grandes
log_temp_files = 0

# Log de comandos DDL
log_statement = 'ddl'

# Autovacuum logging
log_autovacuum_min_duration = 0

############################################################################
# CONFIGURAÇÕES DE LOCALE E ENCODING
############################################################################

# Encoding padrão (UTF-8)
client_encoding = UTF8

# Locale
lc_messages = 'pt_BR.UTF-8'
lc_monetary = 'pt_BR.UTF-8'
lc_numeric = 'pt_BR.UTF-8'
lc_time = 'pt_BR.UTF-8'

# Timezone
timezone = 'America/Sao_Paulo'

# Comportamento de strings vazias
default_text_search_config = 'pg_catalog.portuguese'

############################################################################
# SEGURANÇA E LIMITES
############################################################################

# Timeout de statement (evita queries runaway)
statement_timeout = 0

# Timeout de lock
lock_timeout = 0

# Timeout de idle in transaction
idle_in_transaction_session_timeout = 0

############################################################################
# ESTATÍSTICAS
############################################################################

# Nível de estatísticas coletadas
track_activities = on
track_counts = on
track_io_timing = on
track_functions = all

# Estatísticas de queries (requer pg_stat_statements)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

############################################################################
# OUTROS
############################################################################

# Número de buffers de disco sujos antes de fsync
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0

# JIT compilation (PostgreSQL 11+)
jit = on
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000