Combien d'espace NULL occupe-t-il?

Lors de ma dernière intervention chez le client, celui-ci est venu me voir, rayonnant de joie, pour me dire qu’il avait suivi mon conseil et changé toutes les colonnes de clé primaire de BIGINT (8 octets) à INT (4 octets), et que cela avait beaucoup apporté ! Sa base de données MySQL 8.4 avait désormais perdu 750 Go (sur 5,5 To). Super!

Et oui, je sais que cela contredit les recommandations de certains de mes collègues PostgreSQL (ici et ici). Dans le monde MySQL, on accorde plutôt de l’importance à ce genre de choses (source):

Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables

De plus, InnoDB fonctionne un peu différemment (table clusterée et clé primaire dans toutes les clés secondaires) de PostgreSQL (table heap, index avec pointeur de ligne (ctid)).

Mais ce n’est pas vraiment le sujet. Immédiatement après, il a demandé si la mise à NULL des colonnes de type DOUBLE (8 octets, en langage PostgreSQL DOUBLE PRECISION) permettrait également de gagner de la place ou s’il valait mieux supprimer les colonnes. Ma première réponse instinctive pour DOUBLE a été: NULL est utile, suivi de OPTIMIZE TABLE (dans le langage PostgreSQL, VACUUM FULL). Mais ma deuxième réflexion a été: DOUBLE est un type de données fixe plus long, est-ce que cela s’applique également à NULL ou seulement aux types de données de longueur variable ? La prudence est mère de sûreté ! Il faut d’abord consulter le manuel…

Et on y trouve (source):

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

et (source):

The variable-length part of the record header contains a bit vector for indicating NULL columns. … Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

Essai avec MariaDB/MySQL

Montage expérimental

D’une certaine manière, cela me semble un peu trop compliqué. Un petit croquis aiderait peut-être ? Faisons donc un essai:

SQL> -- DROP TABLE IF EXISTS tracking;

SQL> CREATE TABLE tracking (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
, d0 DOUBLE, d1 DOUBLE, d2 DOUBLE, d3 DOUBLE, d4 DOUBLE
, d5 DOUBLE, d6 DOUBLE, d7 DOUBLE, d8 DOUBLE, d9 DOUBLE
);

SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
SQL> INSERT INTO tracking SELECT NULL, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows

La table atteint une taille d’environ 1,8 Go pour 16 millions de lignes, tant dans MariaDB que dans MySQL. Comme ces informations ne sont indiquées que de manière très imprécise dans INFORMATION_SCHEMA, nous vérifions dans le système de fichiers:

MariaDB 11.8:

SQL> system ls -l tracking.ibd
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:28 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 10:32 tracking.ibd

MySQL 8.4:

SQL> system ls -l tracking.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb  7 10:33 tracking.ibd

Défragmentation du tableau

Nous « défragmentons » ensuite le tableau à l’aide de la commande OPTIMIZE TABLE:

SQL> OPTIMIZE TABLE tracking;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| test.tracking | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.tracking | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+

Attention: le tableau est copié une fois ! Il faut donc temporairement doubler l’espace disque ! Nous pouvons observer cela pendant l’exécution de la commande OPTIMIZE TABLE:

MariaDB:

$ watch -d -n 1 'ls -l trac* \#*'
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:39 '#sql-alter-d57-8c.frm'
-rw-rw---- 1 mysql mysql  968884224 Feb  7 10:39 '#sql-alter-d57-8c.ibd'
-rw-rw---- 1 mysql mysql       1206 Feb  7 10:28  tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 10:32  tracking.ibd

MySQL:

$ watch -d -n 1 'ls -l trac* \#*'
-rw-r----- 1 mysql mysql  369098752 Feb  7 10:40 #sql-ib1594-4164062678.ibd
-rw-r----- 1 mysql mysql 1929379840 Feb  7 10:33 tracking.ibd

Mais le résultat est étonnant ! Avec MariaDB, la taille de la table est restée à peu près la même:

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:39 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 10:39 tracking.ibd

Avec MySQL, en revanche, la taille de la table a même augmenté d’environ 14 % après la « défragmentation »:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 10:41 tracking.ibd

