Uso da extensão pg_pathman - PolarDB: Central de Documentação do Alibaba Cloud

O pg_pathman é uma extensão do PolarDB for PostgreSQL que oferece particionamento de tabelas por hash e por intervalo. Ela gerencia a criação de partições e a migração de dados por meio de funções dedicadas, além de gerar planos de execução otimizados para consultas em tabelas particionadas.

Para ativar o recurso de gerenciamento de partições, entre em contato conosco .

Principais recursos

Particionamento por hash e por intervalo — permite particionar por qualquer tipo de coluna compatível, incluindo INT, FLOAT, DATE e domínios personalizados
Gerenciamento automático e manual de partições — crie partições e migre dados automaticamente via funções ou anexe e desanexe tabelas existentes manualmente
Planejamento de consultas otimizado — gera planos de execução eficientes para joins, subconsultas e outros padrões de consulta em tabelas particionadas
Seleção de partição em tempo de execução — utiliza os nós de plano personalizados RuntimeAppend e RuntimeMergeAppend para selecionar as partições corretas durante a consulta
Filtragem dinâmica de partições — o nó PartitionFilter filtra partições com base nas condições da consulta
Propagação automática de partições — cria novas partições automaticamente quando os dados inseridos ultrapassam os limites das partições existentes (apenas para particionamento por intervalo)
Suporte direto a COPY — lê e grava tabelas particionadas diretamente usando COPY FROM/TO
Atualização de chaves de partição — permite atualizar chaves de partição via trigger (evite adicionar o trigger se não houver necessidade, pois isso reduz a performance de escrita)
Callbacks de criação — invoca uma função de callback personalizada sempre que uma partição é criada
Migração de dados sem bloqueio — migra dados de uma tabela primária para partições de forma concorrente, sem bloquear operações normais
Suporte a tabelas externas — insere dados em tabelas externas gerenciadas por Foreign Data Wrappers (FDW); configure usando pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)

Para a referência completa, consulte pg_pathman no GitHub.

Pré-requisitos

Antes de começar, verifique se você tem:

Um cluster PolarDB for PostgreSQL
Permissão para criar extensões (entre em contato com o administrador do cluster em caso de dúvida)

Instale a extensão

CREATE EXTENSION IF NOT EXISTS pg_pathman;

Verifique a versão instalada:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_pathman';

Saída esperada:

  extname   | extversion
------------+------------
 pg_pathman | 1.5
(1 row)

Atualizar a extensão

O PolarDB for PostgreSQL atualiza o pg_pathman regularmente. Para atualizar manualmente, atualize o cluster para a versão mais recente.

Visualize e tabelas

O pg_pathman cria as seguintes tabelas e views de configuração.

pathman_config

Armazena a configuração de partição para cada tabela particionada.

CREATE TABLE IF NOT EXISTS pathman_config (
    partrel         REGCLASS NOT NULL PRIMARY KEY,
    attname         TEXT NOT NULL,
    parttype        INTEGER NOT NULL,
    range_interval  TEXT,
    CHECK (parttype IN (1, 2))
);

Coluna	Descrição
`partrel`	OID da tabela primária
`attname`	Nome da coluna da chave de partição
`parttype`	Tipo de particionamento: `1` = hash, `2` = intervalo
`range_interval`	Intervalo coberto por cada partição

pathman_config_params

Armazena configurações avançadas por tabela.

CREATE TABLE IF NOT EXISTS pathman_config_params (
    partrel        REGCLASS NOT NULL PRIMARY KEY,
    enable_parent  BOOLEAN NOT NULL DEFAULT TRUE,
    auto           BOOLEAN NOT NULL DEFAULT TRUE,
    init_callback  REGPROCEDURE NOT NULL DEFAULT 0
);

Coluna	Descrição
`enable_parent`	Indica se a tabela primária deve ser incluída nos planos de consulta
`auto`	Indica se novas partições devem ser criadas automaticamente
`init_callback`	OID da função de callback de criação de partição

pathman_partition_list

View que lista todas as partições e seus limites.

CREATE OR REPLACE VIEW pathman_partition_list
AS SELECT * FROM show_partition_list();
-- Columns: parent, partition, parttype, partattr, range_min, range_max

pathman_concurrent_part_tasks

View que exibe tarefas ativas de migração de dados em segundo plano.

CREATE OR REPLACE VIEW pathman_concurrent_part_tasks
AS SELECT * FROM show_concurrent_part_tasks();
-- Columns: userid, pid, dbid, relid, processed, status

