Particionamento de Tabelas no PostgreSQL

Introdução

Neste laboratório, você aprenderá como implementar o particionamento de tabelas no PostgreSQL. O objetivo é dividir uma tabela grande em partes menores e mais gerenciáveis, o que pode melhorar significativamente o desempenho de consultas e simplificar tarefas de gerenciamento de dados, como backups ou arquivamento.

Você começará criando uma tabela principal "pai" projetada para particionamento. Em seguida, definirá várias tabelas "filhas", ou partições, cada uma contendo dados para um intervalo de datas específico. Finalmente, você inserirá dados na tabela pai e observará como o PostgreSQL os roteia automaticamente para a partição correta. Você também aprenderá a consultar a tabela particionada e verá como o PostgreSQL otimiza essas consultas acessando apenas as partições relevantes.

Criar a Tabela Pai Particionada

Nesta etapa, você criará a tabela principal sales, que servirá como a tabela pai para nossas partições. Esta tabela define a estrutura para todas as suas partições, mas não armazenará nenhum dado por si só.

Primeiro, você precisa se conectar ao banco de dados PostgreSQL. Abra um terminal e use o seguinte comando para iniciar o shell interativo psql como o usuário postgres:

sudo -u postgres psql

Você deverá ver o prompt do PostgreSQL, que se parece com postgres=#. Todos os comandos SQL subsequentes neste laboratório serão executados a partir deste prompt.

Em seguida, crie a tabela sales. Esta tabela será particionada por intervalo na coluna sale_date.

CREATE TABLE sales (
    sale_id SERIAL,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2),
    PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);

Vamos detalhar este comando:

• CREATE TABLE sales (...): Define as colunas para nossos dados de vendas.
• PRIMARY KEY (sale_id, sale_date): Em uma tabela particionada, a chave primária deve incluir a coluna de particionamento (sale_date).
• PARTITION BY RANGE (sale_date): Esta é a parte chave. Ela declara que esta tabela é particionada usando o método RANGE na coluna sale_date.

Após executar o comando, você deverá ver uma mensagem de confirmação CREATE TABLE.

Para verificar se a tabela foi criada, você pode usar o comando \d no psql para descrever a estrutura da tabela.

\d sales

A saída mostrará as colunas da tabela e, na parte inferior, confirmará que é uma "Tabela particionada" e listará a "Chave de partição".

                                       Table "public.sales"
   Column    |     Type      | Collation | Nullable |                    Default
-------------+---------------+-----------+----------+------------------------------------------------
 sale_id     | integer       |           | not null | nextval('sales_sale_id_seq'::regclass)
 sale_date   | date          |           | not null |
 product_id  | integer       |           |          |
 sale_amount | numeric(10,2) |           |          |
Partition key: RANGE (sale_date)
Indexes:
    "sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0

Observe que o "Número de partições" é 0. Você criará as partições reais na próxima etapa.

Definir Partições para Intervalos de Datas

Agora que você tem a tabela pai sales, precisa criar as partições reais onde os dados serão armazenados. Cada partição conterá dados para um intervalo de datas específico. Nesta etapa, você criará partições trimestrais para os anos de 2023 e 2024.

Você ainda deve estar no terminal interativo psql.

Primeiro, crie as quatro partições para 2023. Cada comando define uma nova tabela como uma partição de sales e especifica o intervalo de datas que ela cobrirá.

CREATE TABLE sales_2023_q1 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
    FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

CREATE TABLE sales_2023_q3 PARTITION OF sales
    FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');

CREATE TABLE sales_2023_q4 PARTITION OF sales
    FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');

A cláusula FOR VALUES FROM ... TO ... define o intervalo para cada partição. O limite inferior é inclusivo e o limite superior é exclusivo. Por exemplo, sales_2023_q1 armazenará registros onde sale_date está de 2023-01-01 até, mas não incluindo, 2023-04-01.

