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.

Dopo due anni, il nostro Gabriele Bartolini torna in Australia per parlare di PostgreSQL e della cultura devops, in programma presso Inspire9 (Melbourne) alle ore 18.30 di mercoledì 18 febbraio.

Si sente frequentemente parlare di cultura devops e, nonostante sia un concetto importante, non sempre si è consapevoli del significato.
Devops è un metodo di sviluppo del software basato sulla completa collaborazione, integrazione e condivisione fra sviluppatori (developers) e sistemisti (operations team).

Il team di 2ndQuadrant ne adotta la filosofia e segue metodologie agili e lean di gestione dei progetti. Questo ha permesso di migliorare e rendere più efficiente il processo di messa in produzione, nonché di mantenere un elevato livello di qualità.

Gabriele, dopo un’introduzione sulla filosofia devops, racconterà l’esperienza concreta di 2ndQuadrant Italia nell’adozione di questa cultura per la gestione quotidiana di database PostgreSQL.

Il talk si concentrerà, inoltre, sulle nuove caratteristiche di PostgreSQL 9.4 (rilasciato il 18 Dicembre, 2014) in un contesto devops.

Per maggiori informazioni: http://www.meetup.com/melpug/events/219082475

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.

Grazie al linguaggio SQL è possibile, in ogni istante, controllare lo stato di funzionamento del processo di archiviazione dei log transazionali (WAL), componente cruciale di un sistema di disaster recovery con PostgreSQL.

Introduzione e motivazioni

Questa vista nasce dall’esigenza, emersa in questi anni, di utilizzare Barman come soluzione di disaster recovery di database PostgreSQL in ambienti in continuità operativa.

In particolare, alcune delle necessità e delle domande ricorrenti che DBA, sistemisti, CTO e CIO legittimamente ci chiedono, sono:

• Di quanto spazio disco avrò bisogno?
• Come posso tenere sotto controllo il processo di backup e di archiviazione continua?

Il punto di partenza, come è naturale che sia, è un requisito di business, che si traduce nel concetto di retention policy. Solitamente, un’azienda definisce un piano di disaster recovery all’interno di un piano di continuità operativa, dove è stabilito chiaramente il periodo di conservazione dei dati di backup. Negli stessi documenti troviamo sia il recovery point objective (RPO) che il recovery time objective (RTO), le due metriche fondamentali che misurano la quantità di dati che un’azienda può tollerare di perdere e il tempo massimo di ripristino in seguito a un disastro.
Il monitoraggio e lo studio del comportamento passato di un database sono elementi fondamentali per dimensionare, a livello di storage, una soluzione di backup.

Ad esempio, un’azienda può decidere di conservare i dati di un database PostgreSQL per un mese, in modo da ricostruire la situazione del database in maniera consistente in qualsiasi istante a partire dal primo backup a disposizione, fino all’ultimo file WAL correttamente archiviato (tramite il robustissimo meccanismo di Point-In-Time-Recovery di PostgreSQL).

La dimensione necessaria è data non solo dal numero di backup completi periodici (ad esempio uno a settimana), ma anche dal numero di file WAL archiviati, ognuno contenente le transazioni correttamente eseguite sul database in modo differenziale.

Una delle metriche necessarie è pertanto il numero di file WAL archiviati al secondo, tramite le quali è possibile stimare il numero di WAL prodotti in una settimana ed essere in grado di fare previsioni di utilizzo del disco.

A meno di non usare strumenti di campionamento e di trending (e.g. Munin), di esaminare i timestamp dei file WAL sfruttando un wal_keep_segments alto, oppure delegare questa informazione allo script personalizzato di archiviazione invocato da archive_command, fino alla versione 9.4, PostgreSQL non era in grado di fornire statistiche sul numero di file WAL archiviati, l’orario di archiviazione, l’ultimo WAL archiviato e così via. Tantomeno poteva riportare informazioni in merito al numero di file WAL per i quali l’archiviazione era fallita e l’orario di ultimo fallimento.

Sono questi i motivi per cui un anno fa ho deciso di scrivere una piccola patch per Postgres, patch che è stata poi inserita nel core all’interno della versione 9.4.

Questa patch aggiunge una statistica real-time al catalogo di PostgreSQL chiamata pg_stat_archiver.

Overview della statistica

La vista pg_stat_archiver di PostgreSQL 9.4 rende disponibile a tutti coloro che utilizzano Barman o il backup continuo classico tramite WAL file shipping, i seguenti campi:

