Tema: sql
-
Busca de texto completo no PostgreSQL com tsvector
O PostgreSQL converte texto em um tsvector de lexemas normalizados usando uma configuração de idioma, compara-o com tsquery, classifica com ts_rank e indexa com GIN; trata stemming e stop words, mas não erros de digitação nem sinônimos por padrão.
-
Armazenar dados derivados no PostgreSQL: colunas geradas ou views materializadas
Uma coluna gerada deriva um valor por linha apenas a partir dessa mesma linha, podendo ser virtual (calculada na leitura) ou armazenada (calculada na escrita), e aceita só expressões imutáveis; uma view materializada guarda o resultado de qualquer consulta e só está tão atualizada quanto o último REFRESH. Use colunas geradas armazenadas para normalização por linha que deva ser indexada, views materializadas para agregações caras que podem ficar defasadas e uma tabela de resumo mantida quando nenhuma das duas serve.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
NULL em SQL: lógica de três valores e suas armadilhas
NULL significa desconhecido, então comparações com NULL resultam em desconhecido, e não em verdadeiro ou falso: filtros WHERE descartam as linhas cuja condição é desconhecida, NOT IN com um NULL não retorna nada e agregações ignoram NULLs. Use IS NULL, IS DISTINCT FROM e COALESCE de forma deliberada.
-
Common table expressions e consultas recursivas com WITH
WITH dá um nome a uma subconsulta para o resto da instrução; WITH RECURSIVE avalia um termo não recursivo e repete o termo recursivo até que não surjam novas linhas, percorrendo árvores e grafos de qualquer profundidade numa só consulta. CTEs de uso único e sem efeitos colaterais são incorporadas na consulta externa, a menos que se escreva MATERIALIZED; as cláusulas SEARCH e CYCLE tratam da ordenação e dos ciclos.
-
Estatísticas do planejador no PostgreSQL: statistics target, colunas correlacionadas e estimativas erradas
O planejador estima o número de linhas a partir de estatísticas por coluna amostradas pelo ANALYZE (valores mais comuns, histogramas, contagem de valores distintos) e assume que as colunas são independentes; estimativas erradas vêm de estatísticas desatualizadas, colunas assimétricas com poucas entradas, colunas correlacionadas e expressões. Aumente o statistics target em colunas específicas, crie estatísticas estendidas para colunas correlacionadas ou expressões, execute o ANALYZE de novo e compare as linhas estimadas com as reais.
-
Como ler um plano de consulta do PostgreSQL com EXPLAIN ANALYZE
O EXPLAIN mostra a árvore escolhida pelo planejador, com custos estimados; o EXPLAIN ANALYZE executa a consulta e acrescenta tempos e contagens de linhas reais. Compare linhas estimadas com reais, localize o nó com maior tempo real e verifique varreduras sequenciais em tabelas grandes e junções mal estimadas.
-
NULL em SQL: lógica de três valores e suas armadilhas
NULL significa desconhecido, por isso toda comparação com NULL não resulta em verdadeiro nem falso, mas em desconhecido. O WHERE descarta linhas desconhecidas, NOT IN com um NULL não encontra nada, agregações ignoram NULL e uma restrição unique admite vários NULL. Use IS NULL, IS DISTINCT FROM e COALESCE de forma deliberada.
-
Savepoints e o estado de transação abortada no PostgreSQL
Depois de qualquer erro dentro de um bloco de transação, o PostgreSQL rejeita todos os comandos seguintes até que o bloco seja revertido; um savepoint criado antes de uma instrução arriscada permite usar ROLLBACK TO SAVEPOINT para descartar só essa parte e continuar. Use savepoints de forma deliberada e moderada, e faça os tratadores de erro reverterem a transação em vez de tentarem de novo na mesma conexão.
-
Paginação por cursor em vez de offset: páginas que permanecem estáveis quando os dados mudam
A paginação por offset obriga o banco de dados a calcular mesmo assim todas as linhas ignoradas e desloca as páginas quando há inserções ou remoções entre as requisições; a paginação por cursor (ou keyset) pede «os próximos 20 após a chave X», usa o índice e entrega cada linha exatamente uma vez. Exige uma ordenação única e, em troca, abre-se mão de números de página.
-
Convenções de esquema para um novo banco de dados PostgreSQL: nomes, identificadores, timestamps e texto
Defina um conjunto pequeno de convenções antes da primeira migration: nomes em snake_case minúsculo que nunca precisam de aspas, uma única estratégia de id em todo o esquema, timestamptz para todo instante no tempo com created_at em todas as tabelas, text em vez de varchar(n), além de NOT NULL e chaves estrangeiras explícitas; registre tudo por escrito para que cada migration futura as siga.
-
Operações em lote em vez de loops linha a linha: round trips, transações e COPY
Um loop que envia uma instrução por linha paga um round trip, um parse e, muitas vezes, um commit por linha; uma operação em lote envia o conjunto em uma única instrução, um único stream ou uma única transação. A orientação do próprio PostgreSQL é fazer commit uma só vez, usar COPY nas cargas e criar índices depois de carregar os dados; as bibliotecas cliente oferecem executemany e copy pelo mesmo motivo.
-
Verificações de qualidade de dados: atualidade, volume, nulos e unicidade como conjunto mínimo
Quatro verificações baratas detectam a maioria das cargas com problemas: a fonte está suficientemente atualizada, chegou um número plausível de linhas, chaves e métricas estão preenchidas, a granularidade declarada é única? Formule cada verificação como uma consulta que devolve as linhas com erro, execute-a depois da carga e antes da publicação, e separe aviso de bloqueio.
-
Que tamanho de pool de conexões em relação aos núcleos de CPU as equipes adotaram num servidor PostgreSQL, e qual medição as fez mudá-lo?
Pergunta em aberto: a wiki do PostgreSQL propõe uma fórmula para conexões ativas baseada no número de núcleos e no número efetivo de discos, e o padrão de max_connections costuma ser 100; que tamanhos de pool as equipes realmente adotaram após o ajuste, quão longe ficaram da fórmula e que evidências (esperas por locks, filas no pooler, saturação de CPU, latência) motivaram cada mudança?
-
Soft deletes versus tabelas de arquivo
Um soft delete mantém a linha com um marcador deleted_at, de modo que toda consulta precisa filtrá-la e toda restrição de unicidade precisa virar um índice parcial; uma tabela de arquivo move a linha para fora da tabela ativa, mantendo simples as consultas sobre os dados ativos e o histórico num só lugar. A escolha depende de quem lê os dados excluídos e com que frequência, e deve ser codificada em restrições e views, não em cada consulta.
-
Como escrever um upsert com INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (colunas) DO UPDATE SET ... transforma o "inserir ou atualizar" em uma única instrução atômica, guiada por um índice único: indique o alvo do conflito, atualize apenas as colunas que devem mudar, use EXCLUDED para a linha proposta e adicione um WHERE que evite atualizações sem efeito. MERGE cobre os casos com vários ramos que ON CONFLICT não consegue expressar.
-
Consultas N+1: como detectá-las contando e corrigi-las com batching
Carregar N linhas pai e depois acessar uma relação lazy em cada uma gera N+1 consultas; o custo cresce com os dados, não com o código, por isso passa despercebido em testes com fixtures pequenas. Detecte o problema verificando com asserções a contagem de consultas por requisição, faça os lazy loads indesejados lançarem exceção e corrija com joins para relações to-one e com segundas consultas em lote via IN para coleções.
-
Normalização até a terceira forma normal e quando desnormalizar
As formas normais eliminam grupos repetitivos e fatos armazenados em mais de um lugar; na terceira forma normal, toda coluna não chave depende da chave e de nada além dela. Normalize por padrão os dados transacionais e desnormalize apenas em colunas derivadas e nomeadas, mantendo a fonte da verdade normalizada.
-
Constraints declarativas no PostgreSQL: CHECK, UNIQUE e chaves estrangeiras com ON DELETE
As constraints fazem o banco de dados rejeitar estados inválidos para qualquer cliente que escreva nele, e não apenas para a aplicação: CHECK para regras por linha, NOT NULL para valores obrigatórios, UNIQUE para identidade e chaves estrangeiras com uma ação ON DELETE explícita. Escolher a ação referencial e indexar a coluna referenciadora são as duas decisões mais frequentemente ignoradas.
-
Encontrar as instruções mais custosas com pg_stat_statements
O pg_stat_statements agrega, para todo o servidor, o número de chamadas, o tempo de execução e a contagem de blocos por instrução normalizada. Carregue-o via shared_preload_libraries, ordene por total_exec_time e, separadamente, por calls, compare a média com o máximo, leia as colunas de buffer e leve as principais instruções ao EXPLAIN ANALYZE, comparando antes e depois por queryid.
Legível por máquina: JSON