SQLite ou PostgreSQL? É complicado!

Na Twilio, blog é coisa séria. Para nos ajudar a entender o que funciona bem e o que não funciona em nosso blog, temos um dashboard que combina os metadados que mantemos para cada artigo, como autor, equipe, produto, data de publicação etc., com informações de tráfego do Google Analytics. Os usuários podem solicitar gráficos e tabelas interativamente enquanto filtram e agrupam os dados de várias maneiras diferentes.

Eu escolhi o SQLite para o banco de dados que suporta este dashboard. No início de 2021, quando eu construí este sistema, essa parecia ser uma escolha perfeita para o que eu pensei que seria um aplicativo pequeno de nicho que meus colegas de equipe e eu poderíamos usar para melhorar nosso blog. Mas quase um ano e meio mais tarde, este aplicativo rastreia o tráfego diário para cerca de 8.000 artigos nos blogs Twilio e SendGrid, com cerca de 6,5 milhões de registros diários individuais de tráfego, e com uma base de usuários que ultrapassou 200 colaboradores.

Em dado momento, percebi que algumas consultas levavam alguns segundos para produzir resultados, então comecei a me perguntar se um banco de dados mais robusto, como o PostgreSQL, proporcionaria melhor desempenho. Tendo expressado publicamente minha aversão a índices de desempenho, eu resisti ao desejo de pesquisar comparações on-line e, em vez disso, embarquei em uma série de experimentos para medir com precisão o desempenho desses dois bancos de dados para os casos de uso específicos deste aplicativo. O texto a seguir é um relato detalhado do meu esforço, dos resultados do meu teste (incluindo uma reviravolta surpreendente!), e da minha análise e decisão final, que acabou por ter mais envolvimento do que eu esperava.

Portabilidade do aplicativo para o PostgreSQL

A primeira etapa foi verificar se eu poderia reconfigurar facilmente o aplicativo para usar o SQLite ou o PostgreSQL, pois isso me permitiria executar comparações lado a lado. Eu instalei o PostgreSQL no meu notebook Mac para trabalhar no desenvolvimento.

Adaptar o código para que ele fosse compatível com o PostgreSQL foi relativamente fácil porque este aplicativo usa o SQLAlchemy, uma biblioteca de ORM para Python que permite ao desenvolvedor criar esquemas e consultas usando principalmente construções Python independentes de banco de dados. Ao tornar o URL de conexão do banco de dados configurável, foi possível fazer a troca entre os URLs postgresql:// e sqlite:// . O código Python não precisou de nenhuma modificação, pois as diferenças entre esses bancos de dados foram tratadas internamente pelo SQLAlchemy. O SQLAlchemy até criou todas as tabelas e índices no meu novo banco de dados do PostgreSQL, exatamente como estavam no SQLite.

Para permitir que o aplicativo funcionasse totalmente com o PostgreSQL, também precisei migrar os dados atualmente armazenados no banco de dados do SQLite. Eu usei o pgloader, um script open source razoavelmente completo que importa dados de várias fontes para um banco de dados do PostgreSQL. Felizmente, o SQLite é uma das fontes de dados suportadas.

A maneira mais fácil de importar dados com o pgloader é criar um arquivo de configuração que defina a origem, o destino e as opções. O que eu fiz:

A única opção que usei foi data only, que diz ao pgloader para não criar tabelas ou índices, apenas mover dados dos bancos de dados de origem para os de destino. Isso é importante porque eu queria ter o esquema de banco de dados o mais próximo possível do SQLite One, e a melhor maneira de fazer isso é deixar que o SQLAlchemy crie todas as tabelas e índices.

Para executar a migração de dados, o pgloader é invocado com o nome do arquivo de configuração como seu único argumento:

Depois de algumas tentativas, o processo de importação funcionou sem problemas. As tentativas iniciais com falha me alertaram sobre alguns problemas potencialmente graves que eu tinha no meu design de banco de dados, que o SQLite decidiu ignorar ou não relatar:

