0 Travaux pratiques

0.1 Travaux pratiques

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/v1_solutions.

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 :

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) :

-- 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 :

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).

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.

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.

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.

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).

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.

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.

EXPLAIN ANALYZE SELECT * FROM stock WHERE annee=2002;

                               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).

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 ?

EXPLAIN ANALYZE SELECT * FROM stock WHERE vin_id=1725 AND annee=2003 ;

 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 ?

UPDATE stock SET annee=2004 WHERE annee=2003 and vin_id=1725 ;

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.

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.

DROP TABLE stock_2001 ;

• Tenter d’ajouter au stock une centaine de bouteilles de 2006.
• Pourquoi cela échoue-t-il ?

INSERT INTO stock (vin_id, contenant_id, annee, nombre) VALUES (1, 1, 2006, 100) ;

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.

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 ?

CREATE TABLE stock_2006 PARTITION of stock FOR VALUES IN (2006) ;

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.

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.

$ 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.

$ /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 :

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 :

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 :

\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 :

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 :

                            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 :

SELECT aid FROM pgbench_accounts_1 LIMIT 3 ;

 aid
-----
   2
   6
   8

SELECT aid FROM pgbench_accounts_2 LIMIT 3 ;

 aid
-----
   3
   7
  10

SELECT aid FROM pgbench_accounts_3 LIMIT 3 ;

 aid
-----
   1
   9
  11

Après la validation du script, on voit apparaître les lignes dans les nouvelles partitions :

SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname LIKE 'pgbench_accounts%' ;

       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.

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 :

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 :

SELECT ( now()+ interval '60s') AS date_frontiere \gset

Et on peut réutiliser cette variable ainsi ;

SELECT :'date_frontiere'::timestamptz ;

Le script peut être celui-ci :

\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 :

SELECT relname, n_live_tup, now()
FROM pg_stat_user_tables
WHERE relname LIKE 'pgbench_history%' ;

\watch 3

Un \d+ doit renvoyer ceci :

                                   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.

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.

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.

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

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 :

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).

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 :

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 :

CREATE INDEX CONCURRENTLY ON pgbench_history (aid) ;

ERROR:  cannot create index on partitioned table "pgbench_history" concurrently

Mais on peut créer l’index sur chaque partition séparément :

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 :

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.

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 :

CREATE INDEX ON pgbench_history(aid) ;

pgbench=# \d+ pgbench_history
..
Partition key: RANGE (mtime)
Indexes:
    "pgbench_history_aid_idx" btree (aid)
…