Gerenciar partições do PostgreSQL com a extensão pg_partman - Amazon Aurora

Gerenciar partições do PostgreSQL com a extensão pg_partman

O particionamento de tabelas PostgreSQL fornece um framework para processamento de alta performance de entrada e relatórios de dados. Use o particionamento para bancos de dados que exigem entrada muito rápida de grandes quantidades de dados. O particionamento também fornece consultas mais rápidas de tabelas grandes. O particionamento ajuda a manter os dados sem afetar a instância do banco de dados, pois requer menos recursos de E/S.

Ao usar o particionamento, você pode dividir dados em blocos de tamanho personalizado para processamento. Por exemplo, você pode particionar dados de séries temporais para intervalos como por hora, diário, semanal, mensal, trimestral, anual, personalizado ou qualquer combinação destes. Para um exemplo de dados de séries temporais, se você particionar a tabela por hora, cada partição conterá uma hora de dados. Se você particionar a tabela de séries temporais por dia, as partições manterão dados de um dia e assim por diante. A chave de partição controla o tamanho de uma partição.

Quando você usa um comando SQL INSERT ou UPDATE em uma tabela particionada, o mecanismo de banco de dados roteia os dados para a partição apropriada. As partições de tabela PostgreSQL que armazenam os dados são tabelas filhas da tabela principal.

Durante as leituras de consulta de banco de dados, o otimizador PostgreSQL examina a cláusula WHERE da consulta e, se possível, direciona a verificação do banco de dados apenas para as partições relevantes.

A partir da versão 10, o PostgreSQL usa o particionamento declarativo para implementar o particionamento de tabela. Isso também é conhecido como particionamento nativo do PostgreSQL. Antes do PostgreSQL versão 10, você usou gatilhos para implementar partições.

O particionamento de tabelas PostgreSQL fornece os seguintes recursos:

  • Criação de novas partições a qualquer momento.

  • Intervalos variáveis de partição.

  • Partições destacáveis e reanexáveis usando instruções DDL (Data Definition Language, linguagem de definição de dados).

    Por exemplo, partições destacáveis são úteis para remover dados históricos da partição principal, mas manter dados históricos para análise.

  • Novas partições herdam as propriedades da tabela do banco de dados pai, incluindo:

    • Índices

    • Chaves primárias, que devem incluir a coluna de chave de partição

    • Chaves externas

    • Restrições de verificação

    • Referências

  • Criação de índices para a tabela completa ou cada partição específica.

Você não pode alterar o esquema de uma partição individual. No entanto, você pode fazer uma alteração na tabela pai (adicionando uma nova coluna, por exemplo) que se propaga para as partições.

Visão geral da extensão pg_partman do PostgreSQL

Você pode usar a extensão pg_partman do PostgreSQL para automatizar a criação e a manutenção de partições de tabelas. Para obter mais informações gerais, consulte PG Partition Manager na documentação pg_partman.

nota

A extensão pg_partman é compatível com o Aurora PostgreSQL versões 12.6 e posteriores.

Em vez de ter que criar manualmente cada partição, você ajusta o pg_partman com as seguintes configurações:

  • Tabela a ser particionada

  • Tipo de partição

  • Chave de partição

  • Granularidade de partição

  • Opções de pré-criação e gerenciamento de partições

Depois de criar uma tabela particionada do PostgreSQL, registre-a com pg_partman chamando a função create_parent. Fazer isso cria as partições necessárias com base nos parâmetros que você passa para a função.

A extensão pg_partman também fornece a função run_maintenance_proc, que você pode chamar de maneira programada para gerenciar as partições automaticamente. Para garantir que as partições adequadas sejam criadas conforme necessário, agende essa função para ser executada periodicamente (por hora, por exemplo). Você também pode garantir que as partições sejam descartadas automaticamente.

Ativar a extensão pg_partman

Se você tiver vários bancos de dados dentro da mesma instância de banco de dados PostgreSQL para a qual deseja gerenciar partições, habilite a extensão pg_partman separadamente para cada banco de dados. Para habilitar a extensão pg_partman para um banco de dados específico, crie o esquema de manutenção de partição e crie a extensão pg_partman da maneira a seguir.

CREATE SCHEMA partman; CREATE EXTENSION pg_partman WITH SCHEMA partman;
nota

Para criar a extensão pg_partman, certifique-se de que você tenha privilégios rds_superuser.

Se você receber um erro como o seguinte, conceda os privilégios de rds_superuser à conta ou use sua conta de superusuário.

ERROR: permission denied to create extension "pg_partman" HINT: Must be superuser to create this extension.

Para conceder privilégios de rds_superuser, conecte-se à sua conta de superusuário e execute o seguinte comando.

GRANT rds_superuser TO user-or-role;

Para os exemplos que mostram usando a extensão pg_partman, usamos a seguinte tabela de banco de dados de amostra e partição. Esse banco de dados usa uma tabela particionada com base em um carimbo de data/hora. Um esquema data_mart contém uma tabela chamada events com uma coluna chamada created_at. As seguintes configurações estão incluídas na tabela events:

  • Chaves primárias event_id e created_at, que devem ter a coluna usada para orientar a partição.

  • Uma restrição de verificação ck_valid_operation para impor valores para uma coluna operation da tabela.

  • Duas chaves estrangeiras, onde uma (fk_orga_membership) aponta para a tabela externa organization e a outra (fk_parent_event_id) é uma chave estrangeira autorreferenciada.

  • Dois índices, onde um (idx_org_id) é para a chave estrangeira e o outro (idx_event_type) é para o tipo de evento.

