PolarDB:O que é uma tabela particionada

Visão geral

Em um banco de dados PolarDB for PostgreSQL, uma tabela particionada é uma tabela ou índice fisicamente dividido em partes menores e mais fáceis de gerenciar, chamadas de partições. Cada partição é um objeto independente, com nome próprio e atributos de armazenamento opcionais. Do ponto de vista do administrador de banco de dados, uma tabela particionada tem várias partes que podem ser gerenciadas em conjunto ou separadamente. Isso dá grande flexibilidade ao administrador ao gerenciar tabelas particionadas. Já do ponto de vista da aplicação, uma tabela particionada é idêntica a uma tabela não particionada. Não é necessário modificar consultas SQL ou instruções DML (Data Manipulation Language) para acessar uma tabela particionada.

Todas as partições de uma tabela devem ter os mesmos atributos lógicos, como nomes de colunas, tipos de dados e restrições. No entanto, cada partição pode ter atributos físicos próprios, como compactação ativada ou desativada, configurações de armazenamento físico e tablespaces.

Tabelas particionadas são úteis para diversos tipos de aplicações, especialmente aquelas que lidam com grandes volumes de dados. Bancos de dados de processamento transacional online (OLTP) costumam se beneficiar de melhorias em capacidade de gerenciamento e disponibilidade. Já os data warehouses de processamento analítico online (OLAP) ganham em desempenho e facilidade de gerenciamento.

Cenários

• Particione uma tabela para melhorar o desempenho quando ela ultrapassar a memória física do servidor de banco de dados, por exemplo, quando uma tabela for maior que 2 TB.

• Use uma tabela particionada quando uma tabela grande armazena dados históricos e os novos dados vão para a partição mais recente. Por exemplo, uma tabela armazena 12 meses de dados: o mês atual em uma partição atualizável e os meses anteriores em uma partição somente leitura.

Benefícios

• Maior desempenho de consulta

  Em determinados casos, o desempenho das consultas pode melhorar significativamente, sobretudo quando as linhas mais acessadas de uma tabela estão concentradas em uma única partição ou em um pequeno conjunto de partições. O particionamento substitui, de forma eficiente, os níveis superiores de um índice, aumentando a chance de que as partes mais usadas do índice caibam na memória. Quando uma consulta ou atualização acessa uma única partição ou algumas poucas partições, o desempenho melhora porque uma varredura sequencial dessa partição é usada em vez do índice. Isso evita leituras de acesso aleatório espalhadas por toda a tabela.

• Gerenciamento mais simples

  Objetos particionados têm partes que podem ser gerenciadas em conjunto ou separadamente. Instruções DDL podem operar sobre partições em vez de sobre a tabela ou o índice inteiro. Isso permite fragmentar tarefas que consomem muitos recursos, como reindexar uma tabela. Você pode mover uma partição da tabela por vez. Caso ocorra um problema, basta refazer a movimentação daquela partição, e não da tabela inteira. Além disso, se o design do particionamento levar em conta os padrões de uso, você pode executar cargas e exclusões em lote apenas adicionando ou removendo partições. Usar DROP TABLE para excluir uma única partição ou executar ALTER TABLE DETACH PARTITION é muito mais rápido do que uma operação em lote. Esses comandos também evitam completamente a sobrecarga de VACUUM causada por um DELETE em lote.

• Menor contenção de recursos

  Em alguns sistemas OLTP, o particionamento reduz a contenção por recursos compartilhados. Por exemplo, as operações DML se distribuem por várias partições em vez de se concentrar em apenas uma.

• Maior disponibilidade

  A indisponibilidade de uma partição não significa que a tabela inteira fique indisponível. O otimizador de consultas remove automaticamente do plano de execução as partições que não são referenciadas. Assim, as consultas não são afetadas quando uma partição está indisponível.

• Custos de armazenamento menores

  Dados usados com pouca frequência podem ser movidos para mídias de armazenamento mais baratas e mais lentas, reduzindo custos.

Os benefícios das tabelas particionadas normalmente só valem a pena quando a tabela é muito grande. Use uma tabela particionada quando o tamanho de uma única tabela ultrapassar a memória física do servidor de banco de dados.

Características das tabelas particionadas

Embora a implementação interna das tabelas particionadas seja mais complexa do que a das tabelas padrão, essa complexidade é transparente para o usuário. O gerenciamento e o uso das tabelas particionadas também diferem dos das tabelas padrão. Compreender bem essas características ajuda a garantir o uso correto e eficiente das tabelas particionadas.

