Est-ce que Postgres réécrit toute la ligne lors de la mise à jour?
Nous exécutons Postgres 9.0 sur Windows 2008 Server. Il y a une grande table contenant une colonne bytea pour stocker des données binarys allant de 0 à 5 Mo dans chaque ligne:
CREATE TABLE files ( file_id serial NOT NULL, data bytea NOT NULL, on_disk boolean, CONSTRAINT files_pkey PRIMARY KEY (file_id) )
Récemment, nous avons mis à jour le champ on_disk pour chaque ligne (sans toucher au champ de données). Nous pensons que cela a grignoté de la place dans notre tablespace temporaire (ou quelque chose comme ça), pour deux raisons:
1) Nous avons commencé à recevoir cette erreur dans d’autres parties aléatoires du système exécutant des requêtes volumineuses:
ERROR: 53100: could not write block 92271 of temporary file
2) Notre espace libre est passé de 7 Go à 1,5 Go en une semaine, ce qui est inhabituel.
Quelqu’un peut-il confirmer:
a) La mise à jour d’une ligne dans postgres provoquera-t-elle la réécriture de la ligne ENTIRE (y compris les données binarys volumineuses) sans libérer l’ancien espace? Cela expliquerait nos symptômes
b) Ecrit-il dans un autre tablespace temporaire pendant le changement, qui utilise également de l’espace? (Peut-on forcer la libération de l’espace temporaire?)
c) Existe-t-il un moyen d’effectuer des mises à jour mineures de champs booléens sur cette table SANS réécrire la ligne (et de réduire l’espace disque) à chaque fois?
d) Pouvons-nous forcer postgres périodiquement à libérer l’espace utilisé sans réécrire la table entière? (Nos méthodes connues pour libérer de l’espace impliquent une réécriture de table pour laquelle nous n’avons pas de place)
PS: Oui, nous effectuons une migration de notre serveur vers un hôte disposant d’un espace de stockage plus important.
Au moins sur 9.3, PostgreSQL ne réécrit pas les champs stockés hors ligne dans les tables TOAST s’ils sont stockés hors ligne. Je ne sais pas si c’est vrai dans 9.0.
Vous pouvez voir quel stockage est utilisé pour une colonne avec \d+ tablename ; la colonne de storage indique le mode utilisé. Les tuples individuels peuvent être stockés compressés en ligne s’ils sont suffisamment petits (ex: <2K), même dans une colonne de stockage extended où les n-uplets sont éligibles pour le stockage hors ligne.
Voir la documentation de TOAST et ALTER TABLE ... SET STORAGE .
Les fichiers temp_tablespaces sont stockés dans les temp_tablespaces . Par défaut, il est vide, auquel cas il revient à default_tablespace , qui à son tour, si vide, revient à l’espace de pg_default .
L’espace dans les tables / index devrait être libéré pour être réutilisé automatiquement par autovacuum. Assurez-vous que votre démon autovacuum fonctionne assez souvent et ne contient pas trop de cost_delay. Autovacuum a été considérablement amélioré depuis 9.0.
Si vous voulez libérer de l’espace sur le système d’exploitation ou l’utiliser dans d’autres tables, vous devez VACUUM FULL ou utiliser un outil externe tel que pg_repack pour le faire de manière moins intrusive.
C) de vos questions:
Existe-t-il un moyen d’effectuer des mises à jour mineures de champs booléens sur cette table SANS réécrire la ligne (et de gommer l’espace disque) à chaque fois?
Comme l’ explique @Craig , les colonnes “TOAST-able” et supérieures à un certain seuil sont stockées hors ligne dans une table TOAST dédiée par table (“relations forks” séparées, fichiers séparés sur le disque). Ainsi, une colonne bytea 5 Mo bytea dans une mise à jour si la colonne elle-même n’est pas modifiée. Le manuel:
Pendant une opération UPDATE, les valeurs des champs inchangés sont normalement conservées telles quelles; Ainsi, une mise à jour d’une ligne avec des valeurs hors ligne n’entraîne aucun coût TOAST si aucune des valeurs hors ligne ne change .
Emphase audacieuse la mienne
La ligne dans le lien de la relation principale est toujours copiée et une ligne morte rest en arrière lors de la mise à jour (que les valeurs aient réellement changé ou non). Pour les grandes tailles de lignes, la solution suivante pourrait payer:
Créez une petite table séparée 1: 1 pour les indicateurs fréquemment modifiés. Juste la clé primaire (= clé étrangère en même temps) et les indicateurs fréquemment modifiés. Cela rendrait les mises à jour beaucoup plus rapides et préserverait l’espace disque – pour une surcharge initiale et un coût pour les requêtes devant rejoindre les deux tables (les autres requêtes deviennent plus rapides). En savoir plus sur l’espace disque requirejs pour les lignes de tableau:
- Comprendre la taille des lignes Postgres