• Para slugs do artigo, eu defini uma coluna VARCHAR de 256 caracteres de comprimento. A migração de dados falhou para quatro artigos que tinham slugs maiores que esse limite. O SQLite, com seu sistema de digitação incomum, não se importou e armazenou os slugs mais longos sem reclamar. O PostgreSQL tem uma aplicação rigorosa de seus tipos de coluna, portanto, essas quatro postagens não puderam ser migradas. A solução foi aumentar a coluna slug para 512 caracteres e tentar novamente.
• Também relacionado à digitação, uma das minhas tabelas inicialmente tinha chaves primárias de número inteiro, mas em dado momento eu decidi alterar essas chaves para strings UUID. Quando fiz essa alteração no SQLite, migrei a tabela manualmente e não percebi que deixei a coluna principal nesta tabela definida como integer. Mais uma vez, o SQLite armazenou meus UUIDs nesta coluna sem problemas, e até os indexou, mas, para fazer isso através do PostgreSQL, eu precisei corrigir o esquema e declarar adequadamente essas chaves como strings.

Nesse ponto, com um esforço relativamente baixo, eu poderia alternar entre bancos de dados do SQLite e do PostgreSQL equivalentes apenas editando o URL do banco de dados em um arquivo de configuração e reiniciando o servidor Python.

Testei manualmente algumas consultas em meu notebook para desenvolvimento e senti tudo mais rápido com o banco de dados do PostgreSQL, o que foi uma primeira impressão encorajadora.

Criação de um teste de desempenho

Minha próxima tarefa foi criar um teste que fosse automatizável e repetível, para que eu pudesse testar o sistema em ambos os bancos de dados e determinar o vencedor. Simples, certo?

Infelizmente, o projeto de um teste que abrange todas as maneiras pelas quais esse sistema pode ser usado é muito desafiador devido ao grande número de possibilidades, pois eu o projetei para permitir que o usuário crie muitas consultas diferentes. Por exemplo, ele permite que o usuário obtenha estatísticas de tráfego durante um período de tempo que pode cobrir dias, semanas ou até mesmo anos, e categorize os resultados por artigo, autor, equipe, produto, linguagem de computador, idioma escrito ou ano de publicação. O usuário também pode dividir os resultados de tráfego por país de origem. Ao recuperar artigos, o usuário pode optar por ver todas as traduções de um artigo agrupadas com o original. Para todas as consultas, o usuário pode filtrar ou descartar uma seção do conjunto de dados, fornecida por qualquer combinação de autores, equipes, produtos, linguagens de computador, idiomas escritos, ou datas de publicação. E tudo isso pode ser feito para os blogs Twilio ou SendGrid, ou para ambos os blogs combinados.

Para não iniciar um caminho sem volta, decidi me concentrar nos casos de uso mais comuns, então criei uma lista de solicitações de API que as representam. Aqui está a lista que eu criei:

Esses URLs precisam ser preenchidos com argumentos de string de consulta adicionais. Os argumentos start e end são necessários em todas as consultas, pois especificam o período de tempo solicitado. Descobri que isso tem uma grande influência sobre os tempos de resposta, por isso, o meu plano era tentar diferentes períodos.

Há um grande número de argumentos de string de consulta que podem ser usados para implementar filtros. O argumento blog que incluí nos URLs acima seleciona o blog da Twilio, que é o que tem mais tráfego. Eu decidi não complicar meus testes com outros filtros porque, ao olhar os logs de uso, observei que a maioria das consultas por usuários não inclui nenhum.

Munido dessa lista, criei uma função Python curta que executa essas consultas e registra quanto tempo cada consulta levou em um dicionário results:

A função test() faz uma cópia da lista requests, a reordena de forma aleatória e, em seguida, executa a solicitação curl como subprocesso. Usa a função timeit() da biblioteca padrão Python para medir o tempo que cada consulta leva para retornar uma resposta e adiciona essa medição ao dicionário results, sob o URL correspondente.

