La clausola WITHIN GROUP

La clausola WITHIN GROUP è particolarmente utile nei casi in cui si vogliano effettuare aggregazioni su subset ordinati di dati.
PostgreSQL ha introdotto, fin dalla versione 9.0, le window function per poter lavorare su subset di dati correlabili a ciascun record corrente delle tabelle, definendo una sorta di “finestre di aggregazione” centrate su ogni specifico record man mano che la query viene eseguita tramite la clausola SQL OVER (PARTITION BY/ORDER BY) e sfruttando tali funzioni che possono essere eseguite su tali aggregazioni.

Con la versione 9.4 di PostgreSQL è stata introdotta la clausola SQL WITHIN GROUP che permette di semplificare molte operazioni finora possibili solo con l’uso delle window function, definendo aggregazioni di subset ordinati di dati.
Sono state introdotte, inoltre, nuove funzioni che possono essere applicate su tali subset e che ampliano la collezione delle window function presenti:

• percentile_cont(), percentile_disc() per il calcolo di percentili;
• mode() funzione statistica che calcola la moda su subset ordinati;
• rank(), dense_rank(), percent_rank(), cume_dist(): window function già presenti in PostgreSQL per essere eseguite sui subset ottenuti tramite la clausola OVER (PARTITION BY/ORDER BY ) e da adesso in grado di prendere come parametro subset ordinati prodotti con la clausola WITHIN GROUP.

Per capire meglio, supponiamo ad esempio di voler calcolare il 25°, il 50°, il 75° ed il 100° percentile dei primi 20 numeri interi. Finora era possibile solo partizionando i numeri in 4 set tramite la clausola OVER (PARTITION BY/ORDER BY) per poi ordinarli internamente in 4 subset ordinati di cui poi prendiamo il massimo valore, ad esempio sfruttando una CTE:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ WITH subset AS (
    SELECT val,
       ntile(4) OVER (ORDER BY val) AS tile
    FROM t
  )
  SELECT max(val)
  FROM subset GROUP BY tile ORDER BY tile;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Con PostgreSQL 9.4 tutto si riduce ad un solo comando SQL, comportando notevoli vantaggi in termini di leggibilità degli script e di esecuzione dei comandi:

$ CREATE TABLE t AS SELECT generate_series(1,20) AS val;

$ SELECT unnest(percentile_disc(array[0.25,0.5,0.75,1])
    WITHIN GROUP (ORDER BY val))
  FROM t;

   max
  ------
   5
  10
  15
  20
 (4 rows)

Clausola FILTER di SQL

Questa seconda clausola dei comandi SQL è utile nei casi in cui si vogliano applicare dei filtri su subset di dati senza necessariamente eseguire aggregazioni.
Ad esempio, è ora possibile effettuare un count totale dei record di una tabella ed anche parziale di un suo subset che soddisfi una certa condizione (espressa mediante la clausola WHERE) all’interno di una unica query, senza doverne usare ulteriori da eseguire sulle aggregazioni:

$ SELECT count(*) count_all,
         count(*) FILTER(WHERE bid=1) count_1,
         count(*) FILTER(WHERE bid=2) count_2
  FROM pgbench_history;

 count_all | count_1 | count_2
 ----------+---------+---------­­­­­­­­­
      7914 |     758 |     784
 (1 row)

Semplificando, anche in questo caso, la leggibilità degli script e migliorando le performance in esecuzione.

Conclusioni

L’estensione dello standard SQL tramite l’introduzione di queste nuove clausole facilita ulteriormente il compito degli sviluppatori, che si trovano a poter delegare sempre più al database la manipolazione e l’aggregazione di subset di dati.
Con la clausola WITHIN GROUP diventa più semplice la gestione di subset di dati ordinabili, introducendo nuove window function. La clausola FILTER facilita la gestione di subset di dati che soddisfano certe condizioni, evitando le aggregazioni.

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.

Barman 1.4.0: le modifiche più importanti

L’ultima release aggiunge una nuova modalità di backup: il backup incrementale, che permette il riuso dei file non modificati fra un backup periodico e l’altro, riducendo drasticamente i tempi di esecuzione, la banda utilizzata e lo spazio occupato su disco.
Altra nuova funzionalità introdotta con la 1.4.0 è l’integrazione di Barman con la vista pg_stat_archiver, disponibile dalla versione 9.4 di PostgreSQL. Questa vista permette di raccogliere informazioni sulle performance del processo di archiviazione dei WAL e di monitorarne lo stato.
La gestione dei WAL file è migliorata. Il calcolo delle statistiche di archiviazione è stato snellito e ottimizzato. È stata potenziata la logica relativa alla rimozione dei WAL obsoleti, andando a effettuare azioni diverse nel caso di backup esclusivi o concorrenti.
I messaggi prodotti in caso di errore sono stati migliorati, rendendoli, dove possibile, più chiari e leggibili.
Abbiamo inoltre investito nella robustezza del codice: con Barman 1.4.0 disponiamo di circa 200 test di unità che vengono eseguiti ad ogni patch.

