TL; DR: JSONB może znacząco uprościć rozwój schematu bazy danych bez uszczerbku dla wydajności zapytań.
Wprowadzenie
Podamy klasyczny przykład, prawdopodobnie jednego z najstarszych zastosowań w świecie relacyjnych baz danych: mamy encję i musimy zachować pewne właściwości (atrybuty) tej encji. Jednak nie wszystkie instancje mogą mieć tę samą zestaw właściwości, a ponadto w przyszłości można dodać dodatkowe właściwości.
Najprostszym sposobem rozwiązania tego problemu jest utworzenie kolumny w tabeli bazy danych dla każdego możliwego atrybutu i po prostu wypełnianie tych, które są potrzebne dla danej instancji encji. Świetnie! Problem rozwiązany… dopóki twoja tabela nie zawiera milionów rekordów i nie zajdzie potrzeba dodania nowego wpisu.
Rozważmy wzorzec EAV (), występuje on dość często. Jedna tabela zawiera encje (rekordy), inna tabela zawiera nazwy właściwości (atrybutów), a trzecia tabela łączy encje z ich atrybutami i zawiera wartości tych atrybutów dla bieżącej encji. Daje to możliwość posiadania różnych zestawów właściwości dla różnych obiektów, a także dodawania atrybutów „w locie”, bez zmiany struktury bazy danych.
Jednakże, nie napisałbym tej notatki, gdyby nie było wad w podejściu z wykorzystaniem EVA. Na przykład, aby uzyskać jedną lub kilka encji, które mają po jednym atrybucie, wymagane są 2 joiny w zapytaniu: pierwszy – łączenie z tabelą atrybutów, drugi – łączenie z tabelą wartości. Jeśli encja ma 2 atrybuty, potrzebne są już 4 joiny! Co więcej, wszystkie atrybuty zazwyczaj są przechowywane w postaci ciągów, co prowadzi do rzutowania typów, zarówno dla wyniku, jak i dla warunku WHERE. Jeśli piszesz wiele zapytań, jest to dość marnotrawne, jeśli chodzi o wykorzystanie zasobów.
Pomimo tych oczywistych wad, EAV jest od dawna wykorzystywane do rozwiązywania tego rodzaju problemów. To były nieuniknione braki, a nie było lepszej alternatywy.
Ale wówczas w PostgreSQL pojawiła się nowa „technologia”…
Od wersji PostgreSQL 9.4 dodano typ danych JSONB do przechowywania danych binarnych JSON. Chociaż przechowywanie JSON w tym formacie zazwyczaj zajmuje nieco więcej miejsca i czasu niż prosty tekst JSON, operacje na nim są znacznie szybsze. JSONB obsługuje również indeksowanie, co przyspiesza zapytania.
Typ danych JSONB umożliwia zastąpienie nieporęcznego wzorca EAV poprzez dodanie tylko jednej kolumny JSONB do naszej tabeli encji, co znacząco upraszcza projektowanie bazy danych. Jednak wielu twierdzi, że powinno to wiązać się z obniżeniem wydajności… Z tego powodu powstał ten artykuł.
Konfiguracja testowej bazy danych
Do tego porównania stworzyłem bazę danych na nowej instalacji PostgreSQL 9.5 na 80-dolarowej konfiguracji Ubuntu 14.04. Po skonfigurowaniu niektórych parametrów w postgresql.conf uruchomiłem skrypt za pomocą psql. Dla przedstawienia danych w formacie EAV utworzono następujące tabele:
CREATE TABLE entity (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT
);
CREATE TABLE entity_attribute (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE entity_attribute_value (
id SERIAL PRIMARY KEY,
entity_id INT REFERENCES entity(id),
entity_attribute_id INT REFERENCES entity_attribute(id),
value TEXT
);
Poniżej znajduje się tabela, w której będą przechowywane te same dane, ale z atrybutami w kolumnie typu JSONB – properties.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Wygląda znacznie prościej, prawda? Następnie dodano do tabel encji (entity & entity_jsonb) 10 milionów rekordów, a odpowiednio wypełniono identycznymi danymi tabele, w których stosuje się wzorzec EAV oraz podejście z kolumną JSONB – entity_jsonb.properties. W ten sposób otrzymaliśmy kilka różnych typów danych w całym zbiorze właściwości. Przykład danych:
{
id: 1
name: "Entity1"
description: "Testowa encja nr 1"
properties: {
color: "czerwony"
length: 120
width: 3.1882420
hasSomething: true
country: "Belgia"
}
}Więc teraz mamy identyczne dane dla dwóch opcji. Zaczynajmy porównywać implementacje w praktyce!
Uproszczenie projektu
Już wcześniej wspomniano, że projekt bazy danych został znacznie uproszczony: jedna tabela, dzięki wykorzystaniu kolumny JSONB dla właściwości, zamiast stosowania trzech tabel dla EAV. Ale jak to wpływa na zapytania? Aktualizacja jednego atrybutu encji wygląda następująco:
-- EAV
UPDATE entity_attribute_value
SET value = 'niebieski'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"niebieski"')
WHERE id = 120;
Jak widzimy, ostatnie zapytanie nie wydaje się prostsze. Aby zaktualizować wartość właściwości w obiekcie JSONB, musimy użyć funkcji , i musimy przekazać naszą nową wartość jako obiekt JSONB. Niemniej jednak, nie musimy znać żadnego identyfikatora z góry. Patrząc na przykład z EAV, musimy znać zarówno entity_id, jak i entity_attribute_id, aby wykonać aktualizację. Jeśli chcesz zaktualizować właściwość w kolumnie JSONB na podstawie nazwy obiektu, – wszystko to można zrobić w jednym prostym wierszu.
Teraz wybierzmy tę encję, którą właśnie zaktualizowaliśmy, na podstawie jej nowego koloru:
-- EAV
SELECT e.name
FROM entity e
INNER JOIN entity_attribute_value eav ON e.id = eav.entity_id
INNER JOIN entity_attribute ea ON eav.entity_attribute_id = ea.id
WHERE ea.name = 'color' AND eav.value = 'niebieski';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'niebieski';
Myślę, że możemy zgodzić się, że drugie jest krótsze (bez join!), a tym samym bardziej czytelne. Tutaj wygrywa JSONB! Używamy operatora JSON ->>, aby uzyskać kolor jako wartość tekstową z obiektu JSONB. Istnieje również drugi sposób osiągnięcia tego samego wyniku w modelu JSONB z użyciem operatora @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "niebieski"}';
To jest trochę trudniejsze: sprawdzamy, czy obiekt JSON w kolumnie właściwości zawiera obiekt, który znajduje się po prawej stronie operatora @>. Mniej czytelny, bardziej wydajny (patrz dalej).
Uprośćmy korzystanie z JSONB jeszcze bardziej, gdy potrzebujesz wybrać kilka właściwości jednocześnie. To jest miejsce, gdzie podejście JSONB naprawdę się sprawdza: po prostu wybieramy właściwości jako dodatkowe kolumny w naszym zbiorze wyników bez potrzeby unii:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
Z EAV potrzebujesz 2 unii dla każdej właściwości, którą chcesz zapytać. Moim zdaniem powyższe zapytania pokazują duże uproszczenie w projektowaniu bazy danych. Możesz również znaleźć więcej przykładów pisania zapytań do JSONB w poście.
Teraz nadszedł czas, aby porozmawiać o wydajności.
Wydajność
Aby porównać wydajność, użyłem w zapytaniach, do obliczania czasu realizacji. Każde zapytanie było wykonywane co najmniej trzy razy, ponieważ za pierwszym razem planowanie zapytań wymaga więcej czasu. Najpierw wykonałem zapytania bez jakichkolwiek indeksów. Oczywiście, miało to zaletę dla JSONB, ponieważ połączenia wymagane dla EAV nie mogły wykorzystywać indeksów (pola kluczy obcych nie były indeksowane). Następnie stworzyłem indeks dla dwóch kolumn kluczy obcych w tabeli wartości EAV oraz indeks dla kolumny JSONB.
Aktualizacja danych pokazała następujące wyniki czasowe (w ms). Zwróć uwagę, że skala jest logarytmiczna:
Widzimy, że JSONB jest znacznie (> 50000-krotnie) szybszy niż EAV, jeśli nie używamy indeksów, z powodu podanych wcześniej przyczyn. Gdy indeksujemy kolumny z kluczami głównymi, różnica niemal znika, ale JSONB wciąż jest 1,3 razy szybszy niż EAV. Zauważ, że indeks w kolumnie JSONB nie wpływa tutaj w żaden sposób, ponieważ nie używamy kolumny właściwości w kryteriach oceny.
Do wyboru danych na podstawie wartości właściwości uzyskujemy następujące wyniki (zwykła skala):
Można zauważyć, że JSONB znów działa szybciej niż EAV bez indeksów, ale kiedy EAV ma indeksy – nadal działa szybciej niż JSONB. Jednak potem zauważyłem, że czas dla zapytań JSONB był jednakowy, co skłoniło mnie do myślenia, że indeks GIN nie działa. Wygląda na to, że gdy używasz indeksu GIN dla kolumny z wypełnionymi właściwościami, działa on tylko przy użyciu operatora włączenia @>. Użyłem tego w nowym teście, co miało ogromny wpływ na czas: zaledwie 0,153 ms! To 15000 razy szybciej niż EAV i 25000 razy szybciej niż operator ->>.
Myślę, że to było wystarczająco szybko!
Rozmiary tabel bazy danych
Porównajmy rozmiary tabel w obu podejściach. W psql możemy pokazać rozmiar wszystkich tabel i indeksów za pomocą polecenia dti+
Dla podejścia EAV rozmiary tabel wynoszą około 3068 MB, a indeksy do 3427 MB, co w sumie daje 6,43 GB. Przy użyciu podejścia z JSONB używa się 1817 MB dla tabeli i 318 MB dla indeksów, co stanowi 2,08 GB. To trzykrotnie mniej! Ten fakt trochę mnie zaskoczył, ponieważ przechowujemy nazwy właściwości w każdym obiekcie JSONB.
Jednak liczby mówią same za siebie: w EAV przechowujemy 2 klucze zewnętrzne jako liczby całkowite dla wartości atrybutów, co skutkuje dodatkowymi 8 bajtami danych. Co więcej, w EAV wszystkie wartości właściwości są przechowywane jako tekst, podczas gdy JSONB wykorzystuje wartości liczbowe i logiczne tam, gdzie to możliwe, co prowadzi do mniejszej objętości.
Podsumowanie
Ogólnie sądzę, że zachowanie właściwości encji w formacie JSONB może znacznie uprościć projektowanie i utrzymanie bazy danych. Jeśli wykonujesz wiele zapytań, wszystko, co jest przechowywane w jednej tabeli z encją, będzie działać efektywniej. I fakt, że to upraszcza interakcję między danymi, już jest plusem, ale także prowadzi do mniejszej objętości bazy danych, która jest trzykrotnie mniejsza.
Ponadto z przeprowadzonych testów można wyciągnąć wnioski, że straty wydajności są bardzo nieznaczne. W niektórych przypadkach JSONB działa nawet szybciej niż EAV, co czyni go jeszcze lepszym. Jednak ten test wzorcowy oczywiście nie obejmuje wszystkich aspektów (np. encje z bardzo dużą liczbą właściwości, znacznym wzrostem liczby istniejących danych,…), dlatego jeśli masz jakiekolwiek sugestie, jak je poprawić, nie wahaj się zostawić komentarza!
Źródło: habr.com