Em seguida, crie as partições para o ano de 2024 usando o mesmo esquema trimestral:

CREATE TABLE sales_2024_q1 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE sales_2024_q2 PARTITION OF sales
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

CREATE TABLE sales_2024_q3 PARTITION OF sales
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE sales_2024_q4 PARTITION OF sales
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

Após executar cada comando CREATE TABLE, você verá uma mensagem de confirmação.

Para verificar se todas as partições foram criadas, você pode listar as tabelas no banco de dados novamente.

\dt

Você deverá ver a tabela pai sales e todas as oito partições que você acabou de criar (sales_2023_q1, sales_2023_q2, etc.) na saída.

Inserir e Roteirizar Dados

Nesta etapa, você inserirá dados de exemplo. Uma característica fundamental do particionamento é que você insere dados diretamente na tabela pai (sales), e o PostgreSQL roteia automaticamente cada linha para a partição correta com base no valor da chave de particionamento (sale_date).

Você ainda deve estar no terminal interativo psql.

Execute a seguinte instrução INSERT para adicionar 16 registros de vendas de exemplo abrangendo 2023 e 2024:

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);

Após a conclusão do comando, você verá a saída INSERT 0 16, que indica que 16 linhas foram inseridas com sucesso.

Para verificar se os dados foram roteados corretamente, você pode consultar as partições individuais. Por exemplo, vamos verificar a contagem de registros no primeiro trimestre de 2023:

SELECT COUNT(*) FROM sales_2023_q1;

A saída deve ser:

 count
-------
     2
(1 row)

Agora, verifique a contagem para o quarto trimestre de 2024:

SELECT COUNT(*) FROM sales_2024_q4;

A saída também deve ser 2. Isso confirma que o PostgreSQL colocou os dados nas tabelas de partição subjacentes corretas.

Consultar Dados e Analisar Desempenho

Nesta etapa final, você consultará a tabela particionada sales. O principal benefício do particionamento, conhecido como "partition pruning" (poda de partição), é que o planejador de consultas do PostgreSQL é inteligente o suficiente para escanear apenas as partições necessárias, evitando um escaneamento completo de todo o conjunto de dados.

Você ainda deve estar no terminal interativo psql.

Primeiro, execute uma consulta para recuperar todas as vendas do primeiro trimestre de 2023.

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

Você verá os dois registros que se enquadram nesse intervalo de datas. Para ver como o PostgreSQL otimiza isso, você pode usar o comando EXPLAIN, que mostra o plano de execução da consulta.

EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';

A saída será algo como isto:

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on sales_2023_q1 sales  (cost=0.00..31.75 rows=7 width=28)
   Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)

Observe a linha Seq Scan on sales_2023_q1. Isso prova que o PostgreSQL escaneou apenas a partição sales_2023_q1 e ignorou as outras sete, tornando a consulta muito mais rápida em um conjunto de dados grande.

Agora, vamos executar uma consulta mais complexa para encontrar o valor total das vendas para cada produto em 2024.

SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;

Esta consulta escaneará eficientemente apenas as quatro partições de 2024 para calcular o resultado. A saída mostrará as vendas totais para cada produto de 109 a 116.

Finalmente, você pode sair do terminal interativo do PostgreSQL digitando:

\q

Você retornará ao seu prompt de shell regular.

Resumo

Neste laboratório, você aprendeu os fundamentos do particionamento de tabelas no PostgreSQL. Você criou com sucesso uma tabela pai particionada por um intervalo de datas, definiu partições específicas para diferentes períodos de tempo e inseriu dados que foram automaticamente roteados para a partição correta. Mais importante, você usou o comando EXPLAIN para ver o "partition pruning" (poda de partição) em ação, demonstrando como o particionamento pode melhorar significativamente o desempenho de consultas, permitindo que o banco de dados escaneie apenas um subconjunto dos dados. Esta é uma técnica poderosa para gerenciar conjuntos de dados em larga escala de forma eficiente.