TL; DR: JSONB może znacznie uprościć tworzenie schematu bazy danych bez poświęcania wydajności zapytań.
Wprowadzenie
Podajmy klasyczny przykład prawdopodobnie jednego z najstarszych przypadków użycia w świecie relacyjnej bazy danych (bazy danych): mamy encję i musimy zapisać pewne właściwości (atrybuty) tej encji. Jednak nie wszystkie instancje mogą mieć ten sam zestaw właściwości i w przyszłości może zostać dodanych więcej właściwości.
Najłatwiejszym sposobem rozwiązania tego problemu jest utworzenie w tabeli bazy danych kolumny dla każdej wartości właściwości i po prostu wypełnienie tych, które są potrzebne dla konkretnej instancji encji. Świetnie! Problem rozwiązany... dopóki Twoja tabela nie będzie zawierać milionów rekordów i nie będziesz musiał dodać nowego rekordu.
Rozważ wzór EAV (), zdarza się to dość często. Jedna tabela zawiera encje (rekordy), inna tabela zawiera nazwy właściwości (atrybuty), a trzecia tabela kojarzy 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 właściwości na bieżąco, bez zmiany struktury bazy danych.
Nie pisałbym jednak tego postu, gdyby nie było pewnych wad podejścia EVA. Czyli np. aby otrzymać jedną lub więcej encji, z których każda ma po 1 atrybucie, w zapytaniu wymagane są 2 złączenia: pierwsze to złączenie z tabelą atrybutów, drugie to złączenie z tabelą wartości. Jeśli jednostka ma 2 atrybuty, potrzebne są 4 połączenia! Ponadto wszystkie atrybuty są zwykle przechowywane jako ciągi znaków, co skutkuje rzutowaniem typu zarówno dla wyniku, jak i klauzuli WHERE. Jeśli piszesz wiele zapytań, jest to dość marnotrawstwo pod względem wykorzystania zasobów.
Pomimo tych oczywistych niedociągnięć, EAV jest od dawna stosowany do rozwiązywania tego typu problemów. Były to nieuniknione niedociągnięcia i po prostu nie było lepszej alternatywy.
Ale wtedy w PostgreSQL pojawiła się nowa „technologia”…
Począwszy od PostgreSQL 9.4, dodano typ danych JSONB do przechowywania danych binarnych JSON. Chociaż przechowywanie JSON w tym formacie zwykle zajmuje nieco więcej miejsca i czasu niż zwykły tekst JSON, wykonywanie na nim operacji jest znacznie szybsze. JSONB obsługuje także indeksowanie, dzięki czemu zapytania są jeszcze szybsze.
Typ danych JSONB pozwala nam zastąpić uciążliwy wzorzec EAV poprzez dodanie tylko jednej kolumny JSONB do naszej tabeli encji, co znacznie upraszcza projektowanie bazy danych. Ale wielu twierdzi, że powinien temu towarzyszyć spadek produktywności... Dlatego napisałem ten artykuł.
Konfiguracja testowej bazy danych
Dla tego porównania stworzyłem bazę danych na nowej instalacji PostgreSQL 9.5 w wersji 80 dolarów Ubuntu 14.04. Po ustawieniu niektórych parametrów w postgresql.conf uruchomiłem skrypt przy użyciu psql. W celu przedstawienia danych w formie EAV utworzono poniższe 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 – niska zabudowa.
CREATE TABLE entity_jsonb (
id SERIAL PRIMARY KEY,
name TEXT,
description TEXT,
properties JSONB
);
Wygląda o wiele prościej, prawda? Następnie został dodany do tabel encji (jednostka & podmiot_jsonb) 10 milionów rekordów i odpowiednio tabela została wypełniona tymi samymi danymi przy wykorzystaniu wzorca EAV i podejścia z kolumną JSONB - obiekt_jsonb.properties. W ten sposób otrzymaliśmy kilka różnych typów danych spośród całego zestawu właściwości. Przykładowe dane:
{
id: 1
name: "Entity1"
description: "Test entity no. 1"
properties: {
color: "red"
lenght: 120
width: 3.1882420
hassomething: true
country: "Belgium"
}
}Mamy więc teraz te same dane dla obu opcji. Zacznijmy od porównania wdrożeń w pracy!
Uprość swój projekt
Wcześniej stwierdzono, że projekt bazy danych został znacznie uproszczony: jedna tabela dzięki zastosowaniu kolumny JSONB dla właściwości zamiast trzech tabel dla EAV. Ale jak to znajduje odzwierciedlenie w prośbach? Aktualizacja jednej właściwości encji wygląda następująco:
-- EAV
UPDATE entity_attribute_value
SET value = 'blue'
WHERE entity_attribute_id = 1
AND entity_id = 120;
-- JSONB
UPDATE entity_jsonb
SET properties = jsonb_set(properties, '{"color"}', '"blue"')
WHERE id = 120;
Jak widać, ostatnia prośba nie wygląda na prostszą. Aby zaktualizować wartość właściwości w obiekcie JSONB, musimy skorzystać z funkcji i powinien przekazać naszą nową wartość jako obiekt JSONB. Nie musimy jednak znać wcześniej żadnego identyfikatora. Patrząc na przykład EAV, musimy znać zarówno identyfikator_encji, jak i identyfikator_atrybutu, aby przeprowadzić aktualizację. Jeśli chcesz zaktualizować właściwość w kolumnie JSONB na podstawie nazwy obiektu, wszystko można zrobić w jednym prostym wierszu.
Teraz wybierzmy element, który właśnie zaktualizowaliśmy w oparciu o jego nowy kolor:
-- 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 = 'blue';
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties ->> 'color' = 'blue';
Chyba zgodzimy się, że druga część jest krótsza (bez łączenia!), a przez to bardziej czytelna. JSONB tutaj wygrywa! 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 za pomocą operatora @>:
-- JSONB
SELECT name
FROM entity_jsonb
WHERE properties @> '{"color": "blue"}';
To jest trochę bardziej skomplikowane: sprawdzamy, czy obiekt JSON w swojej kolumnie właściwości zawiera obiekt znajdujący się na prawo od operatora @>. Mniej czytelny, bardziej produktywny (patrz poniżej).
Uczyńmy korzystanie z JSONB jeszcze łatwiejszym, gdy musisz wybrać wiele właściwości jednocześnie. W tym miejscu naprawdę pojawia się podejście JSONB: po prostu wybieramy właściwości jako dodatkowe kolumny w naszym zestawie wyników bez potrzeby łączenia:
-- JSONB
SELECT name
, properties ->> 'color'
, properties ->> 'country'
FROM entity_jsonb
WHERE id = 120;
W przypadku EAV będziesz potrzebować 2 złączeń dla każdej właściwości, o którą chcesz zapytać. Moim zdaniem powyższe zapytania pokazują duże uproszczenie w projektowaniu baz danych. Zobacz więcej przykładów pisania zapytań JSONB, także w post.
Teraz czas porozmawiać o wydajności.
produktywność
Aby porównać wydajność, użyłem w zapytaniach, aby obliczyć czas wykonania. Każde zapytanie zostało wykonane co najmniej trzy razy, ponieważ za pierwszym razem planowanie zapytań zajmuje więcej czasu. Najpierw uruchomiłem zapytania bez żadnych indeksów. Oczywiście była to zaleta JSONB, ponieważ złączenia wymagane dla EAV nie mogły korzystać z indeksów (pola kluczy obcych nie były indeksowane). Następnie utworzyłem indeks dla 2 kolumn klucza obcego tabeli wartości EAV, a także indeks dla kolumny JSONB.
Aktualizacja danych wykazała następujące wyniki w czasie (w ms). Należy pamiętać, że skala jest logarytmiczna:
Widzimy, że JSONB jest znacznie (> 50000-x) szybszy niż EAV, jeśli nie używasz indeksów, z powodu podanego powyżej. Kiedy indeksujemy kolumny kluczami podstawowymi, różnica prawie znika, ale JSONB jest nadal 1,3 razy szybszy niż EAV. Należy zauważyć, że indeks w kolumnie JSONB nie ma tutaj żadnego wpływu, ponieważ w kryteriach oceny nie używamy kolumny właściwości.
W przypadku selekcji danych na podstawie wartości właściwości otrzymujemy następujące wyniki (skala normalna):
Można zauważyć, że JSONB ponownie działa szybciej niż EAV bez indeksów, ale gdy EAV z indeksami nadal działa szybciej niż JSONB. Ale potem zobaczyłem, że czasy dla zapytań JSONB były takie same, co skłoniło mnie do faktu, że indeksy GIN nie działają. Najwyraźniej użycie indeksu GIN w kolumnie z wypełnionymi właściwościami działa tylko w przypadku użycia operatora dołączania @>. Użyłem tego w nowym teście i miało to ogromny wpływ na czas: tylko 0,153 ms! Jest to 15000 25000 razy szybsze niż EAV i XNUMX XNUMX razy szybsze niż operator ->>.
Myślę, że to było wystarczająco szybkie!
Rozmiar tabeli bazy danych
Porównajmy rozmiary tabel dla obu podejść. W psql możemy pokazać rozmiar wszystkich tabel i indeksów za pomocą polecenia dti+
W przypadku podejścia EAV rozmiary tabel wynoszą około 3068 MB, a indeksy do 3427 MB, co daje łącznie 6,43 GB. Podejście JSONB wykorzystuje 1817 MB na tabelę i 318 MB na indeksy, co daje 2,08 GB. Okazuje się, że 3 razy mniej! Fakt ten trochę mnie zaskoczył, ponieważ w każdym obiekcie JSONB przechowujemy nazwy właściwości.
Ale liczby mówią same za siebie: w EAV przechowujemy 2 całkowite klucze obce na każdą wartość atrybutu, co daje 8 bajtów dodatkowych danych. Dodatkowo EAV przechowuje wszystkie wartości właściwości jako tekst, podczas gdy JSONB będzie używać wewnętrznie wartości numerycznych i boolowskich, jeśli to możliwe, co skutkuje mniejszym zajmowaniem miejsca.
Wyniki
Ogólnie rzecz biorąc, myślę, że zapisanie właściwości jednostki w formacie JSONB może znacznie ułatwić projektowanie i utrzymywanie bazy danych. Jeśli uruchamiasz wiele zapytań, trzymanie wszystkiego w tej samej tabeli co encja będzie faktycznie działać wydajniej. A fakt, że upraszcza to interakcję między danymi, jest już zaletą, ale powstała baza danych jest 3 razy mniejsza.
Ponadto na podstawie przeprowadzonych testów możemy stwierdzić, że utrata wydajności jest bardzo niewielka. W niektórych przypadkach JSONB jest nawet szybszy niż EAV, co czyni go jeszcze lepszym. Jednak ten benchmark oczywiście nie obejmuje wszystkich aspektów (np. podmioty z bardzo dużą liczbą właściwości, znaczny wzrost liczby właściwości istniejących danych,...), więc jeśli masz jakieś sugestie, jak je ulepszyć , nie wahaj się zostawić komentarza!
Źródło: www.habr.com