• archived_count: numero di file WAL archiviati con successo
• last_archived_wal: nome dell’ultimo file WAL archiviato con successo
• last_archived_time: orario dell’ultima archiviazione di WAL eseguita con successo
• failed_count: numero di tentativi falliti di archiviazione di WAL
• last_failed_wal: nome dell’ultimo file WAL con tentativo di archiviazione fallito
• last_failed_time: orario dell’ultima archiviazione di WAL fallita
• stats_reset: orario di reset delle statistiche

Ecco un esempio ricavato da un database server locale, poco utilizzato:

postgres=# SELECT * FROM pg_stat_archiver;
-[ RECORD 1 ]------+-----------------------------------------
archived_count     | 17
last_archived_wal  | 00000001000000000000000B.00000028.backup
last_archived_time | 2014-12-23 08:40:17.858291+01
failed_count       | 13
last_failed_wal    | 000000010000000000000001
last_failed_time   | 2014-12-04 13:09:07.348307+01
stats_reset        | 2014-12-03 16:52:21.755025+01

Essenzialmente, una volta attivata l’archiviazione continua con archive_mode, PostgreSQL si fa responsabile che, per ogni file WAL prodotto, il comando archive_command venga eseguito con successo, riprovando all’infinito (spazio su disco permettendo) in caso di fallimento.

A differenza delle versioni precedenti, PostgreSQL 9.4 è in grado adesso di raccogliere alcune informazioni sui due principali eventi che possono verificarsi con l’archiviazione: successo e fallimento. In particolare, per entrambe le operazioni vengono resi disponibili:

• Conteggio (a partire dall’inizializzazione del cluster oppure dall’ultimo reset delle statistiche);
• WAL di riferimento dell’ultima operazione;
• Orario dell’ultima operazione.

Tra l’altro, è possibile azzerare le statistiche di archiviazione con l’istruzione:

-- Richiede privilegi superuser
SELECT pg_stat_reset_shared('archiver');

Integrazione con Barman

A partire dalla versione 1.4, Barman sfrutterà la vista pg_stat_archiver per i database PostgreSQL 9.4 in modo automatico e trasparente, riportando le informazioni di archiviazione nei classici comandi Barman di visualizzazione di un server, come status e show-server.

Inoltre, sfruttando la potenza del linguaggio SQL, è stata migliorata l’affidabilità del comando check, adesso in grado di rilevare un problema di archiviazione direttamente dalla fonte.

Esaminiamo velocemente la query introdotta in Barman:

SELECT *,
    current_setting('archive_mode')::BOOLEAN
        AND (last_failed_wal IS NULL
            OR last_failed_wal <= last_archived_wal)
        AS is_archiving,
    CAST (archived_count AS NUMERIC)
        / EXTRACT (EPOCH FROM age(now(), stats_reset))
        AS current_archived_wals_per_second
FROM pg_stat_archiver

Oltre a recuperare tutte le colonne della vista pg_stat_archiver, la query calcola al volo due campi, direttamente dalla fonte:

• is_archiving: il processo di archiviazione dei WAL è in corso oppure no?
• current_archived_wals_per_second: frequenza di WAL archiviati per secondo

Il campo is_archiving deve essere sempre TRUE, in quanto il processo di archiviazione è necessario per Barman. Pertanto, archive_mode deve essere attivo e il valore dell’ultimo WAL deve essere non definito (NULL) oppure non successivo all’ultimo WAL correttamente archiviato. Questo controllo è adesso parte integrante del comando barman check su server Postgres 9.4 (e future versioni).

Il secondo campo invece restituisce una statistica molto interessante sul workload prodotto dai server Postgres e permette pertanto di stimare lo spazio su disco richiesto per memorizzare (anche in modo compresso) giorni, settimane e mesi di file WAL (rispondendo così a una delle importanti domande iniziali).

Come controllare lo stato di funzionamento dell’archiviazione

Grazie a pg_stat_archiver, controllare lo stato di funzionamento dell’archiviazione continua dei WAL si riduce all’esecuzione di una query SQL.

È possibile impiegare la query esposta in precedenza, già utilizzata in Barman, per verificare che l’archiviazione stia procedendo con successo, integrandola nelle sonde o plugin del sistema di alerting utilizzato in azienda.

Coloro che utilizzano Barman con server PostgreSQL 9.4 e hanno già integrato barman check all’interno Nagios o Icinga, beneficeranno in modo trasparente di questa funzionalità.

Conclusioni

La vista pg_stat_archiver, nella sua semplicità, rappresenta uno strumento molto importante per coloro che considerano la disaster recovery una componente cruciale, e non periferica, di un sistema PostgreSQL in business continuity.

