PolarDB:Use um índice columnstore para acelerar análises de dados de séries temporais

Fluxo de trabalho

1. Gravação de dados: sua aplicação de negócios grava dados de séries temporais (por exemplo, registros de transações) em um cluster PolarDB for PostgreSQL.

2. Índice columnstore: crie um índice columnstore na tabela base. O PolarDB for PostgreSQL mantém automaticamente os dados colunares junto ao armazenamento por linhas. Em comparação com o armazenamento por linhas, o armazenamento colunar organiza os dados por coluna, proporcionando maior taxa de compressão e lendo apenas as colunas relevantes durante consultas agregadas, o que reduz a E/S.

3. Aceleração de consultas: o sistema roteia consultas analíticas (como agregações de candlestick) preferencialmente para o índice columnstore, seja pelo otimizador ou por meio de um Hint explícito. O mecanismo de consulta utiliza o armazenamento colunar e o processamento paralelo para varrer e agregar os dados, retornando o resultado em seguida.

Vantagens da solução

• Simplicidade de uso: não é necessário refatorar a aplicação nem realizar processos complexos de ETL. Basta criar um índice columnstore na tabela base para acelerar as consultas analíticas de forma transparente.

• Rico em recursos: oferece suporte nativo a tabelas particionadas e inclui um conjunto abrangente de funções para análise de séries temporais, como time_bucket, first e last, simplificando o desenvolvimento SQL.

Etapa 1: Preparar o ambiente

1. Verifique se a versão e a configuração do seu cluster atendem aos seguintes requisitos:

   • Versões do cluster:

     • PostgreSQL 14 (versão secundária do mecanismo 2.0.14.10.20.0 ou posterior)

     • PostgreSQL 15 (versão secundária do mecanismo 2.0.15.15.7.0 ou posterior)

     • PostgreSQL 16 (versão secundária do mecanismo 2.0.16.8.3.0 ou posterior)

     • PostgreSQL 17 (versão secundária do mecanismo 2.0.17.7.5.0 ou posterior)

     Nota

     Você pode visualizar a versão secundária do mecanismo no console ou executando a instrução SHOW polardb_version;. Se a versão secundária do mecanismo não atender aos requisitos, atualize a versão secundária do mecanismo.

   • Defina o parâmetro wal_level como logical. Essa configuração adiciona as informações necessárias para decodificação lógica ao write-ahead logging (WAL).

     Nota

     Você pode definir o parâmetro wal_level no console. A modificação desse parâmetro reinicia o cluster. Planeje suas operações de negócios adequadamente e proceda com cautela.

   • A tabela de origem deve ter uma chave primária, e a coluna da chave primária deve ser incluída ao criar o índice columnstore. Recomenda-se usar o tipo de dados SERIAL ou BIGSERIAL para a chave primária, pois isso melhora significativamente a eficiência da sincronização de dados.

   • É possível criar apenas um índice columnstore por tabela.

