O que eu gostaria que alguém tivesse me contado sobre Postgres

 (challahscript.com)
7 pontos por GN⁺ 2024-11-13 | 2 comentários | Compartilhar no WhatsApp
  • A documentação oficial do Postgres é excelente, mas o PDF do Postgres 17 tem 3.200 páginas, o que dificulta para iniciantes aprenderem só pela documentação, antes da prática, tudo sobre modelagem de schemas, comportamento do SQL e armadilhas operacionais
  • A menos que haja um motivo específico, os dados devem ser normalizados; a desnormalização, que mantém dados duplicados para melhorar a performance de leitura, exige aceitar o custo de inconsistências e maior complexidade de escrita
  • Palavras-chave SQL não diferenciam maiúsculas de minúsculas, mas NULL é mais próximo de “desconhecido”, então compará-lo como o null de linguagens comuns pode produzir resultados inesperados
  • No psql, só de usar bem pager, \x, .psqlrc, \pset null, autocompletar, comandos com barra invertida e \copy, a legibilidade da saída, a navegação e a exportação para CSV ficam muito mais confortáveis
  • Índices, locks, transações e JSONB são poderosos, mas desconhecer planos de consulta e restrições operacionais pode levar a queda de performance ou problemas de disponibilidade

Contexto antes da imensa documentação oficial

  • A documentação oficial do Postgres, na versão atual 17, tem 3.200 páginas quando impressa como PDF em US Letter; em A4, tem 3.024 páginas
  • Há muito conhecimento prático que é útil saber antes de usar Postgres, e parte dele pode se aplicar também a outros DBMS SQL, embora o escopo de aplicação nem sempre seja claro

Normalize os dados por padrão

  • Normalização é o processo de remover dados duplicados ou desnecessários de um schema de banco de dados
  • Se você armazenar user_email diretamente na tabela documents, quando um usuário mudar o email será preciso atualizar todas as linhas de documentos daquele usuário
    • Em vez disso, cada linha de documents pode referenciar uma linha de outra tabela, como users, por meio de uma chave estrangeira user_id
  • Não é necessário decorar todas as formas normais, como “1st normal form”, mas o processo comum de normalização pode levar a um schema mais fácil de manter
  • Desnormalização é uma abordagem que mantém dados duplicados para leitura rápida, sem recalcular certas informações todas as vezes
    • Em um app de turnos de funcionários, em vez de calcular as horas trabalhadas acumuladas no ano somando a duração de todos os turnos a cada vez, é possível calcular e armazenar esse valor periodicamente ou quando as horas de trabalho mudarem
    • Esses dados podem ficar dentro do Postgres ou em uma camada de cache como Redis
  • A desnormalização quase sempre tem custos; os principais são a possibilidade de inconsistência dos dados e o aumento da complexidade de escrita

Conselhos de “não faça isso” do projeto Postgres

  • A wiki oficial do Postgres tem uma lista “Don’t do this”
  • Não há problema se você não entender todos os itens; se não entende algum deles, provavelmente também é menos provável que cometa aquele erro
  • Vale lembrar especialmente estes conselhos

Comportamentos fáceis de confundir em SQL

  • Palavras-chave SQL não precisam estar em maiúsculas

    • Palavras-chave SQL não diferenciam maiúsculas de minúsculas
    • As consultas abaixo têm o mesmo significado
    SELECT * FROM my_table WHERE x = 1 AND y > 2 LIMIT 10;
select * from my_table where x = 1 and y > 2 limit 10;
SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;
    • Essa característica não é exclusiva do Postgres

  • NULL é diferente de null/nil em linguagens comuns

    • O NULL do SQL é mais próximo de “desconhecido” do que de null ou nil em linguagens de programação comuns
    • NULL = NULL não retorna true, mas sim NULL
    • Comparações em que um dos lados é NULL também costumam resultar em NULL
    • Para comparar NULL, é preciso usar as seguintes operações
      • x IS NULL: true se x for NULL
      • x IS NOT NULL: true se x não for NULL
      • x IS NOT DISTINCT FROM y: parecido com x = y, mas trata NULL como um valor comum
      • x IS DISTINCT FROM y: parecido com x != y/x <> y, mas trata NULL como um valor comum
    • A cláusula WHERE só retorna linhas quando a condição é true
      • SELECT * FROM users WHERE title != 'manager' não retorna linhas em que title é NULL
      • Isso acontece porque o resultado de NULL != 'manager' é NULL
    • COALESCE retorna o primeiro valor não NULL entre vários argumentos
    COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

