Da quest’anno, PyCon Italia diventa un evento politematico ed accoglie le subcommunity più importanti all’interno dell’universo Python: PyData, DjangoVillage e Odoo Italian Community

2ndQuadrant Italia parteciperà alla conferenza con i talk di Gabriele Bartolini, Marco Nenciarini e Giulio Calacoci. Consulta il programma dell’evento.

PostgreSQL 9.4 per devops

Di Gabriele Bartolini
Venerdì 17 aprile alle 12:00, lingua inglese – Domenica 19 aprile alle 9:30, lingua italiana

Il database relazionale open source più avanzato al mondo non smette di migliorarsi. Scopri le novità introdotte nella versione 9.4 di PostgreSQL, opportunamente classificate in un’ottica devops, ed in particolare:

• replica logica in streaming, il primo passo per replica bidirezionale e multi-master;
• supporto per il tipo di dato JSONB, per la felicità degli amanti dei database NoSQL;
• refresh concorrente di viste materializzate, per gli amanti dei data warehouse.

Odoo disaster recovery con Barman

Di Giulio Calacoci
Sabato 18 aprile alle 12:15

Sei stufo di gestire i processi di backup e recovery dei tuoi server PostgreSQL facendo uso di script ‘ad hoc’?
Hai paura di poter essere svegliato nel cuore della notte o peggio mentre sei in vacanza, per eseguire il ripristino di uno dei server critici per il business della tua ditta?
Barman, Backup e Recovery Manager, standardizza le operazioni di backup e recovery, permettendo agli amministratori di database e agli amministratori di sistema di integrare facilmente all’interno del loro piano di disaster recovery le soluzioni basate su Odoo.

HSTORE, JSON e JSONB: dati non strutturati ad alte performance

Di Marco Nenciarini
Sabato 18 aprile alle 15:45

Nelle ultime release di PostgreSQL è stato fatto un enorme lavoro sui dati non strutturati, culminato con l’aggiunta del tipo JSONB. Insieme a JSON, HSTORE e array fornisce una potenza mai vista per le proprie applicazione web e non solo. In questo talk si passeranno in rassegna alcuni dei possibili casi d’uso e si illustreranno i vantaggi delle varie possibilità.

Il progetto Barman: Open Source, Team, devops, kanban, PostgreSQL e Python

Di Gabriele Bartolini e Marco Nenciarini
Domenica 19 aprile alle 11:45

Barman è un software open source per la disaster recovery di database PostgreSQL, probabilmente, in questo momento, il tool più utilizzato al mondo per questo scopo.
È un progetto open source nato nel 2012, scritto in Python e totalmente sviluppato in Italia.
In questo talk vogliamo condividere i processi di sviluppo e le tecnologie impiegati in 2ndQuadrant per lo sviluppo di Barman e l’importanza fondamentale della collaborazione fra tutti i membri del team, grazie a cultura devops, kanban, metodologie agili, pair programming, testing, ecc.

Base di dati di test

Utilizziamo come base di dati le recensioni degli utenti di Amazon del 1998 in formato JSON. Il file customer_reviews_nested_1998.json.gz è scaricabile dal sito di Citus Data.
Il file, una volta decompresso, occupa 209 MB e contiene circa 600k record in formato JSON, con una struttura simile a quella seguente:

{
    "customer_id": "ATVPDKIKX0DER",
    "product": {
        "category": "Arts & Photography",
        "group": "Book",
        "id": "1854103040",
        "sales_rank": 72019,
        "similar_ids": [
            "1854102664",
            "0893815381",
            "0893816493",
            "3037664959",
            "089381296X"
        ],
        "subcategory": "Art",
        "title": "The Age of Innocence"
    },
    "review": {
        "date": "1995-08-10",
        "helpful_votes": 5,
        "rating": 5,
        "votes": 12
    }
}

Dimensioni

I dati possono essere caricati in un database PostgreSQL usando il tipo di dati JSONB con i seguenti comandi:

CREATE TABLE reviews(review jsonb);
\copy reviews FROM 'customer_reviews_nested_1998.json'
VACUUM ANALYZE reviews;

La tabella risultante occuperà circa 268 MB, con un costo aggiuntivo di memorizzazione su disco di circa il 28%. Se proviamo a caricare gli stessi dati usando il tipo JSON, che li memorizza come testo, il risultato sarà una tabella di 233 MB, con un incremento di spazio di circa l’11%. Il motivo di questa differenza è che le strutture interne di JSONB, che servono ad accedere ai dati senza analizzare ogni volta tutto il documento, hanno un costo in termini di spazio occupato.