2. Ative o recurso de índice columnstore.

   O método para ativar o IMCI varia dependendo da versão secundária do mecanismo do seu cluster PolarDB for PostgreSQL:

   PostgreSQL 16 (2.0.16.9.8.0 ou posterior) ou PostgreSQL 14 (2.0.14.17.35.0 ou posterior)

   Para clusters PolarDB for PostgreSQL com essas versões, dois métodos estão disponíveis. A tabela a seguir destaca as diferenças.

   Item de comparação	[Recomendado] Adicionar um nó somente leitura IMCI	Usar diretamente a extensão de índice columnstore pré-instalada
   Método	Adicione manualmente um nó somente leitura IMCI no console.	Nenhuma ação é necessária. Use a extensão diretamente.
   Alocação de recursos	O mecanismo columnstore usa exclusivamente os recursos do nó, incluindo toda a memória disponível.	O mecanismo columnstore fica limitado a 25% da memória do nó. A memória restante é alocada ao mecanismo de armazenamento por linhas.
   Impacto nos negócios	As cargas de trabalho de processamento transacional (TP) e processamento analítico (AP) ficam isoladas em nós diferentes e não se afetam mutuamente.	As cargas de trabalho TP e AP executam no mesmo nó e podem afetar umas às outras.
   Custos	Os nós somente leitura IMCI incorrem em cobranças adicionais e são faturados à mesma taxa dos nós de computação regulares.	Sem custo adicional.

   Adicionar um nó somente leitura IMCI

   Existem duas maneiras de adicionar um nó somente leitura IMCI:

   Nota

   O cluster deve conter pelo menos um nó somente leitura. Não é possível adicionar um nó somente leitura IMCI a um cluster de nó único.

   Console

   1. Faça login no console do PolarDB e selecione a região do cluster. Abra o assistente Add/Remove Node de uma das seguintes formas:

      • Na página Clusters, clique em Add/Remove Node na coluna Actions.

      • Na página Basic Information do cluster desejado, clique em Add/Remove Node na seção Database Nodes.

   2. Selecione Add Read-only IMCI Node e clique em OK.

   3. Na página de upgrade/downgrade do cluster, adicione o nó somente leitura IMCI e conclua o pagamento.

      1. Clique em Add a Read-only IMCI Node e selecione as especificações do nó.

      2. Escolha um horário para o switchover.

      3. (Opcional) Revise os Termos de Serviço do Produto e o Acordo de Nível de Serviço.

      4. Clique em Buy Now.

   4. Após a conclusão do pagamento, retorne à página de detalhes do cluster e aguarde a adição do nó somente leitura IMCI. O nó estará pronto quando seu status mudar para Running.

   During purchase

   Na página de compra do PolarDB, na seção Nodes, especifique o número de IMCI Read-Only Nodes.

   PostgreSQL 16 (2.0.16.8.3.0 a 2.0.16.9.8.0) ou PostgreSQL 14 (2.0.14.10.20.0 a 2.0.14.17.35.0)

   Para clusters PolarDB for PostgreSQL com essas versões, o recurso IMCI é fornecido como a extensão polar_csi. Para usar o IMCI, crie primeiro a extensão no banco de dados desejado.

   Nota

   • A extensão polar_csi tem escopo no nível do banco de dados. Para usar o IMCI em vários bancos de dados dentro de um cluster, crie a extensão polar_csi para cada banco de dados.

   • A conta do banco de dados usada para instalar a extensão deve ser uma conta privilegiada.

   Há duas maneiras de instalar a extensão polar_csi:

   Console

   1. Faça login no console do PolarDB. No painel de navegação à esquerda, clique em Clusters. Selecione a região onde seu cluster está localizado e clique no ID do cluster para abrir a página de detalhes do cluster.

   2. No painel de navegação à esquerda, escolha Settings and Management > Extension Management. Na aba Extension Management, selecione Uninstalled Extensions.

   3. No canto superior direito da página, selecione o banco de dados de destino. Na linha da extensão polar_csi, clique em Install na coluna Actions. Na caixa de diálogo Install Extension, selecione a Database Account de destino e clique em OK para instalar a extensão no banco de dados alvo.

   CLI

   Conecte-se ao cluster de banco de dados e execute a seguinte instrução em um banco de dados onde você tenha permissões suficientes para criar a extensão polar_csi.

   CREATE EXTENSION polar_csi;

Etapa 2: Preparar os dados

