Durante la manifestazione ho avuto il piacere di dare il mio contributo alla comunità, presentando un talk riguardo l’installazione di PostgreSQL su Network File System. Nel talk ho raccontato una mia esperienza professionale condivisa con il mio collega Giuseppe Broccolo, nel quale testavamo l’affidabilità di Postgres su NFS.

Condividere la mia esperienza su PostgreSQL con gli altri partecipanti all’evento è stato piacevole e costruttivo.

Ho potuto apprezzare i talk di Denis Gasparin sui diversi tool per l’upgrade tra major release di PostgreSQL oltre al keynote tenuto da Giorgio Roncolato, Direttore Sistemi Informativi ULSS 5 di Arzignano, sulla migrazione all’Open Source, dimostrando quanto i tempi siano maturi anche per le istituzioni, sempre più sensibili all’argomento Software Libero.

Come non menzionare i divertenti talk animati di Mladen Marinovic, coi quali è riuscito a trasmettere l’importanza del testing e della code review intrattenendo la platea e regalando momenti di ilarità.

Manifestazioni di questo tipo permettono la condivisione della conoscenza attraverso i dibattiti e le discussioni che nascono sia al termine di ogni talk che durante i momenti di networking.

L’evento mi ha dato l’oppurtunità di stringere nuove amicizie e di rafforzarne altre già confermate. La giornata si è conclusa con un aperitivo che ha portato ad uno scambio di opinioni, prolungando il tempo di permanenza di alcuni partecipanti.

Cos’è Barman

Barman (Backup And Recovery Manager per PostgreSQL) è un software open-source scritto in Python. Permette di eseguire backup remoti su più server in ambienti business critical e di supportare gli amministratori di database durante la fase di recovery. Le funzionalità più apprezzate di Barman sono: cataloghi di backup, backup incrementale, retention policy, backup remoto e recovery, archiviazione e compressione dei file WAL e backup. Barman è progettato, implementato e mantenuto da 2ndQuadrant Italia e distribuito secondo licenza GNU GPL 3.

Ereditarietà in PostgreSQL

Vediamo innanzitutto il significato di ereditarietà all’interno del contesto di un database relazionale.

L’ereditarietà permette a una tabella, detta “figlia”, di acquisire tutte le caratteristiche di una tabella già esistente, detta “padre”. In questo modo la tabella “figlia” possiede la stessa struttura e gli stessi vincoli della tabella “padre” in aggiunta alle colonne proprie della sua struttura. Effettuando una query sulla tabella “padre” otteniamo come risultato i record combinati della tabella “padre” e “figlia”, interrogando la tabella “figlia” non è, però, possibile ottenere i record della tabella da cui eredita. Questo fornisce alcune interessanti opzioni quando si tratta di progettare un database.

Piccolo esempio di ereditarietà

Supponiamo di avere una tabella che contenga dati anagrafici di attori, chiamata “actors”, e una tabella che contenga i dati anagrafici degli attori vincitori di un premio Oscar, chiamata “oscar_actors”. È facile dedurre che tutti gli attori presenti nella seconda tabella appartengono legittimamente anche alla prima, ma non il contrario.

Le due tabelle sono state create con i seguenti statement:

CREATE TABLE actors (
    id serial primary key,
    first_name text not null,
    last_name text not null
);

CREATE TABLE oscar_actors (
    id serial primary key,
    first_name text not null,
    last_name text not null,
    oscar_prize text not null
    );

Nota Bene: Tutti gli esempi presenti in questo articolo usano come utente di riferimento “postgres”. Questa è una scelta effettuata per motivi di semplicità e per non deviare dall’argomento trattato ed è una pessima scelta in fatto di sicurezza del vostro database. Vi suggerisco di utilizzare un utente specifico per la vostra applicazione.

Per semplificare la consultazione delle due tabelle possiamo creare una vista che le metta in correlazione tramite una query che usa l’operatore ‘UNION’. Una soluzione alternativa prevede che le tabelle siano strutturate in maniera diversa, usando l’ereditarietà.

CREATE TABLE actors (
    id serial primary key,
    first_name text not null,
    last_name text not null
);

CREATE TABLE oscar_actors (
    oscar_prize text not nul
    ) INHERITS (actors);

