Dopo il successo ottenuto con la prima edizione del libro, uscita nel 2010, i nostri Gianni e Gabriele si uniscono a Simon e Hannu e insieme ne aggiornano i contenuti, concentrandosi sulle caratteristiche più importanti introdotte nel frattempo, fino alla versione 9.4 di PostgreSQL, la più recente.

Questo “libro di ricette” è una guida pratica che vi permetterà di comprendere, gestire senza problemi e sfruttare al meglio il più avanzato sistema open source per la gestione di database.

Un toolkit necessario, e rivolto ad amministratori e sviluppatori di database PostgreSQL.

Grazie ai preziosi contenuti di questo libro:

• Imparerai come implementare le funzionalità di PostgreSQL dedicate all’affidabilità e alla performance
• Vedrai come sfruttare la potenza delle più recenti funzionalità di PostgreSQL
• Saprai gestire tutte le versioni di PostgreSQL attualmente supportate dalla Comunità, ossia: 9.0, 9.1, 9.2, 9.3 e 9.4
• Otterrai consigli tecnici di livello avanzato, rivolti agli utenti più esperti
• Esplorerai in tempo reale le buone prassi nel pianificare e progettare le basi di dati
• Saprai selezionare e implementare tecniche robuste di backup e ripristino
• Avrai una guida chiara e concisa in merito a replica e alta disponibilità
• Scoprirai gli ultimi dettagli su replica logica e replica bidirezionale

Il libro può essere comprato sul sito web dell’editore Packt, sia in versione  ebook che cartacea: https://www.packtpub.com/big-data-and-business-intelligence/postgresql-9-administration-cookbook-second-edition.

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.

Il FOSS4G Nord America si terrà presso l’Hyatt Regency San Francisco Airport a Burlingame, California, dal 9 al 12 marzo 2015.

L’evento riunisce sviluppatori, utenti, decisori e osservatori da un ampio spettro di organizzazioni e campi di attività. Attraverso quattro giorni di workshop, presentazioni, discussioni, offre opportunità per la comunità FOSS4G per imparare, esplorare, condividere e collaborare sulle ultime idee e informazioni.

Martedì 10, alle 16.15, Gianni parlerà di decodifica logica, una importante caratteristica introdotta con la versione 9.4 di PostgreSQL.

Dopo una breve introduzione, Gianni si concentrerà sull’applicazione della decodifica logica per il controllo specifico di un database, confrontando questa soluzione con l’approccio tradizionale in termini di:

• semplicità
• prestazioni
• separazione tra dati in tempo reale e dati di audit

Per maggiori informazioni sull’evento: https://2015.foss4g-na.org

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.

Dalla versione 9.3 di PostgreSQL è possibile aggiornare e inserire nuovi dati direttamente su viste. Le operazioni vengono in modo trasparente e automatico indirizzate sulla tabella sottostante (è infatti necessario che la struttura delle viste sia semplice – ad esempio, che ci sia un solo elemento FROM).
Con PostgreSQL 9.4 è possibile aggiungere l’opzione CHECK per l’INSERT sulla vista.

Consideriamo, ad esempio, una tabella contenente un solo campo di numeri interi e due viste, una relativa ai numeri della tabella divisibili per 2 e l’altra relativa ai numeri divisibili per 3.

Proviamo ad inserire il numero 123 sulla prima vista, chiamata first:

