Convert hot tables to cold with tiered storage - AnalyticDB: Central de Documentação do Alibaba Cloud

O AnalyticDB for PostgreSQL oferece suporte a armazenamento em camadas. Esse recurso permite mover tabelas quentes pouco acessadas para o Object Storage Service (OSS) como tabelas frias, o que reduz os custos de armazenamento.

Nota

Neste tópico, as tabelas armazenadas em discos locais são chamadas de tabelas quentes, e as tabelas armazenadas no Object Storage Service (OSS) remoto são chamadas de tabelas frias.

Requisitos

Nota

O AnalyticDB PostgreSQL V6.0 não oferece suporte ao armazenamento em camadas de dados quentes e frios.

Instâncias do AnalyticDB for PostgreSQL V7.0 com versão secundária 7.0.3.0 ou posterior.

Nota

Verifique a versão secundária na página Basic Information da instância no console do AnalyticDB for PostgreSQL. Caso sua instância não atenda aos requisitos de versão, atualize a versão secundária da instância.

Limitações

O modo Serverless não é compatível.
É possível converter tabelas não particionadas em tabelas frias.
Tabelas particionadas podem ser convertidas em tabelas frias ou ter subpartições específicas transformadas em partições frias.
A leitura e a escrita de dados em tabelas e partições frias são permitidas. No entanto, operações de exclusão e atualização não são suportadas. Operações DDL em tabelas frias, como ALTER COLUMN e DROP COLUMN, estão disponíveis apenas mediante convite. Para utilizar esses recursos, abra um ticket e entre em contato com o suporte técnico.
Não utilize a instrução COPY para gravar dados em tabelas frias ou tabelas particionadas que contenham partições frias. Da mesma forma, não use essa instrução para exportar dados desses tipos de tabela. Utilize a instrução INSERT para importar dados.
Após a conversão de uma subpartição de uma tabela particionada em partição fria, não é possível executar a instrução TRUNCATE nem realizar operações DDL (como ALTER COLUMN e DROP COLUMN) na tabela particionada pai.
Subpartições de tabelas particionadas com chave primária ou índice único não podem ser convertidas em partições frias. Essa limitação não se aplica a tabelas particionadas que possuem apenas índices comuns.
Ao converter uma tabela quente em fria, as chaves primárias, índices, sequências, regras e comentários associados à tabela original são excluídos automaticamente e não podem ser recuperados.
Não é possível reverter diretamente uma tabela ou partição fria para quente. Utilize a instrução CREATE TABLE AS SELECT para criar uma nova tabela quente e migrar os dados da tabela fria para ela.

Faturamento

Ao converter uma tabela quente em fria, os dados passam a ser armazenados no OSS e geram taxas de armazenamento conforme as seguintes regras:

O armazenamento frio utiliza o modelo de pagamento conforme o uso.
A medição do uso ocorre a cada 5 minutos, com faturamento horário.
O preço equivale ao do armazenamento OSS Standard. Para mais detalhes, consulte Preços do OSS.

Por exemplo, nas regiões da China continental, o preço do OSS é de USD 0,017 por GB/mês. O valor horário corresponde a USD 0,0000236111 por GB. Prevalece o valor real apresentado em sua fatura.

Consulte os detalhes de faturamento do armazenamento frio na página Billing > Bill Details.

Procedimento

Importante

A conversão cria uma tabela temporária, grava dados nela e faz o upload para o OSS. Essas operações consomem recursos de I/O local e de rede, podendo afetar consultas em execução. Avalie o impacto potencial antes de prosseguir.
O espaço em disco local ocupado anteriormente é liberado após a conversão de uma tabela quente para fria.
O tempo total necessário varia conforme as especificações da instância, a quantidade de conversões simultâneas e o volume de dados. Para mais informações, consulte Dados de desempenho.

Siga os procedimentos abaixo para converter tabelas quentes em tabelas frias.

Converter uma tabela não particionada

Sintaxe

SELECT pg_tiered_storage_move_table_to_storage_cold('<schema_name>', '<table_name>');

Exemplo

Crie uma tabela não particionada chamada tiered_storage_heap_oss no schema public e grave dados nela.

CREATE TABLE tiered_storage_heap_oss (a int, b int) DISTRIBUTED BY(a) ;
INSERT INTO  tiered_storage_heap_oss SELECT random() * 1000,1 FROM generate_series(1,100);

Exemplo 1: Conversão imediata de toda a tabela para fria.

Execute a instrução a seguir para converter imediatamente toda a tabela não particionada em uma tabela fria.
```
SELECT pg_tiered_storage_move_table_to_storage_cold('public', 'tiered_storage_heap_oss');
```
Exemplo 2: Agendamento da conversão completa via pg_cron.

