Già nel mio precedente articolo sui BRIN ho evidenziato le novità introdotte soprattutto in ambito geospaziale con la 9.5. Oggi mostrerò una funzionalità della nuova versione di PostgreSQL che amplia ancora di più gli strumenti messi a disposizione in ambito GIS: la distanza punto-poligono, definita matematicamente come la minima delle distanze tra il punto di interesse e ciascun punto del poligono. Sebbene PostgreSQL abbia già nativamente introdotto alcune geometrie bidimensionali (point, circle, polygon, …), per poter calcolare la distanza di un punto da un poligono in 2D, fino ad oggi, era necessaria l’installazione di PostGIS (per esempio per trovare i punti di interesse più vicini ad un dato perimetro, o viceversa). Con PostgreSQL 9.5 non sarà più strettamente necessaria l’installazione di PostGIS (per quanto utilissima in ambito GIS) per questo tipo di operazioni.

La patch per il calcolo delle distanze tra point e polygon è stata introdotta da Alexander Korotkov, nome già noto nel mondo PostgreSQL soprattutto per i suoi lavori sugli indici GiST. Alexander ha implementato un algoritmo che iterativamente calcola la distanza del punto dai singoli segmenti, per poi prenderne la minima; tecnicamente, ha esteso l’overloading dell’operatore <-> in modo che non fosse limitato al calcolo della distanza fra coppie di geometrie dello stesso tipo point o polygon.

Cerchiamo di capire con un piccolo esempio come funziona, utilizzando uno script che permette di creare 10 milioni di quadrati con lato di lunghezza unitaria uniformemente distribuiti sul piano. La creazione della tabella sul mio desktop è stata completata in circa 13 minuti. Cerchiamo adesso quali sono i 10 quadrati più vicini all’origine (ovvero il punto di coordinate x=0, y=0), ossia facciamo una ricerca di tipo “the k nearest neighbours” (kNN):

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

L’esecuzione della query ha richiesto, sul mio desktop, circa 17 secondi. Il piano di esecuzione prevede ovviamente un sequential scan di tutta la tabella per la ricerca:

