PolarDB:Análise estatística de colunas específicas no modo de tabela larga

Como funciona

Aplicações SaaS frequentemente armazenam dezenas ou até centenas de colunas por linha. Consultas analíticas geralmente acessam apenas algumas delas, o que gera dois problemas quando o mecanismo de armazenamento por linhas (row store) as processa:

• O mecanismo lê todas as colunas de cada linha correspondente, inclusive as irrelevantes, o que aumenta significativamente a E/S.

• Índices compostos atendem apenas aos padrões de filtro para os quais foram criados. Se as condições da consulta mudarem, os índices existentes perdem a eficácia.

O índice columnstore armazena cada coluna independentemente no disco. A leitura de uma coluna não afeta as demais, e o índice funciona independentemente das colunas filtradas ou da ordem das condições de filtro.

Desempenho

Com um conjunto de dados de 100 milhões de linhas e grau de paralelismo (DOP) igual a 4:

O índice columnstore oferece uma aceleração de 30 vezes em relação à execução paralela nativa do PostgreSQL.

Pré-requisitos

Antes de começar, verifique se:

• Seu cluster executa uma das seguintes versões:

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

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

  Verifique a versão secundária do mecanismo no console do PolarDB ou execute SHOW polardb_version; . Caso a versão não atenda ao requisito, atualize a versão secundária do mecanismo .

• A tabela de source possui uma chave primária, e a coluna da chave primária está incluída no índice columnstore.

• O parâmetro wal_level está definido como logical para habilitar o suporte à replicação lógica no write-ahead log (WAL).

  Importante

  A alteração do parâmetro wal_level reinicia o cluster. Defina o parâmetro no console e planeje uma janela de manutenção antes de aplicar a mudança.

Etapa 1: Ativar o índice columnstore

O procedimento varia conforme a versão secundária do mecanismo.

CREATE EXTENSION polar_csi;

Etapa 2: Criar uma tabela larga e um índice columnstore

Este exemplo utiliza uma tabela de 24 colunas chamada widecolumntable. A consulta analisa cinco colunas — id_1, domain, consumption, start_time e end_time — para calcular o valor total gasto por cada cliente em múltiplos domínios no último ano.

1. Crie a tabela e insira seus dados de teste:

   CREATE TABLE widecolumntable (
     id_1          BIGINT NOT NULL PRIMARY KEY,
     id_2          BIGINT,
     id_3          BIGINT,
     id_4          BIGINT,
     id_5          BIGINT,
     id_6          BIGINT,
     version       INT,
     domain        TEXT,
     consumption   DECIMAL(18,3),
     c_level       CHARACTER VARYING(1) NOT NULL,
     priority      BIGINT,
     operator      TEXT,
     notify_policy TEXT,
     call_id       UUID NOT NULL,
     provider_id   BIGINT NOT NULL,
     name_1        TEXT NOT NULL,
     name_2        TEXT NOT NULL,
     name_3        TEXT,
     start_time    TIMESTAMP WITH TIME ZONE NOT NULL,
     end_time      TIMESTAMP WITH TIME ZONE NOT NULL,
     comment       JSONB NOT NULL,
     description   TEXT[] NOT NULL,
     created_at    TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
     updated_at    TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
   );

2. Crie um índice columnstore nas cinco colunas a serem analisadas. Inclua a coluna da chave primária (id_1).

   CREATE INDEX idx_wide_csi ON widecolumntable USING CSI(id_1, domain, consumption, start_time, end_time);

Etapa 3: Executar a consulta

A consulta a seguir calcula o valor total gasto por cada cliente em cada domínio no último ano, ordenado pelo valor total. Execute-a com o índice columnstore ativado e depois desativado para comparar o desempenho.

Com o índice columnstore (DOP = 4):

-- Enable the columnstore index and set the degree of parallelism to 4.
SET polar_csi.enable_query TO on;
// High kernel version
SET polar_csi.max_parallel_workers TO 4;
// Low kernel version
SET polar_csi.exec_parallel TO 4;

-- Q1
EXPLAIN ANALYZE
SELECT id_1, domain, SUM(consumption)
FROM widecolumntable
WHERE start_time > '20230101' AND end_time < '20240101'
GROUP BY id_1, domain
ORDER BY SUM(consumption);

Com o mecanismo row store (DOP = 4):

-- Disable the columnstore index and use the row store engine with DOP 4.
SET polar_csi.enable_query TO off;
SET max_parallel_workers_per_gather TO 4;

-- Q1
EXPLAIN ANALYZE
SELECT id_1, domain, SUM(consumption)
FROM widecolumntable
WHERE start_time > '20230101' AND end_time < '20240101'
GROUP BY id_1, domain
ORDER BY SUM(consumption);

Compare os valores de Execution Time na saída do EXPLAIN ANALYZE. Com o índice columnstore, a Q1 é concluída em 7,9 s contra 243 s do mecanismo row store — uma melhoria de 30 vezes.

Limitações e considerações

Observe as seguintes restrições antes de implantar o índice columnstore em produção:

• Primary key required: A tabela de source deve possuir uma chave primária. A coluna da chave primária precisa estar incluída na definição do índice columnstore.

• `wal_level` change restarts the cluster: Definir wal_level como logical aciona uma reinicialização do cluster. Programe essa alteração durante uma janela de manutenção.

• Clusters de nó único: Não é possível adicionar um nó somente leitura com índice columnstore (nó IMCI) a um cluster de nó único. O cluster deve ter pelo menos um nó somente leitura existente.

• Compartilhamento de memória ao usar a extensão pré-instalada: Sem um nó IMCI dedicado, o mecanismo columnstore utiliza apenas 25% da memória do nó. Sob cargas pesadas de AP, essa restrição limita o throughput e pode afetar as cargas de TP executadas no mesmo nó.

• Escopo no nível de banco de dados para polar_csi: Em versões secundárias mais antigas que utilizam a extensão polar_csi, a extensão tem escopo por banco de dados. Instale-a separadamente em cada banco onde precisar de suporte ao índice columnstore.

• Conta privilegiada necessária para instalação da extensão: Apenas uma conta de banco de dados privilegiada pode instalar a extensão polar_csi.