Si nous exécutons à nouveau la commande OPTIMIZE TABLE, la taille reste constante tant avec MariaDB qu’avec MySQL:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:46 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 10:48 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 10:48 tracking.ibd

Essai 1: mise à NULL

Nous mettons maintenant les valeurs à NULL:

SQL> UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
  , d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;

Après cette étape, la taille des fichiers a même légèrement augmenté:

MariaDB (+1.3%):

-rw-rw---- 1 mysql mysql       1206 Feb  7 10:49 tracking.frm
-rw-rw---- 1 mysql mysql 1937768448 Feb  7 11:04 tracking.ibd

MySQL (+0.2%):

-rw-r----- 1 mysql mysql 2202009600 Feb  7 11:04 tracking.ibd

Nous défragmentons ensuite à nouveau la table avec la commande OPTIMIZE TABLE. Comme prévu, les tables rétrécissent.

MariaDB (à 23%):

-rw-rw---- 1 mysql mysql      1206 Feb  7 11:09 tracking.frm
-rw-rw---- 1 mysql mysql 448790528 Feb  7 11:10 tracking.ibd

MySQL (à 24%):

-rw-r----- 1 mysql mysql 520093696 Feb  7 11:10 tracking.ibd

Une nouvelle commande OPTIMIZE TABLE n’apporte alors PLUS AUCUN changement dans la taille des fichiers…

Essai 2: suppression des colonnes

Nous essayons maintenant à nouveau avec la commande DROP COLUMN. La situation de départ est à nouveau la même que celle décrite ci-dessus:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:15 tracking.frm
-rw-rw---- 1 mysql mysql 1933574144 Feb  7 11:18 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 1929379840 Feb  7 11:19 tracking.ibd

Après la commande OPTIMIZE TABLE, les valeurs sont similaires à celles du premier essai:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:20 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 11:21 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 11:21 tracking.ibd

Une nouvelle commande OPTIMIZE TABLE n’apporte également aucun changement supplémentaire, comme ci-dessus:

MariaDB:

-rw-rw---- 1 mysql mysql       1206 Feb  7 11:22 tracking.frm
-rw-rw---- 1 mysql mysql 1912602624 Feb  7 11:23 tracking.ibd

MySQL:

-rw-r----- 1 mysql mysql 2197815296 Feb  7 11:24 tracking.ibd

Et maintenant, le deuxième essai avec la suppression des colonnes:

SQL> ALTER TABLE tracking
  DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;

Tout d’abord, nous constatons que la commande INSTANTANEOUS n’apporte aucune modification aux données, mais uniquement aux métadonnées. D’un côté, c’est une bonne chose, car l’impact sur l’application est ainsi réduit au minimum. D’un autre côté, cela signifie également qu’il n’y a pas de gain d’espace.

Nous allons donc à nouveau nous attaquer au problème avec la commande OPTIMIZE TABLE:

MariaDB (à 93%):

-rw-rw---- 1 mysql mysql       925 Feb  7 11:28 tracking.frm
-rw-rw---- 1 mysql mysql 415236096 Feb  7 11:29 tracking.ibd

MySQL (à 92%):

-rw-r----- 1 mysql mysql 478150656 Feb  7 11:28 tracking.ibd

Conclusion

La mise à NULL des colonnes et la suppression des colonnes permettent toutes deux de gagner beaucoup d’espace. La suppression des colonnes permet toutefois de gagner environ 7 % de plus que la mise à NULL. Si cela est possible au niveau de l’application, il convient donc de supprimer les colonnes qui ne sont plus nécessaires ou, si cela n’est pas possible, de les mettre à NULL.

Essai avec PostgreSQL

Voyons maintenant ce qu’il en est avec PostgreSQL 19devel.

Montage expérimental

Le montage expérimental est similaire à celui utilisé pour MariaDB/MySQL:

postgres=# -- DROP TABLE IF EXISTS tracking;

postgres=# CREATE TABLE tracking (
  id SERIAL PRIMARY KEY
, d0 DOUBLE PRECISION, d1 DOUBLE PRECISION, d2 DOUBLE PRECISION, d3 DOUBLE PRECISION, d4 DOUBLE PRECISION
, d5 DOUBLE PRECISION, d6 DOUBLE PRECISION, d7 DOUBLE PRECISION, d8 DOUBLE PRECISION, d9 DOUBLE PRECISION
);

