Upsert : PL/pgSQL vs WITH QUERY vs INSERT ... CONFLICT

Différentes méthodes

      Dans cette page, nous allons réaliser un UPSERT (insert / update) et comparer les performances


      Cette page écarte volontairement tous les aspects concernant la gestion de la concurrence entre les méthodes proposées pour ne s'intéresser qu'à la performance. Dans beaucoup de cas (traitement décisionnel, reprise de données, certains batches), la concurrence n'est de toute façon pas un problème.
      L'environnement est constitué d'une table GEANTS et de tables GEANTS_RECRUES. Il s'agit d'intégrer les informations des tables GEANTS_RECRUES dans la table GEANTS sachant que les tables GEANTS_RECRUES comprennnent de nouveaux géants mais aussi des géants déjà enregistrés. Dans ce dernier cas, il convient de mettre à jour la colonne DEVISE de la table GEANTS.
      Démonstration :

select version(); version -------------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 10.2 (Ubuntu 10.2-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 ligne) CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; NOTICE: l'extension " uuid-ossp " existe déjà, poursuite du traitement CREATE EXTENSION create table geants_seed( idgu uuid primary key, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i + 1 from serie where i < 10000000) insert into geants_seed(idgu, dtn, taille, devise, berserk) select uuid_generate_v4(), current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from serie; INSERT 0 1000000 create table geants_recrues_1( idgu uuid, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE create table geants_recrues_2( idgu uuid, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE create table geants_recrues_3( idgu uuid, dtn timestamp, taille smallint, devise varchar(128), berserk boolean); CREATE TABLE with serie(idgu) as (select case when random() <= 0.01 then idgu else uuid_generate_v4() end , current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from geants_seed TABLESAMPLE SYSTEM(20)) insert into geants_recrues_1 select * from serie; INSERT 0 1998776 with serie(idgu) as (select case when random() <= 0.50 then idgu else uuid_generate_v4() end , current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from geants_seed TABLESAMPLE SYSTEM(20)) insert into geants_recrues_2 select * from serie; INSERT 0 1994696 with serie(idgu) as (select case when random() <= 0.99 then idgu else uuid_generate_v4() end , current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval, 200 + (trunc(random() * 200 + 1)), upper(md5(random()::text)), case when random() < 0.001 then true else false end from geants_seed TABLESAMPLE SYSTEM(20)) insert into geants_recrues_3 select * from serie; INSERT 0 1991792 create table geants as select * from geants_seed; SELECT 10000000 alter table geants add primary key(idgu); ALTER TABLE

      OK pour l'environnement. Première méthode utilisée, une boucle dans un bloc PL/pgSQL anonyme en variant un peu la manière de détecter et gérer le conflit de clé dupliquée :

