Algumas coisas novas que aprendi ao operar o SQLite

• É possível usar SQLite em produção em sites pequenos, mas à medida que o Django ORM passou a assumir mais trabalho, ficou mais clara a complexidade operacional do banco de dados
• Uma busca FTS5 em uma tabela com 4.000 linhas levava 5 segundos, mas após executar ANALYZE caiu para cerca de 0,05 segundo, mostrando que as estatísticas do planejador de consultas podem ter grande impacto no desempenho
• Quando um DELETE em grande volume passa de 5 segundos, outras operações de escrita também estouram o tempo limite e até workers e a VM podem ser encerrados, então as tarefas de limpeza foram divididas em pequenos lotes
• Para backup foram usados VACUUM INTO e restic, mas houve encerramentos por OOM; para um backup incremental mais eficiente, Litestream também está sendo testado
• É possível separar tabelas em vários arquivos SQLite, e o autor avalia que o Mess with DNS foi bem operado com SQLite por 4 anos desde 2022, sendo uma escolha adequada para o projeto

---

Operando SQLite em um site Django

• Adotou SQLite como banco de dados de um site Django com base em vários textos mostrando que ele pode ser usado até em sites pequenos em produção
• SQLite também é um banco de dados complexo que exige conhecimento operacional, e neste projeto ele está assumindo mais tarefas do que antes por meio do Django ORM
• Seguindo várias recomendações, o sistema entrou em produção com o modo WAL ativado desde o início
• Este é o quarto site do autor usando SQLite

O impacto de ANALYZE no desempenho da busca

• Uma consulta de busca textual completa com SQLite FTS5 em uma tabela com 4.000 linhas levava 5 segundos
• Depois de executar ANALYZE, a mesma consulta caiu para cerca de 0,05 segundo, a ponto de não exigir mais investigação
• O autor não conseguiu confirmar exatamente qual problema havia no plano de consulta e suspeita de algo parecido com complexidade temporal quadraticamente acidental
• ANALYZE gera informações estatísticas, incluindo a quantidade de linhas das tabelas, ajudando o planejador de consultas a fazer escolhas melhores
• O autor ainda não aprendeu a ler planos de consulta

Limpeza de dados e a limitação de uma única escrita

• Ao excluir em massa linhas desnecessárias, como tarefas concluídas do django-tasks-db, surgiram problemas em cadeia
  • Um comando de limpeza que processava muitas linhas levava mais de 5 segundos
  • Nesse período, escritas no banco feitas por outros workers ultrapassavam o limite configurado de 5 segundos e falhavam por timeout
  • Workers que falhavam ao escrever travavam, e a VM também era encerrada
• O motivo exato de o DELETE ser lento não foi identificado; o autor também considera que pode haver muito código Python sendo executado dentro da transação
• Agora, as tarefas de limpeza são divididas em pequenos lotes para que cada consulta ao banco não passe de 5 segundos
• Essa experiência deixou claro por que às vezes é necessário um banco como Postgres, capaz de lidar com várias escritas ao mesmo tempo
• No futuro, o autor pensa em derrubar o site e fazer uma manutenção programada durante esse tipo de tarefa, mas ainda não definiu um fluxo para isso

Consultas ORM e o volume atual de dados

• Até agora, o autor montou as consultas desejadas com o Django ORM sem verificar separadamente o desempenho, mas fora o problema com ANALYZE, quase tudo funciona bem
• O banco de dados é pequeno, com cerca de 10.000 linhas, e a expectativa é que continue assim

Formas de backup do SQLite

• Foram testadas duas abordagens de backup do SQLite: restic e Litestream
• As tarefas de backup costumam ser monitoradas com um dead man's switch, mas aparentemente ainda não houve um teste real de restauração

• Backup com restic

  • É criada uma cópia do banco com VACUUM INTO, depois ela é compactada com gzip e enviada ao S3
  • O restic é usado para criar backups, verificar snapshots, apagar backups antigos e fazer a limpeza
  • Às vezes o backup era encerrado por OOM, deixando travas para trás, então também era executado restic unlock

  sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
  gzip /tmp/calendar.sqlite
  restic -r s3://s3.amazonaws.com/some_bucket/ unlock
  restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
  restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
  restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
  restic -r s3://s3.amazonaws.com/some_bucket/ prune
  

• Backup incremental com Litestream

  • Para evitar os encerramentos por OOM do backup com restic e testar a eficiência do backup incremental, o autor começou a usar Litestream
  • Depois de escrever o arquivo de configuração, a replicação é executada com o seguinte comando

  litestream replicate -config litestream.yml
  

  • Foi definido retention: 400h para manter o histórico do banco por um certo período, mas o autor ainda não confirmou se isso está funcionando como esperado
  • Os backups estão sendo feitos na AWS, mas como criar credenciais no console é trabalhoso, o autor considera no futuro migrar para outro serviço compatível com S3

Uso de vários arquivos de banco SQLite

