Zaciekawiła mnie obecność typów json i jsonb w postgresie. Postanowiłam więc sprawdzić, jak się ich używa.

Dziś spróbuję:

• zdefiniować tabelę z kolumną json lub jsonb
• stworzyć dane tego typu (ręcznie, albo wygeneruję losowo)
• zastosować funkcje do operowania na danych json
• sprawdzę, czy i jak mogę utworzyć indeks w oparciu o taką kolumnę

Kontekst

Dlaczego w ogóle postgres ma typy json-owe? Czy to nie jest tak, że świat danych podzielił się między ciężkie, korporacyjne RDBMS-y ze skostniałą strukturą tabel z jednej strony i lekkie, startupowe, prawie-bez-schematu-eventually-consistent bazy no-sql?

Szczerze mówiąc, taki jest mniej więcej model mentalny systemów bazodanowych.

Przypadki użycia

Znalazłam kilka, choć mam wrażenie, że postgresowe typy jsonowe nie są jakoś szczególnie popularne w internetach.

Tak więc kolumna typu json czy jsonb może służyć, na przykład:

• do przechowywania danych wyświetlanych w interfejsie graficznym aplikacji webowych
• do przechowywania danych związanych ze śledzeniem różnych zdarzeń, których zawartość może być różna (dla różnych typów zdarzeń) - taką hipotetyczną sytuację przedstawiam w przykładach poniżej
• do przechowywania danych związanych ze stanem gry pojedynczego gracza, który to stan zmienia się w czasie (i dynamicznie zmienia się schemat danych)
• w narzędziach do integracji danych z różnych systemów charakteryzujących się odrębnymi schematami
• do synchronizacji danych między różnymi systemami

Źródła: When to use.., SO - what are the proper…, Using JSONB

Od kiedy?

Typ json jest już w postgresie prawie od dziesięciu lat; jsonb już od siedmiu, a jsonpath i obsługa standardu SQL/JSON (główne funkcje operujące na i tworzące obiekty json) od wersji 12 czyli od 2019 roku.

Standard SQL:2016:

• Wikipedia: SQL:2016
• What’s new in SQL:2016

A może mongo?

O wyborze między mongodb a postgresem przeczytałam w artykule “Postgres vs mongo for storing json data”

• jeśli przechowywane dane są statyczne, częściej się je zapisuje niż odczytuje, nie ma (zbyt wielu) konieczności modyfikacji danych - użyj postgresa
• jeśli dane będą używane tak, że modyfikacja rekordów będzie częsta - użyj mongo, bo ma do tego odpowiednie narzędzia

Przy dokonywaniu wyboru należy oczywiście rozważyć możliwość skalowania, dostępności specjalitów itd.

Hm, artykuł był pisany w 2016 r., może wtedy nie było jeszcze tylu funkcji do tworzenia danych json, ich przetwarzania, wyszukiwania jsonpath czy filtrowania)

Typy: json, jsonb i jsonpath

Poniższa tabelka przedstawia w kilku słowach, czym są te trzy typy

Przykład

Załóżmy, że w bazie danych przechowuję zdarzenia (w tabeli events) pochodzące z różnych źródeł: z aplikacji działającej na serwerze, z twittera, z serwisu googla.

Każde z nich (są one przechowywane w tabeli sources) generuje zdarzenia w innym formacie, zachowując jedynie wartość userid taką samą w każdym źródle, co pozwoli na skorelowanie zdarzeń pochodzących z różnych źródeł:

Przykładowe dane przygotowałam w sposób, który sugeruje, że istnieje trzech użytkowników (o identyfikatorach będących liczbami), którzy wykonują pewne akcje w interfejsie aplikacji (źródło server), i mniej więcej w tym samym czasie rozpoczynają odsłuchiwanie muzyki (źródło nas) oraz wyszukiwanie haseł w internecie (źródło google bot).

Wyszukiwanie na podstawie identyfikatora użytkownika

Jak wypisać zdarzenia, które dotyczą użytkownika o identyfikatorze 12? Niezależnie od źródła zdarzeń, każde zdarzenie zawiera klucz userrid w obiekcie JSON na najwyższym poziomie.

Do wyciągnięcia danych użytkownika 12 w klauzuli WHERE używam operatora ->> (zwracającego tekst). Operator -> zwraca jsonb, a ja nie bardzo wiem jak mogę porównać jsonb z innym typem danych.

Wyszukanie i sortowanie po czasie

Znacznik czasu, czyli “timestamp” zdarzenia jest częścią danych każdego wygenerowanego zdarzenia, jednak różne źródła umieszczajągo w różnych miejscach swojego schematu:

• źródła 2 (nas) i 3 (google bot) umieszczają timestamp w kluczu “ts” na najwyższym poziomie, obok “user_id”
• źródło 1 (server) umieszcza timestamp również pod kluczem “ts”, jednak poziom głębiej, wewnątrz obiektu “action”

Do wyszukania znacznika czasu użyję operatora #>>, pozwalającego na podanie “ścieżki” do obiektu w postaci tablicy elementów, które są kolejnymmi kluczami bądź indeksami prowadzącymi do szukanej wartości. I tak:

• dla źródła 2 i 3 wystarczy ścieżka {'ts'}
• a dla źródła 1 będzie to {'action', 'ts'}

Aby postgres “wiedział”, że napis {'action', 'ts'} oznacza tablicę tekstów, muszę zrzutować wartość otrzymaną w instrukcji CASE na text[]. Aby zaś móc posortować znaczniki, wyciągnięty tekst rzutuję na timestamptz. Dlatego właśnie używam operatora #>>, który zwraca tekst, a nie operatora #> który daje jsonb (bo z jakiegoś powodu postgres nie chciał rzutować jsonb na timestamptz).