A razão pela qual estou randomizando a lista é que pretendo executar várias instâncias dessa função em paralelo para simular clientes simultâneos. Fazer com que a função passe pelas consultas em ordem aleatória é conveniente, pois isso garante que o banco de dados terá uma variedade de consultas para as quais trabalhar em um determinado momento, em vez de receber várias instâncias da mesma consulta.

A função recebe a parte do domínio do URL como o argumento server. Eu queria que isso fosse facilmente configurável para que eu pudesse alternar entre meus sistemas de desenvolvimento e produção. Uma chave de API também é passada para essa função para que as solicitações possam ser autenticadas. Por fim, os argumentos de string de consulta necessários start e end são passados e adicionados ao URL antes de fazer a solicitação.

O dicionário results usa os URLs da lista requests como chaves. Para cada chave, eu armazeno uma matriz de resultados e não apenas um único valor, pois isso me permitiria registrar várias execuções para cada consulta e obter uma média.

Para concluir meu script de teste, adicionei uma função run_test() e um analisador de linha de comando:

A função run_test() inicia um ou mais threads, de acordo com o argumento num_threads. Todos os threads são configurados para executar a função test() em paralelo. Isso simula a carga proveniente de vários clientes simultâneos, cada um passando pela lista de URLs em sua própria ordem aleatória, para criar alguma imprevisibilidade.

Depois que todos os threads terminarem, o run_test() imprime o tempo médio da solicitação para cada URL de solicitação e também uma média de todas as consultas combinadas, que é a métrica que decidi usar para minha análise.

Os argumentos de linha de comando permitem que eu passe a raiz do servidor, a chave de API e as datas de início e término para consulta e a simultaneidade. Com esses controles, tenho a capacidade de testar uma variedade de cenários.

O script de teste já está pronto, por isso é hora de obter algumas métricas!

Testes no sistema de desenvolvimento

O sistema de desenvolvimento em que trabalho é um notebook Mac com 6 núcleos de hiperprocessamento e 16 GB de RAM. O ambiente de produção desse dashboard é um servidor virtual Linode com 1 vCPU e 2 GB de RAM.

De acordo com experiências de comparação anteriores, sei que os resultados de um sistema rápido nem sempre são os mesmos de um sistema mais lento, então meu objetivo final é testar o sistema de produção e tomar decisões com base nos resultados que obtenho nessa plataforma.

Mas antes de fazer isso, eu queria fazer uma primeira rodada de testes de "prática" em meu notebook, tanto como uma forma de garantir que o script de teste estivesse funcionando corretamente e, também, por que não admitir, porque eu estava curioso sobre como esses dois bancos de dados funcionariam em uma plataforma tão poderosa.

A metodologia de teste que decidi usar é a seguinte: Eu testaria o sistema em execução nos dois bancos de dados, para consultas com períodos de uma semana, um mês, um trimestre e um ano, com todas as consultas tendo 01-01-2021 como a data de início. Eu também repetiria os testes com um, dois e quatro clientes simultâneos. Para cada teste individual, eu executaria o script três vezes e registraria a melhor execução dos três. A métrica que eu usaria é a média total de todas as consultas.

Com esse plano, terminei com 24 pontos de dados (2 bancos de dados x 4 períodos de consulta x 3 níveis de simultaneidade). O gráfico a seguir mostra o tempo de resposta para o PostgreSQL (azul) e para o SQLite (vermelho), com um único cliente.

Aqui é muito claro que o PostgreSQL retorna resultados mais rapidamente e é especialmente eficiente para consultas mais curtas. Os tempos de resposta do PostgreSQL aumentam mais ou menos linearmente à medida que o período de consulta cresce, mas o SQLite teve um tempo de resposta quase plano nos três primeiros testes e depois sofreu muito no último.

