Workshop PostgreSQL: Implementação e Tuning

O projeto centralizou-se na migração de dados de um sistema legado para um novo banco de dados PostgreSQL, utilizando o Pentaho Data Integration (PDI) como ferramenta ETL. A iniciativa envolveu a movimentação de um volume significativo de informações, com destaque para 796.619 compras e 6.421.550 itens, totalizando aproximadamente 180 GB de dados. O processo ETL foi estruturado em duas fases: a extração dos dados do sistema antigo, a transformação destes dados para se adequarem ao novo modelo de banco de dados PostgreSQL, e a subsequente carga destes dados transformados para o novo repositório. A utilização de uma Data Staging Area, um esquema temporário no PostgreSQL, foi crucial para a validação e a limpeza dos dados antes da sua inserção final no banco de dados de produção. O documento descreve as etapas e os desafios enfrentados durante todo o processo, desde as etapas iniciais de planejamento até a implementação e o ajuste fino do sistema. A escolha do PostgreSQL como SGBD livre representou uma decisão estratégica visando a flexibilidade e a customização do sistema. A infraestrutura utilizada, apesar de ter se mostrado funcional, foi um ponto de atenção que influenciou a necessidade de otimização e ajustes finos. A utilização de PL/pgSQL para a escrita das funções de transformação de dados também é um ponto ressaltado, que mostra a capacidade de personalização e adaptação do projeto.

A etapa de extração de dados utilizou o SIDEC (DDM) e arquivos de marcação fixa, com tamanhos que variaram entre 20 GB e 180 GB, evidenciando o grande volume de informações a serem processadas. O Pentaho PDI foi utilizado como ferramenta de apoio para o processo ETL. A transformação dos dados foi realizada através de um conjunto de funções criadas em PL/pgSQL, incluindo funções como CarregarBase(), atualizaSituacaoAvisoCCL(), e outras. A carga dos dados, componente central do processo ETL, ocorreu em duas etapas principais: uma etapa inicial na Data Staging Area, um esquema temporário no PostgreSQL, e uma etapa final no esquema de dados definitivo. Esta abordagem em duas etapas permitiu a validação e a limpeza dos dados antes da carga final. O uso da Data Staging Area permitiu que etapas de verificação, classificação, validação e filtragem fossem realizadas de forma eficiente e controlada, minimizando erros na etapa final. O documento detalha a importância deste processo de transformação, onde as regras de negócio extraídas de programas Natural foram interpretadas e aplicadas durante este estágio. O sucesso do processo dependia diretamente da correta interpretação e implementação destas regras, garantindo a integridade e a consistência dos dados após a migração.

Devido ao grande volume de dados e inserções, o ajuste fino (tuning) do banco de dados PostgreSQL foi fundamental para garantir a performance do sistema. O documento destaca a importância da otimização de parâmetros críticos do SGBD, incluindo shared_buffer, work_mem, maintenance_work_mem, e wal_buffers. A escolha adequada destes parâmetros foi crucial para garantir a eficiência do processo de inserção e a utilização eficiente dos recursos de memória. O documento também aborda a otimização do processo de importação, focando na redução do tamanho das transações para minimizar o consumo de recursos e acelerar a execução. A abordagem de transações menores é comparada à de transações maiores, destacando as vantagens e desvantagens de cada abordagem em termos de consumo de recursos, velocidade de execução, e complexidade do gerenciamento de erros. Um servidor com 2 CPUs e 4 GB de memória foi utilizado, demonstrando o desafio de balancear o processamento de grandes volumes de dados com recursos limitados. A alocação de memória para o log do banco de dados, para garantir a recuperação em caso de falha, também é mencionada como um aspecto crucial da configuração do sistema. A execução automática do vacuum é mencionada como uma prática para otimizar o desempenho a longo prazo.