do $$ DECLARE ligne_geants_recrues geants_recrues_N%rowtype; begin for ligne_geants_recrues in select * FROM geants_recrues_N loop begin insert into geants values(ligne_geants_recrues.*); exception when unique_violation then update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ; when others then raise notice '% %', SQLERRM, SQLSTATE; end; end loop; end$$; DO -- avec geants_recrues_1 : Durée : 58258,427 ms (00:58,258) -- avec geants_recrues_2 : Durée : 93298,238 ms (01:33,298) -- avec geants_recrues_3 : Durée : 119986,162 ms (01:59,986) do $$ DECLARE ligne_geants_recrues geants_recrues_N%rowtype; begin for ligne_geants_recrues in select * FROM geants_recrues_N loop if exists (select 1 from geants where idgu = ligne_geants_recrues.idgu) then update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ; else insert into geants values(ligne_geants_recrues.*); end if; end loop; exception when others then raise notice '% %', SQLERRM, SQLSTATE; end$$; -- avec geants_recrues_1 : Durée : 64040,260 ms (01:04,040) -- avec geants_recrues_2 : Durée : 77842,516 ms (01:17,843) -- avec geants_recrues_3 : Durée : 83794,672 ms (01:23,795) do $$ DECLARE ligne_geants_recrues geants_recrues_N%rowtype; begin for ligne_geants_recrues in select * FROM geants_recrues_N loop update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ; if not found then insert into geants values(ligne_geants_recrues.*); end if; end loop; exception when others then raise notice '% %', SQLERRM, SQLSTATE; end$$; -- avec geants_recrues_1 : Durée : 68440,669 ms (01:08,441) -- avec geants_recrues_2 : Durée : 67484,379 ms (01:07,484) -- avec geants_recrues_3 : Durée : 69605,639 ms (01:09,606)
      Le bloc donnant les résultats les plus constants, entre 1min08s et 1min10s, est celui dans lequel nous tentons systématiquement la mise à jour avant de passer à l'insertion si aucune ligne n'a été affectée.
      Ces bouts de programmes entraînent l'exécution de multiples instructions mais sont par ailleurs relativement optimisés. Il serait possible de faire bien pire que cela en php, java ou autre. En matière de mauvaises idées, il est possible de faire du fetch ligne à ligne et de subir la latence réseau ou encore de faire du commit après chaque instruction DML et de subir la latence en écriture etc.
      Depuis PostgreSQL 9.1, il est possible d'écrire via une CTE (with query) donc nous allons tenter de réaliser l'upsert avec cette forme d'écriture hors standard SQL :
