PRAGMA no SQLite: Configurações Essenciais para Produção

PRAGMA: o jeito de conversar com o engine do SQLite

Um PRAGMA é um comando específico do SQLite que serve para ler ou alterar o comportamento do próprio engine. Você executa como qualquer outro SQL, só que, em vez de mexer nos seus dados, ele mexe nas configurações do banco.

PRAGMA journal_mode;
PRAGMA foreign_keys;
PRAGMA page_size;

Quando você executa um PRAGMA como consulta, ele devolve o valor atual. Quando executa como atribuição, ele altera o valor:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

O modelo mental é simples: a maior parte dos PRAGMAs vale por conexão. Abriu uma conexão nova? Os padrões voltam. Por isso, em código de produção, é comum ter um pequeno bloco de PRAGMAs que roda logo depois que cada conexão é aberta.

Configurações SQLite para produção: o básico

Se você só for lembrar de cinco PRAGMAs do SQLite, lembre-se destes:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;

SELECT 'configurado' AS status;

Esse é um conjunto de defaults sensato para praticamente qualquer aplicação que usa SQLite como banco principal. Vale a pena entender cada um deles separadamente — e é isso que o resto desta página faz.

journal_mode = WAL

O journal mode define como o SQLite garante a durabilidade das escritas. O padrão, DELETE, usa um rollback journal: quem escreve trava quem lê, e quem lê trava quem escreve. Tudo bem para uma ferramenta de linha de comando, mas péssimo para uma aplicação web.

Com o WAL (Write-Ahead Logging), o jogo vira. Leitores e escritores deixam de se atrapalhar — quem está lendo enxerga um snapshot consistente enquanto outro processo está fazendo commit. Você continua limitado a um escritor por vez, mas as leituras seguem rápidas mesmo sob carga.

PRAGMA journal_mode = WAL;
PRAGMA journal_mode;

Algumas coisas que vale saber:

• O journal_mode é persistente — uma vez configurado, ele permanece assim para aquele arquivo de banco. Não precisa configurar a cada nova conexão, mas também não faz mal.
• O modo WAL cria dois arquivos extras junto do seu .db: um -wal e um -shm. Não apague esses arquivos enquanto o banco estiver aberto.
• O WAL não se dá bem com sistemas de arquivos em rede (NFS, SMB). Mantenha o banco em disco local.

Existe um doc separado falando mais a fundo sobre o modo WAL e concorrência. Por enquanto: ative e siga em frente.

synchronous = NORMAL

O pragma synchronous controla com que frequência o SQLite força a gravação no disco. O trade-off aqui é durabilidade contra velocidade.

• FULL (padrão) — faz flush a cada commit. Durabilidade máxima. Mais lento.
• NORMAL — faz flush em pontos seguros (checkpoints). Seguro junto com o WAL. Mais rápido.
• OFF — deixa o sistema operacional decidir. Rápido, mas com risco de corromper o banco em caso de queda de energia.

PRAGMA synchronous = NORMAL;
PRAGMA synchronous;

O inteiro no resultado (1) corresponde ao NORMAL. No WAL mode, NORMAL é a configuração recomendada — você não perde transações já commitadas em caso de crash, só corre o risco de perder as mais recentes em uma queda de energia. Para a maioria das aplicações, esse é o equilíbrio certo.

Não use OFF a menos que esteja populando um banco descartável que dá pra recriar do zero.

foreign_keys = ON

Esse aqui pega muita gente desprevenida. O SQLite suporta foreign keys, mas a checagem vem desligada por padrão, e é uma configuração por conexão:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO books (title, author_id) VALUES ('Fantasmas', 999);

Com foreign_keys = ON, esse último insert falha — não existe nenhum autor com id 999. Sem o PRAGMA, o SQLite grava a linha órfã sem reclamar e você só vai descobrir a bagunça meses depois.

Execute PRAGMA foreign_keys = ON; como a primeiríssima instrução em cada nova conexão. A maioria dos ORMs já faz isso automaticamente; se você está usando o driver direto, a responsabilidade é sua.

busy_timeout = 5000

O SQLite só aceita um escritor por vez. Se uma segunda conexão tentar escrever enquanto a primeira está no meio de uma transação, ela recebe SQLITE_BUSY e desiste na hora — esse é o comportamento padrão.

O busy_timeout faz o SQLite esperar e tentar de novo, em vez de abortar:

PRAGMA busy_timeout = 5000;
PRAGMA busy_timeout;

O valor é em milissegundos. 5000 significa "espere até 5 segundos pelo lock antes de desistir." Junto com o WAL, isso elimina a maior parte dos erros chatos de database is locked em aplicações concorrentes.

