Há pouco mais de um mês, em Moscou, foi realizada a maior conferência da comunidade pós-gree PGConf.Russia 2019, que reuniu mais de 700 pessoas na Universidade Estadual de Moscou. Decidimos postar um vídeo e uma transcrição dos melhores relatórios.
A apresentação de Ivan Frolkov sobre erros típicos ao trabalhar com o PostgreSQL foi apontada como a melhor da conferência, então vamos começar com ela.
Por conveniência, dividimos a descriptografia em duas partes. Neste artigo, falaremos sobre nomes inconsistentes, restrições, sobre onde é melhor concentrar a lógica - no banco de dados ou no aplicativo. A segunda parte tratará do tratamento de erros, acesso simultâneo, operações não canceláveis, CTE e JSON.
Em nossa empresa, estou envolvido no suporte ao cliente em questões relacionadas a aplicativos, ou seja, ajudo em casos de problemas com conexões, otimização de consultas e outras coisas semelhantes. Já vi muitas das mais diversas aplicações. O que eu simplesmente não vi! Talvez até mais do que gostaríamos. Parte do que vou dizer se aplica não apenas ao PostgreSQL, mas a qualquer banco de dados, mas algo principalmente ao PostgreSQL.
A principal conclusão que pude tirar do que vi foi bastante inesperada: de fato, qualquer aplicativo com a devida persistência pode ser feito para funcionar. Havia um projeto maravilhoso (não posso mencionar todas as empresas com as quais trabalhamos) em que um aplicativo ainda mais maravilhoso criava tabelas por milhões. Era assim: na segunda-feira, o sistema funciona bem e na sexta-feira praticamente não funciona. Nos fins de semana, eles lançam o VACUUM FULL, e na segunda-feira funciona novamente. Acontece que você pode zombar do PostgreSQL assim, e tudo isso permanecerá e funcionará por algum tempo. Outro camarada fez uma coisa estranha: tudo foi construído sobre ele, não havia procedimentos. Ou seja, a maioria das tabelas não pode ser tocada, algo não pode ser feito, mas essa base também viveu.
Ele explicou da seguinte maneira: “a base passa de um estado consistente para outro consistente. Se eu voltar a carregar os dados, eles serão quebrados. Mas como tenho gatilhos e uma chave exclusiva, não posso rever os dados. ” A abordagem é selvagem, mas ao mesmo tempo faz algum sentido. Talvez fosse necessário fazer diferente, mas também é necessário levar em consideração os recursos dos clientes. O primeiro erro que falarei é:
Aqui está um exemplo real que me deparei. No slide, você vê como a mesma entidade foi nomeada em colunas diferentes. Pode-se também com espaços. Outros objetos também foram nomeados inconsistentemente. Se você precisar pegar algo em outra tabela, precisará ver como é chamado lá. É o mesmo. Se você tiver id_user e user_id na mesma tabela, o trabalho começará com a pesquisa: o que tudo isso significaria.
Para outros clientes, todos os objetos foram nomeados assim: duas letras e cinco dígitos. Devo dizer que não era "1C". Por que eles fizeram isso - não sei: não havia lógica nisso, mas é da minha conta otimizar as consultas.
Outro exemplo: parte dos nomes em russo, parte em não russo, mas com algum tipo de sotaque russo. Isso dificulta o entendimento e cria novos erros. Eu mesmo tento nomear as colunas como se estivesse contando com um serviço, qual desses nomes de colunas criará automaticamente nomes de colunas normais em algum relatório. Na vida real, infelizmente, não é muito bem-sucedido nomear de forma consistente - incluindo a minha. Isso é especialmente difícil com o desenvolvimento coletivo. Mas devemos nos esforçar.
Outro motivo importante para nomear sequencialmente: nomes de objetos estão disponíveis através de solicitações de metadados, ou seja, nomes também são dados. Você poderá escrever uma solicitação e selecionar, por exemplo, todas as fotos - em geral, todas as fotos - no banco de dados.
Metadados claros são muito convenientes. Especialmente quando você considera os problemas típicos da documentação - e, na minha experiência, a documentação geralmente está ausente, incompleta ou incorreta, ou ambas: porque a tarefa de escrever boa documentação é comparável em complexidade à tarefa de escrever o próprio código. Portanto, é melhor quando o código é auto-documentado. E uma nomeação lógica e consistente de objetos contribui para isso e, quando algo não está claro, você precisa escrever um código de trecho e observar como ele funciona. Uma vez que não é nada, dois nada, mas quando você faz o dia todo, é exaustivo.
O caso real: uma organização muito séria com a qual trabalhamos tinha um fluxo de trabalho básico no Oracle. Nós o mudamos para o Postgres. Um dos termos do contrato era a imposição de CHAVES ESTRANGEIRAS. Eles não estavam lá e, infelizmente, não fomos capazes de impor: as tabelas tinham muitas linhas "deixadas" e ninguém sabe o que fazer com elas, incluindo o cliente.
Quando você não precisa olhar as barras de progresso, mas trabalha com documentos para pagar, a situação é triste. Ajuda muito quando, de acordo com o contrato, o programador paga pelos erros, e é desejável que as quantias sejam grandes - então a iluminação ocorre em minutos, provavelmente quinze. As restrições aparecem imediatamente, imediatamente tudo começa a ser verificado.
Você nem imagina (bem, talvez alguém já imagine) como é mais conveniente lidar com o caso quando o pagamento falhou, do que quando foi aprovado, mas não lá. Especialmente se a quantidade for grande. Isto é da experiência pessoal.
Por outro lado, pode-se ouvir com frequência que a restrição reduz o desempenho. Sim, eles têm, mas se você deseja ter os dados corretos, simplesmente não há outras opções para você. Se você possui um aplicativo que leva em conta o número de visitas à loja pelos clientes, pode haver imprecisões que não afetarão as estatísticas, especialmente, e se contarmos com dinheiro, serão necessárias restrições.
Os nomes de restrições geralmente são gerados por um ORM ou sistema, e geralmente ninguém se incomoda especificamente em nomear restrições - mas em vão! Quando você continuar processando o erro, em seguida, pelo nome da restrição, poderá dar uma mensagem clara ao usuário, classificar o erro e informar se você deve tentar executar a operação novamente ou se essa operação não é mais necessária ou simplesmente não pode ser repetida.
Outra coisa que eu não vi, mas que eu recomendo: para todas as operações importantes de auditoria financeira (e não apenas financeira), deve haver pelo menos duas. O fato é que, mais cedo ou mais tarde, você entrará em algo para alterar o código, e pode muito bem ser que você quebre uma das verificações. Então o segundo vai te salvar. Se você faz três, também não é ruim.
Muitas vezes surge a questão: onde verificar a correção dos dados. No cliente ou no servidor? Na minha opinião, é óbvio que você precisa verificar lá e ali. Você tem um erro no cliente, então o servidor não está
sentirá falta ou você tiver um erro no servidor, pelo menos o cliente ajudará a rastreá-lo. A questão é um tanto discutível, e passamos suavemente ao tópico: onde manter a lógica básica: no aplicativo ou no banco de dados?
É conveniente no banco de dados porque, na minha experiência, uma empresa emite regularmente alterações urgentes: remova ou insira isto e aquilo neste exato momento. Se você tiver lógica no código compilado, precisará coletar, implantar e ver o que aconteceu. Muitas vezes isso é simplesmente impossível. No banco de dados, isso é mais conveniente. Mas existe um aforismo bem conhecido: programadores experientes do Fortran escrevem no Fortran em qualquer idioma. Cerca de 80 códigos de servidor são escritos em um estilo completamente processual: temos a função "get_user ()" e retorna o tipo "user" e, se "get_list_users ()", retorna uma matriz de "users". É realmente mais conveniente escrever essas coisas em Java do que em SQL ou pgsql.
Por outro lado: por que você precisa da função "get_user ()"? Você apenas o leva em uma tabela ou em uma exibição. Como você possui um banco de dados relacional, precisa escrever, como me parece, relacional. É importante, em primeiro lugar, determinar claramente com quais dados estamos trabalhando: se nossos dados são lixo ou meio lixo, o resultado será apropriado e provavelmente não deve ser eliminado. Se os dados são importantes para nós, se são dinheiro, propriedade ou operações legais, então são necessárias restrições e quanto mais, melhor. Repito: é melhor não executar a operação do que executá-la incorretamente. E não escreva código de procedimento em um banco de dados relacional: você se arrependerá muito.
Vi uma tabela com 30 mil linhas (produtos), na qual a solicitação "mostrar uma lista de mercadorias relevantes" foi executada por cerca de um segundo. Aparentemente, eles conseguiram criar um esquema de banco de dados "bonito e complexo". Pessoalmente, acho que se você está fazendo algo muito complicado, provavelmente está fazendo algo errado ou realmente tem uma tarefa muito, muito difícil. Se você possui algum tipo de loja ou aplicativo regular para pessoas da contabilidade, é improvável que haja relacionamentos muito complexos entre entidades.
Quando iniciei minha carreira profissional, a tabela em um arquivo DBF de 60 megabytes no sistema bancário parecia muito grande e agora 60 megabytes não são nada - hardware é melhor, software é melhor, tudo funciona mais rápido, mas a pergunta permanece: onde você consegue tanto dados? Bases muito grandes e inchadas geralmente se tornam assim devido a arquivos. Em qualquer DBMS e no PostgreSQL, muito esforço foi gasto para garantir uma operação competitiva consistente dos aplicativos. O arquivo provavelmente não muda, e a maioria dos recursos do DBMS para trabalhar com ele não é necessária. Vale a pena pensar em retirá-lo do DBMS.
De vez em quando, com uma espécie de estrabismo do comissário, eles fazem a pergunta: o PostgreSQL fará uma base desse e daquele volume? Mas aqui a pergunta em si é estranha: você pode colocar os dados no banco de dados o quanto quiser, desde que haja espaço em disco suficiente e muito ficará. A questão é, por exemplo, como fazer backup de arquivos em petabytes, onde você coloca o backup completo e quanto será retirado. Eu suspeito fortemente que pelo menos parcialmente esses requisitos de volume estejam relacionados ao desejo dos vendedores de equipamentos de vender mais.
Se você armazena documentos no banco de dados, é improvável que os processe lá: a planilha do Excel pode, é claro, ser modificada no servidor, mas essa é uma ocupação estranha. Muito provavelmente, esses arquivos geralmente serão apenas de leitura. É melhor armazenar links para documentos e eles mesmos em algum armazenamento externo. No final, você pode manter a assinatura digital da tabela - para que ela não seja alterada (se você decidir as questões legislativas relevantes).
Outra observação: se você não tem um negócio mega-mega, não algum tipo, digamos, de uma empresa federal, é improvável que tenha uma base muito grande. Se você não armazenar vídeo nele, é claro.
Outro motivo pelo qual o banco de dados é grande são os índices desnecessários. Bases sem índices que não encontrei, mas muitas vezes encontrei bases em que vários índices nas mesmas colunas na mesma ordem. A base permite que você faça isso. Ao criar um índice, verifique se ele duplica um existente. Para ver quais índices não são necessários, consulte pg_stat_user_indexes para ver com que intensidade o índice é usado. Talvez ele não seja necessário.
Me deparei com uma situação (a propósito, típica), quando uma tabela muito grande não é particionada. Em todos os DBMSs, as tabelas grandes são melhor particionadas, mas no PostgreSQL isso é especialmente verdade devido ao nosso amado VACUUM. Eu recomendaria particionar tabelas começando provavelmente com 100 gigabytes. Talvez a partir dos 50 anos. Vi tabelas de terabytes não particionadas e elas viviam, no entanto, em SSDs. Mas isso é um pouco demais, seria melhor cortá-los.
E mais uma observação: quase todos os bancos de dados de um grande volume são apenas arquivos anexados. Dados dinâmicos e alterados raramente são encontrados nesses bancos de dados. Um determinante do que você tem - se o arquivo estiver arquivado, você poderá pensar em como levá-lo a algum lugar. E, a propósito, você pode fornecer acesso a ele a partir do banco de dados. Então o aplicativo não precisa ser alterado: nada mudará para ele.
Algumas dessas observações são da categoria "é melhor ser rico e saudável do que pobre e doente". Muitas vezes, em primeiro lugar, há código legado. Em segundo lugar, algo inesperado aconteceu, eles não pensaram em algo e acontece que nem tudo é tão bonito quanto gostaríamos. Mas, no entanto: não seja muito inteligente. Lembre-se de que, se você é muito inteligente, provavelmente está fazendo algo errado.
[Para ser continuado.]