Suponha que você seja o usuário etl_user e deseje converter a tabela não particionada tiered_storage_heap_oss do banco de dados etl em uma tabela fria às 01:00 do dia seguinte. Conecte-se ao banco de dados postgres e execute a seguinte instrução.
```
SELECT cron.schedule('etl_table_transfer_to_cold', '0 1 * * *', 'SELECT pg_tiered_storage_move_table_to_storage_cold(''public'', ''tiered_storage_heap_oss'');', 'etl', 'etl_user');
```
Após as 01:00 do dia seguinte, confirme o sucesso da conversão e execute a instrução abaixo para excluir o job agendado.
```
SELECT cron.unschedule(<job_id>);
```
Nota
O ID do job é gerado automaticamente durante a criação. Localize-o na coluna jobid da tabela cron.job.

Converter subpartições de uma tabela particionada

Sintaxe

SELECT pg_tiered_storage_move_table_to_storage_cold('<schema_name>', '<child_partition_name>');

Utilize o comando \d+ no psql para visualizar os nomes das subpartições de uma tabela particionada específica.

Exemplo

Exemplo 1: Conversão imediata de uma subpartição em partição fria.

Crie uma tabela particionada chamada tiered_storage_partition_oss no schema public.
```
CREATE TABLE tiered_storage_partition_oss(a int,b int) DISTRIBUTED BY (a) PARTITION BY range(a) (start(1) end(20) every(10));
```
Nota
Este exemplo gera duas subpartições: tiered_storage_partition_oss_1_prt_1 e tiered_storage_partition_oss_1_prt_2.

Grave dados na subpartição tiered_storage_partition_oss_1_prt_1.
```
INSERT INTO  tiered_storage_partition_oss_1_prt_1 VALUES(1, 1), (2, 2), (3, 3), (4, 4);
```
Converta a subpartição imediatamente em uma partição fria.
```
SELECT pg_tiered_storage_move_table_to_storage_cold('public', 'tiered_storage_partition_oss_1_prt_1');
```

Exemplo 2: Agendamento da conversão de subpartições diárias usando pg_cron.

Crie uma tabela particionada diariamente chamada daily_log_details no banco de dados etl.

CREATE TABLE daily_log_details (id INT, log_message text, created_date character varying(64))
PARTITION BY LIST (created_date)
(
   PARTITION p20230601 VALUES ('20230601'),
   PARTITION p20230602 VALUES ('20230602'),
   PARTITION p20230603 VALUES ('20230603'),
   PARTITION p20230604 VALUES ('20230604'),
   PARTITION p20230605 VALUES ('20230605'),
   PARTITION p20230606 VALUES ('20230606'),
   PARTITION p20230607 VALUES ('20230607'),
   PARTITION p20230608 VALUES ('20230608'),
   PARTITION p20230609 VALUES ('20230609'),
   PARTITION p20230610 VALUES ('20230610'),
   PARTITION p20230611 VALUES ('20230611'),
   DEFAULT PARTITION others
);

Configure um job para executar às 03:00 como usuário etl_user para converter subpartições com mais de 10 dias em partições frias. Siga estas etapas:

Crie uma função de limpeza no banco de dados etl.

CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_daily_table_to_cold_storage(schemaname text, tablename text) RETURNS void
AS $$
DECLARE
    fetch_overdue_partition_sql text;
    cold_storage_sql text;
    target record;
