Serrures dans Postgres: 7 conseils pour travailler avec des serrures

Bonjour encore! Mardi prochain, un nouveau flux commence sur le cours «SGBD relationnel» , nous continuons donc à publier des informations utiles sur le sujet. Allons-y.



La semaine dernière, j'ai écrit sur l' accès concurrentiel dans Postgres , quelles équipes se bloquent mutuellement et comment diagnostiquer les équipes bloquées. Bien sûr, après le diagnostic, vous pourriez avoir besoin d'un traitement. Avec Postgres, vous pouvez vous tirer une balle dans le pied, mais Postgres vous offre également des moyens de ne pas frapper la pointe. Voici quelques conseils importants sur comment et comment ne pas le faire que nous avons trouvés utiles lorsque vous travaillez avec des utilisateurs lors du passage de leur base de données Postgres unique à Citus ou lors de la création de nouvelles applications d' analyse en temps réel .

1. N'ajoutez jamais une colonne avec une valeur par défaut

Règle d'or PostgreSQL: lors de l'ajout d'une colonne à une table dans un environnement de production, ne spécifiez jamais de valeur par défaut .

L'ajout d'une colonne nécessite un verrou de table très agressif, qui bloque la lecture et l'écriture. Si vous ajoutez une colonne avec une valeur par défaut, PostgreSQL remplacera la table entière pour remplir la valeur par défaut de chaque ligne, ce qui peut prendre plusieurs heures dans les grandes tables. Dans le même temps, toutes les demandes seront bloquées, votre base de données ne sera donc pas disponible.

Ne faites pas ceci:

--     ,       (?) ALTER TABLE items ADD COLUMN last_update timestamptz DEFAULT now(); 

Faites-le mieux comme ceci:

 -- select, update, insert  delete ,      () ALTER TABLE items ADD COLUMN last_update timestamptz; -- select  insert ,  update  delete ,    UPDATE items SET last_update = now(); 

Ou, mieux encore, évitez de mettre à jour et de delete verrous pendant une longue période, en mettant à jour par petits lots, par exemple:

 do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0); 

De cette façon, vous pouvez ajouter et remplir une nouvelle colonne avec un minimum d'interférences pour vos utilisateurs.

2. Méfiez-vous des files d'attente de verrouillage, utilisez des délais d'attente

Chaque verrou dans PostgreSQL a une priorité. Si la transaction B tente de s'emparer d'un verrou qui est déjà détenu par la transaction A avec un niveau de verrouillage conflictuel, la transaction B attendra dans la file d'attente des verrous. Maintenant, quelque chose d'intéressant se produit: si une autre transaction C arrive, elle devra vérifier non seulement le conflit avec A, mais aussi avec la transaction B et toute autre transaction dans la file d'attente de verrouillage.

Cela signifie que même si votre commande DDL est capable de s'exécuter très rapidement, elle peut rester longtemps dans la file d'attente, en attendant la fin des demandes, et les demandes qui s'exécutent après seront bloquées derrière elle .

Si vous pouvez rencontrer de longues requêtes SELECT sur une table, ne procédez pas ainsi:

 ALTER TABLE items ADD COLUMN last_update timestamptz; 

Mieux vaut faire ceci:

 SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz; 

Si lock_timeout défini lock_timeout commande DDL ne sera pas exécutée si elle attend un verrou et bloque ainsi les demandes pendant plus de 2 secondes. L'inconvénient est que votre ALTER TABLE peut ne pas être exécuté, mais vous pouvez réessayer plus tard. Vous pouvez interroger pg_stat_activity pour voir si vous avez de longues requêtes avant d'exécuter la commande DDL.

3. Utilisez la création d'index non bloquant

Autre règle d'or de PostgreSQL: utilisez toujours la création d'index non bloquant.
La création d'un index pour un grand ensemble de données peut prendre des heures, voire des jours, et la commande CREATE INDEX régulière verrouille tous les enregistrements pendant la durée de la commande. Malgré le fait qu'il ne bloque pas les SELECT, il est encore assez mauvais, et il existe une meilleure façon: CREATE INDEX CONCURRENTLY .

Ne faites pas ceci:

 --    CREATE INDEX items_value_idx ON items USING GIN (value jsonb_path_ops); 

Au lieu de cela, procédez comme suit:

 --    DDL CREATE INDEX CONCURRENTLY items_value_idx ON items USING GIN (value jsonb_path_ops); 

La création d'index non bloquant a un inconvénient. Si quelque chose se passe mal, il ne revient pas en arrière et laisse un index incomplet ("invalide"). Si cela se produit, ne vous inquiétez pas, exécutez simplement

 DROP INDEX CONCURRENTLY items_value_idx 

