La meilleure façon de remplir une nouvelle colonne dans une grande table?

• Quest-ce qui cause le ralentissement dun INSERT volumineux et lexplosion de lutilisation du disque?

Le code ci-dessous utilise une fonction du module supplémentaire uuid-oss .

• Verrouillez la table contre les modifications simultanées en SHARE mode (autorisant toujours les lectures simultanées). Les tentatives décriture dans la table vont attendre et finalement échouer. Voir ci-dessous.

• Copiez le tableau entier tout en remplissant la nouvelle colonne à la volée – éventuellement en ordonnant les lignes favorablement tout en y étant.
  Si vous allez réorganiser les lignes, assurez-vous de définir work_mem suffisamment haut pour effectuer le tri en RAM ou aussi haut comme vous pouvez vous le permettre (juste pour votre session, pas globalement).

• Ensuite ajoutez des contraintes, des clés étrangères, des indices, des déclencheurs, etc. au nouveau table. Lors de la mise à jour de grandes parties dune table, il est beaucoup plus rapide de créer des index à partir de zéro que dajouter des lignes de manière itérative. Conseils connexes dans le manuel.

• Lorsque la nouvelle table est prête, supprimez lancienne et renommez la nouvelle pour en faire un remplacement instantané. Seule cette dernière étape acquiert un verrou exclusif sur lancienne table pour le reste de la transaction – qui devrait être très courte maintenant.
  Elle nécessite également de supprimer tout objet en fonction du type de table (vues, fonctions utilisant le type de table dans la signature, …) et recréez-les ensuite.

• Faites tout cela en une seule transaction pour éviter les états incomplets.

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; 

Cela devrait être le plus rapide. Toute autre méthode de mise à jour en place doit également réécrire lensemble du tableau, mais dune manière plus coûteuse. Vous nemprunterez cette voie que si vous navez pas assez despace libre sur le disque ou si vous ne pouvez pas vous permettre de verrouiller toute la table ou de générer des erreurs pour les tentatives décriture simultanées.

Quarrive-t-il aux écritures simultanées?

Autre transaction (dans dautres sessions) essayant de INSERT / UPDATE / DELETE dans la même table après que votre transaction a pris le verrou SHARE, attendra jusquà ce que le verrou soit libéré ou quun délai dattente entre en vigueur, selon la première éventualité. Ils échouer de toute façon, car la table dans laquelle ils essayaient décrire a été supprimée de dessous.

La nouvelle table a un nouveau table OID, mais les transactions simultanées ont déjà résolu le nom de la table en OID de la table précédente . Lorsque le verrou est finalement libéré, ils essaient de verrouiller la table eux-mêmes avant décrire dessus et trouvent quil  » est parti.Postgres répondra:

ERROR: could not open relation with OID 123456

Où 123456 est lOID de lancienne table. Vous devez détecter cette exception et réessayer les requêtes dans le code de votre application pour léviter.

Si vous ne pouvez pas vous permettre que cela se produise, vous devez conserver votre table dorigine.

Garder la table existante, alternative 1

Mettre à jour en place (éventuellement exécuter la mise à jour sur de petits segments à la fois) avant dajouter la contrainte NOT NULL . Ajouter une nouvelle colonne avec des valeurs NULL et sans contrainte NOT NULL est bon marché.
Depuis Postgres 9.2 vous pouvez également créer une contrainte CHECK avec NOT VALID :

La contrainte sera toujours être appliqué contre les insertions ou mises à jour ultérieures

Cela vous permet de mettre à jour les lignes peu à peu – dans plusieurs transactions distinctes . Cela évite de garder les verrous de ligne trop longtemps et permet également de réutiliser les lignes mortes. (Vous « devrez exécuter VACUUM manuellement sil ny a pas assez de temps pour que lautovacuum se déclenche.) Enfin, ajoutez le NOT NULL contrainte et supprimez la contrainte NOT VALID CHECK:

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; 

Réponse associée discutant de NOT VALID plus en détail:

• Désactivez toutes les contraintes et vérifications de table lors de la restauration dun vidage

Conserver la table existante, alternative 2

Préparer le nouvel état dans une table temporaire , TRUNCATE loriginal et rechargez à partir de la table temporaire. Le tout en une transaction . Vous il faut encore prendre un SHARE verrou avant pr éparer la nouvelle table pour éviter de perdre des écritures simultanées.

Détails dans ces réponses connexes sur SO:

• Meilleur moyen de supprimer des millions de lignes par ID
• Ajouter une nouvelle colonne sans verrou de tableau?

Commentaires

• Réponse fantastique! Exactement les informations que je recherchais. Deux questions 1. Avez-vous une idée sur un moyen simple de tester combien de temps une action comme celle-ci prendrait? 2. Si cela prend par exemple 5 minutes, quadvient-il des actions qui tentent de mettre à jour une ligne dans cette table pendant ces 5 minutes?
• @CollinPeters: 1. Le lion ' du temps serait consacrée à la copie de la grande table – et éventuellement à la recréation dindices et de contraintes (cela dépend). La suppression et le changement de nom sont bon marché. Pour tester, vous pouvez exécuter votre script SQL préparé sans LOCK jusquà et en excluant DROP. Je ne pouvais faire que des suppositions folles et inutiles. Quant à 2., veuillez considérer laddendum à ma réponse.
• @ErwinBrandstetter Continuez à recréer les vues, donc si jai une douzaine de vues qui utilisent encore lancienne table (oid) après le changement de nom de la table. Existe-t-il un moyen deffectuer un remplacement profond plutôt que de réexécuter toute lactualisation / la création de la vue?
• @CodeFarmer: Si vous renommez simplement une table, les vues continuent de fonctionner avec la table renommée. Pour que les vues utilisent à la place la nouvelle table , vous devez les recréer en fonction de la nouvelle table. (Aussi pour permettre la suppression de lancienne table.) Aucun moyen (pratique) de contourner ce problème.
• depuis 9.2 postgres ne ' t throw could not open relation with OID 123456