Rebuild online, reindex concurrently

Initialiser un cluster, gérer les accès, modifier les paramètres par défaut, interroger le catalogue avec psql ou pgAdmin, déplacer les données avec pgdump etc.
Répondre
Phil
Administrateur du site
Messages : 239
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Rebuild online, reindex concurrently

Message par Phil » jeu. 27 déc. 2018 11:04

Merci à un administrateur Oracle pour sa question :

"Avec Oracle Enterprise Edition, il est possible de faire un REBUILD ONLINE d'un index, c'est à dire ne pas bloquer l'activité en écriture pendant la reconstruction de l'index. Est-ce possible avec PostgreSQL ?"

Réponse :

REBUILD ONLINE est du vocabulaire Oracle Database. Avec PostgreSQL, la syntaxe serait REINDEX CONCURRENTLY.
Il n'y a toutefois pas jusqu'à la version 11 incluse de PostgreSQL de syntaxe directe "REINDEX CONCURRENTLY".
La fonctionnalité est cependant disponible. Il est en effet possible avec PostgreSQL de créer deux index avec la même méthode sur les mêmes colonnes d'une même table.
Cela ouvre la voie à :
1. création d'un nouvel index en mode concurrent
2. suppression de l'ancien index en mode concurrent
3. renommage du nouvel index en utilisant le nom de l'ancien index

Attention à l'étape 3 même si elle est rapide. Jusqu'à la version 11 incluse, le renommage n'est pas effectué "CONCURRENTLY". La version 12 en développement au 27/12/2018 lève cette restriction.

Démonstration :

Code : Tout sélectionner


select version();
                                                    version
---------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 (Debian 11.1-1.pgdg+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.2.0-9) 8.2.0, 64-bit
(1 ligne)

create table lancers ( dtl timestamp, idg integer, perf integer );
CREATE TABLE

insert into lancers(dtl, idg, perf)
with recursive serie(i) as 
(select 75000000
 UNION ALL 
 select i - 1 from serie where i > 1)
 select 
 timestamp '2018-12-18' - (i::int || ' minutes')::interval,
 trunc(random() * 100 + 1),
 trunc(random() * 100000 + 1)
 from serie;
INSERT 0 75000000

create index lancers_br1 on lancers using brin(dtl);
CREATE INDEX

create index lancers_fk1 on lancers(idg);
CREATE INDEX


-- session DML
DO 
$$ 
DECLARE 
nb_lignes_inserees integer;
BEGIN
while true loop
insert into lancers values(now(), trunc(random() * 100 + 1), trunc(random() * 100000 + 1));
GET DIAGNOSTICS nb_lignes_inserees = ROW_COUNT;
RAISE NOTICE '% % LIGNE INSEREE', now(), nb_lignes_inserees;
commit;
end loop;
END; $$ LANGUAGE plpgsql;

NOTICE:  2018-12-27 09:55:10.078819+00 1 LIGNE INSEREE
NOTICE:  2018-12-27 09:55:10.079394+00 1 LIGNE INSEREE
...


-- session DBA
create index concurrently lancers_i2 on lancers(idg);
CREATE INDEX
Durée : 153793,366 ms (02:33,793)

drop index concurrently lancers_fk1;
DROP INDEX
Temps : 271,877 ms

alter index lancers_i2 rename to lancers_fk1;
ALTER INDEX
Temps : 1,837 ms

-- pendant ce temps dans la session DML, les insertions sont toujours possibles...
...
NOTICE:  2018-12-27 09:57:20.472867+00 1 LIGNE INSEREE
NOTICE:  2018-12-27 09:57:20.48135+00 1 LIGNE INSEREE
Cdlt. Phil - pgphil.ovh

Phil
Administrateur du site
Messages : 239
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Rebuild online, reindex concurrently

Message par Phil » sam. 30 mars 2019 23:37

PostgreSQL 12, version actuellement en développement qui sortira avant la fin de l'année 2019, permettra de reconstruire les index sans bloquer le DML sur la table (insert, update, delete) via une syntaxe directe (mot clé CONCURRENTLY à ajouter à la syntaxe habituelle REINDEX).
L'exemple du message précédent peut donc être réécrit en :

Code : Tout sélectionner

select version();
                                                                                version                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12devel (Ubuntu 12~~devel~20190330.1759-1~694.git7ad6498.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
(1 ligne)

create table lancers ( dtl timestamp, idg integer, perf integer );
CREATE TABLE

insert into lancers(dtl, idg, perf)
with recursive serie(i) as 
(select 75000000
 UNION ALL 
 select i - 1 from serie where i > 1)
 select 
 timestamp '2018-12-18' - (i::int || ' minutes')::interval,
 trunc(random() * 100 + 1),
 trunc(random() * 100000 + 1)
 from serie;
INSERT 0 75000000

create index lancers_br1 on lancers using brin(dtl);
CREATE INDEX

create index lancers_fk1 on lancers(idg);
CREATE INDEX


-- session DML
DO 
$$ 
DECLARE 
nb_lignes_inserees integer;
BEGIN
while true loop
insert into lancers values(now(), trunc(random() * 100 + 1), trunc(random() * 100000 + 1));
GET DIAGNOSTICS nb_lignes_inserees = ROW_COUNT;
RAISE NOTICE '% % LIGNE INSEREE', now(), nb_lignes_inserees;
commit;
end loop;
END; $$ LANGUAGE plpgsql;

NOTICE:  2019-03-30 23:52:47.906748+01 1 LIGNE INSEREE
NOTICE:  2019-03-30 23:52:47.907302+01 1 LIGNE INSEREE
...

-- session DBA
reindex index concurrently lancers_fk1;

-- pendant ce temps dans la session DML, les insertions etaient toujours possibles...
...
NOTICE:  2019-03-30 23:52:49.757027+01 1 LIGNE INSEREE
NOTICE:  2019-03-30 23:52:49.757878+01 1 LIGNE INSEREE

Cdlt. Phil - pgphil.ovh

Répondre