et essayez de le créer à nouveau.

4. Utilisez des verrous agressifs le plus tard possible

Lorsque vous devez exécuter une commande qui reçoit des verrous de table agressifs, essayez de le faire le plus tard possible dans la transaction afin que les requêtes puissent continuer aussi longtemps que possible.

Par exemple, si vous souhaitez remplacer complètement le contenu du tableau. Ne faites pas ceci:

 BEGIN; --     : TRUNCATE items; -  : \COPY items FROM 'newdata.csv' WITH CSV COMMIT; 

Au lieu de cela, chargez les données dans une nouvelle table, puis remplacez l'ancienne:

 BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL); --  : \COPY items_new FROM 'newdata.csv' WITH CSV --     : DROP TABLE items; ALTER TABLE items_new RENAME TO items; COMMIT; 

Il y a un problème: nous n'avons pas bloqué les enregistrements depuis le tout début, et l'ancienne table des éléments aurait pu changer au moment où nous l'avons réinitialisée. Pour éviter cela, nous pouvons verrouiller explicitement la table pour l'écriture, mais pas pour la lecture:

 BEGIN; LOCK items IN EXCLUSIVE MODE; ... 

Parfois, il vaut mieux prendre le blocage en main.

5. Ajout d'une clé primaire avec un minimum de blocage

Ajouter une clé primaire à vos tables est souvent une bonne idée. Par exemple, si vous souhaitez utiliser la réplication logique ou migrer une base de données à l'aide de Citus Warp .

Postgres facilite la création d'une clé primaire à l'aide d' ALTER TABLE , mais lors de la création d'un index pour la clé primaire, ce qui peut prendre beaucoup de temps si la table est volumineuse, toutes les demandes seront bloquées.

 ALTER TABLE items ADD PRIMARY KEY (id); --      

Heureusement, vous pouvez effectuer tout le travail en premier en utilisant CREATE UNIQUE INDEX CONCURRENTLY , puis utiliser l'index unique comme clé primaire, ce qui est une opération rapide.

 CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id); --   ,     ALTER TABLE items ADD CONSTRAINT items_pk PRIMARY KEY USING INDEX items_pk; --  ,   

La division de la création de la clé primaire en deux étapes n'affecte pratiquement pas l'utilisateur.

6. N'utilisez jamais VACUUM FULL

L'expérience utilisateur postgres peut parfois être juste un peu géniale. Bien que VACUUM FULL ressemble à ce que vous aimeriez faire pour nettoyer la "poussière" de votre base de données, une commande plus appropriée serait:

 PLEASE FREEZE MY DATABASE FOR HOURS; 

VACUUM FULL écrase la table entière sur le disque, ce qui peut prendre des heures ou des jours, et en même temps bloque toutes les demandes. Bien qu'il existe plusieurs VACUUM FULL utilisation valides pour VACUUM FULL , comme une table qui était auparavant grande, mais maintenant elle est petite et prend toujours beaucoup d'espace, mais ce n'est probablement pas votre option.
Bien que vous devez vous efforcer de configurer les options de nettoyage automatique et d'utiliser des index pour accélérer les requêtes, vous pouvez parfois exécuter VACUUM mais PAS VACUUM FULL .

7. Évitez les blocages en organisant les commandes

Si vous utilisez PostgreSQL depuis un certain temps, vous avez probablement vu des erreurs telles que:

 ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661. 

Cela se produit lorsque les transactions simultanées prennent les mêmes verrous dans un ordre différent. Par exemple, une transaction exécute les commandes suivantes.

 BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world END; 

Dans le même temps, une autre transaction peut émettre les mêmes commandes, mais dans un ordre différent.

 BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world'; --    world UPDATE items SET counter = counter + 1 WHERE key = 'hello'; --    hello END; 

Si ces blocs de transactions sont exécutés en même temps, il est probable qu'ils seront bloqués en attente les uns des autres et ne se termineront jamais. Postgres reconnaît cette situation dans environ une seconde et annulera l'une des transactions afin de compléter l'autre. Lorsque cela se produit, vous devriez jeter un œil à votre application pour savoir si vous pouvez vous assurer que vos transactions sont toujours exécutées dans le même ordre. Si les deux transactions changent d'abord hello , puis world , la première transaction verrouillera la seconde sur hello avant de pouvoir capturer d'autres verrous.
Partagez vos conseils!

Nous espérons que ces recommandations vous seront utiles. Si vous avez d'autres conseils, n'hésitez pas à tweeter @citusdata ou notre communauté d'utilisateurs Citus active sur Slack .

Nous vous rappelons que dans quelques heures il y aura une journée portes ouvertes au cours de laquelle nous parlerons en détail du programme du prochain cours.