$ CREATE TABLE some_data(id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id%2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM some_data WHERE 0 = id%3;
CREATE VIEW
$ INSERT INTO first(id) VALUES (123);

Questo verrà inserito nella tabella sottostante (some_data), nonostante la vista sia dedicata solo ai numeri divisibili per 2 (ma non sarà visibile in essa).
L’opzione CHECK in PostgreSQL 9.4 serve proprio a gestire i casi di inserimento sulle viste eseguendo preventivamente un controllo dei valori che si intende inserire, compatibilmente con la definizione della vista.

Sono previste due possibili opzioni:

• CASCADED CHECK (default)  in cui i check vengono applicati in cascata anche sulle altre viste eventualmente presenti sulla stessa tabella;
• LOCAL CHECK in cui i check vengono applicati sulla singola vista in cui viene effettuato la INSERT.

Riprendendo l’esempio già descritto, proviamo a utilizzare l’opzione CHECK:

$ CREATE TABLE some_data (id int4 PRIMARY KEY);
CREATE TABLE
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH CHECK OPTION;
CREATE VIEW
$ INSERT INTO first(id) VALUES (14);
INSERT 0 1
$ INSERT INTO first(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Il valore 14 viene correttamente inserito nella prima vista, mentre il valore 15 no – come è lecito attendersi.
Meno chiaro è l’errore sull’inserimento di 15 sulla seconda vista: non viene inserito (seppure divisibile per 3) in quanto l’opzione CHECK deve essere definita in una sola delle due viste, e di tipo LOCAL CHECK.

Non è sufficente definire su entrambe le viste l’opzione LOCAL CHECK per aggirare il problema:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
ERROR:  new row violates WITH CHECK OPTION for view "first"

Di seguito l’esempio funzionante:

$ DROP VIEW first;
DROP VIEW
$ DROP VIEW second;
DROP VIEW
$ CREATE VIEW first AS SELECT * FROM some_data WHERE 0 = id % 2;
CREATE VIEW
$ CREATE VIEW second AS SELECT * FROM first WHERE 0 = id % 3 WITH LOCAL CHECK OPTION;
CREATE VIEW
$ INSERT INTO second(id) VALUES (15);
INSERT 0 1

La clausola CHECK sulle viste aggiornabili rappresenta un nuovo meccanismo di controllo da inserire nel database, direttamente sull’inserimento di nuovi dati utilizzando viste. Un ulteriore passo che rafforza sempre di più il ruolo del database in materia di applicazione di integrità dei dati.

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.

Tra i temi trattati nel corso del convegno:

• Il software Open Source per il mondo Enterprise
• Il Codice dell’Amministrazione Digitale: cosa dice in ambito Open Source per la Pubblica Amministrazione?
• Quali sono le caratteristiche tecniche e come si è evoluto il mondo delle applicazioni Open Source in azienda?
• Quali sono, per un’azienda interessata a valutare soluzioni Open, i vantaggi tecnologici e in termini di investimenti?
• Le soluzioni Open Source: perché diventano oggi un’opportunità?
• Come è evoluta e quale ruolo riveste oggi “la Community”?

2ndQuadrant parteciperà alla conferenza con un interessante talk dal titolo “Perché adottare PostgreSQL?”.

Gabriele Bartolini, Managing Director di 2ndQuadrant Italia nonché uno dei soci fondatori di Italian PostgreSQL Users Group (ITPUG, 2007) e di PostgreSQL Europe (2008), presenterà il database open source più avanzato al mondo e spiegherà perché affidarsi a Postgres è una scelta strategica in grado di scatenare una vera rivoluzione culturale all’interno di un’organizzazione.

2ndQuadrant, sponsor della conferenza, sarà inoltre presente con uno stand, pronto ad accogliervi per qualsiasi informazione e a valutare l’adozione di PostgreSQL nella vostra realtà.

Vi aspettiamo numerosi.

La partecipazione all’Open Source Conference 2014 è gratuita, previa iscrizione.

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.

Le viste materializzate sono state introdotte in PostgreSQL 9.3 e risultano essere particolarmente utili per query di lunga durata da ripetersi più volte. PostgreSQL 9.4 aggiunge la possibilità di eseguire il REFRESH completo di una vista materializzata in modalità concorrente.

Una vista materializzata è una vista particolare che permette di rendere persistente, memorizzandolo su disco come una qualsiasi tabella, il risultato della propria esecuzione, creando una vera istantanea della situazione in quel momento all’interno del database.

Per questo motivo, agendo su una copia statica dei dati, i tempi di esecuzione di query su viste materializzate sono notevolmente inferiori rispetto a quelli su viste classiche. Inoltre, è possibile anche creare indici ad-hoc sulle viste materializzate, che risultano pertanto molto adatte in contesti di business intelligence e di data warehousing.

In PostgreSQL 9.3, l’unico sistema per aggiornare una vista materializzata è tramite il comando REFRESH, i cui tempi di esecuzione sono paragonabili a quelli di creazione della vista stessa (essendo l’aggiornamento completo e, non ancora, incrementale come in altri DBMS commerciali).
Ben più grave, però, è il fatto che, durante l’esecuzione del REFRESH, Postgres acquisisce sulla vista un AccessExclusiveLock, andando di fatto a bloccare tutti gli accessi concorrenti, anche di lettura.

Con la versione 9.4 di PostgreSQL è possibile lanciare il REFRESH di una vista materializzata in modo concorrente.

Supponiamo di avere una tabella t nel database mydb definita in questo modo:

CREATE TABLE t(
        i serial PRIMARY KEY,
        t timestamp with time zone DEFAULT clock_timestamp()
);

col campo i chiave primaria. Inseriamo poi 50 milioni di record nella tabella t:

mydb=# \timing
Timing is on.
mydb=# INSERT INTO t SELECT generate_series(1, 50000000) AS i;
INSERT 0 50000000
Time: 236580.268 ms

Creiamo adesso una vista materializzata (t_v) sulla query che effettua una SELECT dei record di t generati nei primi 5 secondi di ogni minuto:

mydb=# SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
[...]
Time: 135119.698 ms
mydb=# CREATE MATERIALIZED VIEW t_v AS SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
SELECT 3433227
Time: 29146.775 ms
mydb=# SELECT * FROM t_v;
[...]
Time: 4576.630 ms

È possibile vedere come i tempi di creazione della vista (che contiene 3433227 record estratti da t) siano paragonabili a quelli di esecuzione della query, mentre un comando SELECT lanciato sulla vista risulta essere circa 7-8 volte più veloce.

Sebbene l’operazione non abbia molto senso sul piano pratico, aggiorniamo adesso la tabella t, assegnando ai timestamp dei record generati nei primi 5 secondi di ogni minuto il valore now() (al momento in cui è stato lanciato questo comando di esempio il timestampo reso era pari a
2014-08-04 13:50:15.779483+00):

UPDATE t SET t=now() WHERE extract(second FROM t) BETWEEN 0 AND 5;

Adesso tabella e vista risulteranno disallineati (mentre la tabella t non prevede più record con timestamp compreso tra i primi 5 secondi di ogni minuto, la vista contiene ancora i vecchi dati non aggiornati):

mydb=# UPDATE t SET t=now() WHERE extract(second FROM t) BETWEEN 0 AND 5;
UPDATE 3433227
Time: 55795.959 ms
mydb=# SELECT * FROM t WHERE extract(second FROM t) BETWEEN 0 AND 5;
 i | t
---+---
 (0 rows)

Time: 28219.871 ms
mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
    i    |               t
---------+-------------------------------
 6485918 | 2014-08-04 08:32:00.000002+00
 6485919 | 2014-08-04 08:32:00.000005+00
 6485920 | 2014-08-04 08:32:00.000007+00
 6485921 | 2014-08-04 08:32:00.00001+00
 6485922 | 2014-08-04 08:32:00.000012+00
 6485923 | 2014-08-04 08:32:00.000015+00
 6485924 | 2014-08-04 08:32:00.000018+00
 6485925 | 2014-08-04 08:32:00.00002+00
 6485926 | 2014-08-04 08:32:00.000023+00
 6485927 | 2014-08-04 08:32:00.000025+00
 (10 rows)

Time: 406.141 ms

Proviamo a lanciare, all’interno di una transazione (in modo da permetterci di effettuare ROLLBACK in un secondo tempo), il REFRESH della vista materializzata.

Apriamo quindi una seconda connessione sul database mydb, ed impostiamo il parametro statement_timeout a 10 secondi in modo che la connessione cada se l’esecuzione di una query supera in durata il timeout:

                 prima connessione

mydb=# BEGIN;
BEGIN
mydb=# REFRESH MATERIALIZED VIEW t_v;
REFRESH MATERIALIZED VIEW

                 seconda connessione

mydb=# set statement_timeout = 10000;
SET
mydb=# SELECT * FROM t_v;
ERROR:  canceling statement due to statement timeout

Tenuto conto che la SELECT sulla vista non supera il secondo di esecuzione, la perdita di connessione per timeout è sintomo del lock presente sulla vista stessa.

Eseguiamo ROLLBACK nella transazione aperta con il REFRESH, per lanciare un nuovo REFRESH, questa volta concorrente (sempre all’interno di una transazione in modo che resti “in sospeso” fino al successivo COMMIT):

mydb=# BEGIN;
BEGIN
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
ERROR:  cannot refresh materialized view "public.t_v" concurrently
HINT:  Create a UNIQUE index with no WHERE clause on one or more columns of the materialized view.
mydb=# ROLLBACK;
ROLLBACK

Dopo aver annullato la transazione precedente con ROLLBACK (comunque invalidata dall’errore), ne
rilanciamo una nuova creando opportunamente un indice con vincolo di unicità:

mydb=# \timing
Timing is on.
mydb=# CREATE UNIQUE INDEX idx_i on t_v (i);
CREATE INDEX
Time: 3466.765 ms
mydb=# BEGIN;
BEGIN
Time: 0.118 ms
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
REFRESH MATERIALIZED VIEW
Time: 50522.136 ms

mentre avviene il REFRESH concorrente della vista, proviamo ad interrogare la vista da una seconda
connessione, in cui abbiamo sempre attivato lo statement_timeout a 10s:

mydb=# SET statement_timeout = 10000;
SET
mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
    i    |               t
---------+-------------------------------
 6485918 | 2014-08-04 08:32:00.000002+00
 6485919 | 2014-08-04 08:32:00.000005+00
 6485920 | 2014-08-04 08:32:00.000007+00
 6485921 | 2014-08-04 08:32:00.00001+00
 6485922 | 2014-08-04 08:32:00.000012+00
 6485923 | 2014-08-04 08:32:00.000015+00
 6485924 | 2014-08-04 08:32:00.000018+00
 6485925 | 2014-08-04 08:32:00.00002+00
 6485926 | 2014-08-04 08:32:00.000023+00
 6485927 | 2014-08-04 08:32:00.000025+00
 (10 rows)

Anche se il REFRESH non è terminato (impiega 50 secondi circa, comunque non termina finché non lanciamo il COMMIT della transazione) è possibile accedere alla vista materializzata, seppure mostri ancora i dati non aggiornati con il comando UPDATE. Proviamo quindi ad eseguire il COMMIT, ed a vedere cosa si osserva nella seconda connessione:

            prima connessione

mydb=# CREATE UNIQUE INDEX idx_i on t_v (i);
CREATE INDEX
Time: 3466.765 ms
mydb=# BEGIN;
BEGIN
Time: 0.118 ms
mydb=# REFRESH MATERIALIZED VIEW CONCURRENTLY t_v;
REFRESH MATERIALIZED VIEW
Time: 50522.136 ms
mydb=# COMMIT;
COMMIT
Time: 1.134 ms

            seconda connessione

mydb=# SELECT * FROM t_v ORDER BY i LIMIT 10;
 i | t
---+---
 (0 rows)

Time: 889.921 ms

Una volta quindi terminato il REFRESH della vista materializzata è possibile accederci mostrando
i dati aggiornati (adesso anche la vista non prevede più record con timestamp compreso tra i primi 5 secondi di ogni minuto).

In conclusione, con la versione 9.4 di PostgreSQL le viste materializzate non acquisiscono lock durante l’aggiornamento della vista stessa, permettendo l’accesso ai dati in modo concorrente (seppure i dati siano aggiornati all’ultima operazione di refresh).

Per maggiori informazioni:

• http://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html