• O projeto atual usa apenas um banco, mas no Mess with DNS o autor separou em 3 arquivos de banco de dados tabelas que não precisavam ficar juntas
• O autor considera que essa separação ajudou na operação
• O Mess with DNS foi operado com SQLite por 4 anos desde 2022, e para esse projeto migrar de Postgres para SQLite foi uma boa escolha

Recursos básicos descobertos tardiamente na operação

• Embora o autor use SQLite em projetos web desde 2022, só agora descobriu a existência de ANALYZE
• Mesmo os recursos básicos das tecnologias em uso continuam sendo aprendidos ao longo de anos de operação real

[GN⁺]

Opiniões do Hacker News

• Usar o modo .expert do SQLite permite adiar um pouco o dia em que você precisa aprender a ler planos de consulta: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
  Ao analisar SELECT * FROM x1 WHERE a=? AND b>?;, ele informa um índice recomendado, como CREATE INDEX x1_idx_000123a7 ON x1(a, b);; depois de criá-lo e analisar novamente, indica que nenhum novo índice é necessário
  Mesmo em bancos de dados “de verdade”, como Postgres, em geral recomenda-se dividir tarefas de limpeza em pequenos lotes. Só que, em pequena escala, fica menos evidente que a operação é ineficiente; portanto, a abordagem do texto original estava mais correta do que parecia

  • Em alguns bancos de dados, apagar 10 milhões de linhas também registra o mesmo volume de dados de undo. No Oracle, se isso não for feito backup e limpo a tempo, o espaço em disco para logs de arquivamento pode se esgotar
    Fazer commits frequentes ajuda, mas, ao limpar regularmente bancos de dados grandes, o particionamento foi o mais eficaz. Excluir a partição mais antiga termina quase imediatamente
    Dito isso, em “os workers não conseguem escrever no banco de dados, travam e a VM é encerrada”, não fica claro por que a VM é encerrada. Aqui, VM parece significar a máquina virtual em que o sistema operacional está rodando
  • Em MySQL de grande porte usando replicação baseada em linhas, era necessário executar em lotes UPDATE ou DELETE que alterassem milhões de linhas. Isso porque, se fosse tratado com uma única query, milhões de linhas atualizadas teriam de ser enviadas de uma só vez a todas as réplicas
  • Parece semelhante a EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
    O EXPLAIN comum geralmente imprime bytecode prolixo demais, mais do que o necessário, mas EXPLAIN QUERY PLAN mostra um plano resumido
  • Fico me perguntando se “deixar menos evidente que é uma operação ineficiente” está sendo visto como uma vantagem

• Para alguém que trabalha com bancos de dados, foi frustrante ler, e deu vontade de investigar a causa diretamente e resolver. Se uma tabela tem apenas 10 mil linhas, até uma varredura completa da tabela deveria ser muito rápida; ainda mais no SQLite, rodando dentro do processo no mesmo servidor físico
  A solução que me veio à cabeça foi, obviamente, “crie um índice”. A exclusão lenta parece muito possivelmente o clássico problema N+1 que usuários de ORM costumam enfrentar antes de entender as interações internas com o banco de dados, e espero que Julia publique um texto de acompanhamento

• Como criar credenciais no console da AWS era trabalhoso demais, alguns anos atrás criei uma ferramenta que resolve só esse problema
  uvx s3-credentials create my-existing-s3-bucket
  Esse comando imprime credenciais de leitura e escrita restritas apenas àquele bucket. É possível restringir ainda mais as permissões com --read-only e --write-only, ou permitir ler e escrever apenas chaves que comecem com um prefixo específico usando --prefix foo/bar
  Também testei com Restic e Cloudflare R2, e funcionou muito bem

  • Uma forma mais geral de lidar com serviços complexos da AWS é aprender um pouco de Terraform e deixar o restante para um LLM. Fica muito mais fácil destruir tudo depois, sem precisar lembrar quais recursos você criou
  • Há também uma ferramenta existente parecida: https://litestream.io/
  • É uma ferramenta excelente, a ponto de parecer algo que a AWS CLI poderia incorporar. Mas fico curioso sobre quando seriam necessárias credenciais somente para escrita

• Na era dos LLMs, passei a valorizar ainda mais os textos da Julia. Um processo de investigação sincero é o antídoto para textos gerados automaticamente que fingem saber tudo com confiança

• Executo o backup assim:
  OUT="${i}.sql.zst"
PART="${OUT}.part"
sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
mv "${PART}" "${OUT}"
  Se os writers usarem WAL, isso não os bloqueia, e gera um dump com boa taxa de compressão e fácil de sincronizar. O banco de dados do Home Assistant tem 1,8 GB, mas o dump comprimido tem 286 MB, e estimo que cerca de 90% do conteúdo gerado diariamente seja igual

  • Fico curioso sobre o que é feito backup no Home Assistant. O backup padrão é grande demais, então deixei só as configurações e excluí vídeos, cache e repositórios baixados pelo HACS, mas não sei se estou deixando algo importante de fora
    Também fico curioso se o motivo de o banco de dados ter ficado tão grande é por causa de registros de séries temporais preservados por muito tempo
  • VACUUM INTO, .backup, sqlite3_rsync e Litestream também não bloqueiam writers. .backup usa a API de backup
  • Em um banco de dados em tempo real, tive um travamento causado por .dump e depois migrei para .backup. Ainda assim, a abordagem de escrever em .part e depois fazer mv é elegante