1. Esta solução utiliza uma tabela de registros de transações e simula a geração de 100 milhões de registros abrangendo aproximadamente 2 dias. O horário de negociação é das 08:00 às 16:00 todos os dias, produzindo cerca de 40 milhões de registros diários.

   -- Transaction records table
   CREATE TABLE market_trades (
       trade_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- Auto-increment primary key
       trade_ts   TIMESTAMP,                                        -- Transaction timestamp
       market_id  VARCHAR,                                          -- Market ID
       price      DECIMAL,                                          -- Transaction price
       amount     DECIMAL,                                          -- Transaction amount
       insert_ts  TIMESTAMP                                         -- System write timestamp
   );
   
   INSERT INTO market_trades(trade_ts, market_id, price, amount, insert_ts)
   SELECT
       trade_ts,
       market_id,
       price,
       amount,
       trade_ts + (random() * 500)::INT * INTERVAL '1 millisecond' AS insert_ts
   FROM (
       -- ========================
       -- 1. Day 1 peak: 2025-06-01 8:00 - 16:00, 40 million rows
       -- ========================
       SELECT
           '2025-06-01 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +  -- 28800 seconds = 8 hours
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 2. Day 1 off-peak: 2025-06-01 16:00 - 2025-06-02 08:00, 10 million rows
       -- ========================
       SELECT
           CASE
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-01 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (early morning of day 2)
                   '2025-06-02 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   
       UNION ALL
   
       -- ========================
       -- 3. Day 2 peak: 2025-06-02 8:00 - 16:00, 40 million rows
       -- ========================
       SELECT
           '2025-06-02 08:00:00'::TIMESTAMP +
           (random() * 28800)::INT * INTERVAL '1 second' +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 40000000)
   
       UNION ALL
   
       -- ========================
       -- 4. Day 2 off-peak: 2025-06-02 16:00 - 2025-06-03 08:00, 10 million rows
       -- ========================
       SELECT
           CASE
               WHEN random() < 0.5 THEN
                   -- 16:00 - 24:00
                   '2025-06-02 16:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
               ELSE
                   -- 00:00 - 08:00 (early morning of day 3)
                   '2025-06-03 00:00:00'::TIMESTAMP + (random() * 28800)::INT * INTERVAL '1 second'
           END +
           (random() * 1000)::INT * INTERVAL '1 millisecond' AS trade_ts,
           CASE WHEN random() < 0.6 THEN 'BTC-USDT' ELSE 'ETH-USDT' END AS market_id,
           CASE WHEN random() < 0.6 THEN 30000 + (random() * 1000) ELSE 2000 + (random() * 100) END AS price,
           random() * 10 + 0.1 AS amount
       FROM generate_series(1, 10000000)
   ) AS data;

2. Crie um índice columnstore na tabela de registros de transações.

   CREATE INDEX idx_csi_market_trades ON market_trades USING CSI;

Etapa 3: Executar consultas de agregação de candlestick

Caso de uso: calcular candlesticks em janelas de tempo fixas.

Exemplo de aplicação: calcular preço máximo, preço mínimo, preço de abertura, preço de fechamento e volume total de transações por segundo.

Os exemplos a seguir calculam dados de candlestick por segundo, por minuto, por hora e por dia, respectivamente.

-- Second-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,   -- Data within 1 second
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 second
    MAX(price) AS high,                               -- Highest price within 1 second
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 second
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 second
    SUM(amount) AS vol                                -- Total transaction volume within 1 second
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Minute-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 minute', trade_ts) AS candle_ts,   -- Data within 1 minute
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 minute
    MAX(price) AS high,                               -- Highest price within 1 minute
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 minute
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 minute
    SUM(amount) AS vol                                -- Total transaction volume within 1 minute
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Hour-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 hour', trade_ts) AS candle_ts,     -- Data within 1 hour
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 hour
    MAX(price) AS high,                               -- Highest price within 1 hour
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 hour
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 hour
    SUM(amount) AS vol                                -- Total transaction volume within 1 hour
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

-- Day-level candlestick aggregation
/*+ SET (polar_csi.enable_query on) */
SELECT
    time_bucket('1 day', trade_ts) AS candle_ts,      -- Data within 1 day
    market_id,
    MIN(price) AS low,                                -- Lowest price within 1 day
    MAX(price) AS high,                               -- Highest price within 1 day
    FIRST(price ORDER BY trade_ts) AS open,           -- Opening price within 1 day
    LAST(price ORDER BY trade_ts) AS close,           -- Closing price within 1 day
    SUM(amount) AS vol                                -- Total transaction volume within 1 day
FROM market_trades
WHERE trade_ts >= '2025-06-01 00:00:00' AND trade_ts <= '2025-06-02 00:00:00'
GROUP BY candle_ts, market_id
ORDER BY candle_ts, market_id;

Observações sobre SQL

• /*+ SET (polar_csi.enable_query on) */: força a consulta a usar o plano de execução do índice columnstore. Em alguns cenários, o otimizador pode estimar incorretamente que o armazenamento por linhas é mais eficiente. Utilize este Hint para garantir que a consulta utilize o caminho columnstore.

• time_bucket(bucket_width, ts): função fornecida pelo Time Series Database que agrupa o timestamp ts pelo intervalo especificado em bucket_width.