Início / dba / Perguntas / 341007
Accepted
John Little
Asked: 2024-07-17 22:07:56 +0800 CST

Postgres: existe uma maneira de acelerar a mudança do tipo de coluna de int4 para int8?

  • 772

Temos cerca de 100 alterações simples para fazer em nosso esquema de banco de dados, como esta:

alter table transactions alter customer_sport_id type bigint;

Antes era int4. A maioria das colunas alteradas possui um ou mais índices.

Cada um leva cerca de 30 a 45 minutos em uma poderosa instância RDS dedicada (db.r6i.4xlarge) sem outra carga.

Temos que confirmar após cada linha para evitar o uso de todo o armazenamento.

O problema é que é lento, levará dias para fazer as alterações e não podemos ficar inativos por tanto tempo.

Há algo que possamos fazer para acelerar isso? Por exemplo

  1. descartando índices e criando-os novamente depois? (isso aceleraria?)
  2. desabilitando o WAL? Não tenho certeza se isso é viável ou arriscado (por exemplo, o banco de dados pode ser corrompido se a migração falhar no meio do caminho)
  3. Criar uma nova tabela e, de alguma forma, copiar todos os dados antigos para a nova tabela (poderíamos fazer isso em SQL ou exigiria um procedimento armazenado?), eliminar a tabela antiga e criar as sequências e índices na nova tabela ?

Aparentemente, colocamos aspirador uma vez por semana.

Aqui estão as estatísticas de desempenho do banco de dados da última hora (você pode ver no lançamento do armazenamento que duas instruções foram concluídas):

insira a descrição da imagem aqui

postgresql

2 respostas

  1. Best Answer

    Se você estiver alterando várias colunas na mesma tabela, poderá fazer isso com um comando:

    ALTER TABLE foo ALTER col_a TYPE bigint, ALTER col_b TYPE bigint;

    Se precisar reconstruir a tabela, ainda terá que fazê-lo... o que também requer a reconstrução de todos os índices... mas só fará isso uma vez para todo o comando ALTER .

    Então se precisar alterar muitas colunas na mesma tabela, será muito mais rápido.

    descartando índices e criando-os novamente depois?

    Se você emitir muitos comandos ALTER e cada um deles exigir uma reconstrução de tabela, a eliminação dos índices evitará a reconstrução deles após cada comando. Mas é muito mais simples agrupar todas as suas alterações em um comando ALTER.

    desabilitando o WAL? Não tenho certeza se isso é viável ou arriscado (por exemplo, o banco de dados pode ser corrompido se a migração falhar no meio do caminho)

    Perigoso.

    Criando uma nova tabela e, em seguida, copiando todos os dados antigos para a nova tabela

    Sim, você pode fazer CREATE TABLE e depois INSERT INTO SELECT. Para obter melhor desempenho, você pode criá-lo UNLOGGED, para que não seja à prova de falhas durante a construção e, em seguida, alterne-o para LOGGED antes de colocar a tabela em produção.

    Se você deseja apenas alterar os tipos de colunas, isso quase não tem vantagens... No entanto, se a nova tabela for criada UNLOGGED, ela não gravará nenhum WAL ao preenchê-la, o que pode ajudar com seus problemas de espaço em disco. O principal uso para isso é se você quiser processar os dados com funções ou subconsultas dependentes: provavelmente será muito mais rápido usar um INSERT INTO SELECT que aplica seu processamento com funções e JOINs do que atualizar cada linha individualmente.

    • 1

  2. Alterar o tipo de dados da coluna de INT para BIGINT requer uma reescrita da tabela, que é principalmente E/S de disco, portanto, a única maneira de acelerá-la seria usar um armazenamento mais rápido.

    • 0

relate perguntas