Accesso ai dati

Una volta memorizzati i dati nel database, per potervi accedere in maniera efficiente è necessario creare un indice. Prima della versione 9.4 di PostgreSQL, l’unica opzione per indicizzare il contenuto di un campo contente JSON era quella di utilizzare un indice B-tree su un’espressione di ricerca specifica. Per esempio, se vogliamo effettuare ricerche per categoria di prodotto utilizzeremo:

CREATE INDEX on reviews ((review #>> '{product,category}'));

L’indice appena creato occupa 21 MB, cioè circa il 10% dei dati originali, e permetterà di eseguire query che abbiano all’interno della clausola WHERE l’espressione esatta “review #>> '{product,category}'”, come ad esempio:

SELECT
    review #>> '{product,title}' AS title,
    avg((review #>> '{review,rating}')::int)
FROM reviews
WHERE review #>> '{product,category}' = 'Fitness & Yoga'
GROUP BY 1 ORDER BY 2;
                       title                       |        avg
---------------------------------------------------+--------------------
 Kathy Smith - New Yoga Challenge                  | 1.6666666666666667
 Pumping Iron 2                                    | 2.0000000000000000
 Kathy Smith - New Yoga Basics                     | 3.0000000000000000
 Men Are from Mars, Women Are from Venus           | 4.0000000000000000
 Kathy Smith - Functionally Fit - Peak Fat Burning | 4.5000000000000000
 Kathy Smith - Pregnancy Workout                   | 5.0000000000000000
(6 rows)

La query impiega circa 0.180 ms per essere eseguita sulla macchina di test, ma l’indice che è stato creato è altamente specifico e non è usabile per ricerche diverse.
A partire dalla versione 9.4, il tipo di dati JSONB supporta l’utilizzo di indici inversi (GIN, General inverted Indexes), che permettono di indicizzare le componenti di un oggetto complesso.
Andiamo quindi a creare un indice GIN sulla nostra tabella reviews con in seguente comando:

CREATE INDEX on reviews USING GIN (review);

L’indice risultante occupa 64 MB su disco, che è circa il 30% della dimensione della tabella originale. Tale indice può essere utilizzato per velocizzare i seguenti operatori:

• JSON @> JSON è un sottoinsieme
• JSON ? TEXT contiene un valore
• JSON ?& TEXT[] contiene tutti i valori
• JSON ?| TEXT[] contiene almeno un valore

La query precedente deve quindi essere riscritta usando l’operatore @> per cercare le righe che contengono '{"product": {"category": "Fitness & Yoga"}}':

SELECT
    review #>> '{product,title}' AS title,
    avg((review #>> '{review,rating}')::int)
FROM reviews
WHERE review @> '{"product": {"category": "Fitness & Yoga"}}'
GROUP BY 1 ORDER BY 2;

La query impiega circa 1.100 ms per essere eseguita sulla macchina di test e l’indice che è stato creato è flessibile ed è possibile usarlo per qualsiasi ricerca all’interno dei dati JSON.

In realtà spesso la sola operazione utilizzata nelle applicazioni è la ricerca per sottoinsieme, in tal caso è possibile usare un indice GIN diverso, che supporta solo l’operazione @> ed è quindi considerevolmente più piccolo. La sintassi per creare questo tipo di indice “ottimizzato” è la seguente:

CREATE INDEX on reviews USING GIN (review jsonb_path_ops);

L’indice risultante occupa solamente 46 MB cioè solo il 22% della dimensione dei dati originale e grazie a questa sua dimensione ridotta viene usato da PostgreSQL con maggiore efficienza. Questo permette di eseguire la query precedente in soli 0.167 ms, con un incremento di prestazioni del 650% rispetto all’indice GIN originale e del 8% rispetto all’indice B-tree specifico usato inizialmente, il tutto senza perdere di generalità per quanto riguarda le possibili operazioni di ricerca.

Conclusioni

Con l’introduzione del tipo JSONB e gli indici GIN costruiti con gli operatori jsonb_path_ops, PostgreSQL unisce l’elasticità del formato JSON a una velocità di accesso ai dati strabiliante.
Oggi è quindi possibile memorizzare e elaborare dati in formato JSON con elevate prestazioni, godendo allo stesso tempo della robustezza e della flessibilità a cui PostgreSQL ci ha abituato negli anni.

Con l’introduzione del tipo di dato JSONB in PostgreSQL emerge definitivamente il lato “NoSQL” di questo DBMS relazionale, andando incontro a tutti coloro che prediligono una struttura dei dati in forma “chiave-valore” stile dizionario, molto usata in ambito sviluppo, garantendo allo stesso tempo tutti i vantaggi di un database relazionale.

Già PostgreSQL 9.2 prevedeva l’uso del tipo JSON, permettendo direttamente la persistenza su database di un dato JSON. Tuttavia, si trattava di fatto di un dato di tipo testo, con in più la capacità di validare la sintassi JSON. Col nuovo tipo di dato JSONB le informazioni sono memorizzate in un formato binario dedicato, potendo così beneficiare di algoritmi specifici che ne migliorano le prestazioni di accesso e ottimizzano la memorizzazione su disco:

• operatori avanzati di accesso e confronto: grazie alla sua struttura specializzata JSONB ha permesso l’implementazione di nuovi operatori, che, oltre a dare una maggiore flessibilità all’utente, permettono di usare tutta la potenza di indici hash, btree, GIST e GIN;
• dimensioni su disco ridotte: lo spazio di memorizzazione richiesto per memorizzare documenti con una struttura complessa con il dato JSONB è inferiore rispetto a quanto richiesto per il formato JSON;
• organizzazione interna come un dizionario con chiave univoca: questo significa che l’accesso è molto veloce, ma l’ordine di inserimento delle chiavi nella struttura JSONB non viene preservato. Inoltre, in presenza di chiavi duplicate, viene mantenuto solo l’ultimo valore inserito, a differenza di quanto accadeva nel dato JSON:

$ SELECT '{"a":1, "b":2}'::JSONB = '{"b":2, "a":1}'::JSONB
 ?column?
 --------
  t
 (1 row)

$ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSON
              JSON
  ----------------------------------------------
  {"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}
  (1 row)

$ SELECT '{"a":"abc", "d":"def","z":[1,2,3],"d":"overwritten"}'::JSONB
              JSON
  ----------------------------------------------
  {"a":"abc", "d":"overwritten","z":[1,2,3]}
  (1 row)

È bene comunque precisare che il dato JSONB è compatibile con tutte le funzioni introdotte per il dato JSON.

L’effetto della possibilità di indicizzare il tipo JSONB si traduce in una migliore disponibilità dei dati in lettura, permettendo di accedere in modo efficiente all’intero contenuto di un campo JSONB.

Questo rende possibile usare efficientemente PostgreSQL per analizzare dati privi di uno schema predefinito, avvicinandolo ulteriormente al mondo “NoSQL”. A tale proposito Thom Brown ha condotto alcuni test mostrando come si rilevi un aumento di prestazioni in lettura (ed un più ridotto spazio occupato dagli indici) rispetto a un campo JSON, arrivando a prestazioni in lettura superiori anche a DBMS tipicamente NoSQL quali MongoDB.

Conclusioni

Sicuramente l’introduzione del tipo JSONB avvicina PostgreSQL a quegli sviluppatori che abitualmente usano dati in formato JSON. Primi fra tutti, gli sviluppatori web che fanno ampio uso di JavaScript e che magari hanno già iniziato a lavorare con PostgreSQL usando il tipo JSON per memorizzare i dati. Passando a JSONB avranno la possibilità di usare tutta la potenza del motore di PostgreSQL per elaborare quei dati con facilità ed efficienza.

Nuove funzionalità aumenteranno flessibilità, scalabilità e prestazioni di PostgreSQL!

Flessibilità

Il nuovo tipo di dato JSONB di PostgreSQL 9.4, supporterà lookup veloci e query di ricerca semplici utilizzando gli indici GIN (Generalized Inverted Index). Gli utenti potranno estrarre e manipolare dati JSON con prestazioni che uguagliano e migliorano i database più comuni per la gestione di documenti.

Scalabilità

La decodifica logica (Logical Decoding) della versione 9.4, fornirà una nuova API per leggere, filtrare e manipolare il flusso di replica di PostgreSQL. Nuovi strumenti di replica, come la Replica Bi-Direzionale (BDR), e altri miglioramenti, come replication slot e standby in ritardo (time-delayed), semplificheranno la gestione e aumenteranno l’utilità dei server in replica.

Prestazioni

La versione 9.4 introduce miglioramenti che permetteranno agli utenti di sfruttare ancora di più i loro server PostgreSQL, fra cui:

• Indici GIN fino al 50% più piccoli e fino a 3 volte più veloci
• Viste Materializzate aggiornabili in modalità  concorrente per reportistica più veloce e più aggiornata
• Ricaricamento veloce della cache di un database PostgreSQL dopo un restart grazie a pg_prewarm
• Scrittura in parallelo più veloce sul log delle transazioni di PostgreSQL

Vuoi saperne di più?

Per ulteriori informazioni e spiegazioni sulle funzionalità aggiunte in PostgreSQL 9.4, si consiglia la consultazione del press kit ufficiale rilasciato dalla Comunità.

Segui inoltre la nostra serie di articoli in italiano su PostgreSQL 9.4, a partire dalle novità dedicate agli amministratori di sistema.

Finalmente ci siamo: l’ottava edizione del PGDay italiano è al via!
La principale conferenza annuale a livello italiano sul database open source PostgreSQL si terrà a Prato questo venerdì, 7 novembre 2014, presso il Polo Universitario Città di Prato (PIN), sede distaccata dell’Università degli Studi di Firenze.

L’evento è organizzato dall’associazione no-profit Italian PostgreSQL Users Group (ITPUG) con l’obiettivo di promuovere il software libero e open source, ed in particolare l’adozione di PostgreSQL (o semplicemente Postgres) come soluzione per la gestione di database nelle aziende, nella pubblica amministrazione e nelle scuole.

La registrazione al PGDay italiano 2014 ha un costo complessivo di 90 euro (comprendente coffee break e pranzo). Per gli studenti, la quota di iscrizione è fissata a 30 euro.

In occasione della conferenza, sono previsti tre eventi sociali:

• pg_birra_pre, giovedì 6 novembre, ore 18: Interlogica, uno dei partner dell’evento, offrirà alcune consumazioni – Presso il pub Camelot 3.0 in via Santo Stefano 20-22, accanto al Duomo di Prato
• pg_cena, giovedì 6 novembre, ore 20 : Menu PGDay, acquistabile via Internet: 18 € (primo, secondo, contorni, una birra chiara o un bicchiere di vino, caffè) – Presso il pub Camelot 3.0 in via Santo Stefano 20-22, accanto al Duomo di Prato
  
• pg_birra_post, venerdì 7 novembre, ore 18

Per coloro che venerdì 7 novembre intendono continuare a festeggiare il PGDay dopo la birra e rimanere insieme per cena, 2ndQuadrant ha organizzato la “Elephant Pizzata”, presso il Wallace Pub Piazza Mercatale 24 (inizio dalle ore 20.30). È possibile acquistare il biglietto della cena al desk di 2ndQuadrant durante il PGDay.

Alle ore 22 circa inizierà il concerto di Nick Becattini, uno dei principali chitarristi blues del panorama italiano (aperto a tutti).

2ndQuadrant, partner “diamond” del PGDay, sarà presente con il team italiano al completo e presenterà i seguenti talk:

• Monitoraggio di PostgreSQL in un ambiente devops – Francesco Canovai
• Teoremi sociali con PostgreSQL – Gianni Ciolli, con Marco Maggesi e Alberto Mancini dell’Università di Firenze
• Implementare un sistema di Audit con il Logical Decoding – Gianni Ciolli
• PostgreSQL 9.4 per devops – Gabriele Bartolini
• HSTORE, JSON e JSONB: dati non strutturati ad alte performance – Marco Nenciarini
• Uso degli indici nei database geospaziali con PostgreSQL – Giuseppe Broccolo
• Implementare uno scheduler in User Space – Gianni Ciolli

Simon Riggs, Fondatore e CTO di 2ndQuadrant e major developer e committer del progetto PostgreSQL, presenterà lo stato attuale di “BDR (Bi-Directional Replication)”, interamente progettata e sviluppata dal team di 2ndQuadrant e disponibile in modalità open source.

A dare il via alla manifestazione, Gabriele Bartolini, Managing Director di 2ndQuadrant Italia, con il suo Keynote.

Il personale di 2ndQuadrant sarà felice di accogliervi al desk per qualsiasi informazione.

Vi aspettiamo numerosi.

PostgreSQL 9.2 ha introdotto il supporto alla memorizzazione all’interno del database di dati di tipo JSON (JavaScript Object Notation), formato ideato per lo scambio di dati in applicazioni client-server. PostgreSQL 9.3 introduce importanti funzioni native per la creazione e la manipolazione di dati JSON, oltre a operatori per l’accesso a informazioni contenute in dati JSON.

Per quanto riguarda la generazione di valori JSON, PostgreSQL 9.3 introduce due funzioni:

• to_json(anyelement): converte un valore in JSON, utilizzando il casting oppure la rappresentazione testuale a seconda del tipo di dato
• json_agg(record): aggrega un set di record come un array di oggetti JSON

Il seguente esempio mostra la funzione to_json, visualizzando la serie dei primi 10 numeri interi e il rispettivo quadrato tramite una query WITH:

WITH a AS (
 SELECT n, n^2 AS q FROM generate_series(1, 10) n
)
SELECT to_json(a) AS json FROM a;

Il risultato è un insieme di record (10 per la precisione), ciascuno rappresentato da un oggetto JSON composto da due membri con chiave n e q:

       json
------------------
 {"n":1,"q":1}
 {"n":2,"q":4}
 {"n":3,"q":9}
 {"n":4,"q":16}
 {"n":5,"q":25}
 {"n":6,"q":36}
 {"n":7,"q":49}
 {"n":8,"q":64}
 {"n":9,"q":81}
 {"n":10,"q":100}
(10 rows)

Il successivo esempio applica la funzione aggregata json_agg, alla query precedente:

WITH a AS (
 SELECT n, n^2 AS q FROM generate_series(1, 10) n
)
SELECT json_agg(a) AS json FROM a;

Il risultato è una rappresentazione sintetica di un singolo valore JSON composto da un array di oggetti JSON con caratteristiche simili al precedente esempio:

        json
--------------------
 [{"n":1,"q":1},   +
  {"n":2,"q":4},   +
  {"n":3,"q":9},   +
  {"n":4,"q":16},  +
  {"n":5,"q":25},  +
  {"n":6,"q":36},  +
  {"n":7,"q":49},  +
  {"n":8,"q":64},  +
  {"n":9,"q":81},  +
  {"n":10,"q":100}]
(1 row)

Inutile sottolineare l’impatto di queste nuove funzionalità nello sviluppo, in particolare, di applicazioni web e mobile che memorizzano e recuperano dati JSON direttamente da PostgreSQL e le veicolano, ad esempio, con JQuery. Al prossimo articolo sulle novità di PostgreSQL 9.3!

JSON, acronimo di JavaScript Object Notation, è un formato ideato per lo scambio di dati in applicazioni client-server.

PostgreSLQ 9.2 introduce il supporto al tipo di dato JSON.

Per verificarne la presenza, è sufficiente digitare da psql il comando \dTS pg_catalog.json.

Un valore di tipo JSON è in grado di accettare soltanto stringhe JSON valide. Inserire una stringa JSON non valida in un dato di tipo JSON genera un’eccezione.

Vi sono inoltre due funzioni per la conversione a JSON di array e righe, chiamate rispettivamente array_to_json e row_to_json.

L’esempio sottostante mostra come sia possibile convertire in JSON una riga del catalogo di PostgreSQL, in particolare quella che contiene informazioni circa il database postgres.

select row_to_json(d) from pg_database d WHERE datname='postgres';

Il risultato sarà qualcosa di simile a:

       row_to_json

 -----------------------------------------------------------------------------------------------------------
 -----------------------------------------------------------------------------------------------------------
 -------------------------
  {"datname":"postgres","datdba":10,"encoding":6,"datcollate":"en_US.UTF-8","datctype":"en_US.UTF-8","datist
emplate":false,"datallowconn":true,"datconnlimit":-1,"datlastsysoid":12002,"datfrozenxid":"671","dattablesp
ace":1663,"datacl":null}
 (1 row)

Abbastanza illeggibile, vero?

Per avere una formattazione più carina, basterà aggiungere un parametro booleano impostato a True alla funzione row_to_json:

select row_to_json(d, 'True') from pg_database d WHERE datname='postgres';

Che restituirà qualcosa di simile a:

         row_to_json
------------------------------
 {"datname":"postgres",      +
  "datdba":10,               +
  "encoding":6,              +
  "datcollate":"en_US.UTF-8",+
  "datctype":"en_US.UTF-8",  +
  "datistemplate":false,     +
  "datallowconn":true,       +
  "datconnlimit":-1,         +
  "datlastsysoid":12002,     +
  "datfrozenxid":"671",      +
  "dattablespace":1663,      +
  "datacl":null}
(1 row)

Decisamente meglio.

Uno dei vantaggi del formato JSON è rappresentato dall’elevato numero di applicazioni esistenti che lo supportano. Risulterà quindi estremamente semplice convertire una stringa JSON in formati diversi, ad esempio una tabella HTML. Inoltre, sarà interessante valutare sviluppi futuri di questa funzionalità insieme alla tecnologia websocket di HTML5.

Per adesso il supporto a JSON è limitato. Mancano infatti funzioni per la visita di singole coppie chiave/valore, anche se sono state poggiate le basi per evoluzioni future.