BEGIN
    fetch_overdue_partition_sql := 'WITH targetpartitions AS
    (SELECT * FROM pg_partitions 
    WHERE tablename = $1
    AND schemaname = $2 
    AND partitionlevel = 1
    AND partitionisdefault = FALSE)
    SELECT partitiontablename FROM targetpartitions WHERE 
    to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMMDD'') <= current_date - INTERVAL ''10 days''';
    
    -- fetch overdue partitions
    FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
       cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
       raise notice 'sql %', cold_storage_sql;
       EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Conecte-se ao banco de dados postgres e execute a instrução de conversão.

SELECT cron.schedule('etl_daily_transfer_to_cold', '0 3 * * *', 'SELECT pg_tiered_storage_move_partition_daily_table_to_cold_storage(''public'', ''daily_log_details'');', 'etl', 'etl_user');

Exemplo 3: Agendamento da conversão de subpartições mensais via pg_cron.

Crie uma tabela particionada mensalmente chamada month_log_details no banco de dados etl.

CREATE TABLE month_log_details (id INT, log_message text, created_date character varying(64))
PARTITION BY LIST (created_date)
(
   PARTITION p202306 VALUES ('202306'),
   PARTITION p202307 VALUES ('202307'),
   PARTITION p202308 VALUES ('202308'),
   PARTITION p202309 VALUES ('202309'),
   PARTITION p202310 VALUES ('202310'),
   DEFAULT PARTITION others
);

Configure um job para executar às 05:00 como usuário etl_user, convertendo subpartições com mais de 3 meses em partições frias. Siga estas etapas:

Crie uma função de limpeza no banco de dados etl.

CREATE OR REPLACE FUNCTION pg_tiered_storage_move_partition_table_to_cold_storage(schemaname text, tablename text) RETURNS void
AS $$
DECLARE
    fetch_overdue_partition_sql text;
    cold_storage_sql text;
    target record;
BEGIN
    fetch_overdue_partition_sql := 'WITH targetpartitions AS
    (SELECT * FROM pg_partitions 
    WHERE tablename = $1
    AND schemaname = $2 
    AND partitionlevel = 1
    AND partitionisdefault = FALSE)
    SELECT partitiontablename FROM targetpartitions WHERE 
    to_date(substring(targetpartitions.partitionname FROM 2), ''YYYYMM'') <= current_date - INTERVAL ''3 months''';
    
    -- fetch overdue partitions
    FOR target IN EXECUTE fetch_overdue_partition_sql USING tablename, schemaname LOOP 
       cold_storage_sql := 'SELECT pg_tiered_storage_move_table_to_storage_cold($1::text, $2::text)';
       raise notice 'sql %', cold_storage_sql;
       EXECUTE cold_storage_sql USING schemaname, target.partitiontablename;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Conecte-se ao banco de dados postgres e execute a instrução de conversão.

SELECT cron.schedule('etl_month_transfer_to_cold', '0 5 1 * *', 'SELECT pg_tiered_storage_move_partition_table_to_cold_storage(''public'', ''month_log_details'');', 'etl', 'etl_user');

Consultar o status de armazenamento de uma tabela

Execute a instrução abaixo para verificar o status de armazenamento. A consulta retorna "cold" para tabelas frias e "hot" para tabelas quentes.

SELECT pg_tiered_storage_table_status('<schema_name>', '<table_name>|<child_partition_name>')

Uso de armazenamento quente e frio

Faça login no console do AnalyticDB for PostgreSQL. Na página Basic Information, verifique o cartão Instance Status para visualizar o uso de Hot Storage e Cold Storage.

Backup e restauração

O armazenamento em camadas do AnalyticDB for PostgreSQL oferece suporte a backup e restauração. Durante a restauração de dados, aplicam-se as seguintes regras:

Com um backup completo disponível, é possível restaurar os dados até um ponto específico no tempo. O status de armazenamento (quente ou frio) dos dados restaurados corresponderá ao status no momento do backup. O AnalyticDB for PostgreSQL V7.0 apresenta as seguintes limitações:

Caso nenhum dado tenha sido gravado na tabela após sua conversão para fria, a restauração pode ser feita para qualquer ponto no tempo.
Se houver gravação de dados após a conversão para tabela fria, a restauração será possível apenas para pontos de backup anteriores à conversão. Para restaurações posteriores à conversão, a recuperação limita-se ao estado da tabela na última operação de escrita.

Para viabilizar backup e restauração, o sistema não libera imediatamente o espaço no OSS quando uma tabela é excluída. O espaço permanece retido por um período igual à configuração de Data Backup Retention Period (Days). Você será cobrado pelo uso desse espaço no OSS durante o período de retenção estendida.

Para mais detalhes sobre backup e restauração, consulte Backup e restauração.

Dimensionamento

Operações de dimensionamento não afetam os dados no armazenamento frio. Como não há necessidade de redistribuição de dados ou restauração para o armazenamento local, não é preciso considerar o espaço em disco utilizado pelo armazenamento frio.

Dados de desempenho

Testes de desempenho foram realizados em instâncias de 4 nós e 8 nós, ambas configuradas com nós de 2 núcleos e 8 GB. A tabela de teste foi criada e preenchida com as instruções abaixo.

CREATE TABLE t333 (a int, b int);
INSERT INTO t333 SELECT random() * 1000000, random()*1000000 FROM generate_series(1,3000000000);

Para instâncias do AnalyticDB for PostgreSQL V7.0, execute a instrução SELECT pg_tiered_storage_move_table_to_storage_cold('public', 't333');.

A tabela a seguir apresenta o tempo de conversão para uma única tabela.

Tamanho da tabela quente (GB)

Tempo de conversão com 4 nós (s)

Tempo de conversão com 8 nós (s)

2,8

25,2

100

490

243