Gerenciamento de partições

Particionamento por intervalo

Crie partições por intervalo

Use create_range_partitions para particionar uma tabela por um intervalo numérico ou de data:

create_range_partitions(
    relation       REGCLASS,
    attribute      TEXT,
    start_value    ANYELEMENT,
    p_interval     ANYELEMENT,
    p_count        INTEGER DEFAULT NULL,
    partition_data BOOLEAN DEFAULT TRUE
)

Uma variante sobrecarregada aceita o tipo INTERVAL para particionamento baseado em data:

create_range_partitions(
    relation       REGCLASS,
    attribute      TEXT,
    start_value    ANYELEMENT,
    p_interval     INTERVAL,
    p_count        INTEGER DEFAULT NULL,
    partition_data BOOLEAN DEFAULT TRUE
)

Parâmetro	Descrição
`relation`	OID da tabela primária
`attribute`	Coluna da chave de partição
`start_value`	Limite inferior da primeira partição
`p_interval`	Intervalo entre partições — use `ANYELEMENT` para todos os tipos ou `INTERVAL` para chaves de data/hora
`p_count`	Número de partições a criar
`partition_data`	Indica se os dados devem ser migrados imediatamente. Defina como `false` e use `partition_table_concurrently` como alternativa

Alternativamente, use create_partitions_from_range para especificar um valor final explícito em vez de uma contagem de partições:

create_partitions_from_range(
    relation       REGCLASS,
    attribute      TEXT,
    start_value    ANYELEMENT,
    end_value      ANYELEMENT,
    p_interval     ANYELEMENT,
    partition_data BOOLEAN DEFAULT TRUE
)

Exemplo: particionar uma tabela por mês

Crie a tabela primária e insira dados de teste. A coluna da chave de partição deve ter a restrição NOT NULL.

 CREATE TABLE part_test(id int, info text, crt_time timestamp NOT NULL);

 INSERT INTO part_test
 SELECT id, md5(random()::text), clock_timestamp() + (id || ' hour')::interval
 FROM generate_series(1, 10000) t(id);

Crie 24 partições mensais começando em outubro de 2016. Passe false para partition_data para ignorar a migração imediata de dados.

 SELECT create_range_partitions(
     'part_test'::regclass,
     'crt_time',
     '2016-10-25 00:00:00'::timestamp,
     interval '1 month',
     24,
     false
 );

Migre os dados para as partições sem bloquear consultas em andamento:

 -- Before migration, all data is still in the primary table
 SELECT count(*) FROM ONLY part_test;
 -- count: 10000

 SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0);

 -- After migration, the primary table is empty
 SELECT count(*) FROM ONLY part_test;
 -- count: 0

Desative a tabela primária para excluí-la dos planos de execução:

 SELECT set_enable_parent('part_test'::regclass, false);

 EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;

Saída esperada (tabela primária excluída):

                                    QUERY PLAN
 ---------------------------------------------------------------------------------
  Append  (cost=0.00..16.18 rows=1 width=45)
    ->  Seq Scan on part_test_1  (cost=0.00..16.18 rows=1 width=45)
          Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
 (3 rows)

Ao usar particionamento por intervalo:

A coluna da chave de partição deve ter uma restrição NOT NULL .

Crie partições suficientes para cobrir todos os dados existentes.

Migre os dados sem bloqueio usando partition_table_concurrently .

Após a migração, desative a tabela primária com set_enable_parent .

Particionamento por hash

Crie partições por hash

create_hash_partitions(
    relation         REGCLASS,
    attribute        TEXT,
    partitions_count INTEGER,
    partition_data   BOOLEAN DEFAULT TRUE
)

Parâmetro	Descrição
`relation`	OID da tabela primária
`attribute`	Coluna da chave de partição
`partitions_count`	Número de partições
`partition_data`	Indica se os dados devem ser migrados imediatamente. Defina como `false` e use `partition_table_concurrently` como alternativa

As partições por hash funcionam com qualquer tipo de coluna — a função de hash lida com a conversão de tipo automaticamente. O pg_pathman também reescreve consultas de forma transparente, então instruções como SELECT * FROM part_test WHERE crt_time = '...' funcionam corretamente mesmo com partições por hash.

Exemplo: criar 128 partições por hash

