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.