Melhor maneira de preencher uma nova coluna em uma tabela grande?

Depende muito dos detalhes de sua configuração e requisitos.

Observe que desde o Postgres 11, apenas adicionar uma coluna com um volátilDEFAULT ainda aciona uma reescrita de tabela . Infelizmente, este é o seu caso.

Se você tiver espaço livre suficiente em disco - pelo menos 110% pg_size_pretty((pg_total_relation_size(tbl))- e puder pagar um bloqueio de compartilhamento por algum tempo e um bloqueio exclusivo por um período muito curto, crie uma nova tabela incluindo a uuidcoluna usando CREATE TABLE AS. Por quê?

• O que faz com que o INSERT grande diminua e o uso do disco exploda?

O código abaixo usa uma função do uuid-ossmódulo adicional .

• Bloqueie a tabela contra alterações simultâneas no SHAREmodo (ainda permitindo leituras simultâneas). As tentativas de gravar na tabela aguardarão e, eventualmente, falharão. Veja abaixo.

• Copie a tabela inteira enquanto preenche a nova coluna rapidamente - possivelmente ordenando as linhas favoravelmente enquanto estiver nela.
  Se você for reordenar as linhas, certifique-se de definir work_memalto o suficiente para fazer a classificação na RAM ou o mais alto que puder (apenas para sua sessão, não globalmente).

• Em seguida , adicione restrições, chaves estrangeiras, índices, gatilhos etc. à nova tabela. Ao atualizar grandes porções de uma tabela, é muito mais rápido criar índices do zero do que adicionar linhas iterativamente. Conselhos relacionados no manual.

• Quando a nova tabela estiver pronta, elimine a antiga e renomeie a nova para torná-la uma substituição imediata. Somente esta última etapa adquire um bloqueio exclusivo na tabela antiga para o restante da transação - que deve ser bem curta agora.
  Também requer que você exclua qualquer objeto dependendo do tipo de tabela (visualizações, funções usando o tipo de tabela na assinatura, ...) e os recrie posteriormente.

• Faça tudo em uma transação para evitar estados incompletos.

BEGIN;
LOCK TABLE tbl IN SHARE MODE;

SET LOCAL work_mem = '???? MB';  -- just for this transaction

CREATE TABLE tbl_new AS 
SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order>
FROM   tbl
ORDER  BY ??;  -- optionally order rows favorably while being at it.

ALTER TABLE tbl_new
   ALTER COLUMN tbl_uuid SET NOT NULL
 , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1()
 , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid);

-- more constraints, indices, triggers?

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME tbl;

-- recreate views etc. if any
COMMIT;

Isso deve ser mais rápido. Qualquer outro método de atualização no local também precisa reescrever a tabela inteira, apenas de uma maneira mais cara. Você só seguiria esse caminho se não tivesse espaço livre suficiente no disco ou não pudesse bloquear a tabela inteira ou gerar erros para tentativas de gravação simultâneas.

O que acontece com gravações simultâneas?

Outra transação (em outras sessões) tentando INSERT/ UPDATE/ DELETEna mesma tabela após sua transação ter recebido o SHAREbloqueio, aguardará até que o bloqueio seja liberado ou um tempo limite seja ativado, o que ocorrer primeiro. Eles falharão de qualquer maneira, pois a tabela na qual eles estavam tentando gravar foi excluída sob eles.

A nova tabela tem um novo OID de tabela, mas a transação simultânea já resolveu o nome da tabela para o OID da tabela anterior . Quando o bloqueio é finalmente liberado, eles tentam bloquear a tabela antes de escrever nela e descobrem que ela desapareceu. O Postgres responderá:

ERROR: could not open relation with OID 123456

Onde 123456está o OID da tabela antiga. Você precisa capturar essa exceção e repetir as consultas no código do aplicativo para evitá-la.

Se você não pode permitir que isso aconteça, você deve manter sua mesa original.

Mantendo a tabela existente, alternativa 1

Atualize no local (possivelmente executando a atualização em pequenos segmentos de cada vez) antes de adicionar a NOT NULLrestrição. Adicionar uma nova coluna com valores NULL e sem NOT NULLrestrição é barato.
Desde o Postgres 9.2 você também pode criar uma CHECKrestrição comNOT VALID :

A restrição ainda será aplicada em inserções ou atualizações subsequentes

Isso permite que você atualize as linhas peu à peu - em várias transações separadas . Isso evita manter bloqueios de linha por muito tempo e também permite que linhas mortas sejam reutilizadas. (Você terá que executar VACUUMmanualmente se não houver tempo suficiente para o autovacuum entrar em ação.) Finalmente, adicione a NOT NULLrestrição e remova a NOT VALID CHECKrestrição:

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID;

-- update rows in multiple batches in separate transactions
-- possibly run VACUUM between transactions

ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL;
ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

Resposta relacionada discutindo NOT VALIDcom mais detalhes:

• Desabilite todas as restrições e verificações de tabela ao restaurar um despejo

Mantendo a tabela existente, alternativa 2

Prepare o novo estado em uma tabela temporária , TRUNCATEo original e reabasteça da tabela temporária. Tudo em uma transação . Você ainda precisa fazer um SHAREbloqueio antes de preparar a nova tabela para evitar a perda de gravações simultâneas.

Detalhes nesta resposta relacionada no SO:

• Melhor maneira de excluir milhões de linhas por ID
• Adicionar nova coluna sem bloqueio de tabela?

Não tenho uma resposta "melhor", mas tenho uma resposta "menos ruim" que pode permitir que você faça as coisas razoavelmente rápido.

Minha tabela tinha linhas de 2MM e o desempenho da atualização estava aumentando quando tentei adicionar uma coluna de carimbo de data/hora secundária que padronizou para a primeira.

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ;
UPDATE mytable SET new_timestamp = old_timestamp ;
ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ;

Depois que ele parou por 40 minutos, tentei isso em um pequeno lote para ter uma ideia de quanto tempo isso poderia levar - a previsão era de cerca de 8 horas.

A resposta aceita é definitivamente melhor - mas esta tabela é muito usada no meu banco de dados. Existem algumas dúzias de tabelas que usam FKEY nele; Eu queria evitar a troca de chaves estrangeiras em tantas tabelas. E depois há pontos de vista.

Um pouco de pesquisa de documentos, estudos de caso e StackOverflow, e eu tive o "A-Ha!" momento. O dreno não estava no núcleo UPDATE, mas em todas as operações INDEX. Minha tabela tinha 12 índices - alguns para restrições exclusivas, alguns para acelerar o planejador de consultas e alguns para pesquisa de texto completo.

Cada linha UPDATED não estava apenas trabalhando em um DELETE/INSERT, mas também na sobrecarga de alterar cada índice e verificar as restrições.

Minha solução foi descartar todos os índices e restrições, atualizar a tabela e adicionar todos os índices/restrições de volta.

Demorou cerca de 3 minutos para escrever uma transação SQL que fez o seguinte:

• COMEÇAR;
• índices/contenções descartadas
• atualizar tabela
• re-adicionar índices/restrições
• COMPROMETER-SE;

O script levou 7 minutos para ser executado.

A resposta aceita é definitivamente melhor e mais adequada... e praticamente elimina a necessidade de tempo de inatividade. No meu caso, porém, teria sido necessário muito mais trabalho do "Desenvolvedor" para usar essa solução e tivemos uma janela de 30 minutos de tempo de inatividade programado em que isso poderia ser realizado. Nossa solução abordou isso em 10.