Usando melhor o psql

  • Melhore a legibilidade da saída

    • Se a saída fica difícil de ler ao consultar tabelas com muitas colunas ou valores longos, o pager pode estar desativado
    • Um pager de terminal permite rolar textos grandes ou tabelas do psql dentro de uma viewport
    • Em tabelas com muitas colunas, é possível ativar o expanded mode com \pset expanded ou \x
    • Para usar isso como padrão, basta adicionar \x ao ~/.psqlrc no diretório home

  • Deixe a saída de NULL explícita

    • A configuração padrão não deixa claro na saída se um valor é NULL
    • No psql, é possível definir a string exibida para NULL
    \pset null '[NULL]'
    • Strings Unicode também funcionam; para usar como padrão, adicione o mesmo comando ao ~/.psqlrc

  • Use autocompletar e comandos com barra invertida

    • O psql oferece suporte a autocompletar, como um console interativo
    • Ao digitar parte de uma palavra-chave ou nome de tabela e pressionar Tab, ele pode completar o restante
    • Alguns comandos úteis com barra invertida são
      • \?: lista de todos os shortcuts
      • \d: mostra relations, ou seja, tabelas e sequências, e seus proprietários
      • \d+: adiciona tamanho e alguns metadados ao \d
      • \d table_name: mostra o schema da tabela, tipos das colunas, se aceitam null, valores padrão, índices e restrições de chave estrangeira
      • \e: edita a consulta no editor padrão definido na variável de ambiente $EDITOR
      • \h SQL_KEYWORD: mostra a sintaxe daquela palavra-chave SQL e links para a documentação

  • Exportação para CSV e aliases em SELECT

    • Com \copy, é possível salvar resultados de consultas em CSV
    \copy (select * from some_table) to 'my_file.csv' CSV
    • Para incluir os nomes das colunas na primeira linha, adicione a opção HEADER
    \copy (select * from some_table) to 'my_file.csv' CSV HEADER
    • \copy permite evitar os privilégios elevados necessários para o comando COPY, mais padronizado
    • Colunas de saída de um SELECT podem receber aliases com AS
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY vendor
ORDER BY number_of_backpacks DESC;
    • Em GROUP BY e ORDER BY, é possível referenciar o número da coluna que aparece após o SELECT
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY 1
ORDER BY 2 DESC;
    • Essa forma abreviada é útil, mas é melhor não incluí-la em consultas implantadas em produção