Backup Incrementale

Approfondiamo adesso la principale novità di questa release: il backup incrementale.

Definizione e teoria alla base

Per comprendere la logica su cui si basa il backup incrementale, prendiamo in considerazione due backup completi e consecutivi. Nell’intervallo di tempo che intercorre fra il completamento del primo backup e il completamento del backup successivo, non tutti i file contenuti all’interno della directory PGDATA vengono modificati. Alcuni file del backup più vecchio e del più recente, sono identici e perciò risultano ridondanti, richiedendo tempo e banda per essere trasferiti via rete e occupando spazio sul disco una volta terminata la copia. Se compariamo i file che compongono il backup più vecchio con i file che stiamo per andare a copiare dal server remoto, è possibile distinguere l’insieme di file che ha subito modifiche da quello che invece è rimasto invariato.
Con il backup incrementale diventa quindi possibile eliminarne la ridondanza, andando a copiare solamente i file modificati.

Implementazione e vantaggi tangibili

Abbiamo sviluppato questa funzionalità ponendoci tre obiettivi:

• riduzione del tempo di esecuzione di un backup;
• riduzione dell’utilizzo di banda;
• riduzione dello spazio occupato eliminando le ridondanze (deduplicazione).

Per ottenerli abbiamo sfruttato la capacità di Rsync di comparare una lista di file ricevuta da un server remoto, con il contenuto di una directory locale, identificando quali siano stati modificati o meno. Abbiamo aggiunto quindi una nuova opzione di configurazione per server/globale chiamata reuse_backup. Questa opzione identifica il tipo di backup che verrà effettuato.
Vediamo i tre possibili valori di reuse_backup e i loro effetti:

• off: valore di default, backup classico;
• copy: identifica sul server remoto l’insieme di file modificati, utilizzando l’ultimo backup eseguito come base. Solamente i file che risultano modificati vengono trasferiti via rete, diminuendo i tempi di esecuzione di un backup e risparmiando banda. Al termine del trasferimento dal backup usato come base, vengono copiati i file non modificati, ottenendo così un backup completo;
• link: identifica i file modificati e ne esegue la copia, esattamente come l’opzione copy. Al termine del trasferimento, il riuso dei file identificati come non modificati non viene ottenuto copiandoli, ma utilizzando degli hard link. In questo modo viene ottimizzato lo spazio sul disco occupato dal backup ed effettivamente si eliminano le ridondanze (deduplicazione).

È inoltre possibile utilizzare la seguente opzione --reuse-backup [{copy, link, off}] da linea di comando per modificare il comportamento di default per un singolo backup.
Per esempio:

$> barman backup --reuse-backup link main

forzerà il riuso del backup utilizzando gli hard link indipendentemente dal valore impostato all’interno del file di configurazione.
Utilizzerò adesso come “caso di studio” Navionics, uno dei nostri clienti e sponsor di questa release che, come vedremo, trae grossi vantaggi dall’utilizzo del backup incrementale. Navionics possiede database di notevoli dimensioni (uno dei più grandi arriva a circa 13 Terabyte). Prima dell’introduzione del backup incrementale, tenendo conto delle caratteristiche del server e della rete:

• sarebbero state necessarie circa 52 ore per completare un backup;
• sarebbero stati effettivamente copiati 13 TiB di dati tramite la rete;
• sarebbero stati occupati effettivamente 13 TiB sul disco.

Con Barman 1.4.0, utilizzando reuse_backup=link e facendo barman show-backup di un backup appena terminato, Navionics ottiene:

Base backup information:
  Disk usage           : 13.2 TiB (13.2 TiB with WALs)
  Incremental size     : 5.0 TiB (-62.01%)

Il tempo di esecuzione del backup è sceso drasticamente da 52 ore a 17 ore circa. I vantaggi sono quindi evidenti:

• il tempo di esecuzione diminuisce del 68% circa;
• sono stati copiati via rete solo 5.0 TiB di dati al posto di 13 TiB (-62%);
  
• lo spazio occupato sul disco è 5.0 TiB al posto di 13 TiB (-62%).

