Optymalizacja implementacji `Insert or Update` zapytań na bazie danych

Proszę Państwa, czasem jest potrzeba zrobić coś a’la Insert or Update. Zadanie jest dość trywialne, ale nie zawsze optymalne zarówno pod względem czasu implementacji jak i czasu wykonania – pierwsze co wpada do głowy, to implementacja czterech kroków:

  1. Wyciągnij wpisy z tabeli które spełniają wymagania
  2. Sprawdź czy wynik poprzedniego zapytania jest pusty
  3. Jeśli odpowiedź jest pozytywna – wykonaj UPDATE
  4. W przeciwnym razie wykonaj INSERT

W takich sytuacjach bardzo się przydaje zgłębienie wiedzy w narzędziach i zapytaniach wychodzących poza zakres ANSI SQL np. Transact-SQL (MS Sql Server) lub PL/pgSQL (Postgres). Oba te wyżej wspomniane języki mają cudowne narzędzie umożliwiające wykonanie całej roboty InsertOrUpdate za jednym zapytaniem – tzw UPSERT.

Nazwa pochodzi od połączenia dwóch słów – UPDATE oraz INSERT. Założenie takiej kwerendy jest proste – spróbuj wstawić nowy rekord do tabeli, i w przypadku pojawienia się błędu o duplikacje, zrób update.

W PLPGSQL (Postgres) co prawda nie jest to żaden UPSERT, tylko INSERT … ON CONFLICT UPDATE, ale zasada działania jest ta sama.

insert into testtable values (1, 'test-conflict')
on conflict (id) do update set data = EXCLUDED.data;

Od bazy danych, oprócz przechowywania samych danych, wymaga się szybkość działania – aby czas odpytania bazy o dane był jak najszybszy, a w niektórych przypadkach czas aktualizacji danych też musi spełniać wygórowane wymagania. Dlatego bazy danych (czy może prawidłowo powiedzieć, silniki baz danych?) porównują do siebie za pomocą różnego rodzaju testów wydajnościowych, więc proponuję przejść do takiego porównania tzw UPSERTa oraz funkcji która implementuje to w bardziej surowy sposób.

-- tymczasowa tabela na czas testów
create temporary table testtable
(
    id   integer primary key,
    data text
);

-- wypełniamy ją losowymi danymi w ilości 10000 sztuk
insert into testtable
select generate_series(1, 10000) as id, md5(random()::text) AS data;

W celu sprawdzenia query plan zapytania INSERT … ON CONFLICT UPDATE, użyjemy funkcję, która robi to samo, lecz wykorzystuj IF EXISTS oraz INSERT i UPDATE:

create or replace function func_InsertOrUpdate(newid integer, newdata text)
    returns void
as
$$
begin
    if exists(select id from testtable where id = newid) then
        update testtable set data = newdata where id = newid;
    else
        insert into testtable values (id, newdata);
    end if;
end
$$
    language plpgsql;

Uruchamiamy EXPLAIN ANALYZE i patrzymy na wyniki:

explain analyze
insert into testtable
values (864, 'test-conflict')
on conflict (id) do update set data = EXCLUDED.data;

explain analyze
select func_InsertOrUpdate(864, 'test-old-conflict');

query plan zapytania wykorzystującego INSERT … ON CONFLICT UPDATE

query plan wykonania funkcji

Ja widać z powyższych rzutów, wykonanie INSERT … ON CONFLICT UPDATE jest bardziej wydajne, co jest spowodowane wykonaniem dwóch zapytania SELECT’a oraz UPDATE z WHERE (btw, UPDATE jest zawsze cięższe dla bazy niż INSERT). Niżej można znaleźć bardziej szczegółowe logi z auto_explain.

-- query plan dla INSERT ... ON CONFLICT UPDATE
2019-10-06 20:31:56.415 UTC [140] LOG:  duration: 0.052 ms  plan:
      Query Text: insert into testtable
      values (864, 'test-conflict')
      on conflict (id) do update set data = EXCLUDED.data
      Insert on testtable  (cost=0.00..0.01 rows=1 width=36) (actual time=0.051..0.051 rows=0 loops=1)
        Conflict Resolution: UPDATE
        Conflict Arbiter Indexes: testtable_pkey
        Tuples Inserted: 0
        Conflicting Tuples: 1
        ->  Result  (cost=0.00..0.01 rows=1 width=36) (actual time=0.004..0.004 rows=1 loops=1)
-- query plan wykonania funkcji
2019-10-06 20:32:13.214 UTC [140] LOG:  duration: 0.025 ms  plan:
      Query Text: SELECT exists(select id from testtable where id = newid limit 1)
      Result  (cost=8.30..8.31 rows=1 width=1) (actual time=0.022..0.022 rows=1 loops=1)
        InitPlan 1 (returns $0)
          ->  Index Only Scan using testtable_pkey on testtable  (cost=0.29..8.30 rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=1)
                Index Cond: (id = $1)
                Heap Fetches: 1
  2019-10-06 20:32:13.214 UTC [140] CONTEXT:  SQL statement "SELECT exists(select id from testtable where id = newid limit 1)"
      PL/pgSQL function func_insertorupdate(integer,text) line 3 at IF
  2019-10-06 20:32:13.215 UTC [140] LOG:  duration: 0.539 ms  plan:
      Query Text: update testtable set data = newdata where id = newid
      Update on testtable  (cost=0.29..8.30 rows=1 width=42) (actual time=0.538..0.538 rows=0 loops=1)
        ->  Index Scan using testtable_pkey on testtable  (cost=0.29..8.30 rows=1 width=42) (actual time=0.007..0.020 rows=1 loops=1)
              Index Cond: (id = $1)
  2019-10-06 20:32:13.215 UTC [140] CONTEXT:  SQL statement "update testtable set data = newdata where id = newid"
      PL/pgSQL function func_insertorupdate(integer,text) line 4 at SQL statement
  2019-10-06 20:32:13.215 UTC [140] LOG:  duration: 1.368 ms  plan:
      Query Text: select func_InsertOrUpdate(864, 'test-old-conflict')
      Result  (cost=0.00..0.26 rows=1 width=4) (actual time=1.362..1.363 rows=1 loops=1)

W SQL Server można osiągnąć podobne wykorzystując MERGE – https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017. Na podlinkowanej stornie można znaleźć przykłady wykorzystania tego. Niestety nie mam pod ręką uruchomionej instancji SQL Server’a i nie mogę sprawdzić wydajność obu implementacji, ale zakładam, że wykorzystanie MERGE jest bardziej optymalne.

Lajkujemy, subskrybujemy, i nie zapominamy używać analizatorów naszych zapytań tak aby wydajność naszych baz danych wzleciała na niebywałą wysokość!