Il male non viene dai numeri, ovvero il tipo NUMERIC in PostgreSQL

Secondo Mr. Celko (e moltissimi altri) non si dovrebbe mai pensare allo spazio occupato da un campo ... ehm ... da una colonna, quanto invece al suo effettivo uso. Siamo nel XXI secolo da un po' e mi sembra che questa considerazione abbia molto senso.
Per questo motivo ho deciso di sostituire tutte le colonne REAL e DOUBLE PRECISION con colonne di tipo NUMERIC.
Il tipo NUMERIC non impone limitazioni sullo spazio necessario a rappresentare i numeri e può per questo motivo essere utilizzato (con una buona approssimazione) per l'aritmetica a precisione arbitraria.
Chiunque abbia (avuto) a che fare con quantità esatte di prodotti e con cambi di valuta sa quanto tutto ciò possa essere importante e pericoloso allo stesso tempo. Soprattutto nel caso di applicazioni monetarie.
Ovviamente il passaggio a NUMERIC non l'ho voluto fare in modo bovino (non me ne vogliano i mansueti bovidi), ma ragionato e, magari, provato e pesato.
Prima di tutto ho voluto provare a fare un qualche tipo di test per confrontare i soliti tipi numerici FLOAT (ovvero REAL) e FLOAT8 (cioè il DOUBLE PRECISION) con il NUMERIC.
Che il NUMERIC possa essere meno efficiente di rappresentazioni native per le CPU è un fatto assodato: occupa più spazio in memoria e non ha un'implementazione nativa nella CPU (in realtà non è proprio così).
Ma quanto pesa veramente questa perdita di prestazioni?
Per prima cosa ho creato tre tabelle per contenere una quantità significativa di numeri.

pg=# CREATE TABLE tab_numeric ( n NUMERIC );
CREATE TABLE
pg=# INSERT INTO tab_numeric SELECT random()*generate_series( 1,1000000 );
INSERT 0 1000000
pg=# SELECT * from tab_numeric limit 10;
      n
-------------------
0.752664533443749
1.9673418533057
2.00164743466303
1.29505263641477
1.14009823650122
2.51548232976347
2.09525664849207
3.09556493535638
4.67498113028705
5.38833205588162
(10 rows)

pg=# CREATE TABLE tab_float ( f FLOAT );
CREATE TABLE
pg=# INSERT INTO tab_float SELECT random()*generate_series( 1,1000000 );
INSERT 0 1000000
pg=# CREATE TABLE tab_float8 ( f8 FLOAT8 );
CREATE TABLE
pg=# INSERT INTO tab_float8 SELECT random()*generate_series( 1,1000000 );
INSERT 0 1000000

Un obbiettivo è quello di testare la funzione sum() su quei dati memorizzati, 1 milione di valori reali compresi fra 0 e 1 milione, appunto.
Per aggirare problemi di caching (buffer, file system e PostgreSQL stesso) ho fatto ripartire l'intero sistema operativo. Non si sa mai.
Ed ecco i risultati:

pg=# \timing
Timing is on.
pg=# SELECT sum( n ) FROM tab_numeric;
          sum
------------------------------
249966920426.730893782620486
(1 row)

Time: 388,452 ms
pg=# SELECT sum( f ) FROM tab_float;
    sum
------------------
249743083324.389
(1 row)

Time: 142,194 ms
pg=# SELECT sum( f8 ) FROM tab_float8;
    sum
------------------
250076635234.095
(1 row)

Time: 142,298 ms
pg=# SELECT relname, pg_size_pretty(relpages::BIGINT*8*1024) AS size, relkind, reltuples::BIGINT AS rows, relpages, relfilenode FROM pg_class WHERE relname like 'tab\\_%';

relname   | size  | relkind |  rows   | relpages | relfilenode 
-------------+-------+---------+---------+----------+-------------
 tab_float   | 35 MB | r       | 1000000 |     4425 |       34969
 tab_float8  | 35 MB | r       | 1000000 |     4425 |       34972
 tab_numeric | 42 MB | r       | 1000000 |     5406 |       34963
(3 rows)

Come direbbe Mr. Spok, "Interessante!".

1. Il tipo FLOAT8 funziona tanto bene quanto il tipo FLOAT, probabilmente per via dell'implementazione hardware della sezione a virgola mobile della CPU a 64 bit che ho usato (vedi anche punto 3).
2. Il tipo NUMERIC è banalmente più lento del FLOAT8, ma solo di un fattore circa 2,7.
3. La tab_numeric occupa solo il 20% di spazio disco in più rispetto alla tab_float8 che a sua volta non occupa più spazio della tab_float.
4. La precisione può essere importante. Molto importante. Basta confrontare i risultati delle 3 query.