Crie a tabela primária e insira dados de teste:

 CREATE TABLE part_test(id int, info text, crt_time timestamp NOT NULL);

 INSERT INTO part_test
 SELECT id, md5(random()::text), clock_timestamp() + (id || ' hour')::interval
 FROM generate_series(1, 10000) t(id);

Crie 128 partições sem migrar os dados ainda:

 SELECT create_hash_partitions('part_test'::regclass, 'crt_time', 128, false);

Migre os dados sem bloqueio:

 SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0);

Desative a tabela primária:

 SELECT set_enable_parent('part_test'::regclass, false);

 EXPLAIN SELECT * FROM part_test WHERE crt_time = '2016-10-25 00:00:00'::timestamp;

Saída esperada (apenas a partição correspondente é verificada):

                                    QUERY PLAN
 ---------------------------------------------------------------------------------
  Append  (cost=0.00..1.91 rows=1 width=45)
    ->  Seq Scan on part_test_122  (cost=0.00..1.91 rows=1 width=45)
          Filter: (crt_time = '2016-10-25 00:00:00'::timestamp without time zone)
 (3 rows)

Ao usar particionamento por hash:

A coluna da chave de partição deve ter uma restrição NOT NULL .

Migre os dados sem bloqueio usando partition_table_concurrently .

Após a migração, desative a tabela primária com set_enable_parent .

Migrar dados para partições

Use partition_table_concurrently para mover dados de uma tabela primária para suas partições sem bloqueios.

partition_table_concurrently(
    relation   REGCLASS,
    batch_size INTEGER DEFAULT 1000,
    sleep_time FLOAT8 DEFAULT 1.0
)

Parâmetro	Descrição
`relation`	OID da tabela primária
`batch_size`	Linhas movidas por transação
`sleep_time`	Segundos de espera antes de tentar novamente um lote bloqueado; a tarefa termina após 60 tentativas falhas

Exemplo:

SELECT partition_table_concurrently('part_test'::regclass, 10000, 1.0);

Monitore as tarefas ativas de migração:

SELECT * FROM pathman_concurrent_part_tasks;

Dividir uma partição por intervalo

Divida uma partição grande em duas em um valor especificado:

split_range_partition(
    partition      REGCLASS,
    split_value    ANYELEMENT,
    partition_name TEXT DEFAULT NULL
)

Parâmetro	Descrição
`partition`	OID da partição a ser dividida
`split_value`	Valor no qual a divisão ocorre
`partition_name`	Nome para a nova partição

Exemplo — dividir part_test_1 (cobrindo de 2016-10-25 a 2016-11-25) em 2016-11-10:

SELECT split_range_partition(
    'part_test_1'::regclass,
    '2016-11-10 00:00:00'::timestamp,
    'part_test_1_2'
);

Após a divisão, os dados são redistribuídos automaticamente:

part_test_1: cobre de 2016-10-25 a 2016-11-10 (373 linhas)
part_test_1_2: cobre de 2016-11-10 a 2016-11-25 (360 linhas)

Mesclar partições por intervalo

Mescle duas partições por intervalo adjacentes em uma só:

merge_range_partitions(partition1 REGCLASS, partition2 REGCLASS)

As partições devem ser adjacentes. A mesclagem de partições não adjacentes retorna um erro:

ERROR:  merge failed, partitions must be adjacent

Exemplo — mesclar part_test_1 e part_test_1_2 de volta em uma só:

SELECT merge_range_partitions('part_test_1'::regclass, 'part_test_1_2'::regclass);

Após a mesclagem, part_test_1_2 é removida e seus dados são mesclados em part_test_1 (733 linhas no total).

Adicionar uma partição por intervalo

Três funções estão disponíveis para adicionar partições por intervalo a uma tabela particionada existente.

Anexar uma partição ao final

Anexa uma nova partição no extremo superior do intervalo, usando o intervalo definido em pathman_config. Observação: o parâmetro de tablespace é aceito na assinatura da função, mas não pode ser especificado.

