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:

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.

Em vez de ter que criar manualmente cada partição, você ajusta o pg_partman com as seguintes configuraçõ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;

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:

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:

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:

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