Anterior

Seguinte

Exercício - Criar partições de séries cronológicas

Concluído

O Woodgrove Bank mostra apenas seis meses de transações dentro de seu aplicativo de pagamento sem contato. Após seis meses, essas transações são excluídas para o contexto deste aplicativo. Usando o particionamento de tempo, é fácil identificar quais dados precisam ser mostrados e quais dados precisam ser removidos quando não são mais necessários.

Os usuários do aplicativo Woodgrove Bank também mostraram que consultam dados dentro de algumas semanas. Com esta revelação, faz sentido particionar os dados em intervalos de sete dias.

Neste exercício, você criará as partições para a payment_events tabela. Essas partições dividirão os dados como visto no diagrama abaixo.

Criar as partições para a série temporal na tabela de eventos

Vejamos a declaração usada para a payment_events tabela:

CREATE TABLE payment_events
(
    event_id bigint,
    user_id bigint,
    event_type_id bigint REFERENCES event_types (event_type_id),
    merchant_id bigint REFERENCES payment_merchants (merchant_id),
    event_details jsonb,
    created_at timestamp
) PARTITION BY RANGE (created_at);

A PARTITION BY cláusula indica como os dados devem ser particionados, mas não cria as partições.

1. Para criar as partições para intervalos de sete dias ao longo de seis meses com um mês anterior para mais tarde, execute a seguinte consulta:

   SELECT create_time_partitions(
       table_name := 'payment_events',
       partition_interval := '7 days',
       start_from := now() - interval '1 month',
       end_at := now() + interval '6 months'
   );
   

2. Para confirmar que as partições foram criadas, execute o seguinte comando:

   \d+ payment_events
   

   A saída terá esta aparência:

                                                   Partitioned table "public.payment_events"
       Column     |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
   ---------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
   event_id      | bigint                      |           |          |         | plain    |             |              | 
   user_id       | bigint                      |           |          |         | plain    |             |              | 
   event_type_id | bigint                      |           |          |         | plain    |             |              | 
   merchant_id   | bigint                      |           |          |         | plain    |             |              | 
   event_details | jsonb                       |           |          |         | extended |             |              | 
   created_at    | timestamp without time zone |           |          |         | plain    |             |              | 
   Partition key: RANGE (created_at)
   Foreign-key constraints:
       "payment_events_event_type_id_fkey" FOREIGN KEY (event_type_id) REFERENCES event_types(event_type_id)
       "payment_events_merchant_id_fkey" FOREIGN KEY (merchant_id) REFERENCES payment_merchants(merchant_id)
   Partitions: payment_events_p2022w32 FOR VALUES FROM ('2022-08-08 00:00:00') TO ('2022-08-15 00:00:00'),
               payment_events_p2022w33 FOR VALUES FROM ('2022-08-15 00:00:00') TO ('2022-08-22 00:00:00'),
               payment_events_p2022w34 FOR VALUES FROM ('2022-08-22 00:00:00') TO ('2022-08-29 00:00:00'),
               payment_events_p2022w35 FOR VALUES FROM ('2022-08-29 00:00:00') TO ('2022-09-05 00:00:00'),
               ...
   

   Há duas coisas a observar:

   • Há uma chave de partição chamada logo acima das chaves estrangeiras.
   • As partições sob as chaves estrangeiras devem mostrar os nomes das partições e os intervalos de datas.

Soltar partições

Quando os dados não são mais necessários, você pode usar drop_old_time_partitions() para remover dados desnecessários. Neste exemplo, você verá como drop_old_time_partitions() funciona excluindo as partições mais antigas do que agora.

1. Para remover os dados que não são mais necessários de um mês atrás, execute o seguinte comando:

   CALL drop_old_time_partitions('payment_events',now());
   

   O comando emite quais partições são descartadas. Eis um exemplo do resultado:

   NOTICE:  dropping payment_events_p2022w32 with start time 2022-08-08 00:00:00 and end time 2022-08-15 00:00:00
   NOTICE:  dropping payment_events_p2022w33 with start time 2022-08-15 00:00:00 and end time 2022-08-22 00:00:00
   NOTICE:  dropping payment_events_p2022w34 with start time 2022-08-22 00:00:00 and end time 2022-08-29 00:00:00
   NOTICE:  dropping payment_events_p2022w35 with start time 2022-08-29 00:00:00 and end time 2022-09-05 00:00:00
   NOTICE:  dropping payment_events_p2022w36 with start time 2022-09-05 00:00:00 and end time 2022-09-12 00:00:00
   

2. Para confirmar que as partições foram descartadas, execute o seguinte comando:

   \d+ payment_events
   

   A partição do mês passado não deve aparecer na lista de partições.

Embora você possa executar create_time_partitions() e drop_old_time_partitions() manualmente, você pode usar uma extensão chamada pg_cron para agendar essa manutenção. Saiba mais sobre o dimensionamento de dados de séries cronológicas e o agendamento da manutenção de partições.

Continuar