SQLite também precisa de edições no estilo Rust

 (mort.coffee)
3 pontos por GN⁺ 4 시간 전 | 2 comentários | Compartilhar no WhatsApp
  • O SQLite é um RDBMS autocontido amplamente usado em armazenamento local e projetos embarcados, mas, por causa da compatibilidade retroativa, mantém valores padrão desfavoráveis para segurança e desempenho
  • Por padrão, ele não impõe restrições de chave estrangeira e pode reutilizar ROWID, o que pode causar problemas de integridade de dados, como publicações de um usuário excluído ficarem associadas a outro usuário que recebeu o mesmo ID
  • Tabelas comuns também armazenam tipos de dados diferentes dos declarados nas colunas; as tabelas STRICT impedem isso, mas é preciso especificar strict em cada tabela, o que entra em conflito com práticas que usavam nomes de tipos definidos pelo usuário
  • Em escritas concorrentes, o SQLITE_BUSY ocorre imediatamente; o WAL fica desativado; e as configurações conservadoras de sincronização podem ser melhoradas, respectivamente, com os pragmas busy_timeout, journal_mode e synchronous
  • Um sistema de edições baseadas em ano, como PRAGMA edition = 2026, permitiria escolher um conjunto de padrões seguros e continuar evoluindo as configurações padrão do SQLite sem quebrar o comportamento existente

Pontos fortes do SQLite e padrões defasados

  • O SQLite pode ser chamado de banco de dados padrão da indústria para projetos embarcados e armazenamento local de dados, e até alguns softwares de servidor, como o lobste.rs, também rodam em SQLite
  • Diferentemente dos RDBMS tradicionais executados como processos separados, ele é um RDBMS em forma de biblioteca, o que permite manter o software autocontido
  • Como em formatos de arquivo comuns, não é necessário escrever serializadores e parsers personalizados, oferecendo ao mesmo tempo as vantagens de um RDBMS e do armazenamento baseado em arquivos
  • No entanto, permanece o problema de vários valores padrão inadequados que afetam integridade de dados, concorrência e desempenho

Problemas de integridade por não impor chaves estrangeiras

  • Chaves estrangeiras são um mecanismo essencial para manter a consistência do banco de dados e evitar referências quebradas, mas o SQLite não as impõe por padrão
  • Mesmo declarando que posts.user_id referencia users.id, a configuração padrão não impede as seguintes operações
    • Criar uma publicação sem um ID de usuário válido
    • Excluir um usuário deixando as publicações dele para trás
  • INTEGER PRIMARY KEY se torna um alias para o ROWID da tabela, e o algoritmo de alocação de ROWID do SQLite reutiliza IDs em algumas situações
    • Mesmo que Bob escreva uma publicação com o ID 1 e depois apenas exclua a conta, nenhum erro de chave estrangeira ocorre
    • Se depois Alice receber o ID reutilizado 1, as publicações antigas de Bob passam a ficar associadas a Alice
    • Como a consulta em si funciona normalmente, o problema é mais difícil de detectar do que uma simples referência quebrada
  • É possível impor chaves estrangeiras ativando o seguinte pragma em cada conexão
PRAGMA foreign_keys = ON;
  • Se isso tivesse sido aplicado desde o início, excluir um usuário que ainda tem publicações seria interrompido com o erro FOREIGN KEY constraint failed

Tabelas comuns permitem tipos diferentes dos declarados

  • Valores no SQLite são um entre NULL, INTEGER, REAL, TEXT e BLOB, mas declarações de colunas em tabelas comuns não restringem rigidamente os tipos que podem ser armazenados; elas determinam a afinidade de tipo (type affinity)
  • Em uma coluna com afinidade INTEGER, aplicam-se as seguintes regras de armazenamento
    • TEXT que pode ser interpretado como inteiro é convertido para INTEGER
    • TEXT que pode ser interpretado como número real é convertido para REAL, um ponto flutuante de dupla precisão
    • Os demais valores são armazenados mantendo seu tipo original
  • Outras afinidades também têm regras próprias de conversão
    • A afinidade BLOB armazena os valores como estão
    • A afinidade TEXT mantém BLOB, TEXT e NULL como estão e converte números para TEXT
    • A afinidade REAL se comporta como a afinidade INTEGER, mas converte inteiros para REAL
  • Por isso, mesmo em uma coluna duration_sec INTEGER, é possível armazenar uma string não numérica como 'Way too long, I mean come on'
  • Em projetos reais, também houve casos em que strings '1' e '0' eram gravadas em vez dos números 1 e 0 usados para booleanos, exigindo limpeza de dados e depuração

Tabelas STRICT e conflitos com tipos definidos pelo usuário

  • Tabelas STRICT geram erros de tipo como cannot store TEXT value in INTEGER column ao inserir um tipo de dado incorreto em uma coluna