Come spiegato, la tabella “oscar_actors” eredita tutte le colonne della tabella “actors”. La tabella “oscar_actors” possiede inoltre la colonna aggiuntiva “oscar_prize” che mostra quale è il premio oscar vinto dall’attore.

Eseguendo una query sulla tabella “actors”, per esempio cercando tutti gli attori di nome Michael presenti nel nostro set di dati, il risultato è la combinazione dei contenuti delle due tabelle, “padre” e “figlia”.

Interrogando la tabella “oscar_actors”, invece, otterremmo i nomi di tutti i Michael che hanno vinto un premio Oscar.

Ereditarietà e tabelle esterne

È possibile usare l’ereditarietà per “partizionare” una tabella in maniera “orizzontale”, ovvero per suddividere una tabella in sottotabelle più piccole. Ogni sottotabella contiene un insieme di record suddivisi secondo un criterio comune (ad esempio il mese di inserimento nel caso di una tabella storicizzata).

L’inserimento delle tabelle esterne in questo scenario rende tutto ancora più interessante!

Le varie sottotabelle possono risiedere tutte su database separati, rendendo possibile l’implementazione di una tecnica di partizionamento nota come sharding.

Implementazione dello sharding tramite tabelle esterne

Vediamo ora come è possibile attuare lo sharding tramite tabelle esterne. Supponiamo di avere un database di nome “master” 4 database di nome “db1”, “db2”, “db3” e “db4”. Su tutti i database creiamo una tabella con questa struttura:

CREATE TABLE products (
    id serial PRIMARY KEY,
    name TEXT NOT NULL);

Connettiamoci quindi al database “master”, creiamo una tabella “products” e produciamo quindi 4 tabelle esterne che ereditano da questa:

CREATE TABLE products (id serial PRIMARY KEY, name TEXT NOT NUll);
CREATE EXTENSION postgres_fdw;
CREATE SERVER db1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db1' );
CREATE SERVER db2 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db2' );
CREATE SERVER db3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db3' );
CREATE SERVER db4 FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'db4' );
CREATE USER MAPPING FOR POSTGRES SERVER db1 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db2 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db3 OPTIONS ( user 'postgres' );
CREATE USER MAPPING FOR POSTGRES SERVER db4 OPTIONS ( user 'postgres' );
CREATE FOREIGN TABLE products_db1 () INHERITS (products) SERVER db1 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db2 () INHERITS (products) SERVER db2 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db3 () INHERITS (products) SERVER db3 OPTIONS ( table_name 'products' );
CREATE FOREIGN TABLE products_db4 () INHERITS (products) SERVER db4 OPTIONS ( table_name 'products' );

Creiamo quindi 4 sequenze su “master” per l’assegnazione degli id alle tabelle esterne.

CREATE SEQUENCE products_id_seq_db1 INCREMENT BY 4 RESTART WITH 1;
CREATE SEQUENCE products_id_seq_db2 INCREMENT BY 4 RESTART WITH 2;
CREATE SEQUENCE products_id_seq_db3 INCREMENT BY 4 RESTART WITH 3;
CREATE SEQUENCE products_id_seq_db4 INCREMENT BY 4 RESTART WITH 4;
ALTER FOREIGN TABLE products_db1 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db1');
ALTER FOREIGN TABLE products_db2 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db2');
ALTER FOREIGN TABLE products_db3 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db3');
ALTER FOREIGN TABLE products_db4 ALTER COLUMN id SET DEFAULT nextval('products_id_seq_db4');

Le sequenze degli id delle varie tabelle sono state alterate in modo che abbiano un incremento di 4 e uno sfasamento nel numero di partenza. Gli id generati per le 4 tabelle sono quindi diversi.

Inseriamo, quindi, 4 prodotti direttamente nelle tabelle esterne:

INSERT INTO products_db1 (name) VALUES ('playstation');
INSERT INTO products_db2 (name) VALUES ('xbox');
INSERT INTO products_db3 (name) VALUES ('wii');
INSERT INTO products_db4 (name) VALUES ('saturn');

Facendo una SELECT sulla tabella “products” troviamo tutti i prodotti che abbiamo appena inserito:

master=# SELECT * FROM products;
 id |    name
