1. Visão geral da arquitetura do SQLite

SQLite armazena todo o banco de dados em um único arquivo. A estrutura é baseada em páginas (block units) de tamanho configurável que formam B-trees para tabelas, índices e metadados. O cabeçalho do arquivo, a organização das páginas e os registros são gerenciados para garantir Atomicidade, Consistência, Isolamento e Durabilidade (ACID) sem depender de um servidor separado.

O tamanho da página (page_size) é uma propriedade do arquivo e influencia a compactação de dados, o overhead de ponteiros e a eficiência de acessos aleatórios. O tamanho padrão costuma ser 4096 bytes, mas pode ser ajustado no momento da criação do database. Mudanças de page_size exigem reconstrução do banco para ter efeito efetivo.

Os dados são armazenados em B-trees com páginas de índice e páginas de dados. Valores grandes (BLOBs ou textos longos) podem ser estendidos por meio de páginas de overflow, formando uma cadeia que supera o espaço disponível na célula da página raiz. Além disso, cada banco pode operar em modos de journaling diferentes, o que afeta concorrência e recuperação.

2. Transações, concorrência e journaling

SQLite implementa transações ACID com controle de concorrência baseado em modos de bloqueio. Em modo padrão, leituras compartilham o acesso enquanto escritas solicitam bloqueios exclusivos, o que pode levar a contenção em cenários com alto volume de escrita. Ativar o modo Write-Ahead Logging (WAL) eleva a concorrência entre leituras e escritas, pois leituras podem ocorrer de forma paralela às operações de escrita, com o log de escrita registrado separadamente até o checkpoint ser executado.

A seguir, um exemplo básico de configuração para inicializar um banco em WAL com uma paginação de 4096 bytes e afinidade de durabilidade. Este bloco é útil para cenários de carga de escrita moderada a alta, onde a consistência e a recuperação são críticas.

PRAGMA page_size = 4096;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

-- Exemplo de esquema simples
CREATE TABLE user_sessions (
  user_id INTEGER,
  session_token TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(user_id, session_token)
);

CREATE INDEX idx_user_sessions_created ON user_sessions(created_at);
      

Observação: alguns pragmas afetam o arquivo de cabeçalho e precisam ser executados antes de inserções significativas, ou em bancos recém-criados. O WAL, quando utilizado, também requer a prática de checkpoint periódico para manter o arquivo principal sincronizado com o log.

3. Estrutura de armazenamento: páginas, B-tree e overflow

Cada página constitui a unidade de alocação mínima de dados. Páginas de interior da B-tree contêm chaves e ponteiros para as páginas-filho, enquanto páginas folha contêm as células com os dados reais (linhas das tabelas) ou referências a BLOBs. Quando um valor não cabe na célula da página, SQLite utiliza páginas de overflow para armazenar o conteúdo excedente, encadeando-as para reconstruir o valor completo durante a leitura.

Alguns conceitos-chave para modelar desempenho com SQLite incluem:

• ROWID implícito ou PRIMARY KEY INTEGER para acesso rápido às linhas.
• Uso de WITHOUT ROWID quando apropriado para reduzir overhead de chaves longas.
• Indexação cuidadosa: índices ajudam leituras, mas adicionam custo de escrita e ocupação de espaço.
• Escolha entre journaling e WAL conforme padrão de leitura/escrita da aplicação.

Pragmáticos sobre Tabelas e Índices: manter chaves simples, evitar colunas longas como parte de chaves primárias, e planejar índices com base em consultas frequentes.

4. Boas práticas de modelagem e performance

• Defina a chave primária com Integer PRIMARY KEY para aprovechar o ROWID e buscas rápidas.
• Use índices apenas nas colunas que aparecem com frequência em filtros (WHERE) ou ordenações (ORDER BY).
• Avalie WITHOUT ROWID quando a chave não precisa ser única por ROWID e as consultas envolvem filtros simples.
• Habilite WAL para cargas de escrita concorrentes, mas monitore checkpoint para não impactar leituras sensíveis.
• Utilize PRAGMA optimize, ANALYZE e VACUUM periodicamente para manter estatísticas e organização de páginas.
• Evite SELECT *; retorne apenas as colunas necessárias para reduzir leitura desnecessária.
• Configure PRAGMA synchronous conforme o equilíbrio desejado entre durabilidade e desempenho (NORMAL ou FULL).
• Teste com EXPLAIN QUERY PLAN para entender o custo de execução de consultas e ajustar índices.

Boas práticas adicionais envolvem monitorar tamanho de DB, manter ordens de inserção previsíveis e planejar particionamento lógico quando lidando com volumes muito grandes.