pg_stat_archiver: integrazione in Barman 1.4.0

Fra le novità introdotte da PostgreSQL 9.4 abbiamo la vista pg_stat_archiver che fornisce dati utili riguardanti lo stato di funzionamento del processo di archiviazione dei WAL. Grazie a queste statistiche è possibile inoltre fare previsioni sullo spazio che un nuovo backup andrà a occupare. Gli utenti di Barman 1.4.0 e PostgreSQL 9.4 potranno notare la comparsa di alcuni nuovi campi all’interno dell’output dei seguenti comandi:

• barman check:
  • il campo booleano is_archiving che indica lo stato del processo di archiviazione.
• barman status:
  • last_archived_time riporta l’ora di archiviazione dell’ultimo WAL file;
  • failed_count il numero di tentativi di archiviazione di WAL falliti;
  • server_archived_wals_per_hour il tasso di archiviazione di WAL/ora;
• barman show-server aggiunge all’insieme delle statistiche del server tutti i campi che compongono la vista pg_stat_archiver.

Conclusioni

Il backup incrementale, funzionalità principale di questa release, è sicuramente uno strumento di grande utilità per tutti, permettendo di salvare tempo e spazio anche su database di dimensioni modeste. Diventa invece quasi indispensabile per tutti coloro che devono amministrare database di grosse dimensioni (VLDB) o che contengono un grosso numero di tabelle in sola lettura, fornendo un notevole incremento di prestazioni in termini di spazio occupato, tempo e banda.
Aggiungendo l’integrazione con pg_stat_archiver su PostgreSQL 9.4 migliora la capacità di monitorare lo stato dei server e quindi la salute e la robustezza di tutte quelle infrastrutture che scelgono Barman come soluzione di disaster recovery di database PostgreSQL.

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

Pincipali Feature

Le nuove feature più importanti disponibili per il test nella versione Beta, includono:

• Il nuovo tipo JSONB, comprendente indici e operatori per dati di documento.
• La nuova API di Data Change Streaming permette la decodifica e la trasformazione dello stream di replica.
• Le viste materializzate con la funzione “Refresh Concurrently”.
• ALTER SYSTEM SET, che consente modifiche a postgresql.conf dalla riga di comando SQL.

Queste feature ampliano le capacità di PostgreSQL, introducono nuove sintassi, API e interfacce di gestione.

Altre funzionalità

Ci sono molte altre funzionalità nella versione Beta della 9.4 e tutte hanno bisogno di essere testate da voi:

• Dynamic Background Worker
• Replication Slot
• Miglioramenti nella scalabilità in scrittura
• Miglioramenti di performance per funzioni aggregate
• Riduzione del volume dei WAL
• Indici GIN del 50% più piccoli e veloci
• Viste “security barrier” aggiornabili
• Nuove funzioni per manipolare array e tabelle
• Standby ritardati
• Aggiornamenti MVCC del catalogo di sistema
• Diminuzione del livello di lock per alcuni comandi ALTER TABLE
• Controllo della velocità per il backup
• WITHIN GROUP

Ci sono anche molti cambiamenti interni al funzionamento di Write Ahead Log (WAL), indici GIN, replica, aggregazione, e gestione del catalogo del sistema. In pratica, abbiamo bisogno del vostro aiuto per trovare qualsiasi nuovo bug che potremmo aver introdotto in queste aree prima di rilasciare la release 9.4.

Per un elenco completo delle feature della versione Beta 9.4, potete far riferimento alle note di rilascio. Descrizioni aggiuntive e informazioni sulle nuove feature sono disponibili sul Wiki, alla pagina 9.4 Features Wiki Page.

Testa la versione Beta 1 di 9.4 Beta adesso

Abbiamo bisogno della nostra Community per avere supporto nei test della nuova versione, al fine di garantire elevate prestazioni e l’assenza di bug. Vi preghiamo di effettuare il download di PostgreSQL 9.4 Beta 1 e di provarla con i vostri carichi di lavoro e le vostre applicazioni il prima possibile. Date i vostri feedback agli sviluppatori di PostgreSQL. Le feature e le API nella versione Beta 1 non cambieranno in modo sostanziale prima del rilascio della release finale, in modo da permettervi di cominciare a sviluppare applicazioni basandovi su Postgres 9.4. Maggiori informazioni su come eseguire test e riportare problemi.

Scarica PostgreSQL 9.4 Beta 1, compresi file binari e installer per Windows, Linux e Mac dalla nostra pagina di download.

La documentazione completa della nuova release è disponibile online – e si installa anche con PostgreSQL.