----+-------------
  1 | playstation
  2 | xbox
  3 | wii
  4 | saturn
(4 rows)

Eseguendo un EXPLAIN ANALIZE possiamo vedere l’esecuzione delle scansioni sulle tabelle esterne:

master=# EXPLAIN ANALYZE SELECT * FROM products;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..603.80 rows=5461 width=36) (actual time=0.693..2.682 rows=4 loops=1)
   ->  Seq Scan on products  (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Foreign Scan on products_db1  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.689..0.690 rows=1 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.668..0.669 rows=1 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.665..0.666 rows=1 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..150.95 rows=1365 width=36) (actual time=0.650..0.652 rows=1 loops=1)
 Planning time: 0.316 ms
 Execution time: 4.601 ms
(8 rows)

è possibile modificare anche i record delle tabelle figlie, per esempio con facendo una UPDATE:

master=# EXPLAIN ANALYZE UPDATE products SET name = 'wii-u' WHERE ID = 4 AND name LIKE 'wii';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on products  (cost=0.00..587.52 rows=5 width=10) (actual time=3.387..3.387 rows=0 loops=1)
   Update on products
   Foreign Update on products_db1
   Foreign Update on products_db2
   Foreign Update on products_db3
   Foreign Update on products_db4
   ->  Seq Scan on products  (cost=0.00..2.91 rows=1 width=10) (actual time=0.018..0.018 rows=0 loops=1)
         Filter: ((name ~~ 'wii'::text) AND (id = 4))
         Rows Removed by Filter: 1
   ->  Foreign Scan on products_db1  (cost=100.00..146.15 rows=1 width=10) (actual time=0.878..0.878 rows=0 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..146.15 rows=1 width=10) (actual time=0.861..0.861 rows=0 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..146.15 rows=1 width=10) (actual time=0.759..0.759 rows=0 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..146.15 rows=1 width=10) (actual time=0.866..0.866 rows=0 loops=1)
 Planning time: 0.786 ms
 Execution time: 5.718 ms
(15 rows)

Lo sharding non è ancora completo. Ogni volta che facciamo una query viene eseguita una scansione di tutte le tabelle, anche quando non sarebbe necessario.

master=# EXPLAIN ANALYZE SELECT * FROM products WHERE name LIKE 'xbox';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..508.81 rows=29 width=36) (actual time=1.478..2.845 rows=1 loops=1)
   ->  Seq Scan on products  (cost=0.00..0.00 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)
         Filter: (name ~~ 'xbox'::text)
   ->  Foreign Scan on products_db1  (cost=100.00..127.20 rows=7 width=36) (actual time=0.740..0.740 rows=0 loops=1)
   ->  Foreign Scan on products_db2  (cost=100.00..127.20 rows=7 width=36) (actual time=0.734..0.736 rows=1 loops=1)
   ->  Foreign Scan on products_db3  (cost=100.00..127.20 rows=7 width=36) (actual time=0.631..0.631 rows=0 loops=1)
   ->  Foreign Scan on products_db4  (cost=100.00..127.20 rows=7 width=36) (actual time=0.733..0.733 rows=0 loops=1)
 Planning time: 0.459 ms
 Execution time: 4.878 ms
(9 rows)

Il problema è risolvibile affidandosi ai meccanismi di partizionamento già presenti in PostgreSQL. Aggiungiamo quindi alle tabelle esterne un constraint di tipo CHECK, definendo così quali siano le chiavi che appartengono a quella partizione

ALTER FOREIGN TABLE products_db1 ADD CHECK ( id % 4 = 1 );
ALTER FOREIGN TABLE products_db2 ADD CHECK ( id % 4 = 2 );
ALTER FOREIGN TABLE products_db3 ADD CHECK ( id % 4 = 3 );
ALTER FOREIGN TABLE products_db4 ADD CHECK ( id % 4 = 0 );

In questo caso abbiamo scelto come discriminante un criterio numerico basato sull’operazione di modulo 4 (perché 4 sono le tabelle) del campo id.

