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

• O que faz com que um INSERT grande fique lento e o uso do disco exploda?

O código a seguir usa uma função do uuid-oss módulo adicional.

• Bloqueia a tabela contra alterações simultâneas no SHARE modo (ainda permitindo leituras simultâneas). As tentativas de escrever na mesa irão esperar e eventualmente falhar. Veja abaixo.

• Copie a tabela inteira enquanto preenche a nova coluna rapidamente – possivelmente ordenando as linhas de maneira favorável.
  Se você vai reordenar as linhas, certifique-se de definir work_mem alto o suficiente para fazer a classificação na RAM ou tão alto como você pode pagar (apenas para sua sessão, não globalmente).

• Em seguida, adicione restrições, chaves estrangeiras, índices, gatilhos etc. ao novo tabela. Ao atualizar grandes partes 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á-lo um substituto imediato. Apenas esta última etapa adquire um bloqueio exclusivo na tabela antiga para o resto da transação – que deve ser muito 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 recriá-los depois.

• 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 o mais rápido. Qualquer outro método de atualização em vigor terá que reescrever a tabela inteira também, apenas de uma forma mais cara. Você só seguiria por esse caminho se não tivesse espaço livre suficiente em disco ou não pudesse permitir o bloqueio de toda a tabela ou gerar erros para tentativas de gravação simultâneas.

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

Outra transação (em outras sessões) tentando INSERT / UPDATE / DELETE na mesma tabela após sua transação ter obtido o SHARE bloqueio, esperará até que o bloqueio seja liberado ou um tempo limite seja iniciado, o que ocorrer primeiro. Eles fail de qualquer maneira, pois a tabela na qual eles estavam tentando gravar foi excluída abaixo deles.

A nova tabela tem um novo OID da 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 ” se foi.Postgres irá responder:

ERROR: could not open relation with OID 123456

Onde 123456 é 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 tabela original.

Mantendo a tabela existente, alternativa 1

Atualizar no lugar (possivelmente executando a atualização em pequenos segmentos por vez) antes de adicionar a restrição NOT NULL . Adicionar uma nova coluna com valores NULL e sem NOT NULL restrição é barato.
Já que Postgres 9.2 você também pode criar uma restrição CHECK com NOT VALID :

A restrição continuará ser aplicada contra inserções ou atualizações subsequentes

Isso permite que você atualize linhas peu à peu – em várias transações separadas . Isso evita manter travas de linha por muito tempo e também permite que linhas mortas sejam reutilizadas. (Você terá que executar VACUUM manualmente se não houver tempo suficiente entre o autovacuum iniciar.) Finalmente, adicione o NOT NULL restrição e remova a NOT VALID CHECK restriçã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 VALID em mais detalhes:

• Desative todas as restrições e verificações de tabela ao restaurar um dump

Mantendo a tabela existente, alternativa 2

Prepare o novo estado em uma tabela temporária , TRUNCATE o original e recarregue da tabela temporária. Tudo em uma transação . ainda precisa fazer um SHARE bloqueio antes pr eparar a nova tabela para evitar a perda de gravações simultâneas.

Detalhes nestas respostas relacionadas no SO:

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

Comentários

• Resposta fantástica! Exatamente a informação que eu estava procurando. Duas perguntas 1. Você tem alguma ideia de uma maneira fácil de testar quanto tempo levaria uma ação como essa? 2. Se levar, digamos, 5 minutos, o que acontece com as ações que tentam atualizar uma linha nessa tabela durante esses 5 minutos?
• @CollinPeters: 1. O leão ' s seria gasta em copiar a grande mesa – e possivelmente recriar índices e restrições (isso depende). Eliminar e renomear é barato. Para testar, você pode executar o script SQL preparado sem o LOCK até e excluindo o DROP. Eu só podia proferir suposições selvagens e inúteis. Quanto a 2., por favor, considere o adendo à minha resposta.
• @ErwinBrandstetter Continue a recriar visualizações, portanto, se eu tiver uma dúzia de visualizações que ainda usam a tabela antiga (oid) após a renomeação de tabelas. Existe alguma maneira de realizar uma substituição profunda em vez de reexecutar toda a atualização / criação da visualização?
• @CodeFarmer: Se você apenas renomear uma tabela, as visualizações continuarão trabalhando com a tabela renomeada. Para fazer as visualizações usarem a nova tabela, você precisa recriá-las com base na nova tabela. (Também para permitir que a tabela antiga seja excluída.) Nenhuma maneira (prática) de contornar isso.
• desde 9.2 postgres não ' joga could not open relation with OID 123456