Time-series data: otimizações e padrões de consulta

1. Fundamentos de dados temporais em bancos relacionais

1.1. Características de séries temporais

Dados de séries temporais possuem três propriedades fundamentais que impactam diretamente o design do banco de dados:

  • Cardinalidade: número de identificadores únicos (sensores, ativos, usuários) que geram dados simultaneamente. Alta cardinalidade pode degradar performance de índices tradicionais.
  • Monotonicidade: timestamps geralmente são inseridos em ordem crescente, o que permite otimizações como índices BRIN e particionamento por range.
  • Volume: séries temporais geram dados continuamente, exigindo estratégias de arquivamento e compressão.

1.2. Tipos de dados temporais nativos

A maioria dos bancos relacionais oferece tipos específicos:

-- PostgreSQL
CREATE TABLE leituras (
    sensor_id INTEGER,
    ts TIMESTAMPTZ NOT NULL,  -- com timezone
    valor NUMERIC(10,2),
    PRIMARY KEY (sensor_id, ts)
);

-- Intervalo entre leituras
SELECT ts, ts - LAG(ts) OVER (PARTITION BY sensor_id ORDER BY ts) AS intervalo
FROM leituras;

1.3. Desafios comuns

Gaps (falhas), outliers (valores anômalos) e janelas de tempo irregulares são problemas recorrentes. Um gap pode indicar falha no sensor, enquanto outliers podem representar eventos reais ou ruído.

2. Modelagem de esquemas para séries temporais

2.1. Modelo largo vs. modelo longo

Modelo longo (tidy) é o mais adequado para SQL:

-- Modelo longo (recomendado): uma linha por medição
CREATE TABLE metricas (
    ts TIMESTAMPTZ,
    sensor_id INT,
    metrica TEXT,    -- 'temperatura', 'umidade', 'pressao'
    valor DOUBLE PRECISION
);

-- Modelo largo: colunas separadas por métrica
CREATE TABLE metricas_wide (
    ts TIMESTAMPTZ,
    sensor_id INT,
    temperatura DOUBLE PRECISION,
    umidade DOUBLE PRECISION,
    pressao DOUBLE PRECISION
);

O modelo longo facilita adicionar novas métricas sem alterar schema e permite consultas dinâmicas, mas pode exigir pivoting (crosstab) para análises comparativas.

2.2. Particionamento por tempo

Particionamento por range de timestamp é essencial para gerenciar volumes crescentes:

CREATE TABLE leituras (
    sensor_id INT,
    ts TIMESTAMPTZ NOT NULL,
    valor DOUBLE PRECISION
) PARTITION BY RANGE (ts);

CREATE TABLE leituras_2024_q1 PARTITION OF leituras
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE leituras_2024_q2 PARTITION OF leituras
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

2.3. Índices especializados: BRIN vs. B-tree

Índices B-tree são excelentes para consultas pontuais, mas para varreduras de intervalo em séries temporais, índices BRIN (Block Range INdex) são mais eficientes:

-- B-tree: bom para consultas exatas, mas grande em disco
CREATE INDEX idx_btree_ts ON leituras (ts);

-- BRIN: compacto, ideal para timestamp com ordenação física
CREATE INDEX idx_brin_ts ON leituras USING BRIN (ts) WITH (pages_per_range = 32);

BRIN ocupa ~0.1% do tamanho de um B-tree equivalente, sendo ideal para tabelas com inserção em ordem temporal.

3. Padrões de consulta temporal eficientes

3.1. Filtros por intervalo de tempo

Prefira intervalos half-open (>= e <) para evitar ambiguidades:

-- Eficiente e sem ambiguidade
SELECT sensor_id, AVG(valor)
FROM leituras
WHERE ts >= '2024-06-01' AND ts < '2024-06-02'
GROUP BY sensor_id;

-- BETWEEN inclui ambas as bordas, podendo duplicar dados em partições
SELECT sensor_id, AVG(valor)
FROM leituras
WHERE ts BETWEEN '2024-06-01' AND '2024-06-02';

3.2. Agregações em janelas deslizantes

Funções de janela são fundamentais para séries temporais:

-- Média móvel de 7 pontos
SELECT sensor_id, ts, valor,
    AVG(valor) OVER (
        PARTITION BY sensor_id
        ORDER BY ts
        ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
    ) AS media_movel_7
FROM leituras;

-- Diferença entre leituras consecutivas
SELECT sensor_id, ts, valor,
    valor - LAG(valor, 1) OVER (PARTITION BY sensor_id ORDER BY ts) AS delta
FROM leituras;

3.3. Amostragem e downsampling

Para reduzir volume em visualizações de longo período:

-- Downsampling para médias horárias
SELECT sensor_id,
    date_trunc('hour', ts) AS hora,
    AVG(valor) AS media,
    COUNT(*) AS amostras
FROM leituras
GROUP BY sensor_id, date_trunc('hour', ts)
ORDER BY hora;

-- Gerar buckets contínuos com generate_series
SELECT g.hora_bucket, l.sensor_id, AVG(l.valor)
FROM generate_series(
    '2024-06-01'::TIMESTAMPTZ,
    '2024-06-02'::TIMESTAMPTZ,
    INTERVAL '15 minutes'
) AS g(hora_bucket)
LEFT JOIN leituras l
    ON l.ts >= g.hora_bucket
    AND l.ts < g.hora_bucket + INTERVAL '15 minutes'
GROUP BY g.hora_bucket, l.sensor_id
ORDER BY g.hora_bucket;

4. Otimizações de performance para grandes volumes

4.1. Índices parciais e compostos