master=# EXPLAIN ANALYZE SELECT * FROM products WHERE id = 4 AND (id % 4) = (4 % 4) ;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..137.13 rows=2 width=36) (actual time=0.913..0.915 rows=1 loops=1)
   ->  Seq Scan on products  (cost=0.00..3.22 rows=1 width=36) (actual time=0.013..0.013 rows=0 loops=1)
         Filter: ((id = 4) AND ((id % 4) = 0))
         Rows Removed by Filter: 1
   ->  Foreign Scan on products_db4  (cost=100.00..133.91 rows=1 width=36) (actual time=0.900..0.901 rows=1 loops=1)
 Planning time: 0.525 ms
 Execution time: 1.591 ms
(7 rows)

PostgreSQL in questo modo identifica immediatamente a quale tabella appartiene l’id che stiamo cercando e evita di fare la scansione delle altre.

Gli inserimenti sono ancora non “automatici” ma basta creare un trigger BEFORE INSERT per la tabella users che applichi gli stessi criteri applicati per i vincoli usati per le tabelle esterne alle linee in inserimento.

Conclusioni

La partecipazione delle tabelle esterne all’ereditarietà è una feature di grande importanza. Finora per poter partizionare orizzontalmente le tabelle era necessario ricorrere a strumenti esterni a PostgreSQL come Pg_shard o PL/Proxy. Dalla 9.5 tutto quello che serve per costruire l’architettura per lo sharding è presente all’interno del core di PostgreSQL.

Sono molteplici i benefici che è possibile trarre da una attenta pianificazione del partizionamento delle tabelle. È possibile migliorare le prestazioni delle query su tabelle di grandi dimensioni, riducendone il set di dati da analizzare. Inoltre, ripartendo il carico delle dimensioni di alcune tabelle molto grandi su diverse istanze di PostgreSQL, si riduce lo spazio occupato sul disco, non solo dal database ma anche dai suoi backup, rendendo più rapide se necessario le operazioni di ripristino dei cluster.

Con la release 1.5.0, Barman è in grado di restituire, su richiesta, qualsiasi file WAL disponibile per un determinato server, consentendo, ad esempio, agli amministratori di database di integrarli nel restore_command di un qualsiasi server standby.

Questa versione introduce anche i cosiddetti “retry hook script” che consentono agli utenti di eseguire script personalizzati, prima e dopo un evento (ad esempio un backup) e assicurarsi che Barman riprovi continuamente finché la procedura non ha successo.

Un’approfondita attività di refactoring ed una maggiore attenzione ai controlli, rendono la 1.5.0 la versione più stabile e robusta di Barman.

Il nostro consiglio? Procedete con l’aggiornamento il prima possibile.

Per una lista completa delle modifiche, vi invitiamo a leggere il comunicato ufficiale in lingua inglese.

Cos’è Barman

Barman (Backup And Recovery Manager per PostgreSQL) è un software open-source scritto in Python. Permette di eseguire backup remoti su più server in ambienti business critical e di supportare gli amministratori di database durante la fase di recovery.

Le funzionalità più apprezzate di Barman sono: cataloghi di backup, backup incrementale, retention policy, backup remoto e recovery, archiviazione e compressione dei file WAL e backup.

Barman è progettato, implementato e mantenuto da 2ndQuadrant Italia e distribuito secondo licenza GNU GPL 3.

I Foreign Data Wrapper (FDW)

Prima dell’introduzione dei Foreign Data Wrapper l’unico modo per connettere un database Postgres con una fonte dati esterna era il modulo dblink.
Nel 2003 viene definito all’interno del linguaggio SQL l’insieme di regole per la gestione in maniera standard di fonti di dati esterne: SQL/MED (management of external Data).

All’interno di PostgreSQL 9.1, una prima implementazione dello standard SQL/MED viene introdotta con i Foreign Data Wrapper, fornendo a Postgres l’accesso diretto a fonti di dati come file o altri database (Oracle, Mysql…), permettendone l’utilizzo come tabelle.
Il vantaggio di questo approccio è evidente: la possibilità di interrogare una fonte di dati esterna per poterne estrarre dati in maniera nativa eseguendo una semplice query. Il non dover ricorrere a moduli esterni per ottenere questo risultato è una notevole semplificazione del lavoro per i DBA.

Per saperne di più, potete dare una occhiata all’articolo pubblicato nel 2011, prima dell’uscita di PostgreSQL 9.1,  sul nostro blog: “PostgreSQL 9.1: Tabelle esterne con SQL/MED“.

