AnalyticDB:Definição de tabelas particionadas

Quando usar particionamento

O particionamento é mais eficaz quando os três critérios abaixo são atendidos:

• A tabela é grande. Tabelas com muitas linhas são boas candidatas para particionamento.

• O desempenho da consulta está insatisfatório. Particione apenas tabelas cujas consultas apresentem tempos de resposta acima do aceitável.

• Os predicados da consulta têm padrões de acesso claros. Verifique as cláusulas WHERE nas consultas típicas. Colunas recorrentes são candidatas naturais a chave de partição.

Tipos de particionamento suportados

Escolha da chave de partição

A chave de partição determina a eficácia da eliminação de partições:

• Corresponda aos predicados da consulta. Selecione a coluna ou as colunas mais frequentes nas cláusulas WHERE. A eliminação de partições só funciona quando as condições da consulta incluem a chave de partição.

• Alinhe-se à estratégia de retenção de dados. Se você excluir regularmente dados antigos em massa, projete as partições para concentrar esses dados em uma única partição. Descartar uma partição é muito mais rápido que executar uma instrução DELETE.

• Restrição de chave primária. Se a tabela tiver uma chave primária, a chave de partição deverá ser uma coluna dessa chave. Sem chave primária, qualquer coluna é elegível.

Criação de uma tabela particionada por intervalo

Especifique START, END e EVERY para gerar partições automaticamente dentro de um intervalo. Por padrão, START é inclusivo e END é exclusivo.

Particionamento por data:

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

Particionamento por inteiro com uma partição padrão:

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
  DEFAULT PARTITION extra );

A DEFAULT PARTITION captura linhas fora de todos os intervalos definidos.

Criação de uma tabela particionada por lista

A chave de partição pode ser qualquer coluna com tipo de dado que suporte comparação de valores. Declare uma descrição de valor para cada partição.