A implementação do processo de importação incluiu dois métodos: um processo de importação completo, utilizando o script criarbase.sh, e um processo de importação individual, permitindo a carga de compras ou grupos de compras específicos. O processo completo foi capaz de carregar o esquema Siasgnet em apenas 2 minutos, demonstrando a eficiência do método. A administração do SGBD envolveu a utilização de elementos do catálogo do sistema, tais como pg_constraint, pg_attribute, pg_namespace, e pg_class, para gerenciar e monitorar o banco de dados. A linguagem PL/pgSQL foi utilizada para a construção das funções responsáveis pela transformação e carga dos dados, explorando recursos como recursividade, cursores, e tratamento de erros. A escolha do ambiente de execução final foi crucial e foi considerada a partir de aspectos como disponibilidade de espaço em disco e velocidade de acesso. O suporte ao sistema foi dividido em suporte via SUPCD, com acesso limitado à leitura e modificações sob solicitação, e suporte local. O tempo total de acesso ao ambiente foi de 30 horas, mais 38 horas para suporte via SUPCD (incluindo 1 hora para backup). O documento termina apresentando questões relevantes sobre o tempo de execução do processo, permitindo o monitoramento da sua execução e verificando as etapas concluídas. Um exemplo de controle do processo, com tempos de execução de cada etapa, é apresentado para ilustrar a necessidade de monitoramento.

O projeto de migração de dados foi concluído com sucesso, importando 796.619 compras e 6.421.550 itens do sistema legado. A metodologia de ‘comer o boi aos bifes’ – processando os dados em etapas – se mostrou eficaz. A documentação completa das tarefas de importação de dados é enfatizada como um elemento crucial para a manutenibilidade e replicabilidade do processo. A importância de recursos adequados para realizar a tarefa eficientemente também é ressaltada, uma vez que a falta destes recursos resulta em um processo dispendioso, demorado, cansativo e desmotivante. A análise de dados após a implementação mostrou que, em média, 1279 compras são realizadas em dias úteis, com picos chegando a 1617 compras em um único dia, demonstrando a necessidade de uma infraestrutura robusta para o volume de novas compras esperadas. O documento conclui reforçando a necessidade de um processo de implementação, mesmo que simples, para evitar retrabalho futuro.

A fase de extração de dados envolveu o uso de diferentes fontes e formatos. O SIDEC (DDM) foi uma das fontes de dados utilizadas. Além do SIDEC, foram empregados arquivos de marcação fixa, com tamanhos consideráveis, variando de 20 GB a 180 GB. Esses arquivos continham informações cruciais para o processo de migração, representando o volume total de dados a ser processado. A extração dos dados do sistema legado foi o passo inicial do processo ETL, e a sua complexidade derivou da diversidade de fontes e do grande volume de informações envolvidas. A escolha do SIDEC e arquivos de marcação fixa demonstra a necessidade de lidar com diferentes formatos de dados na origem, e a quantidade significativa dos arquivos aponta para um grande volume de dados a serem tratados, reforçando a importância do planejamento e da capacidade da infraestrutura em lidar com tal volume. A heterogeneidade das fontes de dados exigiu flexibilidade e eficiência na fase de extração, preparando o terreno para as fases posteriores de transformação e carga.

A etapa de transformação dos dados foi crucial para garantir a compatibilidade com o novo modelo de dados do banco de dados PostgreSQL. Este processo foi realizado utilizando funções customizadas em PL/pgSQL. A lista de funções inclui CarregarBase(), atualizaSituacaoAvisoCCL(), atualizaSituacaoAvisoCSL(), atualizaSituacaoAvisoEvento(), trataResultadoCompraVencDisp(), trataResultadoCompraDescMaterial(), trataResultadoCompraDescServico(), carregaCompraOriginal(), carregaSubrogacao(), carregaVersaoCompra(), carregaAviso(), carregaAvisoCompra(), carregaAvisoEvento(), carregaEdital_ccl(), geraAnexoItemLegado(), carregaItensVersoes(), vinculaBeneficioItemLicitacao(), carregaLocalEntrega(), replicaItensParaVersaoSemItem(), atualizaQuantidadeItensVersoes(), carregaPedidoDispensaEletronica(), carregaItemPedidoDispensaEletronica(), e atualizaQuantidadeItensPde(). Essas funções realizaram tarefas específicas de transformação, assegurando que os dados obedecessem ao padrão exigido pelo novo sistema. A transformação dos dados foi um processo complexo que exigiu um profundo conhecimento do modelo de dados original e do modelo de dados de destino, garantindo a integridade e a consistência dos dados no novo sistema. A aplicação correta das regras de negócio, originárias dos programas Natural, foi um ponto crítico durante a transformação, demonstrando a necessidade de uma interpretação precisa destas regras para o sucesso da migração. O uso de PL/pgSQL permitiu a criação de funções altamente customizadas e adaptadas as necessidades específicas do projeto.