Piccolo esempio di uso di un FDW

PostgreSQL 9.3 introduce il supporto ai foreign data wrapper in scrittura ed aggiunge anche il supporto al foreign data wrapper per PostgreSQL. Vediamo adesso un semplice esempio di utilizzo di un FDW connettendo fra loro due database Postgres.
Creiamo due database:

CREATE DATABASE source;
CREATE DATABASE destination;

All’interno di source creiamo una tabella di test con dei dati di test:

\c source
CREATE TABLE test1 AS SELECT id, md5(random()::text) FROM generate_series(1,5) id;

Connettiamoci adesso al db di destinazione e connettiamo i due database:

\c destination
CREATE EXTENSION postgres_fdw ;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS( dbname 'source' );
CREATE USER MAPPING FOR postgres SERVER src_srv OPTIONS ( user 'postgres' );

Qualcuno di voi, giustamente, si sarà alzato in piedi, lamentandosi per la pessima scelta in fatto di sicurezza! Molto bene!

Per semplicità, ho infatti deciso di connetterci con l’utente amministratore “postgres” – anche per non deviare troppo dall’argomento principale dell’articolo. Sappiate che in un ambiente di produzione, per motivi di sicurezza, dovrete prendere altre scelte – ad esempio utilizzando un utente specifico per la vostra applicazione.

Ad ogni modo, una volta stabilita la connessione, possiamo creare sul database di destinazione una tabella esterna che punti a test1 sul database source:

CREATE FOREIGN TABLE test1_ft (id integer, md5 text) server src_srv options(table_name 'test1');

Possiamo adesso confrontare il contenuto fra le due tabelle di test:

select * from test1_ft ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

\c source

select * from test1 ;
 id |               md5
----+----------------------------------
  1 | 63e5bc545b45f5c3961522f2609bedd9
  2 | d74af95e495d946d4a0887c51eb2cbe2
  3 | acce7cba66967332d01d51b74eb293f7
  4 | c5bb57ca54036004de334cf793792d4e
  5 | 02f32751b09042cf28b78cc29321a32e
(5 rows)

È evidente, osservando questo esempio, che uno dei più grandi limiti all’utilizzo dei Foreign Data Wrapper è la necessità di definire separatamente, conoscendone la struttura, ogni tabella.
L’accesso a dati esterni quindi risulta laborioso qualora si voglia importare tabelle più complesse o, addirittura, interi schemi.

Fino ad adesso, operazioni del genere venivano fatte per mezzo di script in grado di connettersi al database sorgente e generare la struttura delle tabelle esterne in maniera automatica.
Fortunatamente la funzionalità IMPORT FOREIGN SCHEMA, presente nella prossima release di PostgreSQL, ci viene in aiuto.

IMPORT FOREIGN SCHEMA: sinossi

L’istruzione IMPORT FOREIGN SCHEMA, permette di importare uno schema intero da una fonte dati esterna, senza dover specificare la struttura di ogni singola tabella:

IMPORT FOREIGN SCHEMA remote_schema_name
FROM SERVER server_name INTO destination_schema;

Qualora non sia necessario importare uno schema intero, è possibile usare la clausola LIMIT TO e circoscrivere l’importazione unicamente alle tabelle a cui siamo interessati:

