PostgreSQL & cstore_fdw

cstore_fdw

cstore_fdw è una (delle molte) estensioni messe a disposizione dalla community di PostgreSQL, che permette di salvare in modalità colonnare i nostri dati all'interno della base dati di PostgreSQL.


Come funziona 
Da come si può dedurre dalle iniziali "fdw" (foreign-data wrapper), questa estensione mette disposizione una nuova tipologia di tabelle con funzionalità e formato diverso da quello standard utilizzato in PostgreSQL. Per gestire i dati, le tabelle sono suddivise in due tipologie di file:

Data File

Qui trovano posto i dati. Questi vengono salvati nell'ordine in cui sono inseriti (vedremo più avanti le conseguenze di ciò). Ogni data file è unico e (a differenza dei data-file PostgreSQL) non viene suddiviso raggiunta una determinata dimensione. Al suo interno i dati vengono suddivisi in "Row Stripes" che raggruppano set di tuple (il numero di righe è configurabile) che costituiscono la nostra tabella. Le Row Stripes sono caratterizzate da tre sezioni:

• Stripe Skip List: si possono considerare l'indice delle nostre tabelle, mantengono il valore minimo e massimo di ogni colonna contenuta all'interno del singolo Row Stripe. Questo permette di identificare (data una condizione where) se l'informazione risiede o meno all'interno del suddetto Row Stripe.
• Stripe Data: qui vengono contenuti i dati più un paio di informazioni di supporto (esempio la presenza o meno di valori nulli). Inoltre questa sezione è soggetta a compressione se tale opzione è attiva. 
• Stripe Footer: qui vengono mantenute informazioni quali la dimensione dei vari Stripe Skip List e Stripe Data.

Per gestire le varie Row Stripes, contenute in un data file, viene aggiunto un secondo file di supporto o "Footer File", dove trovano posto le informazioni sulla dimensione e la posizione di ogni singolo Row Stripe.

Indicizzazione dei dati

Come accennato sopra, i dati all'interno del nostro data file sono raggruppati in set di righe e quindi indicizzati. Per ogni set di righe viene calcolato il minimo e il massimo valore contenuto nelle relative colonne. Questo comporta in fase di ricerca di identificare se una determinata informazione si trovi nel rowset che si sta analizzando, evitando di dover fare lunghe scansioni di ogni singolo Row Stripe. A tal proposito è consigliabile importare i dati nell'ordine con cui, realisticamente, verranno effettuate le ricerche.

Quindi se la nostra tabella conterrà:

(

dt_day timestamp without time zone NOT NULL, -- Data

list_id integer NOT NULL,    --Identificativo di riga

count integer

)

e prevediamo di effettuare query che insisteranno sul campo dt_day e list_day, una buona soluzione sarebbe quella di caricare i dati già ordinati per dt_day, list_id, count. Questo comporta la riduzione di overlap delle informazioni su diverse Row Stripes e quindi aumentare il numero di Row Stripes esclusi in fase di scansione.

Installazione

L'installazione effettuata su CentOS 7 si è rivelata semplice e veloce.

Installate protobuf-c-devel, su CentOS 7 dovrete aggiungere il repository EPEL:

sudo yum install protobuf-c-devel

Aggiungete nel PATH il percorso dove si trova il file pg_config:

PATH=/usr/local/pgsql/bin/:$PATH

Infine eseguite il make install.

Aggiungete nel file postgresql.conf (dovrete riavviare il servizio di PostgreSQL):

shared_preload_libraries = 'cstore_fdw'

Utilizzo

Fatta eccezione per alcune parametri (non obbligatori) che è possibile dichiarare in fase di inizializzazione delle nostre tabelle, non è necessario conoscere nulla di più delle normali istruzioni SQL, che già utilizziamo per inserire o estrarre dati.

Di seguito un esempio:

--Procediamo con l'installare l'estensione nel nostro database. CREATE EXTENSION cstore_fdw;
 

--Creiamo il l'oggetto server per mezzo del quale utilizzeremo il 'cstore_fdw'

CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
 