CREATE TABLE music (
  id INTEGER PRIMARY KEY,
  name TEXT,
  duration_sec INTEGER
) strict;

  • Como não há um pragma que torne todas as tabelas globalmente estritas, é preciso adicionar strict a cada instrução CREATE TABLE sem esquecer nenhuma

  • O SQLite defende que a tipagem flexível é útil, mas mesmo em tabelas STRICT é possível criar colunas que armazenam valores de todos os tipos ao especificar explicitamente o tipo ANY

    • Isso permite distinguir explicitamente colunas que aceitam tipos arbitrários daquelas que impõem tipos específicos

  • O problema de compatibilidade mais realista é que as tabelas STRICT não apenas impõem tipos de dados, mas também mudam as regras de interpretação dos especificadores de tipo

  • Como tabelas comuns interpretam nomes de tipos

    • Tabelas comuns do SQLite determinam a afinidade de acordo com a string de tipo declarada
    • Se contiver "INT", é tratada como INTEGER
    • Se contiver uma entre "CHAR", "CLOB" ou "TEXT", torna-se TEXT
    • Se contiver "BLOB" ou se o tipo for omitido, é tratada como BLOB
    • Se contiver uma entre "REAL", "FLOA" ou "DOUB", torna-se REAL
    • Se não corresponder a nenhuma condição, é tratada como NUMERIC
    • Combinando a tipagem frouxa com essas regras, é possível anexar nomes de tipos definidos pelo usuário a colunas, como DATETIME, KEY_VALUE_SET e COLOR
    • Conectores ou wrappers de banco de dados podem serializar e desserializar automaticamente olhando para o nome do tipo
    • Mesmo sem uma funcionalidade de tratamento especial, o próprio nome do tipo documenta os dados esperados na coluna
    • Se STRICT simplesmente se tornasse o padrão, seria preciso abrir mão desse uso, portanto são necessários aliases explícitos para tipos definidos pelo usuário
    CREATE TYPE KEY_VALUE_SET = TEXT;
    • Se aliases como KEY_VALUE_SET pudessem ser usados em tabelas estritas, o padrão de dados esperado em colunas TEXT que a aplicação precisa fazer parse também poderia ser registrado no esquema
    • Um recurso para vincular restrições CHECK a tipos definidos pelo usuário também seria útil
    • O padrão SQL:1999 já tem uma sintaxe de alias de tipo que também oferece suporte a restrições, chamada CREATE DOMAIN, então o SQLite poderia implementá-la dando suporte a essa instrução padrão

Escritas concorrentes e SQLITE_BUSY

  • O SQLite permite várias leituras simultâneas, mas processa apenas uma escrita por vez
  • Na configuração padrão, quando dois processos tentam obter o bloqueio de escrita ao mesmo tempo, um deles recebe imediatamente um erro SQLITE_BUSY, sem esperar
    • Como escritas que fazem I/O de disco podem naturalmente ficar lentas, é mais natural aguardar por algum tempo até o bloqueio ser liberado
    • Também houve casos em que sistemas reais paravam intermitentemente, exigindo a escrita manual de loops de nova tentativa
  • A configuração abaixo só retorna SQLITE_BUSY depois de tentar obter o bloqueio novamente por até 5 segundos
PRAGMA busy_timeout = 5000;
  • Em operação normal, é adequado concentrar todas as escritas em um único processo e, se possível, em uma única thread; escritas concorrentes não podem ser rápidas
  • Ainda assim, há casos legítimos de operação em que escritas concorrentes são necessárias
    • Limpar o banco de dados de forma interativa com a ferramenta de linha de comando sqlite3
    • Executar scripts para tarefas administrativas raras que não têm um frontend separado
  • Por causa da configuração padrão, essas tarefas podem provocar um SQLITE_BUSY inesperado e interromper o software em execução

WAL e padrões de desempenho

  • O SQLite, quando configurado corretamente, pode ser rápido o bastante para assumir parte dos papéis antes desempenhados por grandes servidores como PostgreSQL ou MySQL, mas seu desempenho padrão não é bom
  • Mais detalhes sobre ajustes finos em ambientes de servidor podem ser vistos em Optimizing SQLite for servers
  • O problema mais importante entre os padrões é que o Write-Ahead Log, ou WAL, fica desativado
PRAGMA journal_mode = WAL;
  • O WAL aumenta significativamente a velocidade de escrita na maioria das situações
  • Usar a configuração a seguir em conjunto pode reduzir muito o número de sincronizações de disco sem risco de corrupção de dados
PRAGMA synchronous = NORMAL;

Edições do SQLite baseadas em ano

  • Alterar diretamente os valores padrão existentes agora quebraria softwares antigos e poderia fazer usuários temerem futuras atualizações do SQLite
  • Para manter a compatibilidade retroativa e ainda permitir escolher padrões melhorados, seria possível adicionar um único super pragma
