Início / dba / Perguntas / 60965
Accepted
maxschlepzig
Asked: 2014-03-15 10:46:44 +0800 CST

O PostgreSQL otimiza a adição de colunas com padrões não NULL?

  • 772

Ao adicionar NOT NULLcolunas com um DEFAULTvalor - o PostgreSQL otimiza esta operação?

Caso a tabela tenha n linhas, uma coluna alter-table-add não otimizada resultaria em n gravações do valor padrão - o que poderia ser muito doloroso, obviamente. Com a otimização, o banco de dados criaria instantaneamente a nova coluna, armazenaria apenas uma cópia do valor padrão que seria retornado quando nenhum valor não padrão fosse encontrado para essa coluna em uma estrutura de dados de índice adequada.

Por exemplo , o Oracle 11g tem essa otimização .

postgresql optimization

2 respostas

  1. Best Answer

    Não existe tal mecanismo no PostgreSQL.

    No entanto, você ainda pode evitar os efeitos excessivos de tal mudança de tabela.

    A instrução a seguir adquire um bloqueio exclusivo de acesso na tabela durante a instrução/transação:

    ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

    Essa instrução altera o catálogo e reescreve toda a tabela para que a nova coluna contenha o valor padrão em todas as linhas. Se a tabela tiver muitas linhas e for acessada com frequência suficiente, isso causaria alguns problemas temporários.

    Para evitá-lo, tente manter o bloqueio exclusivo o mais curto possível:

    ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

    Como isso é basicamente apenas uma (na verdade duas) alteração no catálogo (nenhuma alteração de dados ocorre), ela será concluída rapidamente. Em seguida, dependendo de suas necessidades e do uso da tabela, você pode atualizar a nova coluna para o padrão em uma etapa ou em lotes e, quando terminar, definir a coluna como NOT NULL.

    Atualização sobre um desejo se tornando realidade: o PostgreSQL 11 terá esse recurso. Veja https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/ para mais.

    • 19

  2. Sim, com PostgreSQL 11

    Esse recurso é novo e chegou à versão 11.

    ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

    O comando acima é um desses comandos que será afetado por essa otimização; mas, deve-se dizer que nãoNOT NULL é necessário. Qualquer nova coluna adicionada com um padrão não nulo é otimizada agora. Você pode encontrar a entrada neste commitfest Você também deve verificar este ótimo artigo sobre isso, "A Missing Link in Postgres 11: Fast Column Creation with Defaults" .

    Solução alternativa pré-PostgreSQL 11

    Se você está tentando evitar o table-lock exclusivo na mesa, siga o conselho de Craig Ringer,

    • Adicionar uma coluna sem umDEFAULT
    • ALTERpara adicionar o DEFAULTdepois, para que se aplique às linhas recém-inseridas
    • Em seguida, preencha a nova coluna nas linhas existentes por lote progressivo UPDATEs
    • Quando todas as linhas tiverem o valor, você adiciona a NOT NULLrestrição
    • 13

relate perguntas