TimescaleDB: séries temporais no PostgreSQL

# Ubuntu 22.04/24.04 — instalar TimescaleDB:
sudo apt install -y gnupg postgresql-common apt-transport-https lsb-release wget

# Adicionar repositório:
sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -cs) main' > /etc/apt/sources.list.d/timescaledb.list"
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update

# Instalar para o PostgreSQL instalado:
sudo apt install -y timescaledb-2-postgresql-16

# Configurar (adiciona timescaledb ao shared_preload_libraries):
sudo timescaledb-tune --quiet --yes

# Reiniciar PostgreSQL:
sudo systemctl restart postgresql

# Ativar a extensão no banco:
psql -U postgres -d producao -c "CREATE EXTENSION IF NOT EXISTS timescaledb;"

# Docker — usar imagem oficial com TimescaleDB:
# image: timescale/timescaledb:latest-pg16

-- Criar tabela de métricas de servidor:
CREATE TABLE metricas_servidor (
  time        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  servidor    TEXT NOT NULL,
  cpu_pct     DOUBLE PRECISION,
  memoria_mb  BIGINT,
  disk_io_mb  DOUBLE PRECISION,
  rede_mbps   DOUBLE PRECISION
);

-- Converter em hypertable (particionada automaticamente por time):
SELECT create_hypertable('metricas_servidor', 'time',
  chunk_time_interval => INTERVAL '1 day'  -- nova partição a cada 1 dia
);

-- Ver partições criadas:
SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'metricas_servidor';

-- Inserir dados:
INSERT INTO metricas_servidor (servidor, cpu_pct, memoria_mb)
VALUES ('vps-prod-01', 45.2, 3840);

-- Ingestão em massa (TimescaleDB otimiza automaticamente):
INSERT INTO metricas_servidor (time, servidor, cpu_pct)
SELECT
  NOW() - (i || ' seconds')::INTERVAL,
  'vps-prod-01',
  random() * 100
FROM generate_series(1, 1000000) i;

-- time_bucket: agrupar por período (equivalente ao DATE_TRUNC mas mais flexível):
SELECT
  time_bucket('5 minutes', time) AS periodo,
  servidor,
  avg(cpu_pct) AS cpu_media,
  max(cpu_pct) AS cpu_pico,
  min(cpu_pct) AS cpu_minimo
FROM metricas_servidor
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY periodo, servidor
ORDER BY periodo DESC;

-- first() e last(): primeiro/último valor no período:
SELECT
  time_bucket('1 hour', time) AS hora,
  first(cpu_pct, time) AS cpu_inicio_hora,
  last(cpu_pct, time) AS cpu_fim_hora
FROM metricas_servidor
WHERE servidor = 'vps-prod-01'
GROUP BY hora ORDER BY hora DESC LIMIT 24;

-- Continuous Aggregates: views materializadas que atualizam automaticamente:
CREATE MATERIALIZED VIEW metricas_por_hora
WITH (timescaledb.continuous) AS
SELECT
  time_bucket('1 hour', time) AS hora,
  servidor,
  avg(cpu_pct) AS cpu_media,
  max(cpu_pct) AS cpu_pico,
  count(*) AS amostras
FROM metricas_servidor
GROUP BY hora, servidor;

-- Política de refresh automático:
SELECT add_continuous_aggregate_policy('metricas_por_hora',
  start_offset => INTERVAL '3 hours',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 hour');

-- Consultar a view (muito mais rápida que a tabela original):
SELECT * FROM metricas_por_hora
WHERE hora > NOW() - INTERVAL '7 days'
ORDER BY hora DESC;

-- Ativar compressão para chunks antigos:
ALTER TABLE metricas_servidor SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'servidor',  -- agrupar por servidor
  timescaledb.compress_orderby = 'time DESC'
);

-- Política de compressão automática (comprimir chunks com mais de 7 dias):
SELECT add_compression_policy('metricas_servidor', INTERVAL '7 days');

-- Ver taxa de compressão:
SELECT
  chunk_name,
  pg_size_pretty(before_compression_total_bytes) AS antes,
  pg_size_pretty(after_compression_total_bytes) AS depois,
  round(100.0 * after_compression_total_bytes / before_compression_total_bytes, 1) AS pct
FROM chunk_compression_stats('metricas_servidor');
-- Resultado típico: 90% de redução de espaço!

-- Política de retenção (deletar dados com mais de 90 dias):
SELECT add_retention_policy('metricas_servidor', INTERVAL '90 days');

-- Ver políticas ativas:
SELECT * FROM timescaledb_information.jobs;

-- Comprimir/descomprimir manualmente:
SELECT compress_chunk(chunk => '"_timescaledb_internal"."_hyper_1_1_chunk"');
SELECT decompress_chunk(chunk => '"_timescaledb_internal"."_hyper_1_1_chunk"');

// Enviar métricas do servidor para o TimescaleDB:
import { Pool } from 'pg'
import os from 'os'

const db = new Pool({ connectionString: process.env.DATABASE_URL })

async function coletarMetricas() {
  const cpus = os.cpus()
  const totalMem = os.totalmem()
  const freeMem = os.freemem()

  // Calcular uso de CPU:
  const cpuPct = cpus.reduce((acc, cpu) => {
    const total = Object.values(cpu.times).reduce((a, b) => a + b, 0)
    return acc + (100 - (100 * cpu.times.idle / total))
  }, 0) / cpus.length

  await db.query(
    `INSERT INTO metricas_servidor (servidor, cpu_pct, memoria_mb)
     VALUES ($1, $2, $3)`,
    [
      process.env.HOSTNAME ?? 'unknown',
      Math.round(cpuPct * 100) / 100,
      Math.round((totalMem - freeMem) / 1024 / 1024),
    ]
  )
}

// Coletar a cada 30 segundos:
setInterval(coletarMetricas, 30_000)

// API para consultar métricas das últimas 24h:
app.get('/api/metricas/:servidor', async (req, res) => {
  const { rows } = await db.query(
    `SELECT
       time_bucket('5 minutes', time) AS periodo,
       avg(cpu_pct) AS cpu,
       avg(memoria_mb) AS memoria
     FROM metricas_servidor
     WHERE servidor = $1 AND time > NOW() - INTERVAL '24 hours'
     GROUP BY periodo ORDER BY periodo`,
    [req.params.servidor]
  )
  res.json(rows)
})