Abaixo estão os gráficos para o mesmo teste com dois e quatro clientes simultâneos.

Achei interessante que mudar a simultaneidade não parece ter um grande impacto nos tempos de resposta para a maioria dos testes, provavelmente devido aos vários núcleos que tenho neste notebook. O único caso em que há uma diferença é nas consultas de um ano, em que o PostgreSQL parece sofrer proporcionalmente mais do que o SQLite.

Para ajudar a visualizar isso, veja abaixo um gráfico que mostra os tempos de resposta para os testes do PostgreSQL, comparando os três níveis de simultaneidade para cada um dos períodos de consulta.

Isso confirma que, para consultas envolvendo um número pequeno ou médio de linhas, uma simultaneidade de até quatro clientes não afeta os tempos de resposta de uma forma geral. Mas, à medida que o número de linhas envolvidas cresce, uma simultaneidade mais alta começa a desacelerar as coisas.

Veja o mesmo gráfico para o SQLite:

É realmente uma surpresa, porque isso mostra que o SQLite pode lidar com cargas simultâneas muito melhor do que o PostgreSQL neste sistema. É claro que os tempos de resposta são mais longos aqui, mas o fato de que a simultaneidade tem menos efeito sobre esses tempos de resposta é muito interessante.

A plataforma de produção mostrará resultados semelhantes? Minha expectativa é que uma única CPU, em comparação com as seis no meu notebook, fará da simultaneidade um fator muito maior nos resultados.

Testes no sistema de produção

Munido de um script de comparação agora bem testado, iniciei um novo Linode com um clone do sistema de produção, repeti todas as etapas para migrar o banco de dados do SQLite para o PostgreSQL, e me certifiquei de que poderia alternar facilmente os bancos de dados editando o arquivo de configuração e reiniciando o serviço.

Para isso, decidi executar o script de teste em meu próprio notebook, de modo que todas as latências envolvidas no tráfego, que passam pela Internet, criptografia e proxy seriam incluídas nos resultados. Eu poderia ter optado por executar os testes no mesmo host do serviço e evitar esses custos extras, mas, no final, meu objetivo é tornar o serviço melhor para os usuários, e os usuários não têm escolha, senão passar por todas essas camadas aparentemente não relacionadas.

Aqui está o gráfico que eu obtive ao usar uma simultaneidade de um:

É uma reviravolta totalmente inesperada. As linhas estão se cruzando!

O PostgreSQL bate o SQLite nas consultas de curta e média duração, mas tem muitos problemas com a consulta de um ano, que leva quase duas vezes mais tempo que o SQLite. Caso você esteja pensando que isso foi ocasional, eu repeti esses testes várias vezes e confirmei que esses resultados estão realmente corretos.

Aqui estão os gráficos para dois e quatro clientes neste servidor de produção.

Embora as formas das curvas sejam praticamente idênticas em todos os níveis de simultaneidade, basta olhar para a escala dos eixos Y para ver que, nesta plataforma, os tempos de resposta são muito mais afetados pela simultaneidade do que o meu notebook. Para a consulta de um ano com 4 clientes simultâneos, os tempos de resposta foram extremamente lentos, com cerca de 14 e 8 segundos para o PostgreSQL e o SQLite, respectivamente.

O PostgreSQL e o SQLite são igualmente afetados pela simultaneidade? Vamos dar uma olhada neles separadamente para descobrir. O próximo gráfico mostra como o PostgreSQL se saiu nos quatro períodos com um, dois e quatro clientes simultâneos.

Isso mostra que, para esse banco de dados, as coisas ficam muito fora de controle para consultas grandes, mas para consultas de pequeno e médio porte, os tempos de resposta são relativamente estáveis.

Veja abaixo o mesmo gráfico para o SQLite:

É uma imagem diferente, certo? Com uma única CPU, a simultaneidade no SQLite desempenha um papel até mesmo nas consultas de pequeno e médio porte. Os tempos de resposta crescem menos do que linearmente, mas sempre crescem, mesmo nos testes menores.