Exemplo 1:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

Chave de partição

A chave de partição é uma coluna ou uma combinação de colunas que determina a qual partição cada linha de uma tabela particionada pertence. Uma tabela particionada precisa garantir que cada linha seja atribuída explicitamente a uma partição. O PolarDB for PostgreSQL usa a chave de partição para direcionar automaticamente as operações de inserção, atualização e exclusão para a partição correta.

No Exemplo 1, logdate é a chave de partição da tabela measurement. O limite de cada partição da tabela measurement é determinado pela faixa de valores de logdate.

Estratégias de particionamento

O PolarDB for PostgreSQL oferece várias estratégias de particionamento para controlar como o banco de dados distribui os dados entre as partições:

• Range Partitioning

  A tabela é dividida em "faixas" definidas pela chave de partição. As faixas de valores atribuídas a diferentes partições não se sobrepõem. Por exemplo, você pode particionar por faixas de data ou por faixas de identificadores de um determinado objeto de negócio. Os limites de cada faixa incluem o limite inferior e excluem o limite superior. Por exemplo, se a faixa de uma partição vai de 1 a 10 e a da partição seguinte vai de 10 a 20, o valor 10 pertence à segunda partição, e não à primeira. A tabela measurement do Exemplo 1 é uma tabela particionada por faixa.

  O particionamento por faixa de intervalo (interval range partitioning) é uma extensão do particionamento por faixa. Para mais informações, consulte Interval range partitioning.

• List Partitioning

  Exemplo 2:

  CREATE TABLE department(deptno INT4 Primary Key,dname VARCHAR(50), location VARCHAR(100)) PARTITION BY LIST (deptno);
  CREATE TABLE department_p1 partition of department for values in (10, 20);
  CREATE TABLE department_p2 partition of department for values in (30, 40);

  O particionamento por lista divide uma tabela em partições listando explicitamente os valores da chave que aparecem em cada partição. A tabela department do Exemplo 2 usa o particionamento por lista. Os valores da chave de partição são especificados explicitamente para cada uma de suas partições. Por exemplo, department_p1 armazena apenas linhas em que deptno é 10 ou 20. Já department_p2 armazena apenas linhas em que deptno é 30 ou 40.

• Hash Partitioning

  O particionamento por hash divide uma tabela em partições ao especificar um módulo e um resto para cada partição. Cada partição contém as linhas em que o valor de hash da chave de partição, dividido pelo módulo especificado, produz o resto especificado.

  Exemplo 3:

  create table idxpart (i int) partition by hash (i);
  create table idxpart0 partition of idxpart for values with (modulus 2, remainder 0);
  create table idxpart1 partition of idxpart for values with (modulus 2, remainder 1);

  A tabela idxpart do Exemplo 3 usa o particionamento por hash. Por exemplo, idxpart0 armazena linhas em que o valor de hash de i dividido por 2 tem resto 0. Já idxpart1 armazena linhas em que o valor de hash de i dividido por 2 tem resto 1.

Particionamento em vários níveis

Depois que uma tabela particionada é dividida em partições, essas partições podem, por sua vez, ser particionadas novamente. Uma tabela particionada com essa estrutura recebe o nome de tabela particionada em vários níveis.

Atualmente, o PolarDB for PostgreSQL não impõe um limite para o número de níveis de particionamento. Ainda assim, evite criar níveis em excesso, pois isso pode dificultar o gerenciamento da tabela particionada e também prejudicar o desempenho das consultas. Em geral, recomenda-se uma profundidade de particionamento de no máximo três níveis.

Diferentes estratégias de particionamento podem ser usadas em cada nível. Por exemplo, você pode usar particionamento por faixa no primeiro nível, particionamento por hash no segundo nível e particionamento por lista no terceiro nível.

Exemplo 4:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01') PARTITION BY Hash (city_id);

CREATE TABLE measurement_y2006m03_hash1 PARTITION OF measurement_y2006m03
    for values with (modulus 2, remainder 0) PARTITION BY List (peaktemp);

CREATE TABLE measurement_y2006m03_hash1_l1 PARTITION OF measurement_y2006m03_hash1 for values in (10, 20);

Sintaxe

Para obter informações sobre os comandos e as descrições relacionadas a cada tipo de partição — como criar uma tabela particionada, adicionar partições, mesclar partições, dividir partições e excluir partições —, consulte Partitioned table commands.