append_range_partition(
    parent         REGCLASS,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Exemplo:

SELECT append_range_partition('part_test'::regclass);
-- Creates part_test_25 covering 2018-10-25 to 2018-11-25

Anexar uma partição no início

Insere uma nova partição no extremo inferior do intervalo:

prepend_range_partition(
    parent         REGCLASS,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Exemplo:

SELECT prepend_range_partition('part_test'::regclass);
-- Creates part_test_26 covering 2016-09-25 to 2016-10-25

Adicionar uma partição com um intervalo explícito

Cria uma partição para qualquer intervalo sem sobreposição, incluindo lacunas no intervalo existente:

add_range_partition(
    relation       REGCLASS,
    start_value    ANYELEMENT,
    end_value      ANYELEMENT,
    partition_name TEXT DEFAULT NULL,
    tablespace     TEXT DEFAULT NULL
)

Exemplo — adicione uma partição para janeiro de 2020 sem preencher a lacuna entre 2018 e 2020:

SELECT add_range_partition(
    'part_test'::regclass,
    '2020-01-01 00:00:00'::timestamp,
    '2020-02-01 00:00:00'::timestamp
);
-- Creates part_test_27 covering 2020-01-01 to 2020-02-01

Remover partições

Remover uma única partição

drop_range_partition(
    partition   TEXT,
    delete_data BOOLEAN DEFAULT TRUE
)

Defina delete_data como false para mover os dados da partição de volta para a tabela primária antes de removê-la. Defina como true para excluir os dados permanentemente.

Exemplos:

-- Move data to the primary table, then drop
SELECT drop_range_partition('part_test_1', false);
SELECT drop_range_partition('part_test_2', false);

-- Delete the partition and its data permanently
SELECT drop_range_partition('part_test_3', true);

Remover todas as partições

drop_partitions(
    parent      REGCLASS,
    delete_data BOOLEAN DEFAULT FALSE
)

Quando delete_data é false (o padrão), os dados são copiados de volta para a tabela primária antes da remoção das partições.

Exemplo:

SELECT drop_partitions('part_test'::regclass, false);

Anexar uma tabela como partição

Anexe uma tabela existente como uma partição por intervalo de uma tabela primária. A tabela deve ter o mesmo esquema da tabela primária (mesmas colunas e mesmo histórico de colunas removidas conforme rastreado em pg_attribute).

attach_range_partition(
    relation    REGCLASS,
    partition   REGCLASS,
    start_value ANYELEMENT,
    end_value   ANYELEMENT
)

Exemplo:

-- Create a standalone table with the same schema
CREATE TABLE part_test_1 (LIKE part_test INCLUDING ALL);

-- Attach it as a partition covering January 2019
SELECT attach_range_partition(
    'part_test'::regclass,
    'part_test_1'::regclass,
    '2019-01-01 00:00:00'::timestamp,
    '2019-02-01 00:00:00'::timestamp
);

O pg_pathman adiciona o relacionamento de herança e a restrição de verificação automaticamente.

Desanexar uma partição

Desanexe uma partição da tabela primária, convertendo-a de volta para uma tabela independente. Os dados são preservados; apenas o relacionamento de herança e as restrições são removidos.

detach_range_partition(partition REGCLASS)

Exemplo:

-- Before detaching: part_test has 9256 rows, part_test_2 has 733 rows
SELECT detach_range_partition('part_test_2');
-- After: part_test has 8523 rows; part_test_2 is now a standalone table with 733 rows

Gerenciamento avançado de partições

Desativar a tabela primária

Após migrar todos os dados para as partições, exclua a tabela primária dos planos de execução:

set_enable_parent(relation REGCLASS, value BOOLEAN)

O padrão depende do parâmetro partition_data usado durante o particionamento inicial:

Se partition_data foi true, os dados foram migrados imediatamente e a tabela primária é desativada automaticamente.
Se partition_data foi false, a tabela primária permanece ativada até que você a desative explicitamente.

Exemplo:

SELECT set_enable_parent('part_test', false);

Ative ou desativar a propagação automática de partições

Para particionamento por intervalo, o pg_pathman pode criar novas partições automaticamente quando os dados inseridos ultrapassam os limites das partições existentes.

set_auto(relation REGCLASS, value BOOLEAN)
-- Default: true (enabled)

Importante

Desative a propagação automática de partições para a maioria das cargas de trabalho de produção. Se os dados inseridos estiverem muito fora do intervalo atual, o pg_pathman precisará criar muitas partições intermediárias para preencher a lacuna, o que pode levar muito tempo. Por exemplo, inserir uma linha com crt_time = '2222-01-01' em uma tabela particionada mensalmente começando em 2016 exige a criação de milhares de partições.

Exemplo:

SELECT set_auto('part_test', false);

Desativar o pg_pathman para uma tabela

Remova o gerenciamento do pg_pathman de uma tabela primária. Isso exclui a entrada da tabela de pathman_config e remove quaisquer triggers associados, mas mantém as partições existentes e seus dados intactos.

Importante

disable_pathman_for é irreversível. Após chamar esta função, o pg_pathman deixa de gerenciar as partições da tabela e os nós de varredura personalizados deixam de ser usados nos planos de execução.

SELECT disable_pathman_for('part_test');

Após a desativação, os planos de execução retornam ao comportamento padrão de herança do PostgreSQL (incluindo a tabela primária nas varreduras, mesmo que esteja vazia):

EXPLAIN SELECT * FROM part_test WHERE crt_time = '2017-06-25 00:00:00'::timestamp;

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Append  (cost=0.00..16.00 rows=2 width=45)
   ->  Seq Scan on part_test  (cost=0.00..0.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
   ->  Seq Scan on part_test_10  (cost=0.00..16.00 rows=1 width=45)
         Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)

Configure um callback de criação de partição

Registre uma função de callback que o pg_pathman chama sempre que uma partição é criada (tanto para partições por hash quanto por intervalo):

set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)

O callback deve ter esta assinatura:

part_init_callback(args JSONB) RETURNS VOID

O objeto JSON args contém campos diferentes dependendo do tipo de partição:

/* Range partition */
{
    "parent":    "abc",
    "parttype":  "2",
    "partition": "abc_4",
    "range_max": "401",
    "range_min": "301"
}

/* Hash partition */
{
    "parent":    "abc",
    "parttype":  "1",
    "partition": "abc_0"
}

Exemplo: registrar eventos de criação de partição

Crie uma função de callback que registre metadados da partição em uma tabela de auditoria:

 CREATE OR REPLACE FUNCTION f_callback_test(jsonb) RETURNS void AS
 $$
 DECLARE
 BEGIN
     CREATE TABLE IF NOT EXISTS rec_part_ddl(
         id        serial primary key,
         parent    name,
         parttype  int,
         partition name,
         range_max text,
         range_min text
     );
     IF ($1->>'parttype')::int = 1 THEN
         INSERT INTO rec_part_ddl(parent, parttype, partition)
         VALUES (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
     ELSIF ($1->>'parttype')::int = 2 THEN
         INSERT INTO rec_part_ddl(parent, parttype, partition, range_max, range_min)
         VALUES (
             ($1->>'parent')::name,
             ($1->>'parttype')::int,
             ($1->>'partition')::name,
             $1->>'range_max',
             $1->>'range_min'
         );
     END IF;
 END;
 $$ LANGUAGE plpgsql STRICT;

Registre o callback e crie partições:

 CREATE TABLE tt(id int, info text, crt_time timestamp NOT NULL);

 SELECT set_init_callback('tt'::regclass, 'f_callback_test'::regproc);

 SELECT create_range_partitions(
     'tt'::regclass,
     'crt_time',
     '2016-10-25 00:00:00'::timestamp,
     interval '1 month',
     24,
     false
 );

Verifique se o callback foi invocado para cada partição:

 SELECT * FROM rec_part_ddl;

Saída esperada (24 linhas, uma por partição):

  id | parent | parttype | partition |      range_max      |      range_min
 ----+--------+----------+-----------+---------------------+---------------------
   1 | tt     |        2 | tt_1      | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
   2 | tt     |        2 | tt_2      | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
 ...
  24 | tt     |        2 | tt_24     | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
 (24 rows)

Notas de uso

Cenário	Orientação
Coluna da chave de partição	Deve ter uma restrição `NOT NULL` tanto para particionamento por hash quanto por intervalo
Migração inicial de dados	Passe `partition_data = false` ao criar partições e, em seguida, chame `partition_table_concurrently` para migração sem bloqueio
Tabela primária após migração	Chame `set_enable_parent(..., false)` após migrar todos os dados para excluir a tabela primária vazia dos planos de execução
Propagação automática	Desative com `set_auto(..., false)` para a maioria das cargas de trabalho a fim de evitar a criação não intencional de um grande número de partições
Atualizações de chave de partição	Adicionar um trigger permite atualizações de chave de partição, mas reduz a performance de escrita; adicione o trigger apenas se necessário
Mesclagem de partições	Apenas partições por intervalo podem ser mescladas, e somente se forem adjacentes
Desativação do pg_pathman	`disable_pathman_for` é irreversível — remove o gerenciamento do pg_pathman, mas preserva as partições e os dados

Próximos passos

pg_pathman no GitHub — referência completa da API e exemplos adicionais