PRAGMA edition = 2026;
  • A edição 2026 funcionaria, no mínimo, como um alias para as seguintes configurações
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
  • Nessa edição, o modo strict também seria aplicado como padrão para novas tabelas
  • Assim como as edições do Rust, seria uma forma de preservar o comportamento do código existente enquanto se opta explicitamente por novos comportamentos padrão
  • Diferentemente de um modo único como o "use strict"; do JavaScript, edições baseadas em ano podem mudar, com o tempo, o conjunto de padrões considerados razoáveis
  • Por exemplo, se o WAL2 do Hctree entrasse no branch principal em 2034, um futuro PRAGMA edition = 2034 poderia ser configurado para escolher journal_mode = WAL2
  • Um sistema de edições poderia ser uma solução de compromisso para evoluir os padrões de segurança e desempenho sem quebrar o comportamento padrão dos programas existentes nem manter o SQLite preso a configurações do passado

Leituras relacionadas

2 comentários

 
tomskang 2 시간 전

A teoria parece boa, mas considerando conflitos de compatibilidade e overhead, acho que sairia no prejuízo.
O SQLite nasceu como um DBMS para funcionar em ambientes embarcados como o Aegis,
e o mundo embarcado é esse negócio de sair metendo void** pra todo lado, declarar __weak__ e depois sobrescrever função. Nesse mundo, fico pensando se isso realmente seria necessário.
 
GN⁺ 4 시간 전
Opiniões no Lobste.rs

  • Escrevi este texto depois de ver ontem a notícia de que o lobste.rs migrou para SQLite (https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite)
    Enquanto lia a discussão na época, lembrei de pequenos incômodos nos padrões do SQLite e organizei isso em um post no blog

    • Eu também li aquela discussão e cheguei à mesma conclusão
      Se houver disposição, valeria a pena compartilhar este texto também no fórum do SQLite: https://sqlite.org/forum/forum

  • Em SQL, CREATE TYPE é usado para criar tipos compostos, e o Postgres ainda dá suporte a intervalos, enumerações, arrays e até tipos base bem complexos
    O que você quer parece mais próximo de CREATE DOMAIN. Dá para criar um newtype a partir de um tipo concreto base e também definir valores padrão e restrições, o que deixa muito mais enxuto um schema que repetiria a mesma restrição CHECK em várias tabelas. Por exemplo, dá para escrever create domain mything as text; ou create domain mything as integer not null check (VALUE < 5);

    • Eu deveria ter verificado primeiro se já existia um recurso de alias de tipo. Vou acrescentar isso ao texto

  • Também seria preciso tratar de uma armadilha bem perigosa: só busy_timeout não impede erros SQLITE_BUSY que acontecem imediatamente
    Se você inicia uma transação com uma consulta somente leitura e depois executa uma consulta de escrita para tentar promover o lock que já possui, se houver outra operação de escrita em andamento, a falha acontece na hora independentemente da configuração de busy_timeout. Para resolver isso, é preciso usar begin immediate

    • Eu sei que tecnicamente isso não é um bug, mas sinceramente é um comportamento que quase parece bug

  • Também é preciso considerar que algumas das configurações vinculadas à edição proposta são, no modelo do SQLite, propriedades da conexão, enquanto outras são propriedades do arquivo do banco de dados ou de tabelas individuais
    PRAGMA journal_mode = WAL persiste no arquivo do banco, mas PRAGMA foreign_keys = ON precisa ser configurado em cada conexão. STRICT é uma propriedade de cada tabela, então dá até para misturar tabelas estritas e não estritas dentro do mesmo banco. Portanto, é preciso decidir se esse PRAGMA unificado seria definido como propriedade do arquivo do banco ou da conexão. Também fico curioso se existe algum mecanismo interno de proteção que avise para que bibliotecas antigas não acabem alterando e corrompendo arquivos que incluam recursos que elas não conhecem

  • A abordagem por edições parece, no geral, um bom meio-termo entre a meta da equipe do SQLite de preservar compatibilidade retroativa e o desejo dos usuários por padrões menos confusos e alinhados às recomendações atuais

  • Ultimamente estou obcecado por RocksDB. Assim como o SQLite, é uma biblioteca de banco de dados embarcado, mas vejo como uma grande vantagem justamente o fato de não ter SQL
    Sinto que a exigência de expressar tudo em SQL só atrapalha na hora de lidar com banco de dados

    • RocksDB é um banco de dados chave-valor e SQLite é um banco de dados relacional, então eu diria que são conceitos distintos criados para objetivos diferentes
      É difícil imaginar como migrar para RocksDB ou outro banco chave-valor um sistema que usa SQL, como o lobste.rs

  • Não concordo que definir busy_timeout seja a solução. O SQLite tem desempenho excelente quando você usa uma única thread de escrita e faz o processamento em lote de transações lógicas que não têm relação entre si
    A forma concreta está descrita em https://andersmurphy.com/2025/12/…

    • Em cargas de trabalho normais, o certo é limitar as escritas a uma só e evitar uma estrutura em que várias escritas fiquem competindo o tempo todo
      Mas também é comum modificar diretamente o banco para tarefas de manutenção ou executar por script tarefas administrativas ocasionais que não justificam criar uma tela de administração separada. Nesses casos, não é bom que o servidor ou a aplicação possa parar aleatoriamente só porque, por azar, os momentos de execução coincidiram