# Travaux pratiques > Travaux pratiques de partitionnement déclaratif et leurs solutions, y compris le partitionnement pendant l’activité. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## 0.1 Travaux pratiques La version en ligne des solutions de ces TP est disponible sur . ## 0.1.1 Partitionnement **But** : Mettre en place le partitionnement déclaratif Nous travaillons sur la base **cave**. Si vous ne l’avez pas déjà, la base **cave** (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi : **Exemple 0-1.** Préparation de la base cave\. ```bash curl -kL https://dali.bo/tp_cave -o cave.dump psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'" psql -c "CREATE DATABASE cave OWNER caviste" pg_restore -d cave cave.dump # NB : une erreur sur un schéma 'public' existant est normale ``` Nous allons partitionner la table `stock` sur l’année. Pour nous simplifier la vie, nous allons limiter le nombre d’années dans `stock` (cela nous évitera la création de 50 partitions) : ```sql -- Création de lignes en 2001-2005 INSERT INTO stock SELECT vin_id, contenant_id, 2001 + annee % 5, sum(nombre) FROM stock GROUP BY vin_id, contenant_id, 2001 + annee % 5; -- purge des lignes prédédentes DELETE FROM stock WHERE annee<2001; ``` Nous n’avons maintenant que des bouteilles des années 2001 à 2005. > - Renommer `stock` en `stock_old`. > - Créer une table partitionnée `stock` vide, sans index pour le moment. > - Créer les partitions de `stock`, avec la contrainte d’année : `stock_2001` à `stock_2005`. > - Insérer tous les enregistrements venant de l’ancienne table `stock`. > - Passer les statistiques pour être sûr des plans à partir de maintenant (nous avons modifié beaucoup d’objets). > - Vérifier la présence d’enregistrements dans `stock_2001` (syntaxe `SELECT ONLY`). > - Vérifier qu’il n’y en a aucun dans `stock`. > - Vérifier qu’une requête sur `stock` sur 2002 ne parcourt qu’une seule partition. > - Remettre en place les index présents dans la table `stock` originale. > - Il se peut que d’autres index ne servent à rien (ils ne seront dans ce cas pas présents dans la correction). > - Quel est le plan pour la récupération du stock des bouteilles du `vin_id` 1725, année 2003 ? > - Essayer de changer l’année de ce même enregistrement de `stock` (la même que la précédente). Pourquoi cela échoue-t-il ? > - Supprimer les enregistrements de 2004 pour `vin_id` = 1725. > - Retenter la mise à jour. > - Pour vider complètement le stock de 2001, supprimer la partition `stock_2001`. > - Tenter d’ajouter au stock une centaine de bouteilles de 2006. > - Pourquoi cela échoue-t-il ? > - Créer une partition par défaut pour recevoir de tels enregistrements. > - Retenter l’ajout. > - Tenter de créer la partition pour l’année 2006. Pourquoi cela échoue-t-il ? > - Pour créer la partition sur 2006, au sein d’une seule transaction : > - détacher la partition par défaut ; > - y déplacer les enregistrements mentionnés ; > - ré-attacher la partition par défaut. ## 0.1.2 Partitionner pendant l’activité **But** : Mettre en place le partitionnement déclaratif sur une base en cours d’activité ### 0.1.2.1 Préparation > Créer une base **pgbench** vierge, de taille 10 ou plus. NB : Pour le TP, la base sera d’échelle 10 (environ 168 Mo). Des échelles 100 ou 1000 seraient plus réalistes. > Dans une fenêtre en arrière-plan, laisser tourner un processus `pgbench` avec une activité la plus soutenue possible. Il ne doit pas tomber en erreur pendant que les tables vont être partitionnées ! Certaines opérations vont poser des verrous, le but va être de les réduire au maximum. > Pour éviter un « empilement des verrous » et ne pas bloquer trop longtemps les opérations, faire en sorte que la transaction échoue si l’obtention d’un verrou dure plus de 10 s. ### 0.1.2.2 Partitionnement par hash > Pour partitionner la table `pgbench_accounts` par *hash* sur la colonne `aid` sans que le traitement pgbench tombe en erreur, préparer un script avec, dans une transaction : > > - la création d’une table partitionnée par hash en 3 partitions au moins ; > - le transfert des données depuis `pgbench_accounts` ; > - la substitution de la table partitionnée à la table originale. > > Tester et exécuter. > Supprimer l’ancienne table `pgbench_accounts_old`. ### 0.1.2.3 Partitionnement par valeur `pgbench` doit continuer ses opérations en tâche de fond. > La table `pgbench_history` se remplit avec le temps. Elle doit être partitionnée par date (champ `mtime`). Pour le TP, on fera 2 partitions d’une minute, et une partition par défaut. La table actuelle doit devenir une partition de la nouvelle table partitionnée. > > - Écrire un script qui, dans une seule transaction, fait tout cela et substitue la table partitionnée à la table d’origine. > > NB : Pour éviter de coder des dates en dur, il est possible, avec `psql`, d’utiliser une variable : > > ```sql > SELECT ( now()+ interval '60s') AS date_frontiere \gset > SELECT :'date_frontiere'::timestamptz ; > ``` > Exécuter le script, attendre que les données s’insèrent dans les nouvelles partitions. ### 0.1.2.4 Purge > - Continuer de laisser tourner `pgbench` en arrière-plan. > - Détacher et détruire la partition avec les données les plus anciennes. ### 0.1.2.5 Contraintes entre tables partitionnées > - Ajouter une clé étrangère entre `pgbench_accounts` et `pgbench_history`. Voir les contraintes créées. > Si vous n’avez pas déjà eu un problème à cause du `statement_timeout`, dropper la contrainte et recommencer avec une valeur plus basse. Comment contourner ? ### 0.1.2.6 Index global > On veut créer un index sur `pgbench_history (aid)`. > > Pour ne pas gêner les écritures, il faudra le faire de manière concurrente. Créer l’index de manière concurrente sur chaque partition, puis sur la table partitionnée. ## 0.2 Travaux pratiques (solutions) ## 0.2.1 Partitionnement **But** : Mettre en place le partitionnement déclaratif Pour nous simplifier la vie, nous allons limiter le nombre d’années dans `stock` (cela nous évitera la création de 50 partitions). ```sql INSERT INTO stock SELECT vin_id, contenant_id, 2001 + annee % 5, sum(nombre) FROM stock GROUP BY vin_id, contenant_id, 2001 + annee % 5 ; DELETE FROM stock WHERE annee<2001 ; ``` Nous n’avons maintenant que des bouteilles des années 2001 à 2005. > - Renommer `stock` en `stock_old`. > - Créer une table partitionnée `stock` vide, sans index pour le moment. ```sql ALTER TABLE stock RENAME TO stock_old; CREATE TABLE stock(LIKE stock_old) PARTITION BY LIST (annee); ``` > - Créer les partitions de `stock`, avec la contrainte d’année : `stock_2001` à `stock_2005`. ```sql CREATE TABLE stock_2001 PARTITION of stock FOR VALUES IN (2001) ; CREATE TABLE stock_2002 PARTITION of stock FOR VALUES IN (2002) ; CREATE TABLE stock_2003 PARTITION of stock FOR VALUES IN (2003) ; CREATE TABLE stock_2004 PARTITION of stock FOR VALUES IN (2004) ; CREATE TABLE stock_2005 PARTITION of stock FOR VALUES IN (2005) ; ``` > - Insérer tous les enregistrements venant de l’ancienne table `stock`. ```sql INSERT INTO stock SELECT * FROM stock_old; ``` > - Passer les statistiques pour être sûr des plans à partir de maintenant (nous avons modifié beaucoup d’objets). ```sql ANALYZE; ``` > - Vérifier la présence d’enregistrements dans `stock_2001` (syntaxe `SELECT ONLY`). > - Vérifier qu’il n’y en a aucun dans `stock`. ```sql SELECT count(*) FROM stock_2001; SELECT count(*) FROM ONLY stock; ``` > - Vérifier qu’une requête sur `stock` sur 2002 ne parcourt qu’une seule partition. ```sql EXPLAIN ANALYZE SELECT * FROM stock WHERE annee=2002; ``` ```text QUERY PLAN ------------------------------------------------------------------------------ Append (cost=0.00..417.36 rows=18192 width=16) (...) -> Seq Scan on stock_2002 (cost=0.00..326.40 rows=18192 width=16) (...) Filter: (annee = 2002) Planning Time: 0.912 ms Execution Time: 21.518 ms ``` > - Remettre en place les index présents dans la table `stock` originale. > - Il se peut que d’autres index ne servent à rien (ils ne seront dans ce cas pas présents dans la correction). ```sql CREATE UNIQUE INDEX ON stock (vin_id,contenant_id,annee); ``` Les autres index ne servent à rien sur les partitions : `idx_stock_annee` est évidemment inutile, mais `idx_stock_vin_annee` aussi, puisqu’il est inclus dans l’index unique que nous venons de créer. > - Quel est le plan pour la récupération du stock des bouteilles du `vin_id` 1725, année 2003 ? ```sql EXPLAIN ANALYZE SELECT * FROM stock WHERE vin_id=1725 AND annee=2003 ; ``` ```text Append (cost=0.29..4.36 rows=3 width=16) (...) -> Index Scan using stock_2003_vin_id_contenant_id_annee_idx on stock_2003 (...) Index Cond: ((vin_id = 1725) AND (annee = 2003)) Planning Time: 1.634 ms Execution Time: 0.166 ms ``` > - Essayer de changer l’année de ce même enregistrement de `stock` (la même que la précédente). Pourquoi cela échoue-t-il ? ```sql UPDATE stock SET annee=2004 WHERE annee=2003 and vin_id=1725 ; ``` ```text ERROR: duplicate key value violates unique constraint "stock_2004_vin_id_contenant_id_annee_idx" DETAIL: Key (vin_id, contenant_id, annee)=(1725, 1, 2004) already exists. ``` C’est une violation de contrainte unique, qui est une erreur normale : nous avons déjà un enregistrement de stock pour ce vin pour l’année 2004. > - Supprimer les enregistrements de 2004 pour `vin_id` = 1725. > - Retenter la mise à jour. ```sql DELETE FROM stock WHERE annee=2004 and vin_id=1725; UPDATE stock SET annee=2004 WHERE annee=2003 and vin_id=1725 ; ``` > - Pour vider complètement le stock de 2001, supprimer la partition `stock_2001`. ```sql DROP TABLE stock_2001 ; ``` > - Tenter d’ajouter au stock une centaine de bouteilles de 2006. > - Pourquoi cela échoue-t-il ? ```sql INSERT INTO stock (vin_id, contenant_id, annee, nombre) VALUES (1, 1, 2006, 100) ; ``` ```text ERROR: no partition of relation "stock" found for row DETAIL: Partition key of the failing row contains (annee) = (2006). ``` Il n’existe pas de partition définie pour l’année 2006, cela échoue donc. > - Créer une partition par défaut pour recevoir de tels enregistrements. > - Retenter l’ajout. ```sql CREATE TABLE stock_default PARTITION OF stock DEFAULT ; INSERT INTO stock (vin_id, contenant_id, annee, nombre) VALUES (1, 1, 2006, 100) ; ``` > - Tenter de créer la partition pour l’année 2006. Pourquoi cela échoue-t-il ? ```sql CREATE TABLE stock_2006 PARTITION of stock FOR VALUES IN (2006) ; ``` ```text ERROR: updated partition constraint for default partition "stock_default" would be violated by some row ``` Cela échoue car des enregistrements présents dans la partition par défaut répondent à cette nouvelle contrainte de partitionnement. > - Pour créer la partition sur 2006, au sein d’une seule transaction : > - détacher la partition par défaut ; > - y déplacer les enregistrements mentionnés ; > - ré-attacher la partition par défaut. ```sql BEGIN ; ALTER TABLE stock DETACH PARTITION stock_default; CREATE TABLE stock_2006 PARTITION of stock FOR VALUES IN (2006) ; INSERT INTO stock SELECT * FROM stock_default WHERE annee = 2006 ; DELETE FROM stock_default WHERE annee = 2006 ; ALTER TABLE stock ATTACH PARTITION stock_default DEFAULT ; COMMIT ; ``` ## 0.2.2 Partitionner pendant l’activité **But** : Mettre en place le partitionnement déclaratif sur une base en cours d’activité ### 0.2.2.1 Préparation > Créer une base **pgbench** vierge, de taille 10 ou plus. ```bash $ createdb pgbench $ /usr/pgsql-14/bin/pgbench -i -s 10 pgbench ``` > Dans une fenêtre en arrière-plan, laisser tourner un processus `pgbench` avec une activité la plus soutenue possible. Il ne doit pas tomber en erreur pendant que les tables vont être partitionnées ! Certaines opérations vont poser des verrous, le but va être de les réduire au maximum. ```bash $ /usr/pgsql-14/bin/pgbench -n -T3600 -c20 -j2 --debug pgbench ``` L’activité est à ajuster en fonction de la puissance de la machine. Laisser l’affichage défiler dans une fenêtre pour bien voir les blocages. > Pour éviter un « empilement des verrous » et ne pas bloquer trop longtemps les opérations, faire en sorte que la transaction échoue si l’obtention d’un verrou dure plus de 10 s. Un verrou en attente peut bloquer les opérations d’autres transactions venant après. On peut annuler l’opération à partir d’un certain seuil pour éviter ce phénomène : ```sql pgbench=# SET lock_timeout TO '10s' ; ``` Cela ne concerne cependant pas les opérations une fois que les verrous sont acquis. On peut garantir qu’un ordre donné ne durera pas plus d’une certaine durée : ```sql SET statement_timeout TO '10s' ; ``` En fonction de la rapidité de la machine et des données à déplacer, cette interruption peut être tolérable ou non. ### 0.2.2.2 Partitionnement par hash > Pour partitionner la table `pgbench_accounts` par *hash* sur la colonne `aid` sans que le traitement pgbench tombe en erreur, préparer un script avec, dans une transaction : > > - la création d’une table partitionnée par hash en 3 partitions au moins ; > - le transfert des données depuis `pgbench_accounts` ; > - la substitution de la table partitionnée à la table originale. > > Tester et exécuter. Le champ `aid` n’a pas de signification, un partitionnement par *hash* est adéquat. Le script peut être le suivant : ```sql \timing on \set ON_ERROR_STOP 1 SET lock_timeout TO '10s' ; SET statement_timeout TO '10s' ; BEGIN ; -- Nouvelle table partitionnée CREATE TABLE pgbench_accounts_part (LIKE pgbench_accounts INCLUDING ALL) PARTITION BY HASH (aid) ; CREATE TABLE pgbench_accounts_1 PARTITION OF pgbench_accounts_part FOR VALUES WITH (MODULUS 3, REMAINDER 0 ) ; CREATE TABLE pgbench_accounts_2 PARTITION OF pgbench_accounts_part FOR VALUES WITH (MODULUS 3, REMAINDER 1 ) ; CREATE TABLE pgbench_accounts_3 PARTITION OF pgbench_accounts_part FOR VALUES WITH (MODULUS 3, REMAINDER 2 ) ; -- Transfert des données -- Bloquer les accès à la table le temps du transfert -- (sinon risque de perte de données !) LOCK TABLE pgbench_accounts ; -- Copie des données INSERT INTO pgbench_accounts_part SELECT * FROM pgbench_accounts ; -- Substitution par renommage ALTER TABLE pgbench_accounts RENAME TO pgbench_accounts_old ; ALTER TABLE pgbench_accounts_part RENAME TO pgbench_accounts ; -- Contrôle \d+ -- On ne validera qu'après contrôle -- (pendant ce temps les sessions concurrentes restent bloquées !) COMMIT ; ``` À la moindre erreur, la transaction tombe en erreur. Il faudra demander manuellement `ROLLBACK`. Si la durée fixée par `statement_timeout` est dépassée, on aura cette erreur : ```text ERROR: canceling statement due to statement timeout Time: 10115.506 ms (00:10.116) ``` Surtout, le traitement pgbench reprend en arrière-plan. On peut alors relancer le script corrigé plus tard. Si tout se passe bien, un `\d+` renvoie ceci : ```text Liste des relations Schéma | Nom | Type | Propriétaire | Taille | … --------+----------------------+--------------------+--------------+---------+-- public | pgbench_accounts | table partitionnée | postgres | 0 bytes | public | pgbench_accounts_1 | table | postgres | 43 MB | public | pgbench_accounts_2 | table | postgres | 43 MB | public | pgbench_accounts_3 | table | postgres | 43 MB | public | pgbench_accounts_old | table | postgres | 130 MB | public | pgbench_branches | table | postgres | 136 kB | public | pgbench_history | table | postgres | 5168 kB | public | pgbench_tellers | table | postgres | 216 kB | ``` On peut vérifier rapidement que les valeurs de `aid` sont bien réparties entre les 3 partitions : ```sql SELECT aid FROM pgbench_accounts_1 LIMIT 3 ; ``` ```text aid ----- 2 6 8 ``` ```sql SELECT aid FROM pgbench_accounts_2 LIMIT 3 ; ``` ```text aid ----- 3 7 10 ``` ```sql SELECT aid FROM pgbench_accounts_3 LIMIT 3 ; ``` ```text aid ----- 1 9 11 ``` Après la validation du script, on voit apparaître les lignes dans les nouvelles partitions : ```sql SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE relname LIKE 'pgbench_accounts%' ; ``` ```text relname | n_live_tup ----------------------+------------ pgbench_accounts_old | 1000002 pgbench_accounts_1 | 333263 pgbench_accounts_2 | 333497 pgbench_accounts_3 | 333240 ``` > Supprimer l’ancienne table `pgbench_accounts_old`. ```sql DROP TABLE pgbench_accounts_old ; ``` ### 0.2.2.3 Partitionnement par valeur `pgbench` doit continuer ses opérations en tâche de fond. > La table `pgbench_history` se remplit avec le temps. Elle doit être partitionnée par date (champ `mtime`). Pour le TP, on fera 2 partitions d’une minute, et une partition par défaut. La table actuelle doit devenir une partition de la nouvelle table partitionnée. > > - Écrire un script qui, dans une seule transaction, fait tout cela et substitue la table partitionnée à la table d’origine. > > NB : Pour éviter de coder des dates en dur, il est possible, avec `psql`, d’utiliser une variable : > > ```sql > SELECT ( now()+ interval '60s') AS date_frontiere \gset > SELECT :'date_frontiere'::timestamptz ; > ``` La « date frontière » doit être dans le futur (proche). En effet, `pgbench` va modifier les tables en permanence, on ne sait pas exactement à quel moment la transition aura lieu (et de toute façon on ne maîtrise pas les valeurs de `mtime`) : il continuera donc à écrire dans l’ancienne table, devenue partition, pendant encore quelques secondes. Cette date est arbitrairement à 1 minute dans le futur, pour dérouler le script manuellement : ```sql SELECT ( now()+ interval '60s') AS date_frontiere \gset ``` Et on peut réutiliser cette variable ainsi ; ```sql SELECT :'date_frontiere'::timestamptz ; ``` Le script peut être celui-ci : ```sql \timing on \set ON_ERROR_STOP 1 SET lock_timeout TO '10s' ; SET statement_timeout TO '10s' ; SELECT ( now()+ interval '60s') AS date_frontiere \gset SELECT :'date_frontiere'::timestamptz ; BEGIN ; -- Nouvelle table partitionnée CREATE TABLE pgbench_history_part (LIKE pgbench_history INCLUDING ALL) PARTITION BY RANGE (mtime) ; -- Des partitions pour les prochaines minutes CREATE TABLE pgbench_history_1 PARTITION OF pgbench_history_part FOR VALUES FROM (:'date_frontiere'::timestamptz ) TO (:'date_frontiere'::timestamptz + interval '1min' ) ; CREATE TABLE pgbench_history_2 PARTITION OF pgbench_history_part FOR VALUES FROM (:'date_frontiere'::timestamptz + interval '1min' ) TO (:'date_frontiere'::timestamptz + interval '2min' ) ; -- Au cas où le service perdure au-delà des partitions prévues, -- on débordera dans cette table CREATE TABLE pgbench_history_default PARTITION OF pgbench_history_part DEFAULT ; -- Jusqu'ici pgbench continue de tourner en arrière plan -- La table devient une simple partition -- Ce renommage pose un verrou, les sessions pgbench sont bloquées ALTER TABLE pgbench_history RENAME TO pgbench_history_orig ; ALTER TABLE pgbench_history_part ATTACH PARTITION pgbench_history_orig FOR VALUES FROM (MINVALUE) TO (:'date_frontiere'::timestamptz) ; -- Contrôle \dP -- Substitution de la table partitionnée à celle d'origine. ALTER TABLE pgbench_history_part RENAME TO pgbench_history ; -- Contrôle \d+ pgbench_history COMMIT ; ``` > Exécuter le script, attendre que les données s’insèrent dans les nouvelles partitions. Pour surveiller le contenu des tables jusqu’à la transition : ```sql SELECT relname, n_live_tup, now() FROM pg_stat_user_tables WHERE relname LIKE 'pgbench_history%' ; \watch 3 ``` Un `\d+` doit renvoyer ceci : ```text Liste des relations Schéma | Nom | Type | Propriétaire | Taille | … --------+-------------------------+--------------------+--------------+---------+-- public | pgbench_accounts | table partitionnée | postgres | 0 bytes | public | pgbench_accounts_1 | table | postgres | 44 MB | public | pgbench_accounts_2 | table | postgres | 44 MB | public | pgbench_accounts_3 | table | postgres | 44 MB | public | pgbench_branches | table | postgres | 136 kB | public | pgbench_history | table partitionnée | postgres | 0 bytes | public | pgbench_history_1 | table | postgres | 672 kB | public | pgbench_history_2 | table | postgres | 0 bytes | public | pgbench_history_default | table | postgres | 0 bytes | public | pgbench_history_orig | table | postgres | 8736 kB | public | pgbench_tellers | table | postgres | 216 kB | ``` ### 0.2.2.4 Purge > - Continuer de laisser tourner `pgbench` en arrière-plan. > - Détacher et détruire la partition avec les données les plus anciennes. ```sql ALTER TABLE pgbench_history DETACH PARTITION pgbench_history_orig ; -- On pourrait faire le DROP directement DROP TABLE pgbench_history_orig ; ``` ### 0.2.2.5 Contraintes entre tables partitionnées > - Ajouter une clé étrangère entre `pgbench_accounts` et `pgbench_history`. Voir les contraintes créées. NB : les clés étrangères entre tables partitionnées ne sont pas disponibles avant PostgreSQL 12. ```sql SET lock_timeout TO '3s' ; SET statement_timeout TO '10s' ; CREATE INDEX ON pgbench_history (aid) ; ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ``` On voit que chaque partition porte un index comme la table mère. La contrainte est portée par chaque partition. ```text pgbench=# \d+ pgbench_history Table partitionnée « public.pgbench_history » … Clé de partition : RANGE (mtime) Index : "pgbench_history_aid_idx" btree (aid) Contraintes de clés étrangères : "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid) Partitions: pgbench_history_1 FOR VALUES FROM ('2020-02-14 17:41:08.298445') TO ('2020-02-14 17:42:08.298445'), pgbench_history_2 FOR VALUES FROM ('2020-02-14 17:42:08.298445') TO ('2020-02-14 17:43:08.298445'), pgbench_history_default DEFAULT ``` ```text pgbench=# \d+ pgbench_history_1 Table « public.pgbench_history_1 » … Partition de : pgbench_history FOR VALUES FROM ('2020-02-14 17:41:08.298445') TO ('2020-02-14 17:42:08.298445') Contrainte de partition : ((mtime IS NOT NULL) AND(mtime >= '2020-02-14 17:41:08.298445'::timestamp without time zone) AND (mtime < '2020-02-14 17:42:08.298445'::timestamp without time zone)) Index : "pgbench_history_1_aid_idx" btree (aid) Contraintes de clés étrangères : TABLE "pgbench_history" CONSTRAINT "pgbench_history_aid_fkey" FOREIGN KEY (aid) REFERENCES pgbench_accounts(aid) Méthode d'accès : heap ``` > Si vous n’avez pas déjà eu un problème à cause du `statement_timeout`, dropper la contrainte et recommencer avec une valeur plus basse. Comment contourner ? Le `statement_timeout` peut être un problème : ```sql SET pgbench=# ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ERROR: canceling statement due to statement timeout ``` On peut créer les contraintes séparément sur les tables. Cela permet de ne poser un verrou sur la partition active (sans doute `pgbench_history_default`) que pendant le strict minimum de temps (les autres partitions de `pgbench_history` ne sont pas utilisées). ```sql SET statement_timeout to '1s' ; ALTER TABLE pgbench_history_1 ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ALTER TABLE pgbench_history_2 ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ALTER TABLE pgbench_history_default ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ``` La contrainte au niveau global sera alors posée presque instantanément : ```sql ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_aid_fkey FOREIGN KEY (aid) REFERENCES pgbench_accounts ; ``` ### 0.2.2.6 Index global > On veut créer un index sur `pgbench_history (aid)`. > > Pour ne pas gêner les écritures, il faudra le faire de manière concurrente. Créer l’index de manière concurrente sur chaque partition, puis sur la table partitionnée. Construire un index de manière concurrente (clause `CONCURRENTLY`) permet de ne pas bloquer la table en écriture pendant la création de l’index, qui peut être très longue. Mais il n’est pas possible de le faire sur la table partitionnée : ```sql CREATE INDEX CONCURRENTLY ON pgbench_history (aid) ; ``` ```text ERROR: cannot create index on partitioned table "pgbench_history" concurrently ``` Mais on peut créer l’index sur chaque partition séparément : ```sql CREATE INDEX CONCURRENTLY ON pgbench_history_1 (aid) ; CREATE INDEX CONCURRENTLY ON pgbench_history_2 (aid) ; CREATE INDEX CONCURRENTLY ON pgbench_history_default (aid) ; ``` S’il y a beaucoup de partitions, on peut générer dynamiquement ces ordres : ```sql SELECT 'CREATE INDEX CONCURRENTLY ON ' || c.oid::regclass::text || ' (aid) ; ' FROM pg_class c WHERE relname like 'pgbench_history%' AND relispartition \gexec ``` Comme lors de toute création concurrente, il faut vérifier que les index sont bien valides : la requête suivante ne doit rien retourner. ```sql SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid ; ``` Enfin on crée l’index au niveau de la table partitionnée : il réutilise les index existants et sera donc créé presque instantanément : ```sql CREATE INDEX ON pgbench_history(aid) ; ``` ```text pgbench=# \d+ pgbench_history .. Partition key: RANGE (mtime) Indexes: "pgbench_history_aid_idx" btree (aid) … ```