Stocker des entiers avec PostgreSQL

Trois types avec PostgreSQL

      PostgreSQL propose 3 types de données pour stocker les entiers, conformément au standard SQL : SMALLINT (INT2), INTEGER (INT, INT4) et BIGINT (INT8).

      Démonstration avec PostgreSQL 10 et le type smallint :

select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 ligne) create table t1(c1 smallint); CREATE TABLE insert into t1(c1) values(-32769); ERREUR: smallint en dehors des limites insert into t1(c1) values(-32768); INSERT 0 1 insert into t1(c1) values(32767); INSERT 0 1 insert into t1(c1) values(32768); ERREUR: smallint en dehors des limites insert into t1 values(1.1); INSERT 0 1 insert into t1 values(1.6); INSERT 0 1 select * from t1; c1 -------- -32768 32767 1 2 (4 lignes)
      Il convient simplement de choisir le bon type de données en fonction du besoin fonctionnel afin de ne pas perdre d’espace et donc de performance.
      Pour optimiser au maximum l’espace, vous pouvez aller jusqu’à prendre en compte la notion d’alignement au niveau du stockage, propre à PostgreSQL. Sans entrer dans les détails, dans l’ordre de création des colonnes d’une table, placez d’abord les bigint puis les integer puis les smallint. PostgreSQL gérera peut-être automatiquement cet aspect dans de futures versions.

Et si on part d’Oracle ?

      Oracle Database stocke essentiellement les valeurs numériques, dont font partie les valeurs entières, dans un type appelé NUMBER. Il est possible d’utiliser les syntaxes du standard SQL, SMALLINT et INTEGER (mais PAS BIGINT), mais au final vous obtiendrez des NUMBER(38).
      Démonstration avec Oracle Database 12.2.0.1 :

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production create table t1(c1 smallint); Table created. desc t1; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER(38) insert into t1(c1) values(-32769); 1 row created. insert into t1(c1) values(-32768); 1 row created. insert into t1(c1) values(32767); 1 row created. insert into t1(c1) values(32768); 1 row created. insert into t1 values(1.1); 1 row created. insert into t1 values(1.6); 1 row created. select * from t1; C1 ---------- -32769 -32768 32767 32768 1 2 6 rows selected. create table t2(c1 integer); Table created. desc t2; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER(38) create table t3(c1 bigint); create table t3(c1 bigint) * ERROR at line 1: ORA-00902: invalid datatype create table t4(c1 number(5)); Table created. desc t4; Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NUMBER(5) insert into t4 values(99999); 1 row created. insert into t4 values(100000); insert into t4 values(100000) * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column

      Si vous voulez limiter la taille des entiers avec Oracle, ce n’est pas en créant des SMALLINT ou des INTEGER que vous y parviendrez. Il est en revanche possible de créer des number(N). En pratique, c’est très rarement utilisé car, avec ce type NUMBER, le stockage est alloué en fonction de la taille (précision) du nombre à stocker. Utiliser un number(3) ne prendra pas moins de place qu’utiliser un number(38) pour stocker des entiers jusqu’à 999. Le NUMBER d’Oracle Database est plus proche du NUMERIC de PostgreSQL, un type standard SQL.
      Pourquoi ne pas utiliser NUMERIC pour stocker les entiers sous PostgreSQL alors ? Pour des raisons de performance. Démonstration avec PostgreSQL 10 :

select version(); version ---------------------------------------------------------------------------------------------------------------- PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 ligne) create table t1(c1 integer); CREATE TABLE insert into t1 select random()*1000000 from generate_series(1,1000000,1); INSERT 0 1000000 select avg(c1*10/10) from t1 ; avg --------------------- 499914.986444000000 (1 ligne) Temps : 62,939 ms ... Temps : 65,357 ms ... Temps : 62,327 ms create table t1_bis as select * from t1 order by random(); SELECT 1000000 select count(*) from t1 natural join t1_bis; count --------- 2001300 (1 ligne) Temps : 425,501 ms ... Temps : 424,013 ms ... Temps : 424,013 ms create table t2(c1 numeric); CREATE TABLE create table t2_bis(c1 numeric); CREATE TABLE insert into t2 select * from t1; INSERT 0 1000000 insert into t2_bis select * from t1_bis; INSERT 0 1000000 select avg(c1*10/10) from t2 ; avg ----------------------------- 499914.98644400000000000000 (1 ligne) Temps : 154,365 ms ... Temps : 146,062 ms ... Temps : 148,313 ms select count(*) from t2 natural join t2_bis; count --------- 2001300 (1 ligne) Temps : 528,645 ms ... Temps : 514,588 ms ... Temps : 525,206 ms

      Confirmation avec cet exemple, PostgreSQL permet d’optimiser les opérations et tests sur les entiers grâce aux types dédiés SMALLINT, INTEGER, BIGINT. Même si ce n’est pas obligatoire au niveau fonctionnel, il faut donc idéalement s’interroger sur chaque colonne numérique pour optimiser le stockage et les performances lors de la migration d’Oracle Database vers PostgreSQL.

Mise à jour : 22/10/2017