Questa pagina cerca di trattare in modo semplice e con esempi pratici l'uso del JSON in PostgreSQL.
Dopo una prima introduzione, sono descritti gli operatori, le funzioni, gli indici, ...
Utilizzare il JSON in PostgreSQL e' possibile da molto tempo ed e' facile:
Ma le indicazioni fornite nell'esempio hanno ragioni tecniche ben precise e ci sono parecchi ulteriori dettagli: continuate a leggere!
Il JSON (JavaScript Object Notation) e' un semplice formato per lo scambio di dati
comprensibile per voi umani e facilmente trattabile dai linguaggi di programmazione.
Sintatticamente e' su un sottoinsieme del linguaggio JavaScript ma e' un formato di testo indipendente
utilizzabile da tutti i linguaggi ed e' diventato uno standard di fatto
per lo scambio di dati.
Il JSON e' formato da coppie nome-valore o da elenchi di valori in una struttura gerarchica.
Gli oggetti sono separati da virgole, le strutture sono rappresentate con le parentesi graffe.
E' possibile seguire regole di intentazione che rendano piu' comprensibile la struttura ma non e' obbligatorio.
PostgreSQL e' un database relazionale, anzi un database object-relational
e quindi non e' stato progettato inizialmente per gestire questi tipi di dato.
Con il datype TEXT, che utilizza la tecnica del TOASTing,
PostgreSQL non ha mai avuto particolari problemi nel gestire campi testuali
di grandi dimensioni (1GB).
Dalla versione 9.2 [NdA disponibile dal 2012-09] supporta il datatype JSON
Dalla versione 9.4 supporta il datatype JSONB che utilizza una
rappresentazione nativa binaria del dato e quindi risulta piu' efficiente.
Ogni versione di PostgreSQL ha introdotto migliorie e nuove funzionalita':
JSON datatype in PG9.2,
TO_JSON() e JSON_AGG() in PG9.3,
JSONB datatype in PG9.4,
JSON path expressions in PG12, subscripting operators on jsonb in PG14,
previsti in PG15 ma rilasciati con la PG16 sono SQL/JSON constructors and identity functions.
Anche nell'ultima versione rilasciata PG17 [NdA 2024-09]
sono state introdotte nuove funzioni json:
JSON(), JSON_SCALAR(), JSON_SERIALIZE(), JSON_EXISTS(), JSON_QUERY(), JSON_VALUE(), JSON_TABLE().
Gli indici GIN, spesso utilizzati per le ricerche testuali,
tecnicamente sono inverted index e risultano molto adatti per le ricerche sui JSON.
Quindi, a parte la verifica sulla versione di PostgreSQL utilizzata, in generale non vi sono problemi particolari a memorizzare ed utilizzare dati JSON in PostgreSQL. E' pero' importante conoscere tutte le opzioni possibili per fare le scelte migliori per rappresentare ed indicizzare i dati JSON.
Qual'e' la differenza tra JSON e JSONB?
Vi sono due datatype in PostgreSQL per gestire i dati JSON,
con molta fantasia i nomi dei datatype sono JSON e JSONB (binary).
Con il datatype JSON il dato viene memorizzato esattamente com'e'
mantenendo spaziatura, formattazione, ordine delle chiavi, chiavi doppie...
esattamente come inserito.
Con il datatype JSONB il dato viene ordinato e rappresentato in modo binario.
In fase di INSERT il JSON e' leggermente piu' veloce perche' non richiede alcuna
elaborazione, tuttavia il JSONB
e' piu' efficiente nelle ricerche e nell'utilizzo dei dati.
Il JSONB dispone di molti piu' operatori rispetto al datatype JSON
ed ha un'occupazione su disco minore: nella stragrande maggioranza dei casi
la scelta corretta e' utilizzare il datatype JSONB.
Nel seguito utilizzeremo solo il datatype JSONB.
Postgres fornisce un grande numero di operatori per gestire i dati JSONB.
L'operatore ->> consente di estrarre valori di testo da un campo JSONB.
Combinando gli operatori -> e ->>
si estraggono valori nidificati all'interno di una struttura JSON.
L'operatore ? controlla se un testo e' presente come chiave o come elemento di un vettore al primo livello.
L'operatore @> controlla se un oggetto JSONB contiene un altro oggetto JSONB.
La documentazione ufficiale riporta l'elenco completo e tutti i dettagli.
Postgres fornisce un grande numero di funzioni per gestire i dati JSON.
Vediamo qualche esempio:
La documentazione ufficiale riporta l'elenco completo e tutti i dettagli delle funzioni di creazione, delle funzioni di gestione, ... dei JSON.
E' possibile applicare condizioni di ricerca, ordinamenti o group by basate sul contenuto di un campo JSON. Se non vi sono indici PostgreSQL esegue un Sequential Scan ed applica le condizioni su ogni tupla. Con un indice invece le ricerche possono essere molto piu' efficienti. E' pero' importante utilizzare il tipo indice corretto a seconda del tipo di accesso.
L'uso di un indice su un campo JSON da parte dell'ottimizzatore si controlla come sempre con l'EXPLAIN.
Il contenuto di un JSON puo' contenere coppie nome-valore
in strutture complesse e con un diverso livello di annidamento.
In generale un normale indice B-tree non e' utile per le ricerche
all'interno di un dato JSON.
Tuttavia nel caso di semplici ricerche per uguaglianza e'
possibile creare un indice normale
sfruttando un expression index:
Nota importante: affinche' l'indice B-tree venga utilizzato e' necessario che
nella condizione si
utilizzi l'operatore di uguaglianza = .
Nella creazione dell'indice e'
possibile usare la clausola di partial index come nell'esempio,
ma non e' obbligatorio,
ed e' importante fare attenzione ad eventuali cast da applicare.
Questo e' uno dei pochi casi in cui gli indici B-tree sono utilizzabili
per le ricerche sui JSON.
Ma in PostgreSQL vi e' un altro tipo di indice molto piu' adatto alle diverse modalita'
di ricerca possibili con i JSON: il GIN.
Il tipo di indice di elezione per i campi JSON e' il GIN.
GIN e' un indice invertito disponibile in PostgreSQL dalla versione 8.2:
in pratica analizza il contenuto di un campo,
trova gli elementi che lo costituiscono e crea un elenco ottimizzato dei contenuti.
Gli indici GIN supportano due operator classes per i JSON con possibilita' diverse:
L'operator class jsonb_pathops e' meno potente ma genera un indice di dimensioni piu' ridotte, piu' efficiente e meno pesante da mantenere. E' quindi un'alternativa valida ad esempio quando il numero di modifiche e' elevato.
Vediamo qualche esempio pratico:
Viste alcune delle possibilita' qual'e' l'indice corretto per un campo JSON? Non c'e' una risposta: dipende da come si accede ai dati!
Un ultimo importante consiglio sul GIN: non bevetene troppo!
Per una serie di ragioni l'ottimizzatore di PostgreSQL e' piu' intelligente
[NdA ho scelto questa parola perche' va di moda]
nella scelta dell'execution plan quando sono presenti dati in forma colonnare ed indici B-tree.
Grazie alle statistiche puo' valutare la selettivita' delle condizioni e scegliere l'algoritmo
migliore per ogni query, anche per le piu' complesse.
Per tale motivo se vi sono alcune chiavi nel dato in JSON che vengono interrogate
con frequenza e risultano selettive, un possibile disegno della base dati puo' prevedere
una denormalizzazione ed utilizzare colonne aggiuntive nelle tabelle.
Nella maggior parte dei casi non e' necessario ed una corretta definizione degli indici
e' sufficiente; tuttavia possono esservi casi particolari di utilizzo in cui
questo disegno potrebbe essere utile.
Fino ad ora abbiamo visto l'utilita' degli indici nelle ricerche. In realta' e' anche importante conoscere il costo che comporta mantenere gli indici in fase modifica dei dati con un'istruzione di DML.
Sulle attivita' DML il costo maggiore delle operazioni e' sugli indici: per la variazione dei dati nella heap e' sufficiente inserire quanto necessario su un blocco libero, ma per ogni indice e' necessario correggere non solo la foglia ma anche tutta la struttura dell'indice.
Quanto sopra vale per gli indici B-tree, per gli indici GIN... e' peggio!
GIN e' un indice invertito: quando viene creato analizza il contenuto di un campo,
trova gli elementi che lo costituiscono e crea un elenco ottimizzato dei contenuti.
In pratica viene costruito un B-Tree che contiene tutte le entry
e quindi su ciascuna entry viene creato
l'elenco dei record in cui e' presente (posting list).
Questo vuol dire che a fronte di una INSERT di un solo record in realta'
le entry che vengono aggiunte
nell'indice sul campo JSON possono essere decine o centinaia.
Per default vengono inserite come entry sia le chiavi che i valori JSON
mentre se si utilizza jsonb_pathops vengono inseriti nell'indice GIN solo i valori.
L'aggiornamento di tutte le nuove entry su un indice GIN puo' essere molto pesante...
quindi PostgreSQL non lo fa!
In realta' non e' proprio cosi', semplicemente PostgreSQL non fa tutto subito.
Per fare in modo che le ricerche per indice siano corrette le chiavi ed i riferimenti
appena inseriti vengono mantenuti a parte in una pending list
senza aggregarli nell'albero completo del GIN.
A fronte di una ricerca vengono analizzati sia l'albero delle entry che la pending list.
Naturalmente se la pending list diventa troppo grande la ricerca non e' efficiente.
Tuttavia questa tecnica consente DML efficienti e ricerche corrette e veloci
se il numero di modifiche sui dati non e' troppo pesante.
La tecnica descritta e' chiamata e' chiamata GIN fastupdate ed e' attiva per default
[NdA e' un parametro di storage dell'indice disponibile dalla versione PG 8.4].
La pending list viene svuotata dal VACUUM, quando si raggiunge il limite della pending list
o quando viene lanciata la funzione gin_clean_pending_list().
I parametri di tuning di interesse sono gin_pending_list_limit [NdA default 4MB],
gin_fuzzy_search_limit, tutti i parametri che influenzano
l'avvio dell'autovacuum e, particolamente importante con gli indici GIN,
il parametro di tuning maintenance_work_mem.
NdE per essere precisi va eseguito il tuning di
work_mem per le INSERT/UPDATE,
maintenance_work_mem per le CREATE INDEX e le chiamate a gin_clean_pending_list() ed infine
autovacuum_work_mem per l'autovacuum]
[NdA ma poiche' il default di autovacuum_work_mem e' -1 quanto avevo scritto era gia' sufficiente:
l'Editor non e' piu' preciso... e' solo piu' pignolo dell'Author].
Dal punto di vista pratico in caso di modifiche pesanti su tabelle con un indice GIN e' consigliabile cancellare l'indice prima delle modifiche e ricrearlo alla fine!
Le colonne JSON e JSONB sono a lunghezza variabile, possono contenere fino ad 1GB [NdA JSONB ha l'ulteriore limitazione di 255MB per field] e sono gestite con le normali policy di storage di Postgres.
Saro' lungo...
In PostgreSQL il blocco ha dimensione di 8KB.
Le tabelle vengono memorizzate in blocchi ospitati in sequenza su file (heap).
Postgres per default cerca di riempire completamente ogni blocco tuttavia
e' possibile riservare una spazio per le sucessive UPDATE abbassando
il valore del parametro di storage fillfactor (default 100).
Una riga o tupla deve essere contenuta in un solo blocco.
Per la gestione dei datatype di maggiori dimensioni viene utilizzato il TOAST
(The Oversized-Attribute Storage Technique) che consente di memorizzare dati fino ad 1GB (230) per riga.
Quando una riga supera una certa dimensione viene divisa in parti (chunk)
e mantenuta sul una tabella TOAST associata alla tabella originale.
Sono disponibili 4 differenti strategie per trattare le colonne:
Quando una colonna supera il valore del parametro
TOAST_TUPLE_THRESHOLD (defaul 2K) Postgres la comprime,
se questo non basta la affetta e mette le fette in una tabella d'appoggio di TOAST.
E' possibile utilizzare
storage parameter
specifici (eg. fillfactor e toast_tuple_target) per effetturare un tuning sulle singole tabelle.
La compressione per default e' effettuata con l'algoritmo pglz
ma dalla versione PG 14 e' disponibile l'algoritmo lz4
che e' notevolmente piu' efficiente,
anche se ha un rapporto di compressione leggermente piu' basso.
Accedere alle colonne che vengono tostate ovviamente e' piu'
pesante rispetto alle altre, quindi vanno evitati accessi inutili
con un SELECT * quando i dati non sono necessari.
I tempi di risposta possono essere diversi tra righe che contengono
colonne di dimensioni molto differenti.
Tutto questo vale anche per le colonne con datatype JSON e JSONB
praticamente senza differenze!
La strategia di default per le colonne JSON e JSONB e' EXTENDED e tutte le avvertenze e le possibilita' di tuning
gia' riportate vangono anche per i dati JSON.
E' importante riportare tutte le tipologie di indici disponibili in PostgreSQL:
Anche se e' possibile utilizzare indici B-tree o Hash sui campi JSON, nel caso in cui la condizione sia di uguaglianza, sfruttando la sintassi degli expression index e dei partial index, il tipo di indice piu' utilizzato con i JSON in PostgreSQL e' il GIN, come abbiamo visto negli esempi.
L'Introduzione a PostgreSQL e' una paginetta utile su PostreSQL mentre la sua storia e' dettagliata in questa pagina.
Titolo: JSON in PostgreSQL
Livello: Intermedio
Data:
14 Febbraio 2025 ❤️
Versione: 1.0.1 - 1 Aprile 2025
Autore: mail [AT] meo.bogliolo.name