Wyszukiwanie z użyciem jsonpath

Innym sposobem wskazania ścieżki jest użycie specjalnie w tym celu stworzonego typu danych jakim jest jsonpath. Zadanie wyszukania znaczników czasu zdarzenia można zrealizować przez podanie odpowiedniej ścieżki do funkcji jsonb_path_query(jsonb, jspnpath) która zwraca zbiór wartości jsonb (w szczególności jednoelementowy).

Tutaj znów trzeba rozróżnić typ źródła i wybrać odpowiedni jsonpath:

• dla źródła 2 i 3 $.ts
• a dla źródła 1 będzie to $.action.ts

Optymalizacja

Oczywiście używanie przy każdym zapytaniu instrukcji CASE jest dość karkołomne, ponieważ w przypadku dodania nowego źródła zdarzeń może się okazać, że znaczniki czasu są umieszczone w innym miejscu schematu json. Aktualizacja wszystkich zapytań nie jest tym, czym chcemy się zajmować. Dlatego znacznie wygodniej jest przechowywać ścieżki jsonpath jako część danych tabeli sources, na przykład:

Generowanie danych

Spróbuję powiększyć trochę tabelę events: wygeneruję sztuczne dane dla źródła 1 (server):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

insert into events(body, src_id) 
  select  json_build_object(
    'action', 
    json_build_object('ts', ts,'click_pos', click_pos), 
    'userid', 
    b.userid) body,
    1 src_id
  from (
    select 
      s.t ts,
      array [ round(random() * 1600)::int4, 
            round(random() * 1200)::int4 ] click_pos,
      round(random() * 100)::int userid
    from (
      select generate_series(
        '2012-02-12':: timestamptz, 
        '2013-01-23'::timestamptz,  
        ((random() * 20.0)::text ||' minutes')::interval) as t) s
    )b;

Ponad 60 tysięcy wierszy wstawianych do tabeli zajęło prawie 3 sekundy, ale to stało się już po utworzeniu indeksu na kolumnie body, więc aktualizacja indeksu pewnie zajęła trochę czasu.

1
2

INSERT 0 62985
Query returned successfully in 2 secs 740 msec.

Dla ciekawości, po usunięciu indeksu:

1
2
3

delete from events where src_id=1;
select * from events;
drop index ginbody;

wstawienie ponaddwukrotnej liczby wierszy trwa niecałe dwie sekundy:

1
2
3

INSERT 0 113379

Query returned successfully in 1 secs 876 msec.

Różna liczba wygenerowanych wierszy wynika oczywiście z faktu, że generuję timestampy między dwoma datami z losowym krokiem - jeśli wartości kroku są mniejsze, w rezultacie powstaje więcej wierszy.

Wygenerowane dane

Tak wyglądają wygenerowane dane. Ich kształt jest taki sam jak dla przykładowych danych, które wymyśliłam dla źródła 1 na początku przykładu:

Indeksy

Postgres pozwala na utworzenie kilku typów indeksu na tabelach typu jsonb.

GIN index

GINGeneralized inverted index stosuje się do indeksownania obiektów o złożonej strukturze. Został zaprojektowany tak, aby obłsugiwać zapytania o wartości znajdujące się wewnątrz złożonego obiektu. Ten indeks ma dwa tryby działania:

• jsonb_ops (tryb domyślny) indeksuje każdy klucz i wartość w złożonym obiekcie i pozwala na użycie następujących operatorów: ?, ?|, ?&, @>, @@, @?
• jsonb_pathops - indeksuje tylko wartości pewnego określonego elementu obiektu i pozwala na użycie operatorów: @>, @@, @?

Składniowo utworzenie indeksu (domyślnego) wygląda tak:

1

CREATE INDEX userginidx ON events USING gin (body);

Wyszukiwanie bez indeksu

Jaki jest przewidywany przez planner czas wykonania prostego zapytania o dane użytkownika o id = 12?

1
2

select * from events
where body @> '{"userid": 12}'

Oto analiza czasu wykonania powyższego zapytania w sytuacji, gdy nie ma założonego indeksu. “Sequential scan” w analizie pokazanej przez planner oznacza konieczność przejrzenia wszystkich wierszy. Wyszukanie wierszy dla określonego użytkownika trwa 85.9ms:

Wyszukanie po utworzeniu indeksu typu GIN

Po utworzeniu indeksu (“bitmap heap scan” oznacza użycie indeksu) czas spada do 3.1ms:

Oznacza to, że… jedna chwilka:

1
2
3
4
5
6
7
8

bc 1.07.1
Copyright 1991-1994, 1997, 1998, 2000, 2004, 2006, 2008, 2012-2017 Free Software Foundation, Inc.
This is free software with ABSOLUTELY NO WARRANTY.
For details type `warranty'. 
85.9-3.1
82.8
last/85.9
.96391152502910360884

Indeks poprawił czas wykonania o 96% !.

Taka różnica może mieć znaczenie, prawda?

Źródła: Using JSONB, JSONB type perf

Zakończenie

Na dziś to wszystko. O pozostałych typach indeksów zainteresowany czytelnik poczyta już sobie sam 😂

Za chwilę przyjedzie kurier z jedzeniem, więc zamykam laptopa.

Do następnego razu!

Ten wpis jest częścią serii sql...

• 2021-04-11 - Postgres - daty
• 2021-16-10 - Postgres - krótka analiza danych dot. zajęć dodatkowych
• 2021-04-10 - Postgres - JSON
• 2021-04-10 - Postgres - ćwiczenia
• 2021-30-09 - Postgres - instalacja