Anche se PostgreSQL permette di eseguire backup utilizzando la replica in streaming, l’archiviazione continua dei file WAL tramite shipping rappresenta comunque un metodo di fallback sicuro e affidabile (e comunque l’unico finora supportato da Barman). Pertanto, il corretto monitoraggio di questa componente aumenta sensibilmente la robustezza di tutta la soluzione di database Postgres.

Infine, poter disporre di statistiche circa il numero di WAL archiviati (e pertanto prodotti) da un server PostgreSQL in un periodo di riferimento, permette di conoscere con una semplice query il workload transazionale e poter fare previsioni sull’occupazione a livello di disco di una soluzione di backup.

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.

Nella puntata precedente, abbiamo introdotto la funzionalità di replica logica aggiunta a PostgreSQL 9.4. Continuiamo a nuotare nel mare di novità che la versione 9.4 porta nel campo Operation, con l’obiettivo di migliorare la gestione di database PostgreSQL per amministratori di sistema e DBA.

pg_prewarm

pg_prewarm è una nuova estensione per risolvere il problema dei rallentamenti post riavvio. In seguito a un riavvio di PostgreSQL, infatti, i buffer sono cancellati e Postgres non è più in grado di ritrovare subito in RAM i dati richiesti. Con pg_prewarm è possibile caricare in memoria una tabella importante immediatamente dopo il riavvio con un semplice

SELECT pg_prewarm('my_table');

In questo modo non sarà più necessario avere un database a regime per avere i dati in cache.

Gestione dei tablespace

Due piccole novità sono state introdotte per semplificare l’uso dei tablespace. La prima è l’introduzione del comando ALTER TABLESPACE … MOVE, che permette di spostare tabelle, indici e viste materializzate da un tablespace a un altro. La seconda è la sintassi CREATE TABLESPACE … WITH … options con cui diventa possibile impostare opzioni del tablespace direttamente in fase di creazione, risparmiando un secondo ALTER TABLESPACE. I due parametri possibili al momento sono seq_page_cost e random_page_cost, che possono essere utili al planner per comprendere quali dischi sono più veloci di altri.

CREATE TABLESPACE new_tblspc LOCATION 'my_dir' WITH random_page_cost = 1;
ALTER TABLESPACE old_tblspc MOVE TABLES TO new_tblspc;

Monitoraggio dell’archiviazione dei WAL

Nel corso del nostro lavoro su Barman in 2ndQuadrant, più volte ci siamo trovati a stimare la produzione di WAL di un server. Per questo, il nostro Gabriele Bartolini ha sviluppato questa patch. Adesso è possibile avere a disposizione una tabella di statistiche sull’attività dell’archiver che mostra il numero di WAL archiviati nel tempo (e, eventualmente, di archiviazioni fallite).

SELECT * FROM pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
 archived_count     | 4
 last_archived_wal  | 00000001000000000000000B
 last_archived_time | 2014-10-07 08:58:02.258657+00
 failed_count       | 0
 last_failed_wal    |
 last_failed_time   |
 stats_reset        | 2014-10-07 08:51:29.852523+00

Standby ritardati nel tempo

Se uno standby è uno strumento utile nel caso di crash del master, è totalmente inutile nel caso di un errore umano. Una “DROP TABLE” errata da parte dell’amministratore e diventa immediatamente necessaria una Point In Time Recovery. La possibilità di avere un server in replica che applichi i cambiamenti con un certo ritardo concede invece un intervallo di tempo per fermare la replica, evitando che l’errore si propaghi.

Per impostare ad esempio uno standby in ritardo di almeno un’ora, basta impostare nel recovery.conf:

min_recovery_apply_delay = 1h

Modifica della configurazione

Un altro nuovo comando introdotto nella 9.4 per rendere più comodo lavorare con PostgreSQL è ALTER SYSTEM. Adesso è diventato possibile cambiare il file postgresql.conf da dentro una connessione SQL. Con le sole eccezioni dei parametri impostabili in fase di compilazione e della PGDATA, gli altri possono essere modificati come dell’esempio successivo:

ALTER SYSTEM SET wal_level = hot_standby;

I nuovi valori saranno scritti nel file postgresql.auto.conf. Resta necessario eseguire un reload o un riavvio per i parametri che lo richiedono.

Performance dei WAL

Per quanto riguarda i WAL, non ci sono comandi nuovi, ma miglioramenti delle prestazioni. È stata ridotta la lock contention per le operazioni di insert nei WAL. Inoltre, è stata diminuita la dimensione dei WAL record generati dalle UPDATE. In questo modo sarà possibile effettuare più scritture impiegando minore I/O.

Conclusioni

