Erros comuns em mudanças de esquema de banco de dados no Postgres

 (postgres.ai)
5 pontos por GN⁺ 2024-04-29 | 1 comentários | Compartilhar no WhatsApp
  • As migrações de esquema no Postgres podem causar bloqueios, reescrita de tabela e atraso de replicação, o que pode virar incidente em produção, especialmente em ambientes OLTP de grande porte
  • O risco se concentra em operações que provocam varredura completa e bloqueios longos, como adicionar DEFAULT e NOT NULL ao mesmo tempo, criar índice sem CONCURRENTLY, remover coluna imediatamente, alterar tipos de forma insegura e adicionar chave estrangeira sem validação
  • Desde o PostgreSQL 11, o custo de algumas adições de coluna foi reduzido, mas índices ainda exigem procedimentos como CREATE INDEX CONCURRENTLY, e chaves estrangeiras pedem algo como NOT VALID seguido de VALIDATE CONSTRAINT para reduzir o impacto operacional
  • Mudanças em grande volume devem ser divididas em pequenos lotes, e também é preciso verificar réplicas de leitura, atraso de replicação, objetos dependentes e se instâncias antigas da aplicação ainda referenciam a coluna
  • É preciso testar com dados em escala de produção e só então executar operações destrutivas com deploy em múltiplas etapas e um plano de rollback validado

Premissas da migração de esquema

  • Aqui, migração de banco de dados não significa trocar de DBMS, e sim alterar o esquema do banco
  • As mudanças tratadas aqui têm três características
    • Mudanças versionadas, com identificador único e procedimento automatizado de aplicação para cada alteração
    • Mudanças imutáveis, em que após aplicar em produção não se edita a alteração, apenas se adicionam novas
    • Mudanças incrementais, em que o esquema do banco evolui gradualmente
  • O foco está em casos de uso OLTP, como aplicações web e mobile, em que consultas acima de 1 segundo normalmente já são consideradas lentas demais
  • Em bancos pequenos e com pouca atividade, alguns problemas podem não aparecer com clareza, mas em cargas de cerca de 10 TiB e 10⁴~10⁵ transações por segundo, a maioria deles pode surgir
  • O Database Lab Engine usa clones leves para desenvolvimento e testes, e permite clonar um banco de 10 TiB em menos de 10 segundos para verificar o risco de mudanças de esquema antes do deploy
  • O GitLab Migration Style Guide é uma referência baseada na experiência de automatizar o deploy de muitas mudanças de esquema no Postgres

Adição de coluna e reescrita de tabela

  • Adicionar uma coluna com DEFAULT e NOT NULL ao mesmo tempo é especialmente arriscado em versões antigas do PostgreSQL
    • Antes do PostgreSQL 11, isso exigia reescrever a tabela inteira
    • Em tabelas grandes, isso pode levar horas ou dias, e durante esse período há bloqueio de escrita
  • Um exemplo arriscado é o seguinte
ALTER TABLE users ADD COLUMN status text DEFAULT 'active' NOT NULL;
  • Um procedimento mais seguro é separar adição de coluna, atualização dos dados e inclusão da restrição
    • Primeiro, adiciona-se a coluna sem NOT NULL
    • Se necessário, atualizam-se as linhas existentes
    • Depois, adiciona-se a restrição NOT NULL
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- UPDATE users SET status = 'active' WHERE status IS NULL;

ALTER TABLE users ALTER COLUMN status SET NOT NULL;
  • No PostgreSQL 11 ou superior, adicionar uma coluna com valor DEFAULT não volátil não exige mais reescrita de tabela

Criação de índice e adição de chave estrangeira

  • Criar um índice sem CONCURRENTLY faz com que a criação padrão do índice aplique um bloqueio exclusivo na tabela
    • Até a criação do índice terminar, todas as escritas e parte das leituras podem ficar bloqueadas
  • Um exemplo arriscado é o seguinte