Ovviamente non abbiamo realizzato un test reale e forse non è nemmeno il massimo della scientificità.
Penso inoltre che ci siano veramente poche applicazioni che si limitino a sommare fra di loro 1 milione di valori numerici contenuti in una tabella.
Ciò che questo test credo mostri è invece quale sia un limite superiore alle performance relative fra le varie rappresentazioni numeriche e relative operazioni di PostgreSQL.
Direi che nel mondo reale, dove il calcolo non è utilizzato in modo così massiccio, le performance relative (in termini di tempo macchina) dovrebbero risultare con valori molto simili fra di loro. La grande differenza la farebbe, quindi, la precisione ottenuta dai NUMERIC.


In conclusione, se le prestazioni sono più importanti della precisione in modo assoluto, allora il FLOAT8 è sicuramente la scelta giusta. Per tutto il resto c'è ... NUMERIC.

Divagazioni sul table partitioning in PostgreSQL - parte 4

Affinché tutto quanto abbiamo detto finora abbia un senso nel mondo reale c'è bisogno che PostgreSQL sia in grado di gestire i vari meccanismi sottostanti in modo efficiente. Vediamo il perché.
Nel caso si utilizzi l'ereditarietà è importante che il planner sappia scegliere in modo efficiente quali tabelle figlie effettivamente interrogare. In questo caso definirei "efficiente" un algoritmo a complessità sub-lineare, cioè il cui tempo di esecuzione cresce meno che una funzione lineare. E' il caso tipico della ricerca dicotomica che impiega tempi logaritmici rispetto al numero di elementi dell'insieme.
Lo stesso ragionamento vale per la tecnica che sfrutta il partizionamento degli indici, dove la ricerca si sposta dalle tabelle della gerarchia alla lista degli indici della tabella unica.
Questo perché se il numero di partizioni aumenta (e di norma lo fa col tempo), non vorremmo che aumentasse (troppo) il tempo che il planner impiega a capire quali tabelle figlie (o indici parziali) scartare e quali no.
Con un insieme 100 volte più grande quella ricerca dovrebbe richiede al massimo poche iterazioni in più per essere completata, come nella ricerca dicotomica.
E PostgreSQL?
Stando a quanto riportato nelle mailing list ufficiali da sviluppatori di prima linea del progetto, una gerarchia con più di una dozzina di tabelle figlie è considerata non praticabile. E, per quanto se ne sa, nessuno ha mai preso in considerazione gerarchie con più di un livello!
Le stesse considerazioni portano alle stesse conclusioni in merito al numero di indici parziali.
Questo perché, non solo a mio sentire, nei meandri più nascosti del planner si annidano algoritmi (o loro implementazioni) che non solo non sono sub-lineari ma, questo è il pericolo, sono addirittura super-lineari (che crescono cioè come i polinomi e oltre).
Cosa fare dunque? Le possiblità non sono molte.

1. Rinunciare al partizionamento "automatico" e fare in modo che l'applicazione sappia scegliere da sé quali tabelle effettivamente interrogare.
2. Rinunciare al partizionamento tout-court e affidarsi a grandi quantità di memoria per tenere in cache quanta più parte è possibile delle tabelle in questione e relativi indici;
3. Rimboccarsi le maniche e mettere mano al codice del planner;
4. Provare a rientrare nei limiti di una dozzina o poco più di tabelle figlie (o indici parziali).

Mi sembra che al momento (sfidanti, fatevi avanti!) sia le funzioni di ereditarietà che quelle di indicizzazione parziale siano più dei "segnaposto" accademici (per dire "ce l'ho") che delle caratteristiche utili nel mondo reale. Limitazioni che stanno ben dentro al primo ordine di grandezza tengono certamente lontano un progetto come PostgreSQL dal mondo enterprise, dove i numeri girano per miliardi come unità di misura.

La mia non vuole essere una feroce critica a quello che ritengo veramente essere il più avanzato RDBMS opensource esistente. Vorrebbe essere piuttosto uno sprone alla comunità per una revisione di quelle parti che, per dirla in termini tecnici, "non scalano bene" parallelamente a tutte le altre interessanti attività di cui si sta già occupando per la prossima versione 9.1.

Concludo con un parallelo. Il kernel di Linux ha cominciato a prendere piede nei territori dell' "enterprise grade" (a misura di impresa industriale) anche grazie alla riprogettazione dello scheduler che altrimenti aveva complessità lineare: più processi da gestire = più tempo necessario per gestirli. Ora quel costo è praticamente indipendente  non solo dal numero di processi, ma anche di processori. Aggiungere feature chiaramente non basta.