postgres=# \timing

postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
  SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0;
postgres=# INSERT INTO tracking (d0, d1, d2, d3, d4, d5, d6, d7, d8, d9)
  SELECT 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0 FROM tracking;
... bis 16 M rows

Commençons par déterminer la taille réelle du tableau. PostgreSQL semble connaître cette information avec une grande précision:

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 1963417600 | 1872 MB       | 376856576 | 359 MB        |      2340274176 | 2232 MB               |  2340798464 | 2232 MB

Ensuite, nous voulons savoir où se trouvent ces fichiers dans le système de fichiers:

postgres=# SELECT oid AS db_oid FROM pg_database WHERE datname = current_database();
 db_oid
--------
      5

postgres=# SELECT oid AS table_oid, relname, relnamespace, relfilenode
  FROM pg_class WHERE relname = 'tracking';
 table_oid | relname  | relnamespace | relfilenode
-----------+----------+--------------+-------------
     40965 | tracking |         2200 |       40965

postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid
  FROM pg_index i
  JOIN pg_class c ON i.indrelid = c.oid
 WHERE c.relname = 'tracking';
  index_name   | index_oid
---------------+-----------
 tracking_pkey |     40970

postgres=# SELECT pg_relation_filepath('tracking');
 pg_relation_filepath
----------------------
 base/5/40965

Taille des tables et des index dans le système de fichiers:

$ ls -ltr 40965* 40970*
-rw------- 1 mysql mysql      40960 Feb  7 18:33 40965_vm
-rw------- 1 mysql mysql     499712 Feb  7 18:33 40965_fsm
-rw------- 1 mysql mysql  889675776 Feb  7 18:34 40965.1
-rw------- 1 mysql mysql 1073741824 Feb  7 18:34 40965
-rw------- 1 mysql mysql  376856576 Feb  7 18:35 40970

• *_fsm signifie « free space map » (carte de l’espace libre)
• *_vm signifie « visibility map » (carte de visibilité)
• *.1 signifie 2e segment de l’objet (table ou index)

PostgreSQL semble fonctionner par défaut avec des segments de 1 Go et, contrairement à MariaDB/MySQL (INFORMATION_SCHEMA), connaître très précisément la taille de ses fichiers. Et la différence mentionnée ci-dessus (entre tot_rel_siz et tab_and_idx_siz) s’explique par les fichiers fsm et vm.

L’équivalent PostgreSQL de la commande OPTIMIZE TABLE de MariaDB/MySQL est la commande VACUUM FULL:

postgres=# VACUUM FULL tracking;

$ ls -ltr
-rw------- 1 mysql mysql      40960 Feb  7 18:39 40965_vm
-rw------- 1 mysql mysql     499712 Feb  7 18:39 40965_fsm
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40965
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40965.1
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40972
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40972.1
-rw------- 1 mysql mysql          0 Feb  7 18:39 40975

...

-rw------- 1 mysql mysql      49152 Feb  7 18:39 2704
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2703
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2696
-rw------- 1 mysql mysql      65536 Feb  7 18:39 2674
-rw------- 1 mysql mysql      81920 Feb  7 18:39 2673
-rw------- 1 mysql mysql      98304 Feb  7 18:39 2659
-rw------- 1 mysql mysql     139264 Feb  7 18:39 2658
-rw------- 1 mysql mysql      24576 Feb  7 18:39 2619_fsm
-rw------- 1 mysql mysql     163840 Feb  7 18:39 2619
-rw------- 1 mysql mysql     106496 Feb  7 18:39 2608
-rw------- 1 mysql mysql     491520 Feb  7 18:39 1249
-rw------- 1 mysql mysql     122880 Feb  7 18:39 1247
-rw------- 1 mysql mysql      32768 Feb  7 18:39 2662
-rw------- 1 mysql mysql     114688 Feb  7 18:39 1259
-rw------- 1 mysql mysql      16384 Feb  7 18:39 3455
-rw------- 1 mysql mysql      49152 Feb  7 18:39 2663
-rw------- 1 mysql mysql 1073741824 Feb  7 18:39 40972
-rw------- 1 mysql mysql  889675776 Feb  7 18:39 40972.1
-rw------- 1 mysql mysql  376864768 Feb  7 18:39 40975
-rw------- 1 mysql mysql          0 Feb  7 18:39 40965
-rw------- 1 mysql mysql          0 Feb  7 18:39 40970