--Creiamo la nostra tabella

CREATE FOREIGN TABLE cs_count_view_per_ads
( 

dt_day timestamp without time zone NOT NULL,
list_id integer NOT NULL,
count integer
)

SERVER cstore_server OPTIONS (compression 'pglz');

Qui mi sono limitato a dichiarare solo l'opzione "compress". Ma è possibile utilizzare anche le seguenti opzioni:

filename: indicando il percorso assoluto e il nome del file da utilizzare, è possibile specificare dove il file che conterrà i dati della tabella sarà salvato.

compression: le opzioni previste sono "none" e "pglz". Attiva o meno la compressione dati.

stripe_row_count: numero di righe che costituiscono una singola Row Stripe. Considerate che più è alto questo valore, migliori saranno le prestazioni, ma maggiore sarà la richiesta di memoria. Default 150.000.

block_row_count: numero di righe per singola colonna. Il default è 1.000. Modificando questo valore, si va a modificare la "risoluzione" (o granularità) del nostro indice. Più è basso più preciso sarà il nostro indice. Di contro aumenteranno le richieste di lettura del nostro Row Stripe e diminuirà il rapporto di compressione. Utilizzare valori bassi torna utile nel caso di avere dati poco ordinati e richieste che recuperano set di dati relativamente piccoli.

L'utilizzo di valori più alti, comporta una migliore compressione e una riduzione delle letture, ma (soprattutto se i dati non sono correttamente ordinati) potrebbero crescere il numero di overlap dei dati fra i vari Row Stripe (con conseguente riduzione delle prestazioni).

Ovviamente non c'è la configurazione perfetta, fate qualche prova, utilizzando set di dati il più simile possibile (per quantità e tipologia) a quelli che poi utilizzerete in produzione.

Per caricare la nostra tabella, possiamo utilizzare:

• il comando COPY 
• INSERT INTO ... SELECT ... FROM
• Infine ricordate di fare un ANALYZE della tabella (per aggiornare le statistiche).
  

Alcune considerazioni

Sulla nostra tabella, abbiamo caricato circa 295 milioni di righe. 

count_view_per_ads (normale tabella PostgreSQL):

• righe: 295M
• dimensione tabella: 12GB
• dimensione indici (dt_day, list_id): 9GB

cs_count_view_per_ads:

• righe: 295M
• dimensioni tabella: 1.3GB

Ovviamente il rapporto di compressione può variare in base alla tipologia e allo schema della tabella, in ogni caso i livelli di compressione sono sempre nell'ordine del 50% (o superiori) rispetto alla tabella originale. A questo bisogna aggiungere che non viene allocato spazio aggiuntivo per gli indici.

Per quanto riguarda le prestazioni vi invito a leggere qui:

https://www.citusdata.com/citus-products/cstore-fdw/cstore-fdw-quick-start-guide

Per la mia esperienza non ho riscontrato miglioramenti nei tempi di elaborazione delle query, in alcuni casi sono leggermente inferiori alle rispettive query che utilizzano normali tabelle con indici btree di PostgreSQL.

Inoltre va tenuto in considerazione alcune limitazioni:

• non è possibile utilizzare istruzioni di INSERT INTO ... VALUES, DELETE e UPDATE
• l'estensione non è disponibile per PostgreSQL per Windows.

Considerando le suddette limitazioni, utilizzare questa tipologia di tabelle può rivelarsi una valida soluzione nei casi in cui si ha a che fare con tabelle "in sola lettura" (time-series, log, storicizzazione dei dati, etc ...), dove inserire i dati e non dover provvedere a cancellarli. 

Anche su tabelle di dimensioni relativamente può essere un'ottima soluzione. 

Nel caso sia necessario effettuare cancellazioni periodiche, bisogna intervenire sulla tabella:

• generando una nuova tabella ed eliminando quella vecchia
• lavorando con le tabelle partizionate 

Riferimenti:

https://www.citusdata.com/citus-products/cstore-fdw
https://www.citusdata.com/citus-products/cstore-fdw/cstore-fdw-quick-start-guide