As declarações DDL a seguir criam esses objetos, que serão incluídos automaticamente em cada partição.

CREATE SCHEMA data_mart; CREATE TABLE data_mart.organization ( org_id BIGSERIAL, org_name TEXT, CONSTRAINT pk_organization PRIMARY KEY (org_id) ); CREATE TABLE data_mart.events( event_id BIGSERIAL, operation CHAR(1), value FLOAT(24), parent_event_id BIGINT, event_type VARCHAR(25), org_id BIGSERIAL, created_at timestamp, CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at), CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'), CONSTRAINT fk_orga_membership FOREIGN KEY(org_id) REFERENCES data_mart.organization (org_id), CONSTRAINT fk_parent_event_id FOREIGN KEY(parent_event_id, created_at) REFERENCES data_mart.events (event_id,created_at) ) PARTITION BY RANGE (created_at); CREATE INDEX idx_org_id ON data_mart.events(org_id); CREATE INDEX idx_event_type ON data_mart.events(event_type);

Configurar partições usando a função create_parent

Depois de habilitar a extensão pg_partman, use a função create_parent para configurar partições dentro do esquema de manutenção de partição. Aqui é usado o exemplo de tabela events criado em Ativar a extensão pg_partman. Chame a função create_parent da seguinte forma:

SELECT partman.create_parent( p_parent_table => 'data_mart.events', p_control => 'created_at', p_type => 'native', p_interval=> 'daily', p_premake => 30);

Os parâmetros são os seguintes:

  • p_parent_table – a tabela particionada pai. Essa tabela já deve existir e estar totalmente qualificada, incluindo o esquema.

  • p_control – a coluna na qual o particionamento deve ser baseado. O tipo de dados deve ser um inteiro ou baseado em tempo.

  • p_type: o tipo é 'native' ou 'partman'. Normalmente, você deve usar o tipo native para suas melhorias de performance e flexibilidade. O tipo partman depende de herança.

  • p_interval – o intervalo ou a faixa de inteiros para cada partição. Os valores de exemplo incluem daily, por hora e assim por diante.

  • p_premake – o número de partições para criar antecipadamente a fim de dar suporte a novas inserções.

Para obter uma descrição completa da função create_parent, consulte Funções de criação na documentação do pg_partman..

Configurar a manutenção da partição usando a função run_maintenance_proc

Você pode executar operações de manutenção de partição para criar automaticamente novas partições, desanexar partições ou remover partições antigas. A manutenção da partição depende da função run_maintenance_proc da extesão pg_partman e da extensão pg_cron, que inicia um programador interno. O agendador pg_cron executa automaticamente instruções SQL, funções e procedimentos definidos em seus bancos de dados.

A seguir, será usado o exemplo de tabela events criado em Ativar a extensão pg_partman para definir que as operações de manutenção de partição serão executadas automaticamente. Como pré-requisito, adicione pg_cron ao parâmetro shared_preload_libraries no grupo de parâmetros da instância de banco de dados.

CREATE EXTENSION pg_cron; UPDATE partman.part_config SET infinite_time_partitions = true, retention = '3 months', retention_keep_table=true WHERE parent_table = 'data_mart.events'; SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);

A seguir, você pode encontrar uma explicação detalhada do exemplo anterior:

  1. Modifique o grupo de parâmetros associado à sua instância de banco de dados e adicione pg_cron ao valor do parâmetro shared_preload_libraries. Essa alteração exige a reinicialização da instância de banco de dados para que tenha efeito. Para obter mais informações, consulte Modificar parâmetros em um grupo de parâmetros de banco de dados no Amazon Aurora.

  2. Execute o comando CREATE EXTENSION pg_cron; usando uma conta que tenha as permissões de rds_superuser. Isso habilita a extensão pg_cron. Para obter mais informações, consulte Agendar manutenção com a extensão pg_cron do PostgreSQL.

  3. Execute o comando UPDATE partman.part_config para ajustar as configurações pg_partman para a tabela data_mart.events.

  4. Execute o comando SET . . . para configurar a tabela data_mart.events, com estas cláusulas:

    1. infinite_time_partitions = true, – configura a tabela para poder criar novas partições automaticamente, sem qualquer limite.

    2. retention = '3 months', – configura a tabela para ter uma retenção máxima de três meses.

    3. retention_keep_table=true – Configura a tabela para que, quando o período de retenção for devido, a tabela não seja excluída automaticamente. Em vez disso, as partições que são mais antigas do que o período de retenção são apenas separadas da tabela pai.

  5. Execute o comando SELECT cron.schedule . . . para fazer uma chamada da função pg_cron. Esta chamada define com que frequência o programador executa o procedimento de manutenção pg_partman, partman.run_maintenance_proc. Para este exemplo, o procedimento é executado a cada hora.

Para obter uma descrição completa da função run_maintenance_proc, consulte Funções de manutenção na documentação do pg_partman.