PolarDB:Tabelas particionadas

Introdução

Ao particionar uma tabela no PolarDB for PostgreSQL (Compatible with Oracle), você separa fisicamente uma tabela ou índice em partes menores, chamadas partições, para facilitar o gerenciamento. Cada partição é um objeto com nome e propriedades de armazenamento próprios. Os administradores de banco de dados podem gerenciar as partições de forma coletiva ou individual, o que proporciona muito mais flexibilidade ao trabalho. No entanto, as aplicações não percebem o particionamento das tabelas. Não é necessário modificar instruções de consulta e comandos DML existentes para utilizar o particionamento de tabelas.

As partições de uma tabela particionada compartilham as mesmas propriedades lógicas, como nomes de colunas, tipos de dados e restrições, mas podem ter propriedades físicas diferentes, como compressão, configuração de armazenamento físico e tablespace.

O particionamento traz benefícios para diversos tipos de aplicações, especialmente aquelas que lidam com grandes volumes de dados. Para bancos de dados OLTP, o particionamento melhora a capacidade de gerenciamento e a disponibilidade. Já para bancos de dados OLAP, os principais benefícios estão no desempenho e na facilidade de gestão.

Cenário

• Particione uma tabela quando ela estiver excessivamente grande, por exemplo, quando o tablespace for maior que a memória física do servidor de banco de dados. Essa prática pode melhorar o desempenho do banco de dados. Considere particionar uma tabela se ela tiver mais de 2 GB.

• Utilize o particionamento em tabelas que armazenam dados históricos e continuam recebendo novos registros. Suponha que uma tabela grande armazene dados de um ano inteiro. Nesse caso, armazene os dados do mês mais recente em uma partição separada e atualizável, e os dados dos meses anteriores em outras partições somente leitura.

Benefícios

• Maior desempenho

  O particionamento pode melhorar significativamente o desempenho das consultas, principalmente quando as linhas acessadas com mais frequência ficam concentradas em uma ou poucas partições. Ele substitui os níveis superiores de uma árvore de índices. Quando uma consulta envolve apenas uma ou algumas partições específicas, o sistema de banco de dados realiza uma varredura sequencial nessas partições, sem depender de índices. Isso aumenta o desempenho e facilita o gerenciamento, pois o sistema processa blocos contíguos de dados em vez de registros espalhados por toda a tabela.

• Gerenciamento simplificado

  Por serem objetos independentes, as partições podem ser gerenciadas individualmente ou em conjunto. Como é possível executar operações DDL nas partições, além de toda a tabela ou índice, o particionamento permite dividir tarefas que consomem muitos recursos, como reconstrução de índices ou tabelas. É possível mover uma única partição por vez. Assim, quando ocorre um problema, basta mover a partição afetada, em vez de toda a tabela. Além disso, execute operações em lote nos registros de dados usando as partições como unidade. Por exemplo, para remover dados de uma tabela, use DROP TABLE para excluir a partição relevante ou ALTER TABLE DETACH PARTITION para desanexar a partição da tabela pai. Após essas operações, não é necessário executar VACUUM para recuperar espaço de armazenamento, o que representa outra vantagem em relação à exclusão em lote.

• Menor contenção de recursos

  Em alguns bancos de dados OLTP, o particionamento reduz a disputa por recursos, como durante a execução de uma instrução DML em várias partições.

• Maior disponibilidade

  Se uma partição ficar indisponível, o restante da tabela particionada ainda poderá ser acessado. O otimizador de consultas remove automaticamente as partições indisponíveis dos planos de consulta para evitar impactos nas operações.

• Custos de armazenamento reduzidos

  As partições raramente acessadas podem ser transferidas para mídias de armazenamento mais lentas e econômicas, gerando economia de custos.

Observe que apenas tabelas excessivamente grandes se beneficiam do particionamento. Recomendamos particionar uma tabela quando seu tamanho atingir o da memória física do servidor de banco de dados.

Como funciona

O particionamento adiciona complexidade à estrutura, mas isso é transparente para os usuários. Esta seção descreve as características e o mecanismo do particionamento. Compreender esses conceitos ajuda você a usar tabelas particionadas com eficiência.

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 particionamento

Uma chave de particionamento consiste em uma única coluna ou em uma combinação de várias colunas. Ela determina a qual partição cada registro pertence. Em uma tabela particionada, cada linha deve ser atribuída explicitamente a uma única partição. O PolarDB for PostgreSQL (Compatible with Oracle) roteia automaticamente as operações nos registros de dados, como inserção, atualização ou exclusão, para as partições correspondentes com base na chave de particionamento.

No Exemplo 1, logdate é a chave de particionamento da tabela particionada chamada measurement. Os limites das partições da tabela measurement são definidos pelos valores de logdate.

Estratégia de particionamento

O PolarDB for PostgreSQL (Compatible with Oracle) oferece suporte a diversas estratégias de particionamento.

• Particionamento por intervalo

  Os dados são distribuídos com base em um intervalo de valores da chave de particionamento, como datas ou identificadores específicos do seu negócio. Cada partição possui um limite superior não inclusivo. Por exemplo, se os valores da chave de particionamento de uma partição variam de 1 a 10 e os de outra variam de 10 a 20, o valor 10 pertence à segunda partição, não à primeira. A tabela measurement no Exemplo 1 é uma tabela particionada por intervalo.

  O particionamento por intervalo estendido é uma estratégia derivada do particionamento por intervalo. Para obter mais informações, consulte Particionamento por intervalo estendido.

• Particionamento por lista

  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_p1 partition of department for values in (30, 40);

  Ao aplicar o particionamento por lista em uma tabela, especifique a lista de valores-chave para cada partição. A tabela department no Exemplo 2 utiliza essa estratégia. Os valores permitidos para a chave de particionamento em cada partição são definidos explicitamente. Na partição department_p1, por exemplo, os registros são armazenados apenas se o valor de deptno for 10 ou 20. Já na partição department_p2, todos os valores da coluna deptno são 30 ou 40.

• Particionamento por hash

  Esse tipo de particionamento utiliza um módulo e um resto especificados. Cada partição armazena os registros cujo valor de hash da chave de particionamento, dividido pelo módulo definido, resulta no 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);

  Neste exemplo, a tabela idxpart utiliza particionamento por hash. Na partição idxpart0, os valores de hash de i de todos os registros, divididos por 2, resultam em resto 0. Na partição idxpart1, a divisão dos valores de hash de i por 2 resulta em resto 1.

Particionamento multinível

O particionamento multinível consiste em particionar uma tabela já particionada, criando subpartições que também podem ser subdivididas.

O PolarDB for PostgreSQL (Compatible with Oracle) suporta um número ilimitado de níveis de particionamento, mas recomendamos não ultrapassar três níveis. O excesso de níveis pode comprometer a capacidade de gerenciamento das partições e até mesmo degradar o desempenho das consultas.

É possível combinar diferentes estratégias de particionamento em cada nível. Por exemplo, utilize particionamento por intervalo no primeiro nível, por hash no segundo e por lista no terceiro.

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 mais informações sobre os comandos de gerenciamento de partições, como criação, adição, mesclagem, divisão e exclusão de partições, consulte Lista de comandos para tabelas particionadas.