Per noi amministratori di sistemi Linux e DBA, PostgreSQL 9.4 migliora ulteriormente la gestione ordinaria di database in continuità operativa. Inoltre, pone le basi per aprire nuovi orizzonti in ambito architetturale, fra cui la replica multi-master. Grazie alla replica logica, infatti, vedremo sicuramente gli strumenti di replica basati su trigger (come Londiste, Slony, Bucardo) aggiungere il supporto alla decodifica degli eventi direttamente da replication slot logici, rendendo molto più snella la gestione della replica. Se siete interessati, potete seguire il nostro blog con l’articolo di Giuseppe sulle novità per sviluppatori. A presto!

La versione 9.4 di PostgreSQL, in uscita fra pochi giorni, presenta numerose piccole novità per gli amministratori, in aggiunta all’introduzione del supporto alla replica logica, ovvero il primo passo verso una futura implementazione di replica multi-master in PostgreSQL. In questo articolo in due parti mostreremo le principali novità per gli amministratori, partendo proprio dalla replica logica, costituita da un insieme di nuove feature:

• Replication slot fisici
• WAL level “logical”
• Replication slot logici
• Decodifica logica
• Replica identity

Lo sviluppo di queste funzionalità è un frutto diretto del lavoro effettuato da 2ndQuadrant (e, in particolare, da Andres Freund) all’interno del progetto BDR (BiDirectional Replication), una soluzione open source di replica multi-master basata su PostgreSQL, il cui codice è progressivamente incluso nel core di Postgres con l’obiettivo di diventarne parte integrante nei prossimi anni.

Nel prossimo articolo, vedremo le altre novità dedicate agli amministratori.

Replication slot fisici

I replication slot fisici sono una struttura che mantiene la memoria dello stato di uno standby e dei WAL a questo necessari, anche quando lo standby è offline. In questo modo non è più indispensabile dover stimare wal_keep_segments o configurare il continuous archiving. Utili quindi per server in replica fisica, sono poi indispensabili alla replica logica, dove i server potrebbero avere contenuti differenti non replicati e non sarebbe così possibile ricostruire il server da zero.

Maggiori informazioni in un articolo di Craig Ringer sui replication slot di PostgreSQL 9.4.

Wal level “logical”

È stato introdotto il parametro wal_level = logical. Usando questa impostazione i WAL file avranno una dimensione leggermente maggiore di quanta ne abbiano adesso in hot_standby, ma conterranno le infomazioni necessarie al funzionamento della decodifica logica.

Replication slot logici

Una volta impostato wal_level = logical all’interno del postgresql.conf, sarà possibile iniziare a usare i replication slot logici. Simili come idea ai replication slot fisici, a differenza di questi operano su una singola base di dati, e inviano la sequenza di cambiamenti avvenuti su di essa.

Decodifica logica

La decodifica logica usa i replication slot e dei plugin di decodifica per inviare e rendere comprensibili a elementi esterni i cambiamenti avvenuti all’interno del db. Per la visualizzazione sono state sviluppate le funzioni pg_logical_slot_get_changes e pg_logical_slot_peek_changes, con la differenza che la prima consuma il cambiamento nella coda e la seconda lo legge soltanto. L’output della funzione dipende dal plugin usato per creare lo slot. Al momento ne sono stati sviluppati tre:

• test_decoding, il plugin di default;
• wal2json, che mostra i cambiamenti avvenuti in formato JSON;
• decoder_raw, che ricostruisce le query che hanno applicato la modifica.

Replica identity

REPLICA IDENTITY è un nuovo parametro a livello di tabella. Identifica quali informazioni devono essere scritte nei WAL per identificare le tuple modificate o rimosse se è impostato wal_level = logical. Esistono 4 valori:

• DEFAULT: scrive la precedente chiave primaria della tupla se è stata modificata
• USING INDEX idx: scrive le informazioni dell’indice usato, che deve essere UNIQUE, non parziale e NOT NULL.
• FULL: scrive tutte le colonne della vecchia tupla. Utile se manca una chiave primaria.
• NOTHING: non scrive informazioni sul vecchio record. È il default per le tabelle di sistema.

Utilizzando le funzioni e i plugin di decodifica, è possibile scrivere i propri consumer e rimuovere la dipendenza dei propri database da soluzioni di replica basate su trigger, ben più pesanti.

Il blog di Michael Paquier contiene un esempio di codice SQL che mostra l’uso della replica logica.

Conclusioni

Nella prossima parte, ci occuperemo delle altre novità principali nel campo Operation di PostgreSQL 9.4, fra cui pg_prewarm, tablespace, standby in ritardo, gestione dei WAL, ecc… Alla prossima puntata!

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.