A carga dos dados foi realizada em duas etapas. A primeira etapa utilizou uma Data Staging Area, um esquema temporário no PostgreSQL. Esta etapa serviu para o tratamento preliminar dos dados, conferência dos resultados da importação, e para garantir a correta interpretação e aplicação das regras de negócio extraídas dos programas Natural. O uso do arquivo de marcação fixa, juntamente com o PostgreSQL e o PDI Pentaho, foi fundamental nessa etapa. A Data Staging Area desempenhou um papel crucial permitindo a verificação, classificação, validação e filtragem dos dados antes da carga final no esquema definitivo. Esta abordagem em duas etapas minimizou riscos, permitindo a correção de erros e a garantia da qualidade dos dados antes de sua inserção no sistema de produção. A segunda etapa envolveu a carga final dos dados transformados e validados para o esquema definitivo do banco de dados, utilizando o SIDEC como uma das fontes de informação. Esta etapa concluiu o processo ETL, integrando os dados transformados no sistema de destino. A utilização de um processo em duas etapas demonstrou uma estratégia eficaz para gerenciar riscos e garantir a precisão dos dados no sistema final. A combinação de ferramentas como o PDI Pentaho e o SIDEC demonstrou a capacidade de lidar com diferentes formatos e origens de dados.

Devido ao grande volume de dados e à necessidade de alta performance, o ajuste fino do Sistema Gerenciador de Banco de Dados (SGBD) PostgreSQL foi crucial. A principal demanda consistia em um grande volume de inserções e tratamento de uma grande massa de dados. Para otimizar o desempenho, foram ajustados parâmetros essenciais do PostgreSQL. Entre os parâmetros ajustados estavam: shared_buffer (para alocação de blocos em memória, recomendada entre 25% e 40% do total de memória RAM), work_mem (para operações como ORDER, MERGE, e DISTINCT), maintenance_work_mem (para operações como VACUUM, CREATE, e ALTER), wal_buffers (para otimizar o buffer do Write-Ahead Log - WAL), checkpoint_segments, e autovacuum. Valores específicos foram definidos para esses parâmetros: 1 GB para shared_buffer, 32 MB para work_mem, 100 MB para maintenance_work_mem, 2 MB para wal_buffers, autovacuum ativado e fsync desativado. O servidor utilizado possuía 2 CPUs e 4 GB de memória RAM, com 100 GB de HD, o que ilustra as limitações de recursos em relação ao volume de dados processados. A configuração da memória para o LOG, o sincronismo entre o WAL e o disco (especialmente importante em desenvolvimento), e o número máximo de arquivos de LOG também foram considerados na otimização, buscando um equilíbrio entre performance e recuperação de dados em caso de falhas. A execução automática do vacuum foi ativada para garantir a manutenção da integridade e otimização do banco de dados.

Além do ajuste fino do SGBD, a otimização do próprio processo de importação também foi fundamental. A análise focou na administração eficiente do consumo de recursos do servidor, buscando minimizar o tempo de execução e o impacto no desempenho geral do sistema. O documento discute a relação entre o tamanho das transações e o consumo de recursos. Transações menores implicam em menor consumo de recursos e execução mais rápida, com resultados menos acoplados. Por outro lado, transações maiores, embora reduzam o número de processos adicionais, podem resultar em maior consumo de recursos, tempo de execução mais longo, maior complexidade no controle, e maior dificuldade na correção de erros. Foi realizada uma carga completa de 796.619 compras, enquanto a carga individual processou 200 compras (0,025% do total), ilustrando a eficiência de diferentes abordagens. A implementação utilizou um script auxiliar para auxiliar no controle e gerenciamento dos recursos. A otimização do processo inclui considerações sobre a administração do consumo de recursos do servidor, focando na eficiência da utilização dos recursos e na redução dos tempos de resposta do sistema. A modularização do processo, através de transações menores, é destacada como uma estratégia eficiente para lidar com o grande volume de dados e garantir uma execução eficiente e robusta.

A implementação do processo de importação de dados utilizou duas abordagens distintas: um processo de importação individual e um processo de importação completo. O processo individual permite a carga de compras ou grupos de compras de forma isolada, utilizando as funções CarregarCompra(<chave da compra>) e RecarregarCompra(<chave da compra>). Este processo foi útil para situações em que era necessário atualizar ou carregar dados específicos. Já o processo de importação completo realizou a carga de todos os dados existentes no esquema auxiliar, utilizando o script criarbase.sh. Este script automatizou o processo de carga completa e foi fundamental para carregar todo o esquema Siasgnet em apenas 2 minutos. A existência de dois processos de importação forneceu flexibilidade ao sistema, permitindo a escolha da abordagem mais adequada de acordo com as necessidades, seja para atualização incremental de dados ou para uma carga completa. A capacidade de realizar uma carga completa do esquema em um curto intervalo de tempo demonstra a eficiência do script criarbase.sh e a otimização do processo de importação.