IMPORT FOREIGN SCHEMA remote_schema_name LIMIT TO (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Altrimenti, se siamo interessati a escludere solo alcune tabelle dallo schema, è possibile filtrarle con la clausola EXCLUDE:

IMPORT FOREIGN SCHEMA remote_schema_name EXCLUDE (table_name, table_name, ...)
FROM SERVER server_name INTO destination_schema;

Esempio

Vediamo nel dettaglio come utilizzare questo comando, andando a estendere l’esempio usato in precedenza.
Connettiamoci al database sorgente e aggiungiamo due tabelle a quella che già è presente:

\c source
create table test2 as select id, md5(random()::text) from generate_series(1,20) as id;
create table test3 as select id, md5(random()::text) from generate_series(1,50) as id;

Creiamo adesso nel database di destinazione uno schema che useremo come target dell’istruzione IMPORT FOREIGN SCHEMA:

\c destination
create schema imported;

Adesso possiamo importare lo schema che abbiamo appena ampliato, contando sulla connessione aperta nell’esempio precedente:

IMPORT FOREIGN SCHEMA public FROM SERVER src_srv INTO imported;

Facciamo una rapida ispezione di tutte le tabelle sul database di destinazione per osservare il risultato dell’importazione dello schema:

\dE *.*

               List of relations
  Schema  |   Name   |     Type      |  Owner
----------+----------+---------------+----------
 imported | test1    | foreign table | postgres
 imported | test2    | foreign table | postgres
 imported | test3    | foreign table | postgres
 public   | test1_ft | foreign table | postgres

All’interno dello schema public notiamo la tabella che abbiamo creato in precedenza, mentre il risultato dell’importazione “in massa” è visibile nello schema imported.

Con questo esempio è possibile constatare quanto è più veloce e immediato l’utilizzo delle tabelle esterne con IMPORT FOREIGN SCHEMA.

Conclusioni

Con PostgreSQL 9.5, grazie a questa nuova funzionalità, le migrazioni dei dati diventeranno sempre più semplici e veloci.
Attualmente l’istruzione IMPORT FOREIGN SCHEMA è supportata solo da postgres_fdw e richiede che gli sviluppatori dei singoli driver la implementino nel modo più consono alla fonte di dati.
Aumentando il numero di driver in grado di supportare questa funzionalità si aprono scenari sempre più interessanti per PostgreSQL e per l’integrazione dei dati.

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.

Il talk racconta l’esperienza concreta di 2ndQuadrant Italia in termini di adozione della cultura devops (volutamente scritta con le lettere “D” e “O” minuscole a simbolizzare la completa condivisione fra sviluppatori e amministratori) nella gestione quotidiana di database PostgreSQL e di progetti di sviluppo, sia interni che commissionati da clienti provenienti da tutto il mondo.

L’intervento, oltre a coprire concetti introduttivi, si concentrerà su aspetti organizzativi, sui processi, sul ruolo del DBA, sull’open source e infine sugli strumenti legati all’impiego di database PostgreSQL in un contesto devops.

La presentazione, in lingua inglese, ha già riscosso successo sia al FOSDEM 2014 che al Codemotion 2014 di Roma.

Barman 1.3.3 è in grado di inviare alert nel caso in cui l’ultimo backup disponibile di un server sia più vecchio di un dato intervallo di tempo (ad esempio, una settimana). Inoltre, Barman può adesso riprovare automaticamente la copia dei backup in seguito a problemi temporanei come la perdita della connessione di rete. È stato poi implementato un algoritmo ottimizzato per la copia tramite rsync, al fine di migliorare le prestazioni durante le operazioni di recovery incrementale.

Le consuete attività di bug fixing e di rifattorizzazione del codice hanno fatto sì che la release 1.3.3 di Barman risulti essere la versione più stabile fra quelle disponibili e per questo vi consigliamo di effettuare l’aggiornamento delle vostre installazioni di Barman il prima possibile.

L’ultima nota di rilievo – ma non certo per importanza – è riservata agli utilizzatori che appartengono all’era giurassica di PostgreSQL 8.3 (senza offesa! ;) ): possono finalmente effettuare il backup dei loro database con Barman.

Un ringraziamento speciale per il contributo allo sviluppo di questa release va a Agile Business Group (www.agilebg.com), Jobrapido (www.jobrapido.com), Navionics (www.navionics.com) e Subito.it (www.subito.it).

Per una lista completa delle modifiche, si faccia riferimento alle note di rilascio nel comunicato ufficiale in lingua inglese. Maggiori informazioni su Barman sono disponibili sul relativo sito all’indirizzo www.pgbarman.org.

A proposito di Barman

Barman (Backup and Recovery Manager per PostgreSQL) è un software open-source scritto in Python, progettato, implementato e mantenuto da 2ndQuadrant Italia e distribuito secondo licenza GNU GPL 3. Consente di eseguire backup remoti su più server in ambienti business critical. Strumento di sicuro supporto ai DBA durante la fase di recupero dei dati. Tra le caratteristiche più apprezzate di Barman emergono: catalogo di backup, politiche di conservazione (retention policy), backup e recovery da remoto, archiviazione e compressione dei file WAL e dei backup.