Il y a des situations où,
dans une table sans clé primaire ou autre index unique, des clones complets d'enregistrements déjà existants tombent par inadvertance.
Par exemple, les valeurs de la métrique chronologique sont écrites dans le flux COPY de PostgreSQL, puis une panne soudaine, et une partie des données complètement identiques revient.
Comment débarrasser la base de données des clones inutiles?
Quand PK n'est pas un assistant
Le moyen le plus simple consiste à empêcher que cela ne se produise. Par exemple, lancez la même CLÉ PRIMAIRE. Mais cela n'est pas toujours possible sans augmenter la quantité de données stockées.
Par exemple, si la précision du système d'origine est supérieure à la précision du champ dans la base de données:
metric | ts | data -------------------------------------------------- cpu.busy | 2019-12-20 00:00:00 | {"value" : 12.34} cpu.busy | 2019-12-20 00:00:01 | {"value" : 10} cpu.busy | 2019-12-20 00:00:01 | {"value" : 11.2} cpu.busy | 2019-12-20 00:00:03 | {"value" : 15.7}
Tu as remarqué? Le compte à rebours, au lieu de 00:00:02, a été écrit dans la base de données avec ts une seconde plus tôt, mais est resté tout à fait valide du point de vue de l'application (après tout, les valeurs des données sont différentes!).
Bien sûr, vous pouvez créer
PK (métrique, ts) - mais nous obtiendrons alors des conflits d'insertion pour des données valides.
Vous pouvez créer
PK (métrique, ts, données) - mais cela augmentera considérablement son volume, que nous n'utiliserons pas.
Par conséquent, l'option la plus correcte consiste à créer un index non unique régulier
(métrique, ts) et à résoudre les problèmes après coup, s'ils surviennent.
«La guerre clonique a commencé»
Une sorte d'accident s'est produit et nous devons maintenant détruire les enregistrements de clones de la table.
Simulons les données source:
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl VALUES ('a', 1) , ('a', 3) , ('b', 2) , ('b', 2)
Puis notre main a tremblé trois fois, Ctrl + V coincé, et maintenant ...
Tout d'abord, comprenons que notre table peut être très grande, par conséquent, après avoir trouvé tous les clones, il nous est conseillé de littéralement «toucher un doigt» pour supprimer
des enregistrements spécifiques sans avoir à les rechercher à nouveau .
Et il y a une telle façon - c'est l'
adressage par ctid , l'identifiant physique d'un enregistrement particulier.
Autrement dit, nous devons d'abord collecter les enregistrements ctid dans le contexte du contenu complet de la ligne du tableau. L'option la plus simple consiste à convertir la ligne entière en texte:
SELECT T::text , array_agg(ctid) ctids FROM tbl T GROUP BY 1;
t | ctids --------------------------------- (e,5) | {"(0,9)"} (d,4) | {"(0,8)"} (c,3) | {"(0,5)","(0,6)","(0,7)"} (b,2) | {"(0,3)","(0,4)"} (a,3) | {"(0,2)"} (a,1) | {"(0,1)"}
Est-il possible de ne pas lancer?En principe, c'est possible dans la plupart des cas. Jusqu'à ce que vous commenciez à utiliser
des champs de
type dans cette table
sans l'opérateur d'égalité :
CREATE TABLE tbl(k text, v integer, x point); SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T;
Oui, nous voyons immédiatement que s'il y a plus d'un enregistrement dans le tableau, c'est tout ce qu'il y a des clones. Laissons seulement eux:
SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T;
unnest ------ (0,6) (0,7) (0,4)
Amants plus courtsVous pouvez l'écrire comme ceci:
SELECT unnest((array_agg(ctid))[2:]) FROM tbl T GROUP BY T::text;
Étant donné que la valeur de la chaîne sérialisée elle-même ne nous intéresse pas, nous l'avons simplement supprimée des colonnes renvoyées de la sous-requête.
Tout ce qui reste à faire est de supprimer SUPPRIMER l’ensemble que nous avons reçu:
DELETE FROM tbl WHERE ctid = ANY(ARRAY( SELECT unnest(ctids[2:]) FROM ( SELECT array_agg(ctid) ctids FROM tbl T GROUP BY T::text ) T )::tid[]);
Vérifiez vous-même:
[regardez expliquez.tensor.ru]Oui, c'est vrai: nos 3 enregistrements ont été sélectionnés pour le seul Seq Scan de la table entière, et le nœud Supprimer a utilisé un
seul passage pour rechercher des données à l'
aide de Tid Scan :
-> Tid Scan on tbl (actual time=0.050..0.051 rows=3 loops=1) TID Cond: (ctid = ANY ($0))
Si vous avez effacé de nombreux enregistrements,
n'oubliez pas de conduire VACUUM ANALYZE .
Vérifions une table plus grande et avec beaucoup de prises:
TRUNCATE TABLE tbl; INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k
[regardez expliquez.tensor.ru]Ainsi, la méthode fonctionne correctement, mais elle doit être appliquée avec une certaine prudence. Parce que pour chaque enregistrement supprimé, il y a une lecture de la page de données dans Tid Scan et une dans Supprimer.