QUERY PLAN    --------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505032.60..505032.62 rows=10 width=101) (actual time=16505.291..16505.976 rows=10 loops=1)
       ->  Sort  (cost=505032.60..530032.69 rows=10000035 width=101) (actual time=16504.416..16504.420 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 26kB
             ->  Seq Scan on polygons  (cost=0.00..288935.44 rows=10000035 width=101) (actual time=0.533..13198.879 rows=10000000 loops=1)

Uso degli indici

La patch di Alexander introdotta in PostgreSQL non prevede il supporto per gli indici GiST per l’operatore <-> in ricerche kNN tra point e polygon: proviamo ad esempio a costruire l’indice sui polygon (sul mio desktop l’indicizzazione ha richiesto circa 30 minuti):

CREATE INDEX gist_index
ON polygons
USING gist(polygons);

Rilanciamo la stessa ricerca “nearest neighbours” di prima:

SELECT point(0., 0.) <-> polygons AS distance
FROM polygons
ORDER BY distance DESC
LIMIT 10;

Il tempo di esecuzione è stato nuovamente di 17 secondi, gli stessi del caso in assenza di indici. Infatti se andiamo a vedere quale è stato il piano di esecuzione troveremo che nuovamente è stata eseguita una sequential scan:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
    -
     Limit  (cost=505031.61..505031.63 rows=10 width=101) (actual time=15284.445..15285.214 rows=10 loops=1)
       ->  Sort  (cost=505031.61..530031.62 rows=10000006 width=101) (actual time=15283.761..15283.765 rows=10 loops=1)
             Sort Key: (('(0,0)'::point <-> polygons)) DESC
             Sort Method: top-N heapsort  Memory: 25kB
             ->  Seq Scan on polygons  (cost=0.00..288935.08 rows=10000006 width=101) (actual time=0.236..12805.262 rows=10000000 loops=1)

Conclusioni

PostgreSQL 9.5 ha introdotto la possibilità di effettuare ricerche kNN anche tra point e polygon sul piano, senza necessariamente installare PostGIS. Sebbene le ricerche kNN tra point o circle supportano l’indice GiST, ad oggi ciò non è possibile per quelle miste, tra point e polygon, in quanto richiedono una ricerca basata sul sequential scan completo dei dati.

Questa situazione sta per essere risolta. Alexander ha già presentato una patch in cui vuole introdurre una modifica degli indici GiST per supportare ricerche del tipo kNN-GiST with recheck [¹] tra geometrie miste. Questo dovrebbe, tra le altre cose, rendere l’indice GiST utilizzabile anche per ricerche kNN tra point e polygon.

Annie Prévot, CIO del CNAF, la Cassa Nazionale per gli Assegni Familiari della Francia, afferma:

“Il CNAF fornisce servizi a 11 milioni di persone ed eroga 73 miliardi di Euro ogni anno, attraverso 26 tipi di prestazioni. Questo servizio, essenziale per la popolazione, si basa su un sistema informativo che deve essere efficiente e affidabile. Con soddisfazione, il sistema di CNAF si basa su PostgreSQL per la gestione dei dati.”

UPSERT

Da molti anni una delle funzionalità più richieste dagli sviluppatori di applicazioni, “UPSERT” è la forma breve di “INSERT, ON CONFLICT UPDATE” e permette di trattare in modo identico record nuovi e aggiornati. UPSERT semplifica lo sviluppo di applicazioni web e mobile incaricando il database di gestire conflitti fra modifiche concorrenti ai dati. Inoltre questa funzionalità abbatte l’ultima barriera significativa per la migrazione di applicazioni legacy MySQL verso PostgreSQL.

Sviluppata nel corso degli ultimi due anni da Peter Geoghegan di Heroku, l’implementazione di PostgreSQL di UPSERT è notevolmente più flessibile e potente di quelle offerte da altri database relazionali. La nuova clausola ON CONFLICT consente di ignorare nuovi dati, oppure di aggiornare diverse colonne o relazioni in modo da supportare complesse catene ETL (Extract, Transform, Load) per il caricamento massivo di dati. Inoltre, come tutto PostgreSQL, è progettata per utilizzo concorrente e per integrarsi con tutte le altre funzionalità, replica logica compresa.

Sicurezza a livello di riga

PostgreSQL continua a espandere le sue capacità nel campo della protezione dei dati, aggiungendo il supporto per la sicurezza a livello di riga – in inglese Row Level Security (RLS). RLS implementa un verso controllo di accesso al dato per riga e per colonna e si integra con stack esterni di sicurezza come SE Linux. PostgreSQL è già noto per essere “il più sicuro di default”. RLS consolida questa posizione, rendendolo la migliore scelta per applicazioni con elevati requisiti di sicurezza dei dati; in particolare, conformità a PCI, direttiva europea su Data Protection e standard di protezione dei dati in ambito sanitario.

RLS è l’apice di cinque anni di funzionalità sulla sicurezza aggiunte a PostgreSQL e comprende l’ampio lavoro svolto da KaiGai Kohei di NEC, Stephen Frost di Crunchy Data e Dean Rasheed. Grazie a RLS, gli amministratori di database possono impostare politiche di sicurezza per gestire quali righe particolari utenti sono autorizzati ad aggiornare o a vedere. Implementare la sicurezza del dato in questo modo rende il database resistente a exploit di tipo SQL injection, nonché ad altre falle di sicurezza a livello applicativo.

Funzionalità per i Big Data

PostgreSQL 9.5 include molteplici funzionalità per database di grandi dimensioni e per la loro integrazione con altri sistemi Big Data. Tali funzionalità riaffermano il ruolo dominante di PostgreSQL nel mercato open source dei Big Data, in forte crescita. Fra queste, vale la pena citare:

Indici BRIN

questo nuovo tipo di indice supporta la creazione di indici piccoli ma al tempo stesso molto efficienti per tabelle molto grandi, “naturalmente ordinate”. Per esempio, tabelle contenenti dati di log con miliardi di record possono essere indicizzate e ricercate nel 5% del tempo richiesto da un indice BTree tradizionale.

Ordinamenti più veloci

PostgreSQL riesce a ordinare più velocemente dati testuali e di tipo NUMERIC, utilizzando un algoritmo chiamato “chiavi abbreviate”. Questo algoritmo è in grado di accelerare query che necessitano di ordinare grandi moli di dati da 2 a 12 volte, e di velocizzare la creazione di indici fino a 20 volte.

CUBE, ROLLUP e GROUPING SET

queste nuove clausole dello standard SQL permettono di produrre report a più livelli di riepilogo utilizzando una sola query invece di molteplici, come in passato. CUBE inoltre consente di integrare PostgreSQL con strumenti di reporting come Tableau, tipici di ambienti Online Analytic Processing (OLAP).

Foreign Data Wrapper (FDW)

i FDW consentono già a PostgreSQL di essere utilizzato come motore di query per altri sistemi Big Data come Hadoop e Cassandra. La versione 9.5 aggiunge IMPORT FOREIGN SCHEMA e la propagazione (“pushdown“) delle JOIN, rendendo le connessioni per query a database esterni sia più facili da configurare che più efficienti.

TABLESAMPLE

questa clausola SQL consente di ottenere in modo veloce un campione statistico di una tabella enorme, senza la necessità di ordinamenti dispendiosi.

“Il nuovo indice BRIN di PostgreSQL 9.5 è una funzionalità molto potente che permette a Postgres di gestire e indicizzare volumi di dati che fino ad ora erano impraticabili, se non addirittura impossibili. È in grado di portare la scalabilità e le prestazioni oltre i limiti dei tradizionali database relazionali e rende PostgreSQL una soluzione perfetta per analytics con Big Data”, afferma Boyan Botev, Lead Database Administrator, Premier, Inc.

Vuoi saperne di più?

Per ulteriori informazioni e spiegazioni sulle funzionalità aggiunte in PostgreSQL 9.5, consulta il press kit ufficiale rilasciato dalla Comunità.

Segui inoltre la nostra serie di articoli in italiano su PostgreSQL 9.5.

Questi nuovi operatori potranno essere aggiunti alla clausola GROUP BY e permetteranno il raggiungimento di risultati aggregati in modo più veloce e con un codice più pulito di quanto fosse possibile fare in precedenza, utilizzando più query GROUP BY e UNION ALL.

Facciamo un esempio classico: supponiamo di voler analizzare le vendite di un negozio.

Identifichiamo quindi la vendita come il fatto da analizzare e per praticità d’esempio, consideriamo il prezzo come unica misura a cui siamo interessati. Le dimensioni della nostra analisi saranno il tempo, il luogo e il prodotto (lo script per la creazione delle tabelle e l’inserimento dei dati utilizzati in questi esempi possono essere scaricati da questo link www.dropbox.com/s/sk2n8uz3ddp01ss/create_tables_grouping_sets.sql?dl=0.

Vediamo adesso come utilizzare i nuovi operatori per condurre delle analisi sul db.

GROUPING SETS

Con GROUPING SETS è possibile poter definire insiemi differenti di campi su cui eseguire una GROUP BY e unirne insieme i risultati. Cerchiamo di comprendere con precisione il comportamento di questo operatore con un esempio.

Supponiamo di voler analizzare le vendite dei singoli negozi per anno, e di volere anche avere i dati annuali aggregati di tutti i negozi. Fino alla versione 9.4, avremo ottenuto questo risultato eseguendo UNION ALL:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY (l.nome_negozio, g.anno)
UNION ALL
SELECT NULL AS nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY anno
ORDER BY nome_negozio, anno;

Con la versione 9.5 possiamo utilizzare GROUPING SETS:

SELECT l.nome_negozio, g.anno, sum(prezzo)
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS ((l.nome_negozio, anno), anno)
ORDER BY l.nome_negozio, anno;

Come si può notare, è possibile specificare raggruppamenti su singoli campi o su insiemi di questi, purché ogni insieme sia delimitato da parentesi. L’output delle due query è identico:

 nome_negozio | anno | sum
--------------+------+-----
 Neg1         | 2014 |   7
 Neg1         | 2015 |  31
 Neg2         | 2014 |  10
 Neg2         | 2015 |  72
 Neg3         | 2014 |  20
 Neg3         | 2015 |  64
 Neg4         | 2014 |  32
 Neg4         | 2015 |  37
              | 2014 |  69
              | 2015 | 204

Tuttavia possiamo verificare come il piano della versione che usa GROUPING SETS sia nettamente migliore. Questo è il piano della versione con UNION ALL:

QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=359.27..362.45 rows=1270 width=68) (actual time=0.774..0.775 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  Append  (cost=133.95..293.80 rows=1270 width=68) (actual time=0.379..0.708 rows=10 loops=1)
             ->  HashAggregate  (cost=133.95..147.33 rows=1070 width=68) (actual time=0.379..0.403 rows=8 loops=1)
                   Group Key: l.nome_negozio, g.anno
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.128..0.253 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.073..0.150 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.016..0.033 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.026..0.026 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.012 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.023..0.023 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.008 rows=3 loops=1)
             ->  Subquery Scan on "*SELECT* 2"  (cost=131.28..135.78 rows=200 width=68) (actual time=0.297..0.303 rows=2 loops=1)
                   ->  HashAggregate  (cost=131.28..133.78 rows=200 width=36) (actual time=0.294..0.297 rows=2 loops=1)
                         Group Key: g_1.anno
                         ->  Hash Join  (cost=75.80..125.93 rows=1070 width=36) (actual time=0.097..0.215 rows=32 loops=1)
                               Hash Cond: (v_1.id_data = g_1.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=36) (actual time=0.050..0.120 rows=32 loops=1)
                                     Hash Cond: (v_1.id_luogo = l_1.id)
                                     ->  Seq Scan on vendite v_1  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.006..0.021 rows=32 loops=1)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=4) (actual time=0.017..0.017 rows=4 loops=1)
                                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                           ->  Seq Scan on luoghi l_1  (cost=0.00..16.30 rows=630 width=4) (actual time=0.004..0.008 rows=4 loops=1)
                               ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.015..0.015 rows=3 loops=1)
                                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                                     ->  Seq Scan on giorni g_1  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.003..0.006 rows=3 loops=1)
     Planning time: 1.433 ms
     Execution time: 1.330 ms