La première chose que l’on remarque, c’est que PostgreSQL génère beaucoup de fichiers et que le fichier « free space map » a disparu. Contrairement à MariaDB/MySQL, les segments de table sont restés de taille identique. On constate également que l’ancienne table a « disparu » (40965, 40970) et qu’une nouvelle a été créée (40972 et 40975). La commande VACUUM FULL de PostgreSQL crée donc également une copie des données, comme dans MariaDB/MySQL.

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 1963417600 | 1872 MB       | 376864768 | 359 MB        |      2340282368 | 2232 MB               |  2340282368 | 2232 MB

Pour comprendre quels autres fichiers/objets ont été affectés, la requête suivante est utile:

postgres=# SELECT c.oid, c.relname, ns.nspname
FROM pg_class AS c
JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
  oid  |              relname              |  nspname
-------+-----------------------------------+------------
 40965 | tracking                          | public
 40970 | tracking_pkey                     | public
  2619 | pg_statistic                      | pg_catalog
  1247 | pg_type                           | pg_catalog
  2703 | pg_type_oid_index                 | pg_catalog
  2704 | pg_type_typname_nsp_index         | pg_catalog
  2658 | pg_attribute_relid_attnam_index   | pg_catalog
  2659 | pg_attribute_relid_attnum_index   | pg_catalog
  2662 | pg_class_oid_index                | pg_catalog
  2663 | pg_class_relname_nsp_index        | pg_catalog
  3455 | pg_class_tblspc_relfilenode_index | pg_catalog
  2696 | pg_statistic_relid_att_inh_index  | pg_catalog
  2673 | pg_depend_depender_index          | pg_catalog
  2674 | pg_depend_reference_index         | pg_catalog
  1249 | pg_attribute                      | pg_catalog
  1259 | pg_class                          | pg_catalog
  2608 | pg_depend                         | pg_catalog

postgres=# SELECT i.indexrelid::regclass as index_name, i.indexrelid as index_oid, ns.nspname
  FROM pg_index i
  JOIN pg_class c ON i.indrelid = c.oid
  JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
 WHERE c.oid IN (2704, 2703, 2696, 2674, 2673, 2659, 2658, 2619, 2608, 1249, 1247, 40972, 2662, 1259, 3455, 2663, 40975, 40965, 40970)
;
            index_name             | index_oid |  nspname
-----------------------------------+-----------+------------
 pg_type_typname_nsp_index         |      2704 | pg_catalog
 pg_attribute_relid_attnam_index   |      2658 | pg_catalog
 tracking_pkey                     |     40970 | public
 pg_class_relname_nsp_index        |      2663 | pg_catalog
 pg_class_tblspc_relfilenode_index |      3455 | pg_catalog
 pg_type_oid_index                 |      2703 | pg_catalog
 pg_attribute_relid_attnum_index   |      2659 | pg_catalog
 pg_statistic_relid_att_inh_index  |      2696 | pg_catalog
 pg_depend_depender_index          |      2673 | pg_catalog
 pg_depend_reference_index         |      2674 | pg_catalog
 pg_class_oid_index                |      2662 | pg_catalog

Essai 1: mise à NULL

Nous mettons également à NULL les colonnes dans PostgreSQL. Nous n’avons plus besoin de consulter le système de fichiers, car PostgreSQL semble connaître précisément la taille des fichiers, comme nous l’avons vu ci-dessus:

postgres=# UPDATE tracking
SET d0 = NULL, d1 = NULL, d2 = NULL, d3 = NULL, d4 = NULL
  , d5 = NULL, d6 = NULL, d7 = NULL, d8 = NULL, d9 = NULL
;

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz   | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
------------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 2695716864 | 2571 MB       | 753696768 | 719 MB        |      3449413632 | 3290 MB               |  3450101760 | 3290 MB