explain with do_update as ( update geants g set devise = (select gra.devise FROM geants_recrues_N gra WHERE g.idgu = gra.idgu) where exists (select 1 from geants_recrues_N grb where (g.idgu = grb.idgu)) RETURNING g.idgu ) INSERT INTO geants SELECT * FROM geants_recrues_N grc WHERE NOT EXISTS (SELECT 1 FROM do_update WHERE do_update.idgu = grc.idgu); QUERY PLAN --------------------------------------------------------------------------------------------------------- Insert on geants (cost=95340248432.12..95340288407.64 rows=999388 width=60) CTE do_update -> Update on geants g (cost=79398.46..95339528773.32 rows=1998776 width=313) -> Hash Semi Join (cost=79398.46..95339528773.32 rows=1998776 width=313) Hash Cond: (g.idgu = grb.idgu) -> Seq Scan on geants g (cost=0.00..213636.85 rows=9999985 width=33) -> Hash (cost=42701.76..42701.76 rows=1998776 width=22) -> Seq Scan on geants_recrues_1 grb (cost=0.00..42701.76 rows=1998776 width=22) SubPlan 1 -> Seq Scan on geants_recrues_1 gra (cost=0.00..47698.70 rows=1 width=33) Filter: (g.idgu = idgu) -> Merge Anti Join (cost=719658.80..759634.32 rows=999388 width=60) Merge Cond: (grc.idgu = do_update.idgu) -> Sort (cost=402184.52..407181.46 rows=1998776 width=60) Sort Key: grc.idgu -> Seq Scan on geants_recrues_1 grc (cost=0.00..42701.76 rows=1998776 width=60) -> Sort (cost=317474.28..322471.22 rows=1998776 width=16) Sort Key: do_update.idgu -> CTE Scan on do_update (cost=0.00..39975.52 rows=1998776 width=16) with do_update as ( update geants g set devise = (select gra.devise FROM geants_recrues_N gra WHERE g.idgu = gra.idgu) where exists (select 1 from geants_recrues_N grb where (g.idgu = grb.idgu)) RETURNING g.idgu ) INSERT INTO geants SELECT * FROM geants_recrues_N grc WHERE NOT EXISTS (SELECT 1 FROM do_update WHERE do_update.idgu = grc.idgu); -- avec geants_recrues_1 : Durée : 3327501,852 ms (55:27,502) -- avec geants_recrues_2 : + de 3 heures -- avec geants_recrues_3 : + de 3 heures explain with do_update as ( update geants g set devise = gra.devise from geants_recrues_N gra WHERE g.idgu = gra.idgu RETURNING g.idgu ) INSERT INTO geants SELECT * FROM geants_recrues_N grb WHERE NOT EXISTS (SELECT 1 FROM do_update WHERE do_update.idgu = grb.idgu); QUERY PLAN --------------------------------------------------------------------------------------------------------- Insert on geants (cost=1266260.02..1306235.54 rows=999388 width=60) CTE do_update -> Update on geants g (cost=87206.46..546601.22 rows=1998776 width=72) -> Hash Join (cost=87206.46..546601.22 rows=1998776 width=72) Hash Cond: (g.idgu = gra.idgu) -> Seq Scan on geants g (cost=0.00..213637.00 rows=10000000 width=33) -> Hash (cost=42701.76..42701.76 rows=1998776 width=55) -> Seq Scan on geants_recrues_N gra (cost=0.00..42701.76 rows=1998776 width=55) -> Merge Anti Join (cost=719658.80..759634.32 rows=999388 width=60) Merge Cond: (grb.idgu = do_update.idgu) -> Sort (cost=402184.52..407181.46 rows=1998776 width=60) Sort Key: grb.idgu -> Seq Scan on geants_recrues_N grb (cost=0.00..42701.76 rows=1998776 width=60) -> Sort (cost=317474.28..322471.22 rows=1998776 width=16) Sort Key: do_update.idgu -> CTE Scan on do_update (cost=0.00..39975.52 rows=1998776 width=16) with do_update as ( update geants g set devise = gra.devise from geants_recrues_N gra WHERE g.idgu = gra.idgu RETURNING g.idgu ) INSERT INTO geants SELECT * FROM geants_recrues_N grb WHERE NOT EXISTS (SELECT 1 FROM do_update WHERE do_update.idgu = grb.idgu); -- avec geants_recrues_1 : Durée : 18218,073 ms (00:18,218) -- avec geants_recrues_2 : Durée : 118903,792 ms (01:58,904) -- avec geants_recrues_3 : Durée : 183497,938 ms (03:03,498)

      Il est possible d'effectuer du DML avec les CTE mais elles n'ont pas pour vocation d'implémenter l'upsert. La performance est très variable selon la manière d'écrire la CTE et la nature des données mais, globalement, c'est correct avec la syntaxe PostgreSQL pour l'update et catastrophique avec la syntaxe standard SQL.
      C'est cette incertitude supposée au niveau des plans d'exécution qui conduit parfois à privilégier une boucle dans un programme externe plutôt que de réaliser le travail en SQL.
      Ce n'est cependant pas la meilleure approche pour obtenir un traitement vraiment optimal au niveau des performances lorsqu'il existe une syntaxe dédiée. C'est justement le cas pour l'UPSERT depuis PostgreSQL 9.5 :

explain insert into geants select * from geants_recrues_N on conflict(idgu) do update SET devise = EXCLUDED.devise; QUERY PLAN --------------------------------------------------------------------------------- Insert on geants (cost=0.00..42701.76 rows=1998776 width=60) Conflict Resolution: UPDATE Conflict Arbiter Indexes: geants_pkey -> Seq Scan on geants_recrues_N (cost=0.00..42701.76 rows=1998776 width=60) insert into geants select * from geants_recrues_N on conflict(idgu) do update SET devise = EXCLUDED.devise; -- avec geants_recrues_1 : Durée : 45664,534 ms (00:45,665) -- avec geants_recrues_2 : Durée : 51066,180 ms (00:51,066) -- avec geants_recrues_3 : Durée : 52877,171 ms (00:52,877)

      Entre 46s et 53s, la syntaxe dédiée est la plus compacte ET la plus efficace. Elle pourrait de plus être utilisée en environnement transactionnel car elle est sûre au niveau de la concurrence entre sessions.
      De maniére générale, la bonne méthode est souvent de ne pas réinventer la roue et d'utiliser la syntaxe SQL adéquate pour obtenir les meilleures performances.

Mise à jour : 21/02/2018