As funções utilizadas no processo de transformação e carga de dados foram desenvolvidas utilizando a linguagem PL/pgSQL. Essa escolha permitiu o aproveitamento de recursos específicos da linguagem, tais como recursividade, cursores, e tratamento de erros. A utilização de cursores facilitou o processamento de conjuntos de dados de forma iterativa, enquanto o tratamento de erros garantiu a robustez do sistema. A possibilidade de integrar SQL nativo na linguagem PL/pgSQL proporcionou flexibilidade e eficiência na manipulação dos dados. O documento ressalta algumas percepções sobre o uso da linguagem PL/pgSQL, incluindo a questão da sobrecarga, um fator a ser considerado na otimização do desempenho. A capacidade de lidar com a recursividade e o tratamento eficiente de erros foram pontos positivos, demonstrando a escolha adequada da linguagem para este tipo de aplicação. A integração com o SQL nativo permitiu a exploração das funcionalidades do banco de dados PostgreSQL, otimizando as operações de manipulação de dados.

A administração do Sistema Gerenciador de Banco de Dados (SGBD) envolveu o uso do catálogo do sistema, acessando informações de metadados essenciais para o gerenciamento e monitoramento do banco de dados. Elementos como pg_constraint, pg_attribute, pg_namespace, e pg_class foram utilizados para obter informações sobre restrições, atributos, namespaces e classes, respectivamente. Essa abordagem permitiu uma gestão mais eficiente do banco de dados e contribuiu para o monitoramento da integridade dos dados. O acesso ao catálogo do sistema forneceu informações fundamentais sobre a estrutura do banco de dados, permitindo a identificação de potenciais problemas e facilitando a manutenção e otimização do sistema. A utilização destes elementos do catálogo de sistema permitiu uma administração eficiente do banco de dados, fornecendo informações cruciais para o gerenciamento, monitoramento e manutenção do sistema, garantindo a sua integridade e o seu funcionamento adequado.

O projeto de migração de dados resultou na importação bem-sucedida de um volume substancial de informações. Foram importados dados referentes a 796.619 compras e 6.421.550 itens, demonstrando a capacidade do sistema em processar grandes quantidades de dados. Após a importação, foram observados dados sobre o volume de novas compras, com uma média de 1.279 compras em dias úteis e picos de 1.617 compras em um único dia, dois meses após a implantação. Isso indica um crescimento significativo nas operações do sistema e a necessidade de escalabilidade. A importação do legado, com seus números expressivos, demonstrou a eficácia das técnicas e estratégias implementadas no projeto. A capacidade de processar tais volumes de dados evidencia a eficiência do sistema implementado, e os dados pós-implantação, com a média e picos de compras diárias, demonstram a necessidade de se planejar para o volume crescente de dados que o sistema precisará gerenciar no futuro. A execução do processo gerou um conjunto de métricas relativas ao tempo de processamento de cada etapa, indicando o tempo de execução para cada etapa do processo de carga e possibilitando a análise de gargalos e oportunidades de melhoria.

O projeto proporcionou valiosas lições aprendidas. A estratégia de ‘comer o boi aos bifes’, ou seja, dividir o processo em etapas menores e mais gerenciáveis, mostrou-se eficaz para o sucesso da importação. A importância da documentação detalhada de todas as etapas do processo, por menores que pareçam, é ressaltada. A documentação completa e detalhada das tarefas, incluindo os requisitos, se mostrou essencial para a compreensão do processo, para a manutenção e para a possível replicação do projeto. A disponibilidade de recursos adequados é crucial para a eficiência do processo, pois a falta destes leva a um trabalho dispendioso, demorado, cansativo e desmotivante. O projeto destacou a necessidade de planejamento cuidadoso, considerando os desafios de performance e escalabilidade impostos por grandes volumes de dados. Finalmente, o documento recomenda seguir um processo de implementação, mesmo que simples, para evitar retrabalho e garantir a sustentabilidade do sistema a longo prazo. As lições aprendidas reforçam a importância de um planejamento minucioso, incluindo a alocação adequada de recursos e o desenvolvimento de um processo estruturado e bem documentado, buscando a eficiência e a manutenção do sistema a longo prazo.