Nous voyons ici que les segments de table augmentent considérablement (+37 %), ce qui est appelé « bloat » dans la terminologie PostgreSQL. L’implémentation MVCC de PostgreSQL stocke à la fois l’ancienne et la nouvelle version de la ligne « in-place », c’est-à-dire directement dans la table, contrairement à MariaDB/MySQL qui stocke l’ancienne version dans l’espace UNDO et la nouvelle ligne « in-place ». Le fichier d’index augmente également de manière significative (+100 %). Nous devons encore approfondir nos recherches pour comprendre pourquoi. De plus, une « carte d’espace libre » est à nouveau créée (différence entre tot_rel_siz et tab_and_idx_siz).

Un VACUUM FULL réduit ensuite la taille de la table (à 28 %) et de l’index (à 50 %) par rapport à leur taille précédente:

postgres=# VACUUM FULL tracking;

postgres=# SELECT pg_relation_size('tracking') AS tab_siz
     , pg_size_pretty(pg_relation_size('tracking')) AS tab_siz_prtty
     , pg_indexes_size('tracking') AS idx_siz
     , pg_size_pretty(pg_indexes_size('tracking')) AS idx_siz_prtty
     , pg_relation_size('tracking') + pg_indexes_size('tracking') AS tab_and_idx_siz
     , pg_size_pretty(pg_relation_size('tracking') + pg_indexes_size('tracking')) AS tab_and_idx_siz_prtty
     , pg_total_relation_size('tracking') AS tot_rel_siz
     , pg_size_pretty(pg_total_relation_size('tracking')) AS tot_rel_siz_prtty
;
  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1119780864 | 1068 MB

et par rapport à leur taille d’origine, la table (à 38 %) et l’index (à 100 %) sont également plus petits. Il reste à déterminer pourquoi l’index est resté de la même taille et seule la table a diminué…

Essai 2: suppression des colonnes

Les colonnes sont ensuite supprimées à l’aide de DROP COLUMN.

postgres=# ALTER TABLE tracking
  DROP COLUMN d0, DROP COLUMN d1, DROP COLUMN d2, DROP COLUMN d3, DROP COLUMN d4
, DROP COLUMN d5, DROP COLUMN d6, DROP COLUMN d7, DROP COLUMN d8, DROP COLUMN d9
;

Comme la réponse a été immédiate, on peut supposer que cette opération est également instantanée. Malheureusement, je n’ai rien trouvé à ce sujet dans la documentation PostgreSQL.

La taille n’a pas changé de manière significative, ce qui est normal pour une opération instantanée. Cependant, le fait que la taille n’ait pas changé après la commande VACUUM FULL m’a un peu surpris:

  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1120010240 | 1068 MB

postgres=# VACUUM FULL tracking;

  tab_siz  | tab_siz_prtty |  idx_siz  | idx_siz_prtty | tab_and_idx_siz | tab_and_idx_siz_prtty | tot_rel_siz | tot_rel_siz_prtty
-----------+---------------+-----------+---------------+-----------------+-----------------------+-------------+-------------------
 742916096 | 709 MB        | 376864768 | 359 MB        |      1119780864 | 1068 MB               |  1119780864 | 1068 MB

Remarques

Le verrouillage dans PostgreSQL fonctionne comme suit:

• VACUUM Les commandes DML concurrentes sont possibles, comme pour la commande OPTIMIZE TABLE de MariaDB/MySQL. Le résultat n’est toutefois pas tout à fait le même.
• VACUUM FULL provoque un verrouillage ACCESS EXCLUSIVE. Comme pour MariaDB/MySQL 5.5 et versions antérieures avec la commande OPTIMIZE TABLE. Les commandes DML et SELECT ne sont PAS autorisées.

Sources

• How to Get Sizes of Database Objects in PostgreSQL
• Database File Layout
• System Administration Functions
• CLUSTER
• VACUUM
• Explicit Locking

Essais supplémentaires

1. Au lieu de 0.0, NULL a été inséré dans les colonnes d0 à d9. La table est restée petite (tot_rel_siz_prtty = 1068 Mo). Il est donc également intéressant d’enregistrer NULL au lieu de valeurs fictives dans PostgreSQL.
2. Les colonnes d0 à d9 ont été créées avec DOUBLE PRECISION NOT NULL et remplies avec les valeurs 0.0. Sans effet: la table est restée volumineuse (tot_rel_siz_prtty = 2232 Mo).

Cette page a été traduite à l’aide de deepl.com.