Mentre questo è il piano della versione con GROUPING SETS, più rapido e più gradevole alla vista:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=271.81..274.99 rows=1270 width=68) (actual time=0.475..0.476 rows=10 loops=1)
       Sort Key: l.nome_negozio, g.anno
       Sort Method: quicksort  Memory: 25kB
       ->  GroupAggregate  (cost=179.76..206.34 rows=1270 width=68) (actual time=0.312..0.426 rows=10 loops=1)
             Group Key: g.anno, l.nome_negozio
             Group Key: g.anno
             ->  Sort  (cost=179.76..182.44 rows=1070 width=68) (actual time=0.287..0.299 rows=32 loops=1)
                   Sort Key: g.anno, l.nome_negozio
                   Sort Method: quicksort  Memory: 27kB
                   ->  Hash Join  (cost=75.80..125.93 rows=1070 width=68) (actual time=0.089..0.197 rows=32 loops=1)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=24.18..59.59 rows=1070 width=68) (actual time=0.059..0.124 rows=32 loops=1)
                               Hash Cond: (v.id_luogo = l.id)
                               ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=40) (actual time=0.017..0.031 rows=32 loops=1)
                               ->  Hash  (cost=16.30..16.30 rows=630 width=36) (actual time=0.022..0.022 rows=4 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                     ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36) (actual time=0.006..0.010 rows=4 loops=1)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=8) (actual time=0.013..0.013 rows=3 loops=1)
                               Buckets: 2048  Batches: 1  Memory Usage: 17kB
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.007 rows=3 loops=1)
     Planning time: 0.741 ms
     Execution time: 0.713 ms