Índices parciais reduzem o tamanho e aceleram consultas frequentes:

-- Índice composto para consultas comuns
CREATE INDEX idx_sensor_ts ON leituras (sensor_id, ts);

-- Índice parcial apenas para valores anômalos
CREATE INDEX idx_outliers ON leituras (ts)
    WHERE valor > 100 OR valor < -10;

4.2. Materialized views para agregações

Pré-compute agregações que são consultadas repetidamente:

CREATE MATERIALIZED VIEW metricas_horarias AS
SELECT sensor_id,
    date_trunc('hour', ts) AS hora,
    AVG(valor) AS media,
    MAX(valor) AS maximo,
    MIN(valor) AS minimo,
    COUNT(*) AS amostras
FROM leituras
GROUP BY sensor_id, date_trunc('hour', ts)
WITH DATA;

-- Atualizar periodicamente
REFRESH MATERIALIZED VIEW CONCURRENTLY metricas_horarias;

4.3. Compressão e armazenamento columnar

TimescaleDB oferece compressão nativa que reduz armazenamento em 90%+:

-- Ativar compressão em hypertable TimescaleDB
ALTER TABLE leituras SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'sensor_id',
    timescaledb.compress_orderby = 'ts DESC'
);

-- Política de compressão automática para dados com mais de 7 dias
SELECT add_compression_policy('leituras', INTERVAL '7 days');

5. Padrões avançados de análise temporal

5.1. Detecção de outliers com desvio padrão

WITH stats AS (
    SELECT sensor_id,
        AVG(valor) AS media,
        STDDEV(valor) AS desvio
    FROM leituras
    WHERE ts >= NOW() - INTERVAL '1 hour'
    GROUP BY sensor_id
)
SELECT l.sensor_id, l.ts, l.valor,
    (l.valor - s.media) / s.desvio AS z_score
FROM leituras l
JOIN stats s ON l.sensor_id = s.sensor_id
WHERE ABS((l.valor - s.media) / s.desvio) > 3;

5.2. Variações percentuais e taxas de crescimento

SELECT sensor_id, ts, valor,
    LAG(valor) OVER (PARTITION BY sensor_id ORDER BY ts) AS valor_anterior,
    (valor - LAG(valor) OVER (PARTITION BY sensor_id ORDER BY ts))
        / NULLIF(LAG(valor) OVER (PARTITION BY sensor_id ORDER BY ts), 0) * 100
        AS variacao_percentual
FROM leituras;

5.3. Preenchimento de gaps com interpolação linear

WITH base AS (
    SELECT sensor_id, ts, valor,
        LAG(valor) OVER (PARTITION BY sensor_id ORDER BY ts) AS valor_ant,
        LAG(ts) OVER (PARTITION BY sensor_id ORDER BY ts) AS ts_ant,
        LEAD(valor) OVER (PARTITION BY sensor_id ORDER BY ts) AS valor_prox,
        LEAD(ts) OVER (PARTITION BY sensor_id ORDER BY ts) AS ts_prox
    FROM leituras
)
SELECT sensor_id, ts,
    COALESCE(valor,
        valor_ant + (EXTRACT(EPOCH FROM ts - ts_ant) /
                    NULLIF(EXTRACT(EPOCH FROM ts_prox - ts_ant), 0))
        * (valor_prox - valor_ant)
    ) AS valor_interpolado
FROM base
ORDER BY sensor_id, ts;

6. Extensões e ferramentas especializadas

6.1. TimescaleDB

TimescaleDB transforma PostgreSQL em um banco otimizado para séries temporais:

-- Criar hypertable (particionamento automático por tempo)
SELECT create_hypertable('leituras', 'ts', chunk_time_interval => INTERVAL '1 day');

-- Política de retenção automática
SELECT add_retention_policy('leituras', INTERVAL '90 days');

6.2. pg_partman

Automatiza criação e manutenção de partições:

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.leituras',
    p_control := 'ts',
    p_type := 'native',
    p_interval := '1 day',
    p_premake := 30
);

6.3. Abordagens nativas vs. extensões

Para volumes moderados (< 100M linhas), PostgreSQL nativo com particionamento e BRIN é suficiente. Acima disso, TimescaleDB oferece compressão, chunking inteligente e funções específicas como time_bucket() e first()/last().

7. Anti-padrões e boas práticas

7.1. Evitar SELECT * e subconsultas correlacionadas

-- Ruim: SELECT * em tabela larga + subconsulta correlacionada
SELECT * FROM leituras l
WHERE valor > (SELECT AVG(valor) FROM leituras l2 WHERE l2.sensor_id = l.sensor_id);

-- Bom: CTE + colunas específicas
WITH medias AS (
    SELECT sensor_id, AVG(valor) AS media FROM leituras GROUP BY sensor_id
)
SELECT l.sensor_id, l.ts, l.valor
FROM leituras l
JOIN medias m ON l.sensor_id = m.sensor_id
WHERE l.valor > m.media;

7.2. Cuidados com timezones

Sempre armazene em UTC e converta na exibição:

-- Armazenar em UTC
INSERT INTO leituras VALUES (1, '2024-06-01 12:00:00+00', 25.5);

-- Converter para timezone local na consulta
SELECT ts AT TIME ZONE 'America/Sao_Paulo' AS hora_local
FROM leituras;

7.3. Monitoramento com EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT sensor_id, AVG(valor)
FROM leituras
WHERE ts >= '2024-06-01' AND ts < '2024-07-01'
GROUP BY sensor_id;

Analise se o plano está usando índices BRIN ou B-tree, se há sequential scans em partições desnecessárias e o número de buffers lidos.

Referências