Se você está pensando em subir esse valor pra mais de 30 segundos, o problema real provavelmente são transações longas demais — não o timeout curto.

cache_size

O cache_size define quantas páginas do banco o SQLite mantém em memória. Quanto mais cache, menos leituras de disco — ou seja, queries mais rápidas em dados acessados com frequência.

O valor aceita dois formatos:

• Número positivo — páginas. Com o tamanho de página padrão de 4 KB, 2000 equivale a 8 MB.
• Número negativo — kibibytes. -20000 são 20 MB, independente do tamanho da página.

PRAGMA cache_size = -20000;
PRAGMA cache_size;

A forma negativa é mais fácil de raciocinar — você está dizendo "me dá 20 MB de cache" em vez de ficar fazendo conta com o tamanho de página. Para uma aplicação pequena, 20–50 MB já está de bom tamanho. Para uma carga de trabalho com muita leitura em um banco maior, pode aumentar mais. Assim como synchronous, o cache_size é definido por conexão.

mmap_size

A I/O mapeada em memória (memory-mapped I/O) permite que o SQLite leia partes do arquivo do banco direto do cache de páginas do SO, evitando uma cópia. Isso pode acelerar bastante as leituras em bancos grandes:

PRAGMA mmap_size = 268435456;
PRAGMA mmap_size;

Isso dá 256 MB. O SQLite vai mapear até esse tanto do banco para a memória, se houver espaço. Quem cuida da paginação é o sistema operacional, então você não está alocando 256 MB de cara — está só autorizando o mapeamento até esse limite.

O mmap_size brilha em workloads com muita leitura. E é inofensivo em bancos pequenos. Os valores padrão são conservadores, então aumentar costuma ser ganho certo.

PRAGMA optimize

O planejador de consultas usa estatísticas para escolher índices. Estatísticas desatualizadas geram planos ruins. O PRAGMA optimize atualiza essas estatísticas de forma barata:

PRAGMA optimize;

O padrão recomendado é executar esse comando logo antes de fechar conexões de longa duração — no shutdown da aplicação, ou no final de um handler de requisição que mantém a conexão aberta por um tempo. É rápido (geralmente questão de milissegundos) e só faz trabalho de verdade quando algo realmente precisa ser atualizado.

Não confunda com o ANALYZE, que faz uma reconstrução completa das estatísticas. O optimize é o primo mais leve, feito pra rodar com frequência.

Lendo todas as configurações atuais

Pra ver com quais valores uma conexão está configurada no momento, basta consultar os PRAGMAs sem passar nenhum valor:

PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;
PRAGMA busy_timeout;
PRAGMA cache_size;
PRAGMA page_size;
PRAGMA temp_store;

É algo útil na hora de depurar — quando você se conecta com outro driver e fica se perguntando por que o comportamento mudou, quase sempre a diferença está em algum PRAGMA.

Existe também o PRAGMA pragma_list;, que lista todos os PRAGMAs suportados pela sua build:

PRAGMA pragma_list;

Não é algo que você vá decorar, mas é bom saber onde encontrar quando precisar.

Configurações que vão no CREATE, não em tempo de execução

Alguns PRAGMAs configuram o próprio arquivo do banco e só fazem efeito antes de qualquer tabela ser criada:

• PRAGMA page_size = 8192; — tamanho da página em disco. O padrão é 4096, que dá conta da maioria dos casos. Páginas maiores ajudam quando você tem linhas grandes.
• PRAGMA encoding = 'UTF-8'; — codificação do texto.

PRAGMA page_size = 8192;
PRAGMA encoding = 'UTF-8';
CREATE TABLE ...

Se você alterar page_size em um banco já existente, é preciso rodar VACUUM para a mudança valer. Defina esses parâmetros uma vez, na criação do banco, e esqueça.

Um trecho real de configuração da conexão

No código da aplicação, isso normalmente fica em quem abre a conexão. Na prática, fica mais ou menos assim:

-- Execute uma vez em cada nova conexão:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

-- Execute periodicamente, ou antes de fechar:
PRAGMA optimize;

temp_store = MEMORY mantém tabelas e índices temporários em RAM, o que acelera consultas que precisam ordenar ou agregar sem um índice.

E é isso que compõe o checklist de produção. Meia dúzia de linhas, e o SQLite deixa de ser "ok para desenvolvimento" e passa a ser "adequado para uma carga de trabalho real".

A seguir: erros comuns

Mesmo com os PRAGMAs bem ajustados, você vai esbarrar no elenco habitual de erros do SQLite — database is locked, disk I/O error, constraint failed. A próxima página mostra o que cada um realmente significa e como resolver.