Para me ajudar a visualizar melhor essas diferenças, criei outro conjunto de gráficos. Para cada período de consulta que testei, os gráficos a seguir comparam os dois bancos de dados em cada ponto de simultaneidade.

Esses gráficos mostram claramente que o PostgreSQL pode lidar com consultas pequenas e médias muito melhor do que o SQLite, até o ponto em que aumentar a simultaneidade tem um efeito muito pequeno nos tempos de resposta, o que é ótimo. O SQLite, por outro lado, é afetado mais diretamente pela simultaneidade para todos os tamanhos de consulta e, embora funcione pior nas consultas de pequeno e médio porte, ele acaba significativamente à frente do PostgreSQL para as consultas mais longas.

Ajuste de desempenho

Até agora, não me preocupei em configurar os bancos de dados, simplesmente presumi que a configuração padrão seria boa o suficiente. No caso do SQLite não há muito a configurar, mas o PostgreSQL tem algumas opções de ajuste.

Depois de examinar as opções de configuração relacionadas ao uso da memória, encontrei a opção work_mem, que configura a quantidade de memória que cada consulta pode usar antes de começar a armazenar dados em arquivos de disco temporários. O padrão para esta opção é 4 MB. Mudei para 16 MB:

Os testes de semana, mês e trimestre tiveram tempos de resposta semelhantes aos anteriores. Mas essa mudança melhorou as consultas mais longas. Abaixo estão os gráficos para um, dois e quatro clientes depois que eu executei os testes do PostgreSQL de um ano novamente:

Com essa alteração, as linhas que não se cruzaram e o PostgreSQL começaram a retornar mais rapidamente do que o SQLite, mesmo para as consultas mais longas. Para o teste mais pesado com consultas de um ano e 4 clientes, os tempos de resposta passaram de 14 segundos para 6 com o servidor de banco de dados ajustado para memória. Essas consultas ainda são mais lentas do que eu gostaria, mas, considerando que estou executando em uma plataforma low-end, acho que são razoáveis.

Conclusão

Como você pode adivinhar, depois dos resultados que obtive, decidi mudar para o PostgreSQL. Depois de atualizar o sistema de produção, perguntei informalmente a alguns usuários sobre o desempenho e todos pensaram que o sistema estava respondendo notavelmente mais rápido do que antes, então minha missão foi cumprida.

Com este artigo, espero deixar claro que os únicos índices valiosos são aqueles executados em sua própria plataforma, com sua própria pilha, com seus próprios dados e com seu próprio software. E, mesmo assim, você pode precisar adicionar otimizações personalizadas para obter o melhor desempenho.

Em termos de melhorar ainda mais o desempenho, este exercício de teste me deixa com duas possibilidades:

• Se eu receber o orçamento, agora sei que a adição de CPUs provavelmente melhorará esses testes mais longos. Mudar para um servidor dedicado com quatro CPUs deve fazer uma diferença considerável e aproximar os tempos de resposta aos que eu medi no meu notebook.
• Sem custos adicionais, a principal área de melhoria está nessas consultas mais longas, que podem ser simplificadas se o banco de dados tiver armazenado totais mensais pré-separados, além dos dados diários. Um ano de tráfego poderia então ser recuperado a partir de 12 linhas por artigo em vez de 365. Algumas das economias serão compensadas por uma lógica mais complexa quando as consultas não estiverem alinhadas nos limites do mês, mas, em geral, isso deve reduzir os tempos de consulta mais longos.

Espero que você tenha encontrado algumas ideias novas neste artigo, e sinta-se encorajado a comparar e otimizar seus próprios projetos!

Miguel Grinberg é engenheiro chefe de software para conteúdo técnico na Twilio. Entre em contato com ele pelo e-mail mgrinberg@twilio.com caso você tenha um projeto interessante que queira compartilhar neste blog!