GMail ha l'aiuto in linea per i comandi da tastiera! Lo sapevate?

Basta premere il tasto '?' ed ecco comparire in sovraimpressione una finestra d'aiuto analoga a questa:

Snapshot

Cliccando su "Apri in una nuova finestra" si aprirà la corrispondente pagina della guida di GMail in italiano.
Cliccando sull'altro link, invece ...

Divagazioni sul table partitioning in PostgreSQL - parte 3

Torniamo per un momento al concetto originario di partizionamento di una tabella. E in particolare alla domanda ultima (non quella sulla vita, l'universo e tutto quanto): perché mai partizionare una tabella?
La risposta non è unica, come sempre. Personalmente ne vedo almeno 3:

1. accelerare l'accesso ai dati quando questo non è completamente causale ma limitato ad alcuni sottoinsiemi della tabella originaria;
2. semplificare lo "smaltimento" di gruppi di righe non più necessari alle operazioni correnti;
3. allocare le singole partizioni su tablespace distinti (per accelerare l'accesso in modo più sottile).

Del primo caso abbiamo già detto. Eseguire una DROP TABLE su una tabella figlia, dopo averla esclusa dall'albero genealogico, è certamente più efficiente che una classica DELETE FROM ... WHERE, oltre al fatto che già la semplice esclusione dall'albero genealogico potrebbe essere sufficiente (ALTER TABLE ... NO INHERIT ...) ad un costo a dir poco ridicolo.
Quella dell'allocazione delle tabelle figlie su tablespace separati è una tecnica raffinata che richiede però uno studio comparativo attento fra i benefici derivanti dall'ottimizzazione sull' I/O rispetto al costo intrinseco derivante dalla gestione dell'albero genealogico. Su database molto "trafficati" in genere le ottimizzazioni sull' I/O dei dischi portano sempre a risultati significativi in positivo.
Tornando alla prima considerazione, va notato come in realtà la velocità dell'accesso dipende solo minimamente (ammesso che possa veramente dipendere) dalle dimensioni della tabella in sé. Una definizione oculata degli indici e delle query realmente in uso è la vera ricetta del successo. Del resto con dischi che stanno "sforando" la barriera del terabyte e con la gestione dei volumi logici, il problema di alloggiare tabelle enormi è diventato sempre meno sentito.
In sostanza, durante le query il planner (ovvero l'ottimizzatore) verifica se esistono uno o più indici, che di norma risiedono sui dischi, che possano tornare utili a recuperare le righe interessanti. In caso positivo cerca di caricare in memoria quelle porzioni di indici che possono servire per poi procedere al recupero effettivo delle righe.
Ciò porta quindi a pensare di partizionare gli indici piuttosto che le tabelle.
In questo modo rendo gli indici più piccoli e, se le query non "spazzolano" a caso sull'intera tabella, è probabile che alcuni di quei piccoli indici più utilizzati restino in cache, a tutto vantaggio della velocità.
Ovviamente PostgreSQL supporta nativamente questa tecnica tramite i cosiddetti "indici parziali". A questi è in sostanza associata un'espressione booleana che determina quali righe della tabella siano indicizzate. Si tratta di un'espressione che, ai nostri fini, ha le stesse finalità di quella della CHECK constraint usata nelle tecniche "ortodosse" illustrate in precedenza.
Chiaramente gli indici parziali, come anche le CHECK constraint, devono essere definite in modo che ogni riga della tabella possa "finire" in almeno uno degli indici parziali. Se è uno solo, tanto meglio.
Questa tecnica ha anche degli effetti collaterali benefici. Vediamone alcuni.

1. Non serve un supporto in tempo reale (leggi "TRIGGER");
2. Non serve un supporto "batch" per lo smistamento delle righe nelle tabelle figlie;
3. Restando unica la tabella si può salvaguardare l'integrità referenziale.

Chiaramente c'è anche da pagare un qualche costo che è rappresentato dalla valutazione delle espressioni booleane associate agli indici ogni volta che il contenuto della tabella cambia (INSERT, UPDATE ecc.). Quindi, l'aggiornamento degli indici sarà intrinsecamente sempre più costoso.

E' chiaro, concludendo (o quasi) come non esista una ricetta unica e buona per tutti i palati. Del resto le condizioni a contorno della specifica applicazione (per non parlare degli specifici data set) cambiano da caso a caso. Lo scopo di queste divagazioni è quello di provare a guardare oltre lo status quo, sempre considerando i limiti, intrinseci e non, non solo delle tecniche ma anche di PostgreSQL.
Per i quali abbiamo dedicato le prossime divagazioni finali.