Per lungo tempo una delle più note mancanze di PostgreSQL è stata la possibilità di parallelizzare le query. Con l’uscita della versione 9.6 non sarà più così. È stato infatti svolto un grande lavoro sul tema, per il quale il primo risultato è stato il commit 80558c1, in cui viene introdotta la parallelizzazione dei sequential scan in alcuni casi che vedremo nel corso di questo articolo.

Innanzitutto, una premessa: lo sviluppo di questa feature è stato continuo e alcuni parametri hanno cambiato nome nel susseguirsi di commit. L’articolo è stato scritto con un checkout al 17 giugno, e presenta alcune caratteristiche che saranno presenti solo dalla beta2 della 9.6.

Rispetto alla major 9.5 sono stati introdotti nuovi parametri all’interno della configurazione. Questi sono:

• max_parallel_workers_per_gather: il numero di worker che possono assistere un sequential scan su una tabella;
• min_parallel_relation_size: la dimensione minima che deve avere una relazione affinché il planner consideri l’uso di worker aggiuntivi;
• parallel_setup_cost: parametro del planner che valuta il costo di istanziare un worker;
• parallel_tuple_cost: parametro del planner che valuta il costo di trasferire una tupla da un worker a un altro;
• force_parallel_mode: parametro utile per i test, forza il parallelismo anche su query su cui il planner agirebbe in altri modi.

Vediamo come i worker aggiuntivi possono essere usati per velocizzare le nostre query. Creiamo una tabella di test con un campo INT e cento milioni di record:

postgres=# CREATE TABLE test (i int);
CREATE TABLE
postgres=# INSERT INTO test SELECT generate_series(1,100000000);
INSERT 0 100000000
postgres=# ANALYSE test;
ANALYZE

Di default PostgreSQL ha max_parallel_workers_per_gather impostato a 2, per cui verranno attivati due worker durante un sequential scan.

Un semplice sequential scan non presenta novità alcuna:

postgres=# EXPLAIN ANALYSE SELECT * FROM test;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=0.081..21051.918 rows=100000000 loops=1)
 Planning time: 0.077 ms
 Execution time: 28055.993 ms
(3 rows)

È infatti richiesta la presenza di una clausola WHERE per la parallelizzazione:

postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..964311.60 rows=1 width=4) (actual time=3.381..9799.942 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..963311.50 rows=0 width=4) (actual time=6525.595..9791.066 rows=0 loops=3)
         Filter: (i = 1)
         Rows Removed by Filter: 33333333
 Planning time: 0.130 ms
 Execution time: 9804.484 ms
(8 rows)

Possiamo tornare al comportamento precedente e osservarne le differenze impostando max_parallel_workers_per_gather a 0:

postgres=# SET max_parallel_workers_per_gather TO 0;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1692478.40 rows=1 width=4) (actual time=0.123..25003.221 rows=1 loops=1)
   Filter: (i = 1)
   Rows Removed by Filter: 99999999
 Planning time: 0.105 ms
 Execution time: 25003.263 ms
(5 rows)

Un tempo 2.5 volte maggiore.

Non sempre il planner considera un sequential scan parallelo la migliore opzione. Se la query non è abbastanza selettiva e ci sono molte tuple da trasferire, è possibile che sia preferito un sequential scan "classico":

postgres=# SET max_parallel_workers_per_gather TO 2;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i<90000000;
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1692478.40 rows=90116088 width=4) (actual time=0.073..31410.276 rows=89999999 loops=1)
   Filter: (i < 90000000)
   Rows Removed by Filter: 10000001
 Planning time: 0.133 ms
 Execution time: 37939.401 ms
(5 rows)

Infatti, se proviamo a forzare un sequential scan parallelo, otteniamo un risultato peggiore:

postgres=# SET parallel_tuple_cost TO 0;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i<90000000;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..964311.50 rows=90116088 width=4) (actual time=0.454..75546.078 rows=89999999 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on test  (cost=0.00..1338795.20 rows=37548370 width=4) (actual time=0.088..20294.670 rows=30000000 loops=3)
         Filter: (i < 90000000)
         Rows Removed by Filter: 3333334
 Planning time: 0.128 ms
 Execution time: 83423.577 ms
(8 rows)

Possiamo incrementare il numero di worker fino a raggiungere max_worker_processes (default: 8). Ripristiniamo il valore di parallel_tuple_cost vediamo quello che accade aumentando max_parallel_workers_per_gather a 8.

postgres=# SET parallel_tuple_cost TO DEFAULT ;
SET
postgres=# SET max_parallel_workers_per_gather TO 8;
SET
postgres=# EXPLAIN ANALYZE SELECT * FROM test WHERE i=1;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..651811.50 rows=1 width=4) (actual time=3.684..8248.307 rows=1 loops=1)
   Workers Planned: 6
   Workers Launched: 6
   ->  Parallel Seq Scan on test  (cost=0.00..650811.40 rows=0 width=4) (actual time=7053.761..8231.174 rows=0 loops=7)
         Filter: (i = 1)
         Rows Removed by Filter: 14285714
 Planning time: 0.124 ms
 Execution time: 8250.461 ms
(8 rows)

Nonostante PostgreSQL potesse usare fino a 8 worker, ne ha instanziati solo 6. Questo perché Postgres ottimizza il numero di worker anche in base alle dimensioni della tabella e al parametro min_parallel_relation_size. Il numero dei worker messi a disposizione da postgres si basa su una successione geometrica di ragione 3 il cui primo termine è min_parallel_relation_size. Facciamo un esempio. Considerando gli 8MB del default del parametro:

Possiamo vedere che, essendo la nostra tabella 3458MB, 6 è il massimo numero di worker disponibili.

postgres=# \dt+ test
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 3458 MB |
(1 row)

Per concludere, una breve dimostrazione dei miglioramenti ottenuti da attraverso questa patch. Lanciando la nostra query abilitando un numero crescente di worker, otteniamo i seguenti risultati:

Possiamo vedere che i tempi migliorano notevolmente, fino ad arrivare ad un terzo del valore iniziale. È semplice da spiegare anche il fatto che non ci siano miglioramenti fra l’uso di tre e 6 worker: la macchina su cui è stato eseguito il test ha 4 cpu disponibili, per cui dopo 3 worker più il processo originale i risultati si stabilizzano.

Per concludere, con la 9.6 PostgreSQL ha posto le basi per la parallelizzazione delle query, di cui il sequential scan parallelo è solo il primo, ottimo, risultato. Vedremo infatti come sempre nella 9.6 siano state parallelizzate anche le aggregazioni, ma questo è materiale per un altro articolo che uscirà nelle prossime settimane.

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à.