CREATE INDEX idx_users_email ON users(email);
  • Em produção, usar CREATE INDEX CONCURRENTLY é mais seguro
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
  • CONCURRENTLY tem limitações
    • Leva mais tempo, mas não bloqueia o acesso à tabela
    • Não pode ser usado dentro de bloco de transação
    • Em caso de falha, pode deixar para trás um índice inválido que precisa ser removido
  • Adicionar diretamente uma restrição de chave estrangeira em uma tabela grande provoca varredura completa da tabela para validar os dados existentes e pode causar bloqueios longos
  • Um procedimento mais seguro é adicionar primeiro a restrição com NOT VALID e validá-la quando o tráfego estiver mais baixo
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

Remoção de coluna e mudança de tipo

  • Remover uma coluna imediatamente em produção pode causar erros na aplicação se o código ainda estiver referenciando essa coluna
  • A remoção de coluna deve ser feita em múltiplas etapas
    • Primeiro, faz-se o deploy do código da aplicação que não usa mais a coluna
    • Depois, espera-se até que todas as instâncias antigas da aplicação sejam substituídas
    • Por fim, a coluna é removida em uma migração separada
  • Alterar o tipo de uma coluna pode causar reescrita de tabela ou problemas de compatibilidade
    • Isso pode levar a downtime, perda de dados e erros na aplicação
  • Exemplos problemáticos incluem
ALTER TABLE users ALTER COLUMN id TYPE bigint;
ALTER TABLE users ALTER COLUMN email TYPE varchar(100);
  • Ao mudar de integer para bigint, é necessário um procedimento em múltiplas etapas usando uma nova coluna
  • Ao reduzir o tamanho de varchar, é preciso primeiro verificar os dados e avaliar se a mudança é realmente necessária

Mudanças em grande volume, replicação e objetos dependentes

  • Deve-se evitar migrações que alterem dados demais em uma única transação
    • Isso aumenta a contenção de bloqueios e o uso de memória
    • Se houver problema, o tempo de recuperação será maior
    • O atraso de replicação pode crescer bastante
  • Em migrações de dados de grande escala, é mais seguro dividir em lotes pequenos
  • Também é preciso observar o impacto da migração em réplicas de leitura e no atraso de replicação
    • Migrações grandes podem causar atraso de replicação significativo
    • Elas também podem afetar o desempenho das réplicas de leitura
  • Também devem ser verificados objetos que dependem da coluna ou tabela a ser alterada
    • Se views, funções, triggers e outros objetos dependentes forem ignorados, isso pode provocar falhas em cascata ou exigir intervenção manual adicional

Testes e plano de rollback

  • Testar a migração apenas com um dataset pequeno de desenvolvimento dificulta verificar as características de desempenho em conjuntos de dados grandes
  • É preciso testar em clones com dados em escala de produção, e ferramentas como o Database Lab Engine podem ser usadas para isso
  • Se não houver como reverter a migração quando surgir um problema, um incidente em produção pode virar um downtime prolongado
  • Especialmente em operações destrutivas, é necessário ter um plano de rollback validado
  • Os princípios básicos para mudanças seguras de esquema são
    • Testar com dados em escala de produção
    • Usar abordagem em múltiplas etapas para operações arriscadas
    • Aproveitar recursos do PostgreSQL como CONCURRENTLY e NOT VALID
    • Monitorar desempenho e impacto na replicação
    • Sempre preparar um plano de rollback

Leituras relacionadas