Índices nem sempre são usados só porque foram adicionados

  • Índices e planos de consulta

    • Um índice é uma estrutura de dados que funciona como um diretório de atalhos para encontrar linhas de uma tabela com base em um campo específico
    • O tipo de índice mais comum é o B-tree, que funciona para condições de igualdade exatas como WHERE a = 3 e condições de intervalo como WHERE a > 5
    • Não é possível instruir diretamente o Postgres a usar um índice específico
    • Com base nas estatísticas que mantém para cada tabela, o Postgres estima se um índice será mais rápido do que uma sequential scan que lê a tabela do início ao fim
    • Ao colocar EXPLAIN antes de SELECT ... FROM ..., você pode ver o plano de consulta de como o Postgres pretende executar a consulta
    • Para ler planos de consulta, podem ajudar o guia de EXPLAIN ANALYZE da thoughtbot, a documentação da pganalyze, a documentação oficial e o explain.depesz.com

  • Tabelas pequenas e índices multicoluna

    • Em tabelas com poucas linhas, como um DB local de desenvolvimento, índices podem não ajudar muito
    • Com algo como 100 linhas, o Postgres pode decidir que uma sequential scan é mais rápida do que usar um índice
    • O Postgres oferece suporte a índices multicoluna
    CREATE INDEX CONCURRENTLY ON tbl (a, b);
    • Condições como WHERE a = 1 AND b = 2 podem ser mais rápidas do que ter índices separados em a e b
    • Isso acontece porque é possível percorrer uma única B-tree e combinar as condições de busca de forma eficiente
    • Um índice (a, b) torna consultas que filtram apenas por a tão rápidas quanto um índice somente em a
    • Consultas como WHERE b = 5 também podem ficar mais rápidas, mas talvez não de forma ideal
      • Como o índice é chaveado primeiro por a e depois por b, é preciso passar por todos os valores de a para encontrar os valores de b
    • Se você precisa consultar várias combinações de colunas, é comum manter tanto (a, b) quanto um índice somente em b
    • Dependendo da necessidade, também é possível depender de índices individuais em a e b

  • Use text_pattern_ops para prefix match

    • Ao armazenar diretórios hierárquicos usando a abordagem de materialized path, talvez seja necessário encontrar todos os descendants que começam com um determinado prefixo
    SELECT * FROM directories WHERE path LIKE '/1/2/3/%'
    • Mesmo criando um índice B-tree padrão na coluna path, ele pode não ser usado para essa consulta
    CREATE INDEX CONCURRENTLY ON directories (path);
    • Para permitir a ordenação caractere a caractere necessária para prefix match ou pattern match, é preciso especificar uma operator class
    CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

Problemas operacionais causados por locks e transações

  • Locks no Postgres

    • Um lock ou mutex é um mecanismo que permite que apenas um cliente execute uma operação perigosa por vez
    • Em um banco de dados, atualizações de objetos como rows, tables e views precisam ter sucesso por completo ou falhar por completo; para evitar situações em que operações simultâneas tenham sucesso apenas parcial, locks são adquiridos sobre os objetos relacionados
    • O Postgres tem vários níveis de locks de tabela, dos menos restritivos aos mais restritivos
      • ACCESS SHARE: SELECT
      • ROW SHARE: SELECT ... FOR UPDATE
      • ROW EXCLUSIVE: UPDATE, DELETE, INSERT
      • SHARE UPDATE EXCLUSIVE: CREATE INDEX CONCURRENTLY
      • SHARE: CREATE INDEX, sem CONCURRENTLY
      • ACCESS EXCLUSIVE: muitas formas de ALTER TABLE, ALTER INDEX
    • Em uma mesma tabela, as seguintes ações podem acontecer ou precisar esperar
      • UPDATE durante SELECT: possível
      • UPDATE durante CREATE INDEX CONCURRENTLY: possível
      • SELECT durante CREATE INDEX: possível
      • SELECT durante ALTER TABLE: geralmente espera
      • ALTER TABLE durante SELECT: geralmente espera
    • Algumas formas de ALTER TABLE podem exigir locks mais fracos; as informações completas estão na documentação oficial sobre locks explícitos e no guia de conflitos de lock por operação

  • ALTER TABLE lento e fila de locks

    • Se um ALTER TABLE demorar muito, até SELECTs que leem a mesma tabela podem ser bloqueados
    • Se for uma tabela central como users, referenciada por todas as requisições de um app web, as requisições podem ficar esperando, dar timeout e retornar 503
    • Causas comuns de um ALTER TABLE lento incluem
      • adicionar uma coluna com non-constant default
      • alterar o tipo de uma coluna
      • adicionar uma uniqueness constraint
    • A partir do Postgres 11, o problema em que todos os defaults deixavam a adição de coluna lenta foi corrigido; non-constant default ainda pode ser problemático
    • Mesmo que o ALTER TABLE em si seja uma operação rápida, ele só executa depois de obter o lock
      • Se um SELECT lento de um dashboard interno antigo já estiver em execução, o ALTER TABLE precisará esperar
    • Como locks no Postgres formam uma fila, consultas posteriores na mesma tabela que chegarem atrás do ALTER TABLE em espera também podem ter que esperar
    • O mesmo cenário pode ser explorado em mais detalhes em Migrations and exclusive locks

  • Transações longas também são perigosas

    • Uma transação é uma forma de agrupar várias instruções de banco de dados em all-or-nothing; ela começa com BEGIN e termina com COMMIT
    • Alterações feitas durante uma transação não são visíveis para outros clientes e são publicadas no banco de dados no COMMIT
    • Isso é adequado para operações como uma transferência bancária, em que a redução do saldo de uma conta e o aumento do saldo de outra precisam ter sucesso juntas ou ser cancelados juntos
    • Quando uma transação obtém um lock, ela mantém esse lock até o COMMIT
    • Se você der BEGIN, fizer UPDATE em uma row específica e sair da mesa, o DELETE dessa row por outro cliente ficará parado até a transação receber commit
    • Transações mantidas abertas por mais tempo do que o necessário podem bloquear consultas ou atualizações de outros clientes