• Como só há expressões como “não investiguei mais”, “é meu melhor palpite”, “talvez outras coisas também?” e “talvez muito código Python seja executado dentro da transação”, vejo que o texto não tem conteúdo substancial. Às vezes faz até palpites errados sem verificar ou investigar direito
  Como usuário Debian, o motivo de eu nem abrir resultados sobre Linux que apontam para fóruns do Ubuntu é justamente haver palpites errados demais. Por outro lado, embora o Arch seja bem diferente do Debian, em geral consulto a Arch Wiki, escrita por pessoas com conhecimento

  • Julia Evans tem um conhecimento técnico muito amplo e é especialmente boa em remover a aura de mistério da tecnologia e ajudar a entender como é o processo real de resolver problemas
    Este texto não finge ser uma explicação da maior especialista em SQLite do mundo, e já no título define claramente as expectativas como “aprender algumas coisas sobre operar SQLite”
    A mensagem que atravessa os textos da Julia é que é possível entender problemas de forma simples e acumular conhecimento, sem precisar saber tudo nem fingir que sabe. Compartilhar da forma mais clara possível aquilo que se descobriu até agora é uma boa postura
  • Acho este texto ótimo porque mostra bem a situação de um usuário comum inteligente que usa essa tecnologia. O foco de quem escreveu é operar um site, e esse tipo de problema é algo em que usuários do dia a dia, que não passam o dia inteiro lidando com uma ferramenta específica, costumam tropeçar
    Ontem mesmo, ao criar uma funcionalidade, usei 2 linguagens de programação, 2 sistemas de build, um provedor de nuvem, uma ferramenta de gerenciamento de segredos, um framework complexo de comunicação cliente-servidor em duas linguagens, um sistema de controle de versão, um editor e uma ferramenta de CI. Além disso, ainda dá para se aprofundar em sistema operacional e versões de runtime, banco de dados, proxy reverso, cache e lógica de domínio
    Se você se aprofundar em todos os temas que encontra, não termina nada; por isso, como a autora, é preciso escolher o problema em que focar
  • A Arch Wiki é uma das melhores fontes sobre Linux. Quando eu usava Mint, consultava sempre, mas, depois que passei a usar Arch de fato, acabei consultando menos do que na época do Mint

• O problema do DELETE pode ser facilmente mitigado com exclusões em lote, pausas entre lotes e pré-carregamento de rowid via SELECT. O SELECT não causa bloqueio
  Se os dados foram adicionados sequencialmente à mesma tabela, há uma boa chance de terem sido armazenados no arquivo em uma ordem parecida; apagar nessa ordem, ou na ordem inversa, pode ser mais rápido. O resultado varia conforme o meio de armazenamento e outras condições

  • O pré-carregamento de IDs de linha foi muito eficaz não só no SQLite, mas também em grandes clusters Aurora MySQL ou Postgres. Era possível enviar o SELECT para uma réplica, e a pressão de memória nos índices causada pela filtragem de linhas estava impondo uma carga grande à CPU e ao cache de buffers do banco de dados
    Se for difícil excluir dados desnecessários do caminho principal de processamento com algo como pruning de partições, essa é uma estratégia muito poderosa

• Aprofundar-se em bancos de dados um pouco além do seu nível atual de familiaridade ou das exigências do trabalho ainda é uma boa forma de melhorar a habilidade técnica. Muitos desenvolvedores web travam diante de ferramentas de banco de dados, e eu também travo de forma parecida em algumas tecnologias de operações, como K8s
  Saber como SQL é transformado em operações de leitura e escrita de dados em disco ajuda muito a julgar intuitivamente quais abordagens podem ser aceitáveis. Também é preciso entender o sistema de bloqueios do banco de dados
  Com esse conhecimento, você fica menos propenso a se assustar quando até um simples COUNT no Postgres não termina rapidamente

• sqlite_stat1 e sqlite_stat4 armazenam várias informações estatísticas sobre a distribuição dos valores de índices, e o planejador de consultas as usa para estimar a seletividade e a utilidade de um índice
  sqlite_stat1 fornece apenas o número de registros do índice e o número médio de registros por valor; quando ativado, sqlite_stat4 também armazena dados de histograma

• O Litestream é muito interessante, e consegui executá-lo usando S3 como backend. Ele pode fazer com que muitos apps que usam SQLite passem a depender muito pouco do estado do sistema de arquivos
  O estado do S3 é muito mais fácil de gerenciar, e o provedor também cuida de backups e sincronização