1 comentários

 
GN⁺ 2024-04-29
Opiniões do Hacker News

  • Gosto muito do Postgres, mas a maior parte deste texto fala de coisas evitáveis e que merecem atenção. Ainda assim, acho que o pior do Postgres é o gerenciamento de roles
    O recurso é poderoso e, se bem usado, pode ser ótimo, mas o processo de fazê-lo funcionar parece magia negra. Várias partes da interface parecem feitiços obscuros que você não sabe se vão se comportar como esperado, e é uma forma horrível de gerenciar algo tão importante
    O manual dessa parte também é raso, só mostra mais ou menos como deveria funcionar em casos de uso restritos. Se não sai como esperado, você precisa descobrir por tentativa e erro o que fez de errado, e o jeito correto ainda continua pouco claro. Migrar um DB com permissões de usuário complexas dá muito trabalho
    Sinto que deveria reservar mais ou menos um mês para escrever um cookbook. Se isso impedir ao menos uma pessoa de chorar até dormir, já valeria a pena

    • Concordo que o IAM do PostgreSQL é complexo. Ele é complexo porque a hierarquia de objetos tem 3 níveis: Database, Schema e Tables, e também há permissões concedidas implicitamente ao proprietário de objetos do DB
      Para fazer SELECT em uma tabela, é preciso ter CONNECT no Database e USAGE no Schema, que são concedidos implicitamente ao proprietário do Schema. Também é preciso ter SELECT na Table, concedido implicitamente ao proprietário da tabela
      Para ver as permissões, é preciso entender entradas de ACL no formato grantee=privilege-abbreviation[]/grantor:. As permissões de Database podem ser vistas com \l+, as de Schema com \dn+ e as de Table com \dp+
      A lista de permissões está aqui. Por exemplo, user=arwdDxt/postgres significa que a role postgres concedeu todas as permissões ao usuário
      Se a coluna grantee de algum objeto estiver vazia, isso pode significar as permissões padrão do proprietário, ou seja, todas as permissões, ou permissões para a role PUBLIC, que representa todas as roles existentes. Um exemplo é =r/postgres
      Usar o Schema public deixa tudo ainda mais confuso. Como o Schema tem permissão CREATE, se você criar tabelas com o mesmo usuário que consulta os dados, ele recebe permissões de proprietário por padrão e consegue consultá-las imediatamente
    • A documentação do postgREST, que depende de roles para autenticação, também não parece tão detalhada: https://postgrest.org/en/v12/explanations/db_authz.html
      Se você for mesmo escrever um cookbook sobre roles do Postgres e abrir algo como um Kickstarter, acho que eu seria uma das primeiras pessoas a apoiar
    • Concordo com a frase de que “fazer funcionar parece magia negra”. No ano passado implementei um servidor postgREST simples com segurança em nível de linha, e chegar até lá foi bem difícil
      Ainda assim, depois que começou a funcionar, pareceu realmente mágico, e os mecanismos envolvidos em si eram surpreendentemente simples
    • Eu leria um texto desses. O gerenciamento de roles envolve muita adivinhação, e como resultado é comum demais roles acabarem com permissões excessivas
    • Por favor, escreva. Eu pagaria de bom grado algo em torno de 20 dólares por um conteúdo desse nível

  • Se você executa migrações de Schema em ambiente de produção, deve usar lock_timeout
    Mesmo alterações aparentemente inofensivas, como apagar uma tabela com chave estrangeira ou remover uma chave estrangeira, que em testes terminam quase instantaneamente, podem encontrar conflitos de bloqueio em um DB de produção com muito tráfego por causa de transações existentes ou do autovacuum
    Esse ALTER fica aguardando o bloqueio da primeira transação enquanto tenta obter um bloqueio ACCESS EXCLUSIVE, e então todas as queries na tabela bloqueada ficam travadas
    Ao operar Postgres em escala, esse tipo de conflito é só questão de tempo. Ao configurar lock_timeout, em vez de ficar esperando enquanto bloqueia todas as outras queries, a migração falha quando o tempo limite é atingido

    • statement_timeout inclui também o tempo de espera por bloqueios, então permite estimar melhor o impacto sobre uma tabela ocupada
      Se você definir o limite em 5 segundos, sabe que a interrupção total será de no máximo 5 segundos, e as transações posteriores continuam. Usando apenas lock_timeout, não há como controlar quanto tempo a operação levará depois de obter o bloqueio, e ela pode ser rápida ou lenta dependendo do tráfego concorrente
    • Dependendo da versão do Postgres, varia bastante se uma determinada query DML obtém ou não um bloqueio exclusivo
      Fico curioso se existe uma boa maneira de analisar uma query e informar que tipo de bloqueio ela vai obter. Quando não tenho certeza, sempre acabo relendo a documentação
    • Bom conselho. Mas, tecnicamente, eu entendia que ele não fica esperando depois de já ter adquirido um bloqueio ACCESS EXCLUSIVE, e sim por causa da fila de bloqueios
      O ALTER fica no estado de espera até que bloqueios de nível inferior a ACCESS EXCLUSIVE sejam liberados
    • Fazendo isso, pode ser que o ALTER nunca seja executado. Se houver tráfego suficiente nessa tabela, isso pode acontecer
      Nesses casos, se o app conseguir se recuperar, acho que o melhor é matar outras queries em andamento que estejam bloqueando o ALTER

  • Consulto várias vezes por semana o guia Safe Migrations in Ecto da Fly.io. Ecto é o adaptador de DB do Elixir
    É uma referência muito útil para verificar rapidamente se uma migração padrão é suficiente ou se é necessário um procedimento mais complexo
    https://fly.io/phoenix-files/safe-ecto-migrations/

  • Quando eu era iniciante, o que mais me surpreendeu nos índices do Postgres foi que um índice UNIQUE podia afetar os resultados de consultas concorrentes por causa de locks adicionais
    Uma query como INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);, se executada simultaneamente no modo padrão, pode inserir valores bar duplicados. Isso porque uma transação pode não enxergar o novo valor máximo criado por outra transação
    Ao adicionar um índice UNIQUE, parece que a transação que “perdeu” receberia um erro de restrição, mas na prática ambas as transações têm sucesso e a condição de corrida também desaparece

    • Isso não é verdade. A subtransação que perde na disputa do índice é abortada
      =# INSERT INTO foo (bar) (SELECT max(bar) + 1 FROM foo);
      ERROR: duplicate key value violates unique constraint "foo_bar_idx"
      DETAIL: Key (bar)=(2) already exists.
    • Se a afirmação é que, mesmo com um índice UNIQUE, as duas inserções têm sucesso e acabam inserindo valores duplicados, então, se isso for verdade, é um bug
    • Se não me engano, dá para fazer sem downtime criando um índice normal com CONCURRENTLY e depois criando uma restrição UNIQUE ainda não verificada
      Essa restrição se aplica apenas a novos INSERT/UPDATE. Depois, ao executar VALIDATE na restrição, ela se torna uma restrição UNIQUE completa
    • Se isso parece surpreendente, acho que é porque você foi exposto demais a linguagens imperativas
      Concordo que é algo comum, mas o problema está mais no desenvolvimento de software em geral do que no Postgres
    • Em qual nível de isolamento isso acontece?

  • Por causa dessas armadilhas, criei o Reshape [0] com o objetivo de automatizar migrações de schema sem downtime
    Não posso dizer que evitamos todos os problemas, mas estamos criando um novo produto com esse objetivo. Se você se interessa por essa área, especialmente por Postgres, gostaria de receber seu contato: fabian@reshapedb.com
    [0] https://github.com/fabianlindfors/reshape

    • Há chance de funcionar também no crdb?

  • Outro erro que vejo com frequência é esquecer os índices ao copiar uma tabela
    CREATE TABLE SELECT * FROM WHERE <> não funciona desse jeito. As pessoas fazem isso com frequência quando querem criar uma tabela de backup ou fazer uma exclusão em massa

    • No caso de criar uma tabela de backup, ou seja, quando estou prestes a fazer uma operação complexa e ambígua que pode quebrar de formas imediatamente imprevisíveis, não me importo nem um pouco com índices ou restrições
      O que quero é uma cópia dos dados que exista imediatamente, embora eu provavelmente não vá usá-la, para não precisar restaurar a partir de backup do DB e do WAL. Criar índices seria desperdício de tempo do servidor e de espaço em disco
      Se algo der errado, ou se eu realmente precisar, posso criar esses índices depois
    • Então você também poderia dizer qual seria a forma adequada?

  • A seção “Caso 2. Uso indevido de IF [NOT] EXISTS” não apresentou um bom exemplo de uso indevido
    E, na prática, é correto usar assim. É limpo, simples e não tem armadilhas ocultas. Se há apenas algumas tabelas, uma ferramenta de migração de schema é um peso excessivo

    • A armadilha é simples: “mascarar o problema com lógica e aumentar o risco de um estado inconsistente”
      Colocar um curativo em cima de dados ruins não resolve o problema, só o esconde. Dependendo do tipo de problema, ele pode explodir depois de uma forma inesperada, no pior momento possível
      Nesse caso, os “dados ruins” são tabelas, colunas e views que deveriam existir ou não existir, mas estão no estado oposto. Por que uma tabela que ainda não deveria existir existe? A remoção falhou? O schema da tabela existente está correto? A mesma migração foi executada duas vezes por engano?
      Depois de cada migração, o schema deve estar no estado exato. Se a migração contém IF [NOT] EXISTS, isso significa que, após a migração anterior, o schema não ficou no estado correto. Não é bom não ter certeza sobre o estado do schema
    • Acho que o texto explicou bem o uso indevido. O ponto central é que mudanças de schema por caminhos separados são um problema de processo e workflow, então precisam ser resolvidas diretamente
      E se uma coluna de uma tabela que já existe for diferente da que a migração quer criar? IF EXISTS faz a migração “passar”, mas deixa o schema em um estado ruim. Nesses casos, é melhor que a migração falhe rápido

  • Um pequeno comentário sobre o uso de int4 como chave primária substituta
    O importante não é o tamanho da tabela, mas o tamanho do índice, certo? O tamanho da tabela já inclui um cabeçalho de 23 bytes e padding de alinhamento, então uma diferença de 4 bytes não impacta tanto. Mas, se der para manter mais do índice em memória, pode haver benefício. Cada entrada de índice tem um cabeçalho de 8 bytes
    Além disso, o exemplo de 1 bilhão de linhas fica perto demais do valor máximo de int4, o que dá insegurança
    Ainda assim, o texto é excelente

    • Sim. Há também o tamanho do índice e o tamanho em disco. O Postgres empacota as linhas da tabela de forma compacta no disco, mas não faz isso na RAM
      Isso quer dizer que uma página de 8 KB no disco pode ficar maior que 8 KB na RAM?
      Parece afetar apenas a memória de trabalho dos dados das linhas da tabela. Ainda é importante. Especialmente porque, no Postgres, as linhas ficam em ordem aleatória, o que é péssimo para a localidade em queries de intervalo. Ainda assim, não acho que seja um insight decisivo

  • Sou um desenvolvedor que, em geral, foi protegido de problemas relacionados a DB. No Django, sei criar migrações, criar tabelas de modelos e consultar com o ORM, mas muita coisa que acontece por baixo parece magia negra
    Agora que estou abrindo uma empresa, fico inseguro de que vou esbarrar nesses problemas e ter que resolvê-los sozinho. Como devo abordar o aprendizado do que fazer em um ambiente de desenvolvimento?

    • Falhe e aprenda com os erros. Ou contrate desenvolvedores para falharem junto com você e aprenderem juntos

  • Gosto do Postgres, mas realmente odeio o fato de ele não ter um mecanismo embutido de updates/deletes em lote
    É a parte mais irritante, e quase todo mês preciso reescrever um batcher quando bato nessa parede