JSONB é uma ferramenta afiada

  • Problemas de performance e schema do JSONB

    • JSONB é flexível, mas pode ter grandes desvantagens se usado de forma errada
    • O Postgres não rastreia estatísticas de colunas JSONB, então uma consulta de igualdade em uma única coluna JSONB pode ser muito mais lenta do que uma consulta sobre um conjunto de colunas comuns
    • Um caso mostra um exemplo de lentidão 2000 vezes maior por causa de JSONB
    • Uma coluna JSONB pode conter praticamente qualquer coisa, o que é poderoso, mas oferece poucas garantias sobre a estrutura
    • Em tabelas comuns, é possível prever o resultado de consultas olhando o schema; com JSONB, não fica claro se o nome da key é camelCase ou snake_case, ou se um estado é boolean ou enum
    • As características de tipagem estática dos dados comuns do Postgres não se aplicam ao JSONB da mesma forma

  • A estranheza das comparações de tipos em JSONB

    • Ao tentar encontrar, na coluna JSONB data da tabela backpacks, linhas cujo campo brand seja JanSport, a consulta abaixo não funciona
    select * from backpacks where data['brand'] = 'JanSport';
    • O Postgres espera que o tipo à direita da comparação corresponda ao tipo à esquerda, e o lado direito precisa ser um documento JSON válido
    • Um documento JSON precisa ser um objeto, array, string, número, boolean ou null, então JanSport sozinho não é JSON válido
    • A consulta correta compara com uma string JSON ou converte o lado esquerdo para text do Postgres
    select * from backpacks where data['brand'] = '"JanSport"';

select * from backpacks where data['brand'] = '"JanSport"'::jsonb;

select * from backpacks where data->>'brand' = 'JanSport';
    • O NULL do SQL e o null do JSONB se comportam de forma diferente
      • 'null'::jsonb = 'null'::jsonb é true, mas NULL = NULL é NULL
    • O JSONB tem muitos operadores e funções próprios, difíceis de memorizar de uma vez
    • O Postgres tem tanto JSON, que armazena valores JSON como texto, quanto JSONB, que os converte para um formato binário eficiente
    • O JSONB tem vantagens como possibilidade de indexação, e o formato JSON pode ser visto como um caso especial

Leituras relacionadas

2 comentários

 
bbulbum 2024-11-19

Vou precisar ler isso algum dia, sobre o que não se deve fazer.
 