CREATE TABLE rank (id int, rank int, year int, gender
char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

Criação de uma tabela particionada multinível

O exemplo a seguir cria uma tabela particionada em três níveis:

• Nível 1: particionado por intervalo com base em year

• Nível 2 (subpartição): particionado por intervalo com base em month

• Nível 3 (subpartição): particionado por lista com base em region

CREATE TABLE sales (id int, year int, month int, day int,
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
    SUBPARTITION BY RANGE (month)
       SUBPARTITION TEMPLATE (
        START (1) END (13) EVERY (1),
        DEFAULT SUBPARTITION other_months )
           SUBPARTITION BY LIST (region)
             SUBPARTITION TEMPLATE (
               SUBPARTITION usa VALUES ('usa'),
               SUBPARTITION europe VALUES ('europe'),
               SUBPARTITION asia VALUES ('asia'),
               DEFAULT SUBPARTITION other_regions )
( START (2002) END (2012) EVERY (1),
  DEFAULT PARTITION outlying_years );

Adição de uma partição

Use ALTER TABLE ... ADD PARTITION para adicionar uma partição a uma tabela particionada existente.

Se a tabela foi criada com um modelo de subpartição, a nova partição será subparticionada automaticamente:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE;

Caso nenhum modelo de subpartição tenha sido usado, defina as subpartições diretamente ao adicionar a partição:

ALTER TABLE sales ADD PARTITION
            START (date '2017-02-01') INCLUSIVE
            END (date '2017-03-01') EXCLUSIVE
      ( SUBPARTITION usa VALUES ('usa'),
        SUBPARTITION asia VALUES ('asia'),
        SUBPARTITION europe VALUES ('europe') );

Para criar subpartições em uma partição existente:

ALTER TABLE sales ALTER PARTITION FOR (RANK(12))
      ADD PARTITION africa VALUES ('africa');

Não é possível adicionar partições a uma tabela que já possui uma partição padrão. Para adicionar partições nesse caso, divida a partição padrão. Consulte Dividir uma partição .

Especificação de um nome de partição

A partir do AnalyticDB for PostgreSQL V6.3.10.9, use a cláusula WITH(tablename=<tablename>) para atribuir nomes explícitos às partições ao criar uma tabela:

CREATE TABLE partition_with_name_list (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY LIST (a)
(
    PARTITION p1 VALUES (1)  WITH (tablename='partition_with_name_list_p1'),
    PARTITION p2 VALUES (2)  WITH (tablename='partition_with_name_list_p2'),
    PARTITION p3 VALUES (3)  WITH (tablename='partition_with_name_list_p3'),
    PARTITION p4 VALUES (4)  WITH (tablename='partition_with_name_list_p4')
);

Renomeação de uma partição

A partir do AnalyticDB for PostgreSQL V6.3.10.9, renomeie uma partição com:

ALTER TABLE partition_with_name_list_p1 RENAME TO partition_with_name_list_p1r;

Divisão de uma partição

Use ALTER TABLE ... SPLIT PARTITION para dividir uma partição em duas.

Limites:

• Se existirem subpartições, apenas a subpartição de nível mais baixo poderá ser dividida.

• O valor de divisão na cláusula AT é atribuído à segunda partição.

Divisão de uma partição existente:

O exemplo a seguir divide uma partição de janeiro de 2017 no dia 16. Os dados de 1º a 15 de janeiro vão para a primeira partição; os dados de 16 a 31 de janeiro vão para a segunda.

ALTER TABLE sales SPLIT PARTITION FOR ('2017-01-01')
AT ('2017-01-16')
INTO (PARTITION jan171to15, PARTITION jan1716to31);

Divisão da partição padrão para adicionar uma nova partição:

Quando uma tabela tem uma partição padrão, divida-a para extrair uma partição nomeada. A partição padrão deve ser a segunda partição na cláusula INTO.

ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2017-01-01') INCLUSIVE
END ('2017-02-01') EXCLUSIVE
INTO (PARTITION jan17, default partition);

Determinação da granularidade da partição

A granularidade da partição envolve um equilíbrio entre a quantidade de dados em cada partição e o número total de partições. Uma granularidade mais fina resulta em menos dados por partição, mas em um número maior de partições.

Como diretriz geral, mantenha o número de partições em 200 ou menos por tabela. Um excesso de partições pode reduzir o desempenho do banco de dados. Por exemplo, o otimizador de consultas leva mais tempo para gerar planos de execução e as operações VACUUM demoram mais para concluir.

Otimização de consultas em tabelas particionadas

O AnalyticDB for PostgreSQL suporta eliminação de partições: quando uma consulta inclui condições sobre a chave de partição, o otimizador verifica apenas as partições correspondentes em vez de toda a tabela.

Verifique se a eliminação está ativa:

EXPLAIN
  SELECT * FROM sales
  WHERE year = 2008
    AND month = 1
    AND day = 3
    AND region = 'usa';

Saída esperada (eliminação funcionando corretamente):

Gather Motion 4:1  (slice1; segments: 4)  (cost=0.00..431.00 rows=1 width=24)
  ->  Sequence  (cost=0.00..431.00 rows=1 width=24)
        ->  Partition Selector for sales (dynamic scan id: 1)  (cost=10.00..100.00 rows=25 width=4)
              Filter: year = 2008 AND month = 1 AND region = 'usa'::text
              Partitions selected:  1 (out of 468)
        ->  Dynamic Table Scan on sales (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=24)
              Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text

Neste plano, Partitions selected: 1 (out of 468) confirma que a eliminação está ativa. Se houver muitas partições selecionadas apesar de condições de filtro específicas, verifique se a coluna do filtro corresponde à chave de partição.

Consulta de definições de partição

Visualize todas as partições de uma tabela na visualização do sistema pg_partitions:

SELECT
  partitionboundary,
  partitiontablename,
  partitionname,
  partitionlevel,
  partitionrank
FROM pg_partitions
WHERE tablename='sales';

Operações relacionadas

Para obter uma lista completa de operações de gerenciamento de partições — incluindo remoção, truncamento e troca de partições — consulte Particionamento de tabelas grandes.

Perguntas frequentes

Como escolher a chave de partição?

Se a tabela tiver uma chave primária, a chave de partição deverá ser uma das colunas dessa chave. Sem chave primária, especifique qualquer coluna como chave de partição. Para obter os melhores resultados de eliminação, escolha a coluna mais frequente nas cláusulas WHERE.