PostgreSQL er en dokumentdatabase

Dette er andre artikkel i en artikkelserie om PostgreSQL. Målet med serien er å synliggjøre bruksområder og muligheter du kanskje ikke var klar over at din favorittdatabase hadde, og hjelpe deg i gang med praktiske tips og triks.

‍

I forrige artikkel dekket vi hvordan en kan bruke den populære utvidelsen pgvector til å gi PostgreSQL-databasen din støtte for å håndtere datatypen `VECTOR` og vektorsøk. PostgreSQL har et svært godt økosystem for å kunne utvide mulighetene dine, men det ikke alle vet er at PosgreSQL allerede kommer en hel haug innebygd funksjonalitet utover relasjonsdatabase-konsepter som ikke krever noen utvidelser for å ta i bruk.

‍

En av disse innebygde kapabilitetene er støtte for behandling av JSON-dokumenter, som først ble introdusert i versjon 9.2 helt tilbake i 2012 og senere forbedret med innføringen av JSONB, en binær datatype som gjør det mer effektivt å håndtere og søke i JSON-dokumenter. I praksis er det svært få grunner til at en skulle velge å bruke JSON-datatypen i PostgreSQL fremfor JSONB, så selv om resten av denne artikkelen omtaler det som JSON er det JSONB-versjonen vi referer til.

‍

‍

JSON-støtten i PostgreSQL lar deg definere datatypen i en kolonne til JSONB, hvor du dermed kan dumpe alt du har av ustrukturert JSON-data. Dette kan være primitive typer som tall og tekst eller komplekse typer som lister og objekter. Generelt sett er alt som er tillatt i JSON-standarden støttet. Å lagre JSON på denne måten er simpelt, det gjøres så enkelt som:

‍

CREATE TABLE document (
	key BIGINT PRIMARY KEY,
    value JSONB NOT NULL
);
INSERT INTO document (key, value) VALUES (1, '1'::jsonb);
INSERT INTO document (key, value) VALUES (2, '"Hello world"');
INSERT INTO document (key, value) VALUES (3, '{"hello": "world", "foo": ["bar", 456], "sample": {"nested": {"structure": 123}}}');
INSERT INTO document (key, value) VALUES (4, '{"hello": "santi", "balloo": "baz", "sample": {"nested": "container"}}');

‍

Husk alltid på at om du først skal lagre dataen din som fulle dokumenterer i stedet for normalisert tabeller så burde tilgangsmønsteret, det vil si hvordan du ønsker å hente ut dataen fra databasen, være slik at det gir mening å hente ut hele dokumentet samtidig, og at du ikke hele tiden må slå opp subsett av dataene i JSON-strukturen uten å gå via primærnøkkelen. Det er selvfølgelig fullt mulig å gjøre dette også, men det bryter litt med prinsippene om hvorfor en velger en dokumentdatabase fremfor andre databasetyper.

‍

Om en likevel ønsker å kunne slå opp dokumenter basert på spesifikke verdier i den nøstede JSON-strukturen, så har PostgreSQL naturligvis støtte for dette også. I likhet med vektorstøtte som vi var inne på i forrige artikkel finnes det indekser som er spesialiserte til å kunne indeksere JSON-dokumenter, såkalte GIN (Generalized Inverted Index).

‍

Disse indeksene fungerer ved at de bygger en oversikt over verdiene til den gitte strukturen i en JSON-kolonne, slik at databasen raskt kan svare på spørsmål som “Hvilke dokumenter inneholder verdien “world” i strukturen `$.hello`. I eksempelet over vil dette returnere dokument 4. Å lage slike indekser er enkelt:

‍

CREATE INDEX document_gin_idx ON document USING GIN (value);

‍

Det hadde vært fantastisk om disse indeksene kunne brukes i alle tilfeller hvor JSON-kolonner skal søkes gjennom, men slik er det dessverre ikke. GIN-indekser må gjøre en del antakelser rundt hvordan de lagrer dataene, og dermed er det kun noen type oppslag som kan benytte seg av indeksen. Følgende operatorer kan benytte seg av en standard GIN-indeks på en JSON-kolonne:

‍

‍?, Inneholder JSON-dokumentet en nøkkel på toppnivå med det gitte navnet?

SELECT * FROM document WHERE value ? 'foo';
 -> Dokument 3

‍

?|, Inneholder JSON-dokumentet en nøkkel på toppnivå med et av de gitte navnene i lista?

SELECT * FROM document WHERE value ?| array['foo', 'balloo'];
-> Document 3 og 4

‍

?&, Inneholder JSON-dokumentet alle nøklene i den gitte lista på toppnivå?

SELECT * FROM document WHERE value ?& array['hello', 'foo'];
-> Dokument 3

‍

@>, Inneholder JSON-dokumentet det gitte dokumentet?

SELECT * FROM document WHERE value @> '{"hello": "santi"}';
-> Dokument 4

‍

@@, Inneholder JSON-dokumentet den gitte JSONPATH-spørringen?

SELECT * FROM document WHERE value @@ '$.hello == "world"'::JSONPATH;
-> Dokument 3

‍

@?, Finnes det et JSON-dokument som oppfyller JSONPATH-spørringen?

SELECT * FROM document WHERE value @? '$.hello ? (@ == "world")'::JSONPATH;
-> Dokument 3

‍

I tillegg er det mulig å opprette GIN-indekser som gjør @>, @@ og @?-spørringen raskere, men hvor indeksen ikke kan brukes til ?, ?| og ?&-operatorene. Om du kun kommer til å kun benytte deg av de førstnevnte operatorene kan det dermed lønne seg å opprette GIN-indeksen sin slik:
‍

CREATE INDEX document_gin_idx ON document USING GIN (value jsonb_path_ops);

‍

En siste huskeregel er at ingenting kommer gratis, og GIN-indekser er intet unntak. Disse indeksene krever relativt mye arbeid for å oppdatere når en setter inn eller endrer dokumenter, så innsetting av store mengder dokumenter kommer til å oppleves tregere. Den offisielle PostgreSQL-dokumentasjonen går til og med så langt som å foreslå at en sletter indeksen før en setter inn haugevis med rader, for deretter å opprette den på nytt når radene er satt inn.

‍

Det er med andre ord lekende lett å lagre JSON-dataen din i PostgreSQL, og om du ønsker å gjøre avanserte spørringer på dataen er det mulig det også. Dette er store fordeler i seg selv, men den virkelig store fordelen kommer av at all den andre dataen din antakeligvis befinner seg i PostgreSQL den også! Det er med andre ord mulig å kombinere det beste fra to verdener ved å bruke verdier fra de ustrukturerte JSON-dokumentene og kombinere dem med relasjonell data i andre tabeller. Slik blir en kvitt kompleksitet hvor en må gjøre oppslag og spørringer på tvers av systemer, om for eksempel en skulle kombinert data fra MongoDB og PostgreSQL. Med en litt utvidet SQL-spørring kan du med andre ord slippe både kostnadene og vedlikeholdsbehovet for andre tjenester i infrastrukturen din. Kjekt, hva?