PolarDB tabela particionada - cenários e benefícios - PolarDB: Central de Documentação do Alibaba Cloud

Este tópico descreve os benefícios e os recursos das tabelas particionadas no PolarDB for PostgreSQL.

Visão geral

Em um banco de dados PolarDB for PostgreSQL, uma tabela particionada é uma tabela ou índice dividido fisicamente em partes menores e mais gerenciáveis, chamadas partições. Cada partição é um objeto independente com nome próprio e atributos de armazenamento opcionais. Da perspectiva de um administrador de banco de dados, uma tabela particionada tem várias partes que podem ser gerenciadas em conjunto ou separadamente, oferecendo grande flexibilidade no gerenciamento. Da perspectiva de um aplicativo, porém, uma tabela particionada é idêntica a uma tabela não particionada: você não precisa modificar consultas SQL nem instruções de Linguagem de Manipulação de Dados (DML) para acessá-la.

Cada partição de uma tabela deve 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 distintos, como compressão ativada ou desativada, configurações de armazenamento físico e tablespaces.

As tabelas particionadas são úteis para muitos tipos de aplicativos, especialmente aqueles que gerenciam grandes volumes de dados. Bancos de dados de processamento de transações online (OLTP) frequentemente se beneficiam de melhorias na capacidade de gerenciamento e na disponibilidade. Data warehouses de processamento analítico online (OLAP) se beneficiam de melhorias no desempenho e na capacidade 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 a tabela tiver mais de 2 TB.
Use uma tabela particionada quando uma tabela grande armazenar dados históricos e os novos dados forem 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

Melhor desempenho de consultas
Em alguns casos, o desempenho das consultas pode melhorar significativamente, especialmente quando as linhas mais acessadas de uma tabela estão em uma única partição ou em poucas partições. O particionamento substitui efetivamente os níveis superiores de um índice, aumentando a probabilidade de que as partes mais utilizadas do índice caibam na memória. Quando uma consulta ou atualização acessa uma única partição ou poucas partições, o desempenho pode melhorar porque uma varredura sequencial dessa partição é usada em vez de um índice. Isso evita leituras de acesso aleatório espalhadas por toda a tabela.
Gerenciamento simplificado
Objetos particionados têm partes que podem ser gerenciadas em conjunto ou separadamente. Instruções DDL podem operar em partições em vez de em toda a tabela ou índice, permitindo dividir tarefas com uso intenso de recursos, como a reindexação de uma tabela. Você pode mover uma partição de tabela por vez: se ocorrer um problema, basta refazer a movimentação dessa partição, não da tabela inteira. Além disso, se o design do particionamento considerar padrões de uso, é possível realizar cargas e exclusões em lote 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 eliminam completamente a sobrecarga de VACUUM causada por um DELETE em lote.
Menor contenção de recursos
Em alguns sistemas OLTP, o particionamento pode reduzir a contenção por recursos compartilhados. Por exemplo, as operações DML são distribuídas entre várias partições em vez de uma única.
Disponibilidade aprimorada
A indisponibilidade de uma partição não significa que toda a tabela esteja indisponível. O otimizador de consultas remove automaticamente do plano de execução as partições não referenciadas. Portanto, as consultas não são afetadas quando uma partição está indisponível.
Menor custo de armazenamento
Dados utilizados com pouca frequência podem ser movidos para mídias de armazenamento mais baratas e lentas, reduzindo custos.

Os benefícios das tabelas particionadas geralmente só se tornam relevantes 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.

Recursos 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 os usuários. O gerenciamento e o uso de tabelas particionadas também diferem das tabelas padrão. Compreender esses recursos claramente 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

Uma 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 deve garantir que cada linha seja explicitamente atribuída 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 pelo intervalo 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:

Particionamento por intervalo
A tabela é particionada em "intervalos" definidos pela chave de partição. Os intervalos de valores atribuídos a diferentes partições não se sobrepõem. Por exemplo, você pode particionar por intervalos de datas ou por intervalos de identificadores de um objeto de negócio específico. Os limites de cada intervalo são inclusivos no limite inferior e exclusivos no limite superior. Por exemplo, se o intervalo de uma partição vai de 1 a 10 e o intervalo da próxima vai de 10 a 20, o valor 10 pertence à segunda partição, não à primeira. A tabela measurement do Exemplo 1 é uma tabela com particionamento por intervalo.
O particionamento por intervalo automático é uma extensão do particionamento por intervalo. Para mais informações, consulte Particionamento por intervalo automático.
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_p2 partition of department for values in (30, 40);
```
O particionamento por lista divide uma tabela em partições listando explicitamente os valores de chave que aparecem em cada partição. A tabela department do Exemplo 2 usa particionamento por lista. Os valores da chave de partição são especificados explicitamente para cada uma das suas partições. Por exemplo, department_p1 armazena apenas linhas em que deptno é 10 ou 20. department_p2 armazena apenas linhas em que deptno é 30 ou 40.
Particionamento por hash
O particionamento por hash divide uma tabela em partições especificando um módulo e um resto para cada partição. Cada partição armazena as linhas nas quais o valor 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 particionamento por hash. Por exemplo, idxpart0 armazena as linhas em que o valor hash de i dividido por 2 tem resto 0. idxpart1 armazena as linhas em que o valor hash de i dividido por 2 tem resto 1.

Particionamento multinível

Após uma tabela particionada ser dividida em partições, essas partições podem ser particionadas novamente. Esse tipo de tabela é chamado de tabela particionada multinível.

O PolarDB for PostgreSQL não limita atualmente o número de níveis de particionamento. No entanto, evite criar muitos níveis, pois isso pode tornar o gerenciamento da tabela particionada mais difícil e também pode degradar o desempenho das consultas. Geralmente recomenda-se uma profundidade de particionamento de três níveis ou menos.

Diferentes estratégias de particionamento podem ser usadas em diferentes níveis. Por exemplo, você pode usar o particionamento por intervalo no primeiro nível, o particionamento por hash no segundo nível e o 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 relacionados a cada tipo de partição, como criação de tabelas particionadas, adição de partições, mesclagem de partições, divisão de partições e exclusão de partições, consulte Comandos de tabela particionada.