PolarDB:Uso de IMCI em tabela particionada

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.

   • O parâmetro wal_level deve estar definido como logical. Essa configuração adiciona as informações necessárias para decodificação lógica ao log write-ahead (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 source 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 IMCI.

   O método para ativar o IMCI varia conforme a 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 descreve 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 utiliza 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 row store.
   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	Nós somente leitura IMCI geram 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 a quantidade 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)

   Em 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 alvo. Na linha da extensão polar_csi, clique em Install na coluna Actions. Na caixa de diálogo Install Extension, selecione a Database Account alvo e clique em OK para instalar a extensão no banco de dados desejado.

   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

Este cenário cria uma tabela particionada em vários níveis, insere cerca de 320 milhões de linhas (~16 GB) de dados simulados e, em seguida, executa análises estatísticas com base nas condições de partição.

O schema da tabela particionada de teste é o seguinte:

• sales: a tabela principal.

• sales_2023: particionada por ano.

  • sales_2023_a: particionada por mês; os meses de 1 a 6 são definidos como partição a.

  • sales_2023_b: particionada por mês; os meses de 7 a 12 são definidos como partição b.

• sales_2024: particionada por ano.

  • sales_2024_a: particionada por mês; os meses de 1 a 6 são definidos como partição a.

  • sales_2024_b: particionada por mês; os meses de 7 a 12 são definidos como partição b.

1. Crie uma tabela particionada em vários níveis chamada sales, com a coluna de tempo sale_date como chave de partição. A definição é a seguinte:

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. Gere os dados e insira-os na tabela particionada (cerca de 16 GB).

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. Crie um IMCI na tabela e inclua as colunas sale_id, product_id, sale_date e amount no IMCI.

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

Etapa 3: Executar as consultas

Execute consultas usando diferentes mecanismos de execução. Três consultas (Q1, Q2 e Q3) são geradas com base em diferentes condições de partição.

• Use o IMCI.

  --- Enable the IMCI and set the query 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 sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

  Nota

  O parâmetro polar_csi.max_parallel_workers era anteriormente denominado polar_csi.exec_parallel em versões anteriores do kernel. Para versões do kernel que não suportam polar_csi.max_parallel_workers, use polar_csi.exec_parallel em seu lugar.

  • PostgreSQL 14:

    • Use polar_csi.exec_parallel nas versões 2.0.14.20.42.0 e anteriores.

    • Use polar_csi.max_parallel_workers nas versões 2.0.14.20.43.0 e posteriores.

  • PostgreSQL 16:

    • Use polar_csi.exec_parallel nas versões 2.0.16.11.15.0 e anteriores.

    • Use polar_csi.max_parallel_workers nas versões 2.0.16.13.16.0 e posteriores.

• Desative o IMCI e use o mecanismo row-store.

  --- Disable the IMCI, use the row-store engine, and set the query degree of parallelism to 4.
  SET polar_csi.enable_query to off;
  SET max_parallel_workers_per_gather to 4;
  
  --- Q1
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
  --- Q2
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
  --- Q3
  EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;