Indeksowanie danych typu JSONB: użycie indeksów GIN i BTREE

Pierwsze pytanie: po co w ogóle bawimy się z JSONB? JSONB pozwala trzymać dane w formacie JSON, dając mega elastyczną strukturę. To super wygodne, gdy dane są złożone i mają zagnieżdżone relacje (np. profile użytkowników z listą adresów albo ustawień). W przeciwieństwie do zwykłego JSON, JSONB trzyma dane w formacie binarnym, co sprawia, że operacje wyszukiwania i filtrowania są dużo szybsze.

Ale bez indeksów szukanie po JSONB może być naprawdę wolne, szczególnie jeśli tabela ma tysiące albo miliony wierszy. Wyobraź sobie, że mamy tabelę z info o użytkownikach, gdzie trzymamy ustawienia każdego usera w JSONB. Próbować znaleźć wszystkich użytkowników z konkretną wartością w tych ustawieniach bez indeksów — to zadanie, które zjada masę zasobów. I tu właśnie wchodzą nasze indeksy!

Indeksowanie JSONB: ważne rzeczy

Do pracy z JSONB PostgreSQL wspiera indeksowanie na dwa główne sposoby:

1. GIN (Generalized Inverted Index) — do wyszukiwania po kluczach i wartościach wewnątrz JSONB.
2. BTREE — do prostszego wyszukiwania i sortowania.

Każdy z nich ma swoje cechy. Rozkminmy je trochę dokładniej.

Indeks GIN dla JSONB

GIN — to mocny indeks, który działa z tablicami, tekstami, a także z danymi JSONB. On "rozbija" zawartość obiektu JSONB na osobne klucze i wartości, tworząc specjalną strukturę do szybkiego wyszukiwania tego wszystkiego.

Zalety GIN dla JSONB:

• Pozwala szukać zarówno po kluczach, jak i po wartościach.
• Działa z zagnieżdżonymi strukturami.
• Przyspiesza operacje z operatorami @>, ?, ?|, ?& (filtrowanie kluczy i wartości).

Załóżmy, że mamy tabelę users, gdzie w kolumnie settings trzymane są ustawienia użytkowników w formacie JSONB. Przykład danych:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    settings JSONB
);

INSERT INTO users (name, settings) VALUES
('Alice', '{"theme": "ciemny", "notifications": {"email": true, "sms": false}}'),
('Bob', '{"theme": "jasny", "notifications": {"email": false, "sms": true}}'),
('Charlie', '{"theme": "ciemny", "notifications": {"email": true, "sms": true}}');

Teraz chcemy szybko znaleźć wszystkich użytkowników z ciemnym motywem (theme: ciemny). Najpierw tworzymy indeks:

CREATE INDEX idx_users_settings_gin ON users USING GIN (settings);

Potem wykonaj zapytanie z operatorem @> (szukanie po wartości):

SELECT name
FROM users
WHERE settings @> '{"theme": "ciemny"}';

Teraz PostgreSQL użyje indeksu GIN do wyszukiwania i zapytanie pójdzie dużo szybciej.

Jak to działa? Gdy tworzysz indeks GIN na kolumnie JSONB, PostgreSQL buduje "odwrócony" indeks, czyli tworzy osobne wpisy dla wszystkich kluczy i wartości JSON. Na przykład z obiektu:

{"theme": "ciemny", "notifications": {"email": true, "sms": false}}

on zrobi indeksowanie dla kluczy theme, notifications.email, notifications.sms i ich wartości. To sprawia, że szukanie po pojedynczych elementach jest dużo szybsze.

Indeks BTREE dla JSONB

BTREE — to klasyczny typ indeksu. Używasz go, jeśli chcesz porównywać obiekty JSONB w całości albo robić sortowanie. Ale w przeciwieństwie do GIN, BTREE nie rozbija zawartości obiektu JSON.

Zalety BTREE dla JSONB:

• Świetny do operacji sortowania i porównywania obiektów.
• Działa szybciej, jeśli JSONB używasz jako "monolit" (np. porównujesz go z innym obiektem albo szukasz wierszy, gdzie JSONB jest równy zadanej wartości).

Przykład użycia indeksu BTREE. Załóżmy, że w tabeli users często porównujesz kolumnę settings z jakimś konkretnym obiektem:

{"theme": "ciemny", "notifications": {"email": true, "sms": false}}

Najpierw tworzymy indeks:

CREATE INDEX idx_users_settings_btree ON users USING BTREE (settings);

Teraz możesz robić zapytania porównujące obiekty:

SELECT name
FROM users
WHERE settings = '{"theme": "ciemny", "notifications": {"email": true, "sms": false}}';

To zapytanie użyje indeksu BTREE dla przyspieszenia.

Porównanie GIN i BTREE

Podsumowując, GIN nadaje się do bardziej złożonych zapytań, a BTREE jest przydatny, gdy trzeba porównywać całe obiekty albo sortować.

Jaki indeks wybrać?

• Jeśli chcesz wyszukiwać po pojedynczych kluczach i wartościach w JSONB, użyj GIN.
• Jeśli musisz porównywać albo sortować całe obiekty JSONB, lepszy będzie BTREE.

Ale pamiętaj, że nikt nie zabrania łączyć tych indeksów! Możesz na przykład zrobić i GIN, i BTREE na tym samym polu, jeśli tabela wymaga obu typów zapytań.

Typowe błędy przy indeksowaniu JSONB

Tworzenie niepotrzebnych indeksów: nie zawsze warto indeksować każde pole JSONB. Indeksy zajmują miejsce i mogą spowolnić operacje wstawiania i aktualizacji danych.

Indeksowanie rzadko używanych operatorów: nie indeksuj pola tylko dlatego, że wydaje się to "słuszne". Analizuj zapytania i używaj indeksowania tylko tam, gdzie naprawdę przyspiesza to operacje.

Ignorowanie cech GIN: GIN może potrzebować więcej czasu na stworzenie indeksu niż BTREE. Trzeba to brać pod uwagę przy indeksowaniu dużych tabel.

Praktyczne zastosowanie

Praca z JSONB jest przydatna w realnych projektach, gdzie dane są elastyczne i dynamiczne. Na przykład:

• Aplikacje webowe z ustawieniami użytkownika.
• Przechowywanie logów, które mają różne pola dla różnych zdarzeń.
• Cache'owanie danych w formacie JSON.

Indeksowanie tych danych za pomocą GIN i BTREE pomaga mocno poprawić wydajność zapytań. Na przykład na rozmowie kwalifikacyjnej możesz pokazać, jak przyspieszyłeś system, dodając indeksowanie dla złożonych struktur danych.

Oficjalna dokumentacja PostgreSQL o indeksach JSON jest dostępna tutaj. Nie zapominaj tam zaglądać po szczegóły i przykłady.