Como lidar com travamentos no PostgreSQL ao alterar tabelas em produção

Esta é aquela boa receita para evitar a ressaca causada por locks no seu banco de dados de produção em momentos críticos. Sabe aquele momento em que você "queria apenas adicionar uma coluninha na tabela" e acabou paralisando sua plataforma que processa 383438473847 transações por segundo? Então: esta receita é para você.

O importante é lembrar que o PostgreSQL sempre irá criar algum lock (bloqueio) nas tabelas quando estas sofrem qualquer tipo de alteração. Sendo assim esta receita vai te ajudar apenas a minimizar o tempo de bloqueio, ok?

Ingredientes

• PostgreSQL
• Um banco de dados que processe um número significativo de consultas e modificações por segundo
• Sua tranquilidade ao manipular bases de dados em produção (o mais importante)

Vai criar novos campos? Evite not null e valores padrão

Se for adicionar novos campos, evite incluir valores padrão. Ao criar novos campos sem valores padrão apenas a o esquema do sistema (system catalog) será alterado, e com isto não serão gerados bloqueios nem para escrita nem para leitura.

Se você definir valores padrão, leve em consideração que todos os registros precisarão ser alterados para o novo valor: se a tabela tiver milhões de registros o tempo do bloqueio pode ser significativo.

Evite então instruções tais como a exposta abaixo:

ALTER TABLE imensa_tabela_muito_concorrida ADD COLUMN tipo VARCHAR(255) DEFAULT 'padrão';

Opte por

ALTER TABLE imensa_tabela_muito_concorrida ADD COLUMN tipo VARCHAR(255);

Legal, mas os registros existentes precisavam ter na coluna tipo o texto "padrão". Como faço para alterar todos estes registros sem bloquear minha imensa tabela com milhões de registros por horas e horas?

Se for para editar registros em massa, tenha paciência e use lotes

Ingenuamente você pode achar que o passo anterior te evitou um bloqueio na tabela. Então espertamente você executa o seguinte comando SQL:

UPDATE imensa_tabela_muito_concorrida SET tipo = "padrão";

E sabe quem aparece? Ele: o bloqueio, pois a tabela é usada por diversos usuários simultaneamente. Você pode evitar o problema usando a instrução LIMIT do sql, tal como no exemplo a seguir:

UPDATE imensa_tabela_muito_concorrida SET tipo = "padrão" WHERE tipo is null LIMIT 100;

Com isto apenas 100 registros serão alterados no máximo a cada invocação do método. Esta instrução retorna o número de registros que foram alterados. Basta executar em loop até que retorne zero (mas faça com cuidado, evite enviar um lote imenso de instruções de uma única vez).

Defina o tempo máximo de espera para um bloqueio: lock_timeout

Já que você vai ter de viver com os bloqueios, é possível pelo menos definir o tempo máximo de ocorrência dos mesmos. Use a instrução SET lock_timeout, tal como no exemplo a seguir:

SET lock_timeout '120s';  --  lock de 120 segundos
UPDATE imensa_tabela_muito_concorrida SET tipo = "padrão" WHERE tipo is null LIMIT 1000;

Com isto você não irá definir que o bloqueio dure apenas 120 segundos, mas sim que sua instrução irá falhar caso o bloqueio dure mais do que o tempo definido.

Planeje

Finalmente, planeje: se é um recurso muito concorrido no banco de dados, evite realizar mudanças em horários de pico sabendo que seus usuários finais podem ser impactados.

É isto

No caso, estas são as medidas rápidas que você pode tomar para evitar acidentes em produção. Com certeza há outras ações que podem ser tomadas, e gostaria muito de ouvir suas opiniões.

Para tal, basta se inscrever na itexto Insights e incluir aqui nos comentários o que achou, ok?