Inoltre, potremmo voler raggruppate le nostre vendite per regione e categoria, con l’aggiunta di una riga che mostri il totale. In questo caso, utilizzeremo la seguente query:

SELECT l.regione, p.categoria, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
GROUP BY GROUPING SETS ((l.regione, p.categoria), ())
ORDER BY regione, categoria;

Ottenendo come risultato:

 regione | categoria | totale
---------+-----------+--------
 Toscana | cat1      |    104
 Toscana | cat2      |    100
 Umbria  | cat1      |     14
 Umbria  | cat2      |     55
         |           |    273

GROUPING SETS è l’operatore più generico dei tre, e permette di raggruppare dati in base a qualsiasi insieme di parametri definito dall’utente. CUBE e ROLLUP, come vedremo, sono solo comode scorciatoie per alcuni utilizzi molto comuni di GROUPING SETS.

CUBE

L’operatore CUBE raggruppa sull’insieme di tutte le combinazioni possibili dei vari campi passati come parametro. Supponiamo, ad esempio, di voler vedere i totali delle vendite organizzati per categoria, provincia e mese/anno. Lanciando la query:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY CUBE((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

otterremo il seguente risultato:

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Firenze   |           |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Perugia   |           |   11 | 2014 |     32
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
 Prato     |           |   11 | 2014 |     17
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Firenze   |           |   10 | 2015 |     40
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Perugia   |           |   10 | 2015 |     15
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
 Prato     |           |   10 | 2015 |     47
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Firenze   |           |   11 | 2015 |     24
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Perugia   |           |   11 | 2015 |     22
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
 Prato     |           |   11 | 2015 |     56
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
 Firenze   | cat1      |      |      |     44
 Firenze   | cat2      |      |      |     40
 Firenze   |           |      |      |     84
 Perugia   | cat1      |      |      |     14
 Perugia   | cat2      |      |      |     55
 Perugia   |           |      |      |     69
 Prato     | cat1      |      |      |     60
 Prato     | cat2      |      |      |     60
 Prato     |           |      |      |    120
           | cat1      |      |      |    118
           | cat2      |      |      |    155
           |           |      |      |    273

Avremmo potuto scrivere la stessa query usando GROUPING SETS in questo modo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  ((g.mese, g.anno),l.provincia),
  (p.categoria, l.provincia),
  (g.mese, g.anno),
  p.categoria,
  l.provincia,
  ()
)
ORDER BY anno, mese, provincia, categoria;

Il piano di esecuzione è lo stesso per entrambe le versioni:

QUERY PLAN
    ------------------------------------------------------------------------------------------------------
     Sort  (cost=856.15..868.35 rows=4881 width=104)
       Sort Key: g.anno, g.mese, l.provincia, p.categoria
       ->  GroupAggregate  (cost=223.60..557.12 rows=4881 width=104)
             Group Key: p.categoria, l.provincia, g.mese, g.anno
             Group Key: p.categoria, l.provincia
             Group Key: p.categoria
             Group Key: ()
             Sort Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno, p.categoria
               Group Key: g.mese, g.anno
             Sort Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia, g.mese, g.anno
               Group Key: l.provincia
             ->  Sort  (cost=223.60..226.28 rows=1070 width=104)
                   Sort Key: p.categoria, l.provincia, g.mese, g.anno
                   ->  Hash Join  (cost=104.92..169.76 rows=1070 width=104)
                         Hash Cond: (v.id_data = g.id)
                         ->  Hash Join  (cost=53.30..103.43 rows=1070 width=100)
                               Hash Cond: (v.id_prodotto = p.id)
                               ->  Hash Join  (cost=24.18..59.59 rows=1070 width=72)
                                     Hash Cond: (v.id_luogo = l.id)
                                     ->  Seq Scan on vendite v  (cost=0.00..20.70 rows=1070 width=44)
                                     ->  Hash  (cost=16.30..16.30 rows=630 width=36)
                                           ->  Seq Scan on luoghi l  (cost=0.00..16.30 rows=630 width=36)
                               ->  Hash  (cost=18.50..18.50 rows=850 width=36)
                                     ->  Seq Scan on prodotti p  (cost=0.00..18.50 rows=850 width=36)
                         ->  Hash  (cost=28.50..28.50 rows=1850 width=12)
                               ->  Seq Scan on giorni g  (cost=0.00..28.50 rows=1850 width=12)

ROLLUP

ROLLUP, come CUBE, è una semplificazione di GROUPING SETS. In questo caso i raggruppamenti sono effettuati prima su tutti i campi su cui è stato dichiarato l’operatore, poi su tutti meno l’ultimo, su tutti meno gli ultimi due e così via. Ad esempio, usando ROLLUP al posto di CUBE nell’ultima query otterremo:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY ROLLUP((g.mese, g.anno),p.categoria, l.provincia)
ORDER BY anno, mese, provincia, categoria;

 provincia | categoria | mese | anno | totale
-----------+-----------+------+------+--------
 Firenze   | cat1      |   11 | 2014 |     20
 Perugia   | cat1      |   11 | 2014 |      2
 Perugia   | cat2      |   11 | 2014 |     30
 Prato     | cat1      |   11 | 2014 |     12
 Prato     | cat2      |   11 | 2014 |      5
           | cat1      |   11 | 2014 |     34
           | cat2      |   11 | 2014 |     35
           |           |   11 | 2014 |     69
 Firenze   | cat1      |   10 | 2015 |     20
 Firenze   | cat2      |   10 | 2015 |     20
 Perugia   | cat1      |   10 | 2015 |     10
 Perugia   | cat2      |   10 | 2015 |      5
 Prato     | cat1      |   10 | 2015 |     22
 Prato     | cat2      |   10 | 2015 |     25
           | cat1      |   10 | 2015 |     52
           | cat2      |   10 | 2015 |     50
           |           |   10 | 2015 |    102
 Firenze   | cat1      |   11 | 2015 |      4
 Firenze   | cat2      |   11 | 2015 |     20
 Perugia   | cat1      |   11 | 2015 |      2
 Perugia   | cat2      |   11 | 2015 |     20
 Prato     | cat1      |   11 | 2015 |     26
 Prato     | cat2      |   11 | 2015 |     30
           | cat1      |   11 | 2015 |     32
           | cat2      |   11 | 2015 |     70
           |           |   11 | 2015 |    102
           |           |      |      |    273

ovvero, l’equivalante di:

SELECT l.provincia, p.categoria, g.mese, g.anno, sum(prezzo) AS totale
FROM vendite v
JOIN luoghi l ON (v.id_luogo=l.id)
JOIN prodotti p ON (v.id_prodotto=p.id)
JOIN giorni g ON (v.id_data=g.id)
GROUP BY GROUPING SETS(
  ((g.mese, g.anno),p.categoria, l.provincia),
  ((g.mese, g.anno),p.categoria),
  (g.mese, g.anno),
  ()
)
ORDER BY anno, mese, provincia, categoria;

Conclusioni

Versione dopo versione PostgreSQL sta facendo passi da gigante in ambito OLAP e data warehouse – o, se preferite, BigData e Analytics. Oltre ai comandi mostrati in questo articolo, solo nella 9.5 ad esempio sono stati aggiunti gli indici BRIN, ereditarietà delle tabelle esterne e IMPORT FOREIGN SCHEMA, che permettono a PostgreSQL di gestire quantità ancora più grandi di dati e con più facilità.

Siamo sicuri che chi usa PostgreSQL per fare Business Intelligence apprezzerà notevolmente questa nuova major release.

Le query UPSERT servono a gestire in modo efficiente i casi in cui ad una tabella vogliamo aggiornare il contenuto di una riga oppure inserirne una nuova.

UPSERT = IF NOT found THEN insert ELSE update

Quello che segue è un piccolo esempio pratico che mette in mostra il funzionamento delle interrogazioni UPSERT.

Relazioni fra film e compositori

IMdb è un sito che contiene molte informazioni su film e personale di produzione. Oltre ad utilizzare la sua interfaccia web è anche possibile ottenere i dati direttamente in formato testo.

In questo esempio useremo il file composers.list, di cui quello che segue è un estratto:

Abraham, Alexander William      The Play Front (2010)
                        Voices from the Basement (2010)
                        "Rwby" (2012) {A Minor Hiccup (#2.3)}
                        "Rwby" (2012) {Best Day Ever (#2.1)}
                        "Rwby" (2012) {Black and White (#1.16)}
                        "Rwby" (2012) {Breach (#2.12)}
                        "Rwby" (2012) {Burning the Candle (#2.6)}
                        "Rwby" (2012) {Dance Dance Infiltration (#2.7)}
                        "Rwby" (2012) {Extracurricular (#2.5)}
                        "Rwby" (2012) {Field Trip (#2.8)}

Aceto, Robby            101 Ways to Retire--or Not! (2007) (V)
                        Freak the Language (2015)
                        Hidden Books: The Art of Kumi Korf (2011)
                        Inside (2013/IV)
                        Invisible Ink (2011)
                        Jungle Warfare College (2013)  (music composer)
                        Mr. Stokes' Mission (2012)

Goldshein, Steve        Red vs. Blue Season 9 (2011)
                        Whispering Willows (2014) (VG)
                        "Rwby" (2012) {A Minor Hiccup (#2.3)}
                        "Rwby" (2012) {Breach (#2.12)}
                        "Rwby" (2012) {Field Trip (#2.8)}
                        "Rwby" (2012) {Forever Fall (#1.13)}
                        "Rwby" (2012) {Forever Fall: Part 2 (#1.14)}
                        "Rwby" (2012) {Jaunedice (#1.11)}
                        "Rwby" (2012) {Jaunedice: Part 2 (#1.12)}
                        "Rwby" (2012) {Mountain Glenn (#2.10)}
                        "Rwby" (2012) {No Brakes (#2.11)}
                        "Rwby" (2012) {Painting the Town... (#2.4)}
                        "Rwby" (2012) {Search and Destroy (#2.9)}
                        "Rwby" (2012) {The Emerald Forest (#1.6)}
                        "Rwby" (2012) {The First Step: Part 2 (#1.5)}
                        "Rwby" (2012) {Welcome to Beacon (#2.2)}
                        "X-Ray and Vav" (2014) {Operation: Rescue Friend (#1.2)}
                        "X-Ray and Vav" (2014) {X-Ray & Vav Rise (#1.1)}

Il file è composto di associazioni fra nomi di compositori e film. I dati non hanno un ordine particolare e sia il nome del compositore che il nome del film possono essere ripetuti più volte.

Supponiamo di voler popolare queste tabelle:

-- In questa tabella dovremo inserire un record
-- per ogni compositore. Il campo how_many_films dovra'
-- contenere il numero di film nei quali il compositore
-- ha lavorato
CREATE TABLE composer (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(1024) NOT NULL UNIQUE,
    how_many_films INTEGER NOT NULL DEFAULT 1
);

-- In questa tabella dovremo inserire un record
-- per ogni film. Il campo how_many_composers
-- dovra' contenere il numero dei compositori
-- che hanno lavorato nel film
CREATE TABLE film (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(1024) NOT NULL UNIQUE,
    how_many_composers INTEGER NOT NULL DEFAULT 1
);

-- In questa tabella dovremo inserire un record
-- per ogni coppia compositore/film
CREATE TABLE composer_film (
    id SERIAL NOT NULL PRIMARY KEY,
    composer_id INTEGER NOT NULL,
    film_id INTEGER NOT NULL,
    CONSTRAINT fk_composer_film_composer 
      FOREIGN KEY (composer_id) REFERENCES composer (id),
    CONSTRAINT fk_composer_film_film
      FOREIGN KEY (film_id) REFERENCES film (id)
);

Adesso analizzeremo due strategie per l’inserimento di dati. Le istruzioni che seguono dovranno essere ripetute per ogni riga del file.

Strategia classica

Come prima cosa occorre inserire il compositore, se non esiste già:

SELECT id FROM composer WHERE name='Abraham, Alexander William';
-- ==> (nessun risultato, quindi lo devo inserire)

INSERT INTO composer (name) VALUES ('Abraham, Alexander William') 
RETURNING (id);
-- ==> 22

Se abbiamo già un id per il compositore, dobbiamo aggiornare il numero di film nei quali ha lavorato:

UPDATE composer SET how_many_films=how_many_films+1 WHERE id=12;

Analogamente a quanto fatto per i compositori, inseriamo i film:

SELECT id FROM film WHERE name='The Play Front (2010)'
-- ==> (nessun risultato, quindi lo devo inserire)

INSERT INTO film (name) VALUES ('The Play Front (2010)') RETURNING (id);
-- ==> 454  

Se il film è già presente nella tabella, basterà incrementare il numero di compositori:

UPDATE film SET how_many_composers=how_many_composers+1 WHERE id=343; 

Una volta gestite le tabelle composer e film possiamo inserire i dati nella tabella composer_film:

INSERT INTO composer_film (composer_id, film_id) VALUES (22, 343);  

In totale eseguiremo:

• due query per ogni compositore (controllo e inserimento oppure controllo e aggiornamento);
• due query per ogni film (controllo e inserimento oppure controllo e aggiornamento);
• una query di inserimento per ogni coppia film/compositore.

NOTA: Per una maggiore efficacia a livello didattico, abbiamo semplificato la gestione delle transazioni negli esempi precedenti (che non escludono infatti possibili casi di concorrenza sugli stessi record).

Strategia UPSERT

Utilizzando l’istruzione UPSERT il primo passo si semplifica perché PostgreSQL controlla per noi l’esistenza del compositore:

INSERT INTO composer (name) VALUES ('Abraham, Alexander William')
ON CONFLICT (name) DO UPDATE SET how_many_films=composer.how_many_films+1 
RETURNING (id)
-- ==> 22 (funziona indipendentemente dall'esistenza del film)

Cosa analoga succede anche per l’inserimento del film:

INSERT INTO film (name) VALUES ('"Rwby" (2012) {Best Day Ever (#2.1)}')
ON CONFLICT (name) DO UPDATE SET how_many_composers=film.how_many_composers+1 
RETURNING (id)
-- ==> 343 (funziona indipendentemente dall'esistenza del film)

Non rimane che inserire la relazione fra le due tabelle:

INSERT INTO composer_film (composer_id, film_id) VALUES (22, 343);

In totale eseguiremo:

• una query di upsert per i compositori;
• una query di upsert per i film;
• una query di inserimento per ogni coppia film/compositore.

Con UPSERT risparmiamo le query di controllo, cioè una query per ogni film ed una query per ogni compositore. Visto che il file dal quale stiamo leggendo i dati contiene circa un milione di righe ci aspettiamo che le performance del programma che utilizza le UPSERT siano nettamente migliori.

Conclusione

Le UPSERT sono istruzioni molto comode per gli sviluppatori: permettono di esprimere in una singola interrogazione combinazioni di istruzioni molto frequenti. Nel nostro caso, con una unica istruzione abbiamo inserito un nuovo record e ne abbiamo aggiornato uno già presente.

Quello che abbiamo applicato è ovviamente un caso particolare di UPSERT e per la documentazione completa rimandiamo al manuale di PostgreSQL, che ne documenta tutte le funzionalità.

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.

Gli indici BRIN (Block Range INdex) rappresentano una delle maggiori novità presenti in PostgreSQL 9.5. Si tratta di un nuovo tipo di indice che arricchisce la collezione già presente, aggiungendosi a quelli “ad albero” (btree, GiST/SP-GiST e GIN) ed Hash. Si tratta comunque di un indice molto differente dagli altri: non è basato sui singoli valori che devono essere indicizzati, ma sulle pagine da 8kB di PostgreSQL.

L’algoritmo alla base di questa indicizzazione unisce le caratteristiche della scansione sequenziale dei record di una tabella (SeqScan) con quella basata su un indice ad albero (IndexScan): durante la costruzione dei BRIN, le pagine di PostgreSQL vengono scansionate in blocchi, sequenzialmente, e per ogni blocco vengono mappati gli estremi dei valori contenuti che devono essere indicizzati. In un secondo tempo poi, il planner di PostgreSQL saprà quali sono i blocchi di pagine PostgreSQL che devono essere presi in considerazione durante l’esecuzione delle query.

Esistono due tipi di supporto per questo tipo di indice:

• minmax, che si occupa di mappare i valori minimi e massimi di un attributo indicizzato;
• inclusion, in cui vengono mappati gli estremi dell’intervallo in cui i valori dell’attributo indicizzato possono variare.

La differenza tra quest’ultimo tipo di supporto ed il precedente sta nel fatto che esso permette anche l’indicizzazione di tipi di dato cosiddetti “non-ordinabili”, ovvero che non presentano cardinalità come ad esempio per i numeri o le stringhe.

Per come sono definiti, gli indici BRIN presentano due grandi vantaggi:

• occupano uno spazio su disco notevolmente minore degli altri indici (che hanno invece una dimensione paragonabile a quella dell’intera tabella indicizzata), e dunque sono particolarmente utili nel caso di tabelle molto grandi;
• richiedono poca manutenzione rispetto agli altri indici.

Vari esempi sono stati mostrati sull’uso dei BRIN[¹][²][³], in particolare per il tipo di supporto minmax. Vorrei invece parlare adesso del supporto di tipo inclusion, e di quanto sia utile se si ha a che fare con dati geospaziali.

Un esempio di uso per i punti

I punti, come ogni altra entità geospaziale, soffrono del fatto che non contemplano criteri di ordinamento (non è possibile definire “un punto maggiore di un altro”), per cui non possono essere indicizzabili con le metodologie standard usate ad esempio per gli indici btree.

Esistono tuttavia in PostgreSQL l’indice GiST, che si basa su algoritmi “di ordinamento” quali R-tree ed k-NN, e quello SP-GiST, basato invece su Quad-tree e kd-tree, che sono in grado di indicizzare dati geospaziali. La differenza tra i due indici è data dal fatto che, mentre il primo utilizza una struttura ad albero bilanciato, il secondo ne usa uno non bilanciato.

Le strutture non bilanciate non sono generalmente molto utili quando si ha a che fare con numeri e stringhe; viceversa, tendono ad essere usati in ambito geospaziale, soprattutto per ricerce del tipo “inclusione all’interno di bounding box”.

Il mio intento adesso è di presentare un semplice esempio in cui confrontare le prestazioni ottenibili effettuando questo tipo di ricerche basandosi sugli indici attualmente presenti (GiST, SP-GiST) e sui futuri BRIN sfruttando il supporto di tipo inclusion.

Innanzitutto è necessario installare la versione beta (per il momento in cui è stato scritto questo articolo) di PostgreSQL 9.5: ad esempio, io ho eseguito i miei test su una macchina CentOS 6.5, ed ho dunque installato il repository yum di PGDG per poter accedere ai pacchetti di PostgreSQL 9.5beta[⁴].

Consideriamo poi, ad esempio, di costruire una tabella contenente 10000000 di punti casualmente distribuiti sul piano all’interno di un’area 100unità X 100unità (sfruttiamo qui a titolo di esempio il tipo di dato point presente nel core del database PostgreSQL, tralasciando le geometrie fornite dall’estensione PostGIS):

CREATE TABLE points AS (
SELECT id,
point(100.0 * random(), 100.0 * random()) AS point
FROM generate_series(1,10000000) AS id);

Costruiamo poi sulla colonna point un primo indice di tipo GiST:

CREATE INDEX gist_index ON points USING gist(box(point));

ed un secondo indice di tipo SP-GiST:

CREATE INDEX spgist_index ON points USING spgist(box(point));

Osserviamo dimensioni e tempi di esecuzione nella costruzione dei due indici:

L’indice SP-GiST, essendo non bilanciato, presenta una struttura meno complessa che si traduce in minor spazio occupato e minori tempi di esecuzione per la sua costruzione.

Costruiamo adesso l’indice BRIN sullo stesso campo della tabella:

CREATE INDEX brin_index ON points
USING brin(box(point) box_inclusion_ops);

Da notare la specifica dell’operator class box_inclusion_ops, che detta all’indice quali siano gli operatori con supporto inclusion che dovranno utilizzarlo. È bene precisare che gli indici BRIN vengono costruiti considerando di default blocchi da 128 pagine da 8kB di PostgreSQL: questo significa che potrà restituire un numero di blocchi (fino a 128) che potrebbero anche non contenere i dati richiesti sulla base della ricerca e che quindi, come anticipato sopra, dovranno essere scartati in un secondo tempo dal planner PostgreSQL.

È possibile comunque aumentare la “risoluzione” dell’informazione immagazzinata dall’indice BRIN diminuendo la dimensione del blocco di pagine PostgreSQL usato durante la sua costruzione, in modo da aumentarne l’efficienza di utilizzo: questo a scapito ovviamente della dimensione dell’indice che occuperà più spazio. Proviamo a confrontare come cambia un indice BRIN richiedendo che la dimensione del blocco di pagine PostgreSQL considerate sia pari a 64 (la metà del default) configurando il parametro pages_per_range:

CREATE INDEX brin64_index ON points
USING brin(box(point) box_inclusion_ops)
WITH (pages_per_range = 64);

Il risultato ci sorprende: l’indice BRIN occupa davvero uno spazio molto ridotto, con tempi di esecuzione considerabili “istantanei” rispetto a quelli degli altri indici.

Conclusioni

Proviamo adesso a lanciare la query di ricerca dei punti inclusi all’interno di un quadrato 50×50, “immerso” tra i punti della tabella:

SELECT * FROM points
WHERE box(point) <@ box(point(20, 20), point(70, 70));

Effettivamente notiamo, soffermandoci agli indici finora presenti in PostgreSQL, come la struttura non bilanciata sia più efficiente in questo tipo di ricerche (~50% in meno di tempo necessario):

Confrontiamo i tempi di esecuzione della query sfruttando gli indici BRIN:

Anche qui rimaniamo piacevolmente sorpresi: gli indici BRIN assicurano tempi di esecuzione paragonabili a quelli dell’indice non bilanciato SP-GiST nel caso di ricerche del tipo “inclusione all’interno di bounding box”, ma potendo vantare dimensioni e tempi di costruzione dell’indice stesso praticamente irrisori rispetto all’SP-GiST.

Concludendo, dagli altri blog[⁵][⁶] abbiamo imparato che gli indici BRIN, se usati per ricerche che si basano sul supporto minmax ad esempio su numeri o stringhe, hanno prestazioni generalmente inferiori agli altri indici (esempio il btree) che aumentano man mano che la “risoluzione” del BRIN viene espansa tramite il parametro pages_per_range.

In questo articolo abbiamo visto come gli indici BRIN usati per ricerche che si basano sul supporto inclusion hanno prestazioni del tutto simili a quelle degli altri indici, occupando uno spazio su disco molto inferiore.

In ogni caso, con PostgreSQL 9.5 i BRIN possono vantare miglioramenti in termini di manutenibilità, dimensione e tempi di creazione rispetto agli indici finora presenti in PostgreSQL.

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.