Mise à jour TRÈS GRANDE base de données PostgreSQL table efficacement

Si vous essayez de réinitialiser l'ensemble de la table, et pas seulement quelques lignes, il est généralement plus rapide (sur de très grands ensembles de données -- pas sur les tables régulières) pour simplement CREATE TABLE bar AS SELECT everything, but, copied, 0 FROM foo, puis échangez les tables et la chute de l'ancien. Évidemment, vous devez assurer que rien n'est inséré dans la table d'origine tout en vous faites cela. Vous aurez besoin de recréer l'index, trop.

Modifier: Une simple amélioration, afin d'éviter le blocage de la table pendant que vous copiez 14 Gigaoctets:

lock ;
create a new table, bar;
swap tables so that all writes go to bar;
unlock;
create table baz as select from foo;
drop foo;
create the index on baz;
lock;
insert into baz from bar;
swap tables;
unlock;
drop bar;

(laissez écrit se produire pendant que vous faites les copier et de les insérer post-factum).

OriginalL'auteur SquareCog

C'est pseudocode. Vous aurez besoin de 400 mo (pour les entiers) ou 800 MO (pour bigints) fichier temporaire (vous pouvez le compresser avec zlib si c'est un problème). Il aura besoin d'environ 100 scans d'une table pour aspirateurs. Mais il ne va pas gonfler une table de plus de 1% (à plus de 1000000 morts lignes à tout moment). Vous pouvez également le commerce de moins de scans pour plus de table de ballonnement.

// write all ids to temporary file in disk order                
// no where clause will ensure disk order
$file = tmpfile();
for $id, $replicated in query("select id, replicated from table") {
        if ( $replicated<>0 ) {
                write($file,&$id,sizeof($id));
        }
}

// prepare an update query
query("prepare set_replicated_0(bigint) as
        update table set replicated=0 where id=?");

// reread this file, launch prepared query and every 1000000 updates commit
// and vacuum a table
rewind($file);
$counter = 0;
query("start transaction");
while read($file,&$id,sizeof($id)) {
        query("execute set_replicated_0($id)");
        $counter++;
        if ( $counter % 1000000 == 0 ) {
                query("commit");
                query("vacuum table");
                query("start transaction");
        }
}
query("commit");
query("vacuum table");
close($file);

OriginalL'auteur Tometzky

Je suppose que ce que vous devez faire est de
un. copier les enregistrements de 2000 valeur de clé primaire dans une table temporaire avec la même limite standard, etc.
b. sélectionnez le même 2000 dossiers et effectuer les opérations nécessaires dans le curseur comme il est.
c. En cas de succès, exécutez une seule requête de mise à jour contre les enregistrements dans la table temporaire. Effacer la table temporaire et exécutez l'étape a de nouveau.
d. En cas d'échec, désactivez la table temporaire sans l'exécution de la requête de mise à jour.
Simple, efficace et fiable.
En ce qui concerne,
KT

OriginalL'auteur Kapil