PostgreSQL + Unlogged Tables + Partitioning + Parallel Programming = ETL reescrito passando de ~8h para ~25min de execução

Já faz algum tempo que não escrevo nada por aqui, mas não é por falta de tempo ou coisa parecida, é que na realidade não tenho muita intimidade com artigos ou posts em blogs, mas resolvi escrever um "causo" a pedido do amigo Fernando Ike sobre um tweet que lancei há algum tempo depois de obter sucesso em um projeto.

Aviso antecipadamente que o post é um pouco longo, então se não estiver com paciência agora recomendo vc sair tomar um café (ou uma cerveja) e voltar outra hora... desculpe mesmo, tentei reduzir o máximo... :-(

Contextualização

Para vcs entenderem porque cheguei aqui, vou começar dos primórdios... eu tenho (ou tinha...hehehe...) um problema com um ETL em uma aplicação de um cliente (não tenho autorização para "dar nome aos bois") que basicamente processava os registros de uma grande tabela com dados financeiros e gerava uma "posição" da mesma calculando correção monetária, juros, multas, descontos, etc...

Esse ETL sempre foi e ainda é ridiculamente simples, porque basicamente é uma PL/pgSQL dentro do PostgreSQL que faz todo esse trabalho de ler os dados de uma tabela, processar e carregar os mesmos em outra tabela. Até aqui tudo bem, sempre funcionou maravilhosamente bem, mas com bases pequenas... mas também não queremos maravilhas de desempenho processando milhares de registros em uma única transação né, é óbvio que isso gera problemas.

Primeira tentativa... e um sucesso, digamos, opaco...

Há alguns anos eu já tinha melhorado essa rotina dividindo o processamento em lotes menores, através de um shell script que fazia esse trabalho de divisão em lotes por uma coluna da tabela que categorizava os registros em um determinado tipo, então primeiro identificamos os tipos existentes na tabela origem e processava os mesmos gerando tabelas individuais para cada tipo, e ao final juntava tudo na tabela de destino e removia as tabelas temporárias... e vejam só, SEMPRE a MESMA tabela de destino, e para isso precisamos remover os índices, executar o ETL e depois criar novamente os índices para termos um desempenho decente. Claro que junto dessa rotina implementamos também uma outra para expurgo de registros desnecessários/obsoletos (antigos), o que também sempre foi uma rotina que onerava bastante o servidor pois era SEMPRE a MESMA tabela de destino, então imaginem precisar remover uma porção de registros de uma tabela com mais de 100milhões de registros... isso me lembra um post do Fábio Telles: "Não use DELETE use INSERT" que ajudou muito para torná-la "menos pior".

Na época (2007/2008) essa melhoria ajudou pois desafogou bastante a carga de processamento desse ETL, porém com o passar do tempo e a tabela com dados financeiros crescendo constantemente, o ETL foi ficando cada vez mais oneroso chegando ao seu ápice (final de 2012) de ~8h de execução para processar ~8.5milhões de registros. Eu sei que esse número não é tão expressivo assim, mas a complexidade do processamento envolvido para fazer os cálculos de corrreção, juros e multas e as diversas configurações existentes para cada um justificam, de certa forma, todo esse tempode processamento, sem contar que o coitado do servidor ficava "imprestável".

Mas vejam bem, estou falando de dados *financeiros* que sob ponto de vista do negócio se fazem muito necessários para vários tipos de procedimentos e análises. Ainda existem instâncias com bases menores (outros clientes) que rodam esse ETL _diariamente_ por necessidade de negócio, mas nesse cliente em especial não é possível fazer isso, nem que eles quisessem  pois o tempo total de execução consume 1/3 de um dia, então os finais de semana são usados :-)

A hora da verdade ...

Após todos os problemas e sem muitas perspectivas, discutimos sobre a re-implementação da PL/pgSQL que executava o ETL, porém isso não é algo trivial, ainda mais em um ERP complexo onde tal iniciativa teria um impacto de grandes proporções visto que seria necessária uma re-modelagem em  alguns pontos criticos. Apesar de ser uma idéia interessante,  não existe tempo hábil para tanto, pois o cliente não pode mais aguardar uma solução, pois qto mais tempo demorar pior fica.

Como eu já tenho algum tempo de estrada com PostgreSQL e conheço bem a estrtutura do ETL e do ERP em questão, sugeri a equipe que eu poderia re-implementar o antigo shell script reaproveitando a PL/pgSQL do ETL existente (sem mudar regras de negócio), usando tecnologias e técnicas conhecidas. Então o que fiz:

1) Particionamento da tabela: esse foi o ponto fundamental, pois dividimos a grande tabela em outras menores tomando como base uma coluna que indica a "data" em que os dados financeiros foram calculados, e que o ERP usa constantemente para ler informações da mesma, portanto as queries iriam se beneficiar do recurso. Sobre esse assunto, além da documentação oficial, vcs podem dar uma olhada em alguns artigos recém lançados pelo Fábio Telles sobre esse assunto.

2) Implementação de um script PHP (não estou de sacanagem... é PHP mesmo, mas no console) que tivesse a habilidade de gerar processos filhos (fork) para processamento em paralelo, e para isso usei uma classe para realizar esse trabalho. Confesso que no inicio tive um certo receio em implementar essa rotina em PHP, inclusive cogitei a possibilidade de fazê-la em Perl, Python ou Ruby, mas como eu domino mais esta do que as outras e o tempo era curto implementei nela mesmo, e os resultados foram muito satisfatórios.

COPY no lugar de INSERT

A primeira coisa que fiz para continuar esse projeto foi *abolir* o INSERT... isso mesmo... não tem INSERT... vc deve estar pensando que estou maluco e se perguntando: "Tá e como adicionar linhas a uma tabela então?" R: usando COPY, ao invés de INSERT... na realidade implementei uma classe que armazena uma coleção (linhas) em memória, e quando eu preciso uso um método para persistir os dados em uma tabela usando COPY... simples assim... então o código usado para INSERT é algo do tipo:

DDL da tabela exemplo:

create table foo (
  bar integer
);

PHP:

$tabela = new PgCopy('foo');
for ($i=0; $i<10; $i++) {
    $tabela->bar = $i;
    $tabela->insertValue(); // adiciona em memória
}
$tabela->persist(); // realiza COPY dos dados em memória

Dividir para conquistar

Um dos problemas que tinhamos com o processo antigo era justamente que ele era linear, ou seja, um processo apenas com inicio, meio e fim. Então resolvi investir em programação paralela, dividindo o grande volume de registros a processar em vários trabalhos menores sendo capaz de executar alguns em paralelo, de acordo com o nro de núcleos do servidor.

Para tal atividades crio uma tabela que planeja a execução do trabalho, ou seja, cria lotes para que o script possa processar em paralelo, isso baseado em uma chave artifical (sequencial) que existe no modelo e facilitou a criação de trabalhos com lotes de N registros (neste caso usei 1000).

A tabela que crio para planejar a execução do ETL é algo do tipo:

create table jobs (
  id_start bigint,
  id_end bigint,
  status varchar,
  constraint jobs_pk primary key (id_start, id_end),
  constraint jobs_status_ck check (status in ('NOT RUNNING', 'RUNNING', 'FINISHED'))
);

Dessa forma utilizo o "id_start" e "id_end" para buscar as informações na origem em "lotes" de 1000 (mil) registros, e com isso consigo disparar vários processos em paralelo, e dessa forma conseguimos aproveitar melhor os recursos do servidor e assim agilizar bastante o processo.

Unlogged Tables são bem legais

Esse novo recurso presente apartir da versão 9.1 permite criar tabelas que não são escritas no log de transações (WAL), acelerando e muito a inserção de registros na mesma.

Assim cada processo disparado pelo script gera e escreve em uma unlogged table os dados, e junto com os processos de trabalho (workers) implementei um processo especial que serve com um tipo de coletor de lixo (garbage collector) para ir gradativamente lendo os lotes processados (unlogged tables geradas) e inserindo (com copy claro) na partição de destino.

Com essa estratégia posso ter um certo nível de escala na escrita pois consigo separar as tabelas em tablespaces distintas. Claro que se algum imprevisto ocorrer, tipo um desligamento não previsto do servidor, o próprio script tem habilidade de detectar essa situação e fazer uma limpeza geral antes de inicar um novo processo, até mesmo porque as unlogged tables tem seu conteúdo eliminado nessas situações, e não queremos perder parte dos registros não é mesmo... :-)

Finalizando...

Resumindo o que fiz foi:
- Particionar uma tabela grande em outras menores
- Planejar o processamento dividindo em lotes menores para poder fazer processamento paralelo
- Utilizar unlogged tables para receber os dados oriundos dos lotes que são processados em paralelo
- Implementar um processo que irá ler os lotes já processados e inserir os registros na partição de destino.

Existem outras coisas que foram feitas para melhorar o desempenho, tipo desligar o autovacuum nas tabelas, aumentar o work_mem, criar índices necessários ao final do processamento, e outros que podem ser feitos e que vcs podem visualizar neste post do Fábio.

Bom, se vc chegou até aqui então obrigado pela paciência e se quiser mais informações fico a disposição.


---
Fabrízio de Royes Mello
fabriziomello [at] gmail.com