GN⁺ 2024-11-13
Opiniões do Hacker News

  • O PostgreSQL, em geral, diferencia maiúsculas de minúsculas, mas escrever palavras-chave SQL em maiúsculas costuma ser uma tentativa de melhorar a legibilidade por meio de reconhecimento visual de padrões
    Não é obrigatório, mas, se eu tivesse que depurar a query de outra pessoa, provavelmente a colocaria em um prettifier para examinar rapidamente a definição sem tropeçar em detalhes triviais da forma da sintaxe
    Assim como a formatação de código em outras linguagens, uma estrutura visual como indentação consistente reduz o tempo gasto entendendo as partes óbvias e ajuda a focar no que importa
    Mas eu realmente detesto quando identificadores misturam maiúsculas e minúsculas de verdade, como actuallyUsingCaseInIdentifiers, e não quero ver colunas que exigem aspas duplas para serem verificadas na CLI

    • Identificadores em maiúsculas parecem blocos intercambiáveis, o que deixa a leitura mais lenta do que as formas das palavras em minúsculas
    • Ao lidar com SQL de forma interativa, conhecer essa distinção é bem útil
      Se vou digitar rapidamente uma query temporária que ninguém verá e descartá-la, não me preocupo com maiúsculas/minúsculas, mas SQL que é commitado no repositório usa comandos em ALL CAPS
    • Entendo que maiúsculas funcionavam como realce de sintaxe em telas em preto e branco
      Agora que temos cores, isso não é mais necessário, mas é uma lembrança antiga e não tenho material de referência
    • O PostgreSQL converte identificadores para minúsculas, enquanto o padrão os converte para maiúsculas; portanto, ele viola o padrão no tratamento de maiúsculas/minúsculas
      Ainda assim, não se deve misturar identificadores entre aspas e sem aspas, e a consulta de estruturas internas também não é muito padronizada, então isso não tem grande importância
    • Tenho curiosidade sobre recomendações de prettifiers ou linters para SQL

  • Eu não conhecia a seção “don’t do this” da wiki do PostgreSQL, e ela é bem útil: https://wiki.postgresql.org/wiki/Don%27t_Do_This

    • Se esses recursos são armadilhas tão fáceis, fico me perguntando por que não os tornam obsoletos
      Por exemplo, parece correto desabilitar recursos como herança de tabelas em novos schemas e exigir uma configuração deliberadamente complicada para reativá-los
    • Isso me lembra SQL Anti-patterns, que considero um livro que todos que lidam com bancos de dados deveriam ler
    • Fez-me repensar alguns hábitos que aprendi no lado do MySQL

  • Muito do que aparece aqui não se aplica só ao PostgreSQL
    É o caso dos comportamentos estranhos de NULL e da ordem das colunas em índices; em especial, a interação entre NULL e índices/restrições únicas também não é intuitiva no MySQL
    Por exemplo, se você criar uma restrição única em (email, username) numa tabela de usuários em que email não pode ser NULL e username pode ser NULL, será possível inserir várias vezes o mesmo email com username NULL. Isso porque NULL não é igual a outro NULL

    • Como referência, desde o PostgreSQL 15 é possível influenciar esse comportamento em constraints e índices únicos com NULLS [NOT] DISTINCT
      https://www.postgresql.org/docs/devel/sql-createtable.html#S...
    • Acho esse padrão bom do ponto de vista prático
      Casos de uso que exigem o comportamento oposto são muito mais raros

  • É problemático apenas dizer “normalize os dados se não houver uma boa razão” e seguir em frente
    Mesmo na página linkada pelo autor há 11 formas normais, incluindo a forma não normalizada, e a maioria das pessoas nem sabe o que elas são; 7 delas nem serão usadas
    Não se deve fazer as pessoas saírem procurando formas normais mais altas

    • Ainda assim, o autor em geral adicionou um parágrafo explicando o que queria dizer, e acho que a direção está certa
      No projeto para o qual migrei recentemente, também tive que corrigir alguns problemas desse tipo, e quase nunca há motivo para duplicar dados
    • Se este texto é voltado a iniciantes, quando não há certeza a resposta quase sempre é a terceira forma normal
    • A regra geral é normalizar o máximo possível e depois desnormalizar até obter a performance necessária

  • A primeira dica é fazer VACUUM todos os dias
    Quando comecei, eu não sabia disso e nunca fazia VACUUM no banco de dados do reddit; um dia não tive escolha a não ser executá-lo e, enquanto esperava terminar, o reddit ficou fora do ar por quase um dia

    • Pelo visto não havia autovacuum
      No porte do reddit, é surpreendente que os IDs de transação não tenham se esgotado antes

  • Eu gostaria que desenvolvedores se preocupassem mais com normalização e parassem de enfiar tudo em colunas JSONB

    • Muito antes de bancos de dados conseguirem armazenar JSON estruturado, desenvolvedores juniores já travavam debates acalorados e teóricos sobre o grau adequado de normalização
      Desenvolvedores mais experientes sabiam que a resposta certa era não duplicar nada além de chaves e desnormalizar só muito a contragosto
      Depois surgiram bancos como o Mongo, oferecendo algo “parecido com um banco de dados” em que normalização era difícil ou sem sentido, incentivando esses juniores; como resultado, designs de banco de dados horríveis e torres de lixo impossíveis de manter floresceram por um breve período
      Agora o pêndulo voltou e redescobrimos as vantagens de bancos de dados normalizados, mas colunas JSON ainda são uma válvula de escape onde más práticas podem crescer
    • Há dois motivos para usar colunas JSONB
      Primeiro, para armazenar JSON. Quando um servidor web chama uma API de terceiros, salvar a resposta original da API em uma coluna JSONB e processá-la a partir dali deixa um registro auditável para depurar problemas vindos daquela API
      Segundo, para armazenar tipos soma (sum types). O fato de SQL não oferecer suporte a tipos soma pode ser considerado uma das maiores falhas ao modelar dados em bancos SQL
      Existem várias soluções de contorno, e “simplesmente colocar numa coluna JSONB e validar na aplicação” é uma delas, mas nenhuma dessas soluções é particularmente ótima
    • Mesmo quando se toma cuidado com normalização, muitas vezes acaba surgindo uma gaveta de JSONB de quinquilharias
      Se você não estiver escrevendo consultas ruins dentro do JSONB em vez de promover os valores para colunas separadas, não vejo isso, por si só, como um grande problema
    • A maioria dos desenvolvedores que usa essas ferramentas hoje está, na prática, criando seu próprio sistema de gerenciamento de banco de dados e deixando apenas a persistência para outro DBMS
      Isso acontece porque, se os requisitos de persistência forem atendidos com sucesso, não há uma pressão forte para pensar em um bom design
      É questionável se faz sentido construir outro DBMS em cima de um DBMS, mas, de todo modo, esse é o estado atual
    • Para esse método funcionar direito, é necessário um processo de migração de schema que inclua até a capacidade de reverter alterações de schema
      Se uma nova coluna prejudicar a performance ou causar problemas, deve ser possível reverter
      Se houver ferramentas de CLI envolvidas, também é preciso lidar com quanto downtime é aceitável, se é possível fazer uma atualização de versão sincronizada em toda a empresa, ou se será necessário oferecer suporte aos schemas antigo e novo por algum tempo
      Se o banco de dados não fizer parte do produto principal da equipe, tudo isso pode simplesmente estar ausente

  • Escrevi este texto para ajudar iniciantes: https://tomcam.github.io/postgres/

  • O artigo é realmente muito bom, e eu não sabia que a documentação do PostgreSQL tinha 3.200 páginas
    Venho usando há algum tempo e aprendendo conforme preciso; também gosto bastante da documentação oficial, e gosto de ler artigos relacionados quando preciso de um tema específico
    Acho que ajudaria os leitores se o autor acrescentasse em https://challahscript.com/what_i_wish_someone_told_me_about_... que um índice nas colunas (b, a) funciona bem ao consultar apenas por b
    Isso fica meio implícito quando ele fala de consultas apenas por a, mas não faria mal deixar mais explícito
    Não usei muito a parte de JSON/JSONB, então não cheguei a olhar muito

  • Pensando nos SQLs ridículos que já vi no mundo real, acho que seria bom começar lendo o artigo de Codd e tentando entender o que é o modelo relacional
    São só 11 páginas, e só de lê-lo já haveria menos sofrimento neste mundo

  • Quase tudo neste texto também se aplica a outros bancos de dados MVCC, como MySQL
    Os detalhes podem variar, mas o MySQL também sofre com transações longas e pega bloqueios de metadados durante ALTER, além de problemas divertidos parecidos