Les contenus binaires peuvent être stockés avec PostgreSQL soit dans des tables utilisateurs avec des colonnes de type bytea, soit instanciés en tant qu’objets larges et gérés dans des tables systèmes et par des fonctions spécifiques, côté client comme côté serveur.

Quelles sont les raisons de choisir l’un plutôt que l’autre?

Très schématiquement, on pourrait les résumer dans ce tableau comparant les deux approches:

Caractéristique Objet Large Colonne Bytea
Taille max par donnée 4 To 1 Go
Segmentation intra-donnée Oui Non
Stockage segmenté TOAST Non Oui
Compression LZ par segment sur totalité
Une seule table par base Oui Non
Référence indirecte (OID) Oui Non
Accès extra-requête Oui Non
Réplication logique Non Oui
Lignes par donnée Taille / 2048 1 (+ Toast)
Partitionnement Impossible Possible
Transferts en binaire Toujours Possible mais rare
Verrous en mémoire partagée Oui Non
Choix du tablespace Non Oui
Triggers possibles Non Oui
Droits d’accès par donnée Oui Non (hors RLS*)
Chargement par COPY Non Oui
Disponibilité dans langages Variable Toujours

* RLS = Row Level Security

Voyons plus en détail certaines de ces différences et leurs implications.

Usage

Les colonnes en bytea sont plus simples à utiliser, dans le sens où elles s’intégrent de manière plus standard au SQL, et qu’elles sont accessibles via toutes les interfaces. Pour insérer une donnée binaire littérale dans une requête, il faut l’exprimer dans un format textuel, par exemple: '\x41420001'::bytea pour le format hex, ou encore 'AB\000\001'::bytea pour le format escape. Dans le sens inverse, pour un résultat retourné du serveur vers le client, les colonnes bytea sont encodées dans un de ces deux formats selon le paramètre bytea_output, sauf si l’appelant a appliqué une fonction explicite d’encodage telle que base64, ou encore demandé du binaire. Ce dernier cas est plutôt rare, car beaucoup de programmes et d’interfaces avec les langages ne gèrent pas les résultats de requête au format binaire, même si le protocole et la bibliothèque libpq en C le permettent.

Ce passage par un format texte présente un inconvénient: les conversions en texte gonflent la taille des données en mémoire et sur le réseau, d’un facteur 2 pour hex, variable (entre 1 et 4) pour escape, et 4/3 pour base64, et consomment du temps CPU pour coder et décoder.

Les objets larges, de leur côté, s’appuient sur une API particulière, où chaque contenu binaire se présente un peu comme un fichier, identifié par un numéro unique (OID), avec des permissions individuelles par objet, et accessible via des opérations sur le modèle de celles des fichiers:

Fonction SQL Fonction libpq Fichier (libc)
lo_create lo_create creat
lo_open lo_open open
loread lo_read read
lowrite lo_write write
lo_lseek[64] lo_lseek[64] lseek[64]
lo_tell[64] lo_tell[64] lseek/ftell
lo_truncate[64] lo_truncate[64] truncate
lo_close lo_close close
lo_unlink lo_unlink unlink
lo_import lo_import N/A
lo_export lo_export N/A
lo_put N/A N/A
lo_get N/A N/A
lo_from_bytea N/A N/A

La plupart de ces opérations sont appelables de deux manières différentes: d’une part en tant que fonctions SQL côté serveur, et d’autre part directement par le client, en-dehors d’une requête SQL. Par exemple avec psql, la commande \lo_import /chemin/fichier.bin insérera le contenu du fichier client sur le serveur sans passer par une requête INSERT ou COPY. En interne, elle appelera la fonction libpq lo_import dans une transaction, qui elle-même appelera les fonctions distantes de création et écriture à travers le protocole.

Avec les objets larges, il n’y a pas d’encodage intermédiaire en format texte, ce sont les données binaires brutes qui transitent. Par ailleurs, comme pour un fichier, le client accède généralement au contenu par morceaux, ce qui permet de travailler en flux (streaming), sans avoir besoin d’ingérer une donnée d’un seul tenant pour la traiter.

A contrario, dans le cas d’un SELECT ou COPY avec des colonnes bytea, le client ne peut pas accéder à une ligne partiellement récupérée, et encore moins à une partie de colonne, sauf à descendre au niveau du protocole et à lire directement la socket réseau.

Importons des photos

Soit un répertoire avec 1023 photos JPEG d’une taille moyenne de 4,5 Mo. On va importer ces photos dans des objets larges, puis dans une table pour faire quelques comparaisons.

Import

Déjà, comment importer un fichier dans une colonne bytea? psql n’offre pas de solution simple. Cette question sur DBA.stackexchange ouverte en 2011 : How to insert (file) data into a PostgreSQL bytea column? suggère différentes méthodes plus ou moins indirectes et compliquées, dont notamment celle de passer par un objet large temporaire.

Pour les objets larges, c’est assez simple:

$ (for i in *.JPG; do echo \\lo_import "$i" ; done) | psql

La sortie va ressembler à ça, et nos 4,5 Go sont importés en quelques minutes.

lo_import 16456
lo_import 16457
lo_import 16458
...

chacun de ces numéros étant l’OID d’un objet nouvellement créé.

Maintenant copions ces données en un seul bytea par photo avec une version simplifiée de la réponse de stackexchange (lo_get n’existait pas en 2011).

CREATE TABLE photos(id oid PRIMARY KEY, data BYTEA);

INSERT INTO photos SELECT oid, lo_get(oid) from pg_largeobject_metadata ;

Export

Pour réexporter ces images avec psql, dans le cas des objets larges il suffit d’utiliser pour chacun:

  \lo_export :oid /chemin/vers/fichier`

Pour les contenus de la table photos, le format le plus simple à restituer en binaire sur le client est le base64. Par exemple la commande psql ci-dessous fait que la donnée bytea est transformée explicitement via encode(data, 'base64') en SQL et conduite via l’opérateur ‘|’ (pipe) dans le programme base64 de la suite GNU coreutils.

SELECT encode(data, 'base64') FROM photos
  WHERE id= :id \g | base64 -d >/chemin/fichier

Stockage

Structure des objets larges

Les objets larges sont stockés dans deux tables systèmes dédiées.

pg_largeobject_metadata a une ligne par objet large, indiquant le possesseur et les droits d’accès. Comme d’autres tables systèmes (pg_class, pg_type, …), elle utilise la pseudo-colonne oid comme clef primaire.

=# \d pg_largeobject_metadata
 Colonne  |   Type    | Collationnement | NULL-able | Par défaut 
----------+-----------+-----------------+-----------+------------
 lomowner | oid       |                 | not null  | 
 lomacl   | aclitem[] |                 |           | 
Index :
    "pg_largeobject_metadata_oid_index" UNIQUE, btree (oid)

La seconde table pg_largeobject porte les données, découpées en segments ou mini-pages bytea d’un quart de bloc maximum, soit 2048 octets par défaut. Sa structure:

=# \d pg_largeobject
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 loid    | oid     |                 | not null  | 
 pageno  | integer |                 | not null  | 
 data    | bytea   |                 | not null  | 
Index :
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Chaque ligne de cette table comporte l’OID qui référence l’entrée correspondante de pg_largeobject_metadata, le numéro de page en partant de 0, et la mini-page elle-même dans data.

Bien que le stockage de la colonne data soit déclaré extended, il n’y a délibérément pas de table TOAST associée, l’objectif étant que ces mini-pages tiennent dans les pages principales. Cette stratégie est expliquée en ces termes dans le code source:

src/include/storage/large_object.h:

/*
 * Each "page" (tuple) of a large object can hold this much data
 *
 * We could set this as high as BLCKSZ less some overhead, but it seems
 * better to make it a smaller value, so that not as much space is used
 * up when a page-tuple is updated.  Note that the value is deliberately
 * chosen large enough to trigger the tuple toaster, so that we will
 * attempt to compress page tuples in-line.  (But they won't be moved off
 * unless the user creates a toast-table for pg_largeobject...)
 *
 * Also, it seems to be a smart move to make the page size be a power of 2,
 * since clients will often be written to send data in power-of-2 blocks.
 * This avoids unnecessary tuple updates caused by partial-page writes.
 *
 * NB: Changing LOBLKSIZE requires an initdb.
 */
#define LOBLKSIZE		(BLCKSZ / 4)

Autrement cette taille est choisie pour:

  • permettre des petites mises à jour intra-données peu coûteuses.
  • être au-dessus du seuil de compression.
  • être une puissance de 2.

Structure des tables TOAST

Au contraire de pg_largeobject, la table photos a une table TOAST associée. On n’a pas besoin de le savoir pour accéder aux données binaires, puisque qu’en sélectionnant photos.data, PostgreSQL va automatiquement lire dedans si nécessaire, mais regardons quand même sous le capot pour continuer la comparaison.

La table TOAST est identifiable via cette requête:

=# SELECT reltoastrelid,
  pg_total_relation_size(reltoastrelid) FROM pg_class
  WHERE oid='photos'::regclass;

 reltoastrelid | pg_total_relation_size 
---------------+------------------------
         18521 |             4951367680

La doc nous indique à quoi s’attendre au niveau de la structure:

Chaque table TOAST contient les colonnes chunk_id (un OID identifiant la valeur TOASTée particulière), chunk_seq (un numéro de séquence pour le morceau de la valeur) et chunk_data (la donnée réelle du morceau). Un index unique sur chunk_id et chunk_seq offre une récupération rapide des valeurs

Et là, surprise (ou pas): c’est exactement le même type de structure que pg_largeobject ! Vérifions dans psql:

=# select relname from pg_class where oid=18521;
    relname     
----------------
 pg_toast_18518

=# \d+ pg_toast.pg_toast_18518
Table TOAST « pg_toast.pg_toast_18518 »
  Colonne   |  Type   | Stockage 
------------+---------+----------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain

Vu ces similarités, on pourrait penser que physiquement, les deux modèles de stockage pèsent pareillement sur disque. En fait, ce n’est pas vraiment le cas.

Poids réel des données

Calculons le surpoids global, c’est-à-dire tailles des tables versus tailles des données contenues, avec les deux méthodes de stockage, sur l’exemple du millier de photos.

D’abord les tailles des tables:

=# select n,pg_size_pretty(pg_total_relation_size(n))  from
   (values ('pg_largeobject'), ('pg_largeobject_metadata'), ('photos')) as x(n);
            n            | pg_size_pretty 
-------------------------+----------------
 pg_largeobject          | 6106 MB
 pg_largeobject_metadata | 112 kB
 photos                  | 4722 MB
(3 lignes)

La taille des données contenues à proprement parler étant:

=# select pg_size_pretty(sum(octet_length(data))) from photos;
 pg_size_pretty 
----------------
 4551 MB
(1 ligne)

Avec seulement 10% de surpoids pour la table photos versus 34% de surpoids pour pg_largeobject, il n’y a pas photo justement: sur le plan de l’espace disque, les objets larges et leur stockage “mini-page” en ligne perdent largement par rapport au stockage TOAST.

Alors on peut légitimement se demander pourquoi les mêmes contenus rangés dans des structures similaires consomment des espaces assez différents.

Première hypothèse: il y aurait plus de lignes, et le surcoût par ligne ferait la différence.

Les entêtes de ligne prennent effectivement de la place dans PostgreSQL, au minimim 27 octets comme détaillé dans le HeapTupleHeaderData.

Le nombre de lignes de la table TOAST diffère effectivement de celui de pg_largeobject, mais en fait il s’avère plus grand, ce qui invalide donc complètement cette hypothèse:

=# select (select count(*) from pg_toast.pg_toast_18518),
          (select count(*) from pg_largeobject);

  count  |  count  
---------+---------
 2390981 | 2330392

Deuxième hypothèse: les données seraient mieux compressées dans la table TOAST. Concernant des photos JPEG déjà compressées, en principe il ne faut pas s’attendre à une compression supplémentaire par l’algorithme LZ d’un côté comme de l’autre, mais vérifions quand même.

La taille nominative d’un bytea est donné par la fonction octet_length(), la taille sur disque (donc après compression éventuelle) correspond à pg_column_size() moins 4 octets pour l’entête varlena.

Muni de ça, voici une requête qui va calculer et comparer les taux de compression dans les deux cas:

SELECT
 100-avg(ratio_lo) as "% moyen compression LO",
 100-avg(ratio_bytea) as "% moyen compression BYTEA",
 sum(bcmp) as "taille post-compression BYTEA",
 sum(lcmp) as "taille post-compression LO",
 sum(braw) as "taille pré-compression BYTEA",
 sum(lraw) as "taille pré-compression LO"
FROM (
SELECT s.id, bcmp, braw, lcmp, lraw,
  (bcmp*100.0/braw)::numeric(5,2) as ratio_bytea,
  (lcmp*100.0/lraw)::numeric(5,2) as ratio_lo
FROM (
SELECT t.id,
     octet_length(t.data)::numeric as braw,
     (pg_column_size(t.data)-4)::numeric as bcmp,
     s.lraw::numeric,
     s.lcmp::numeric
   FROM photos as t
   JOIN
    (select loid,
       sum(octet_length(data)) as lraw,
       sum(pg_column_size(data)-4) as lcmp
      FROM pg_largeobject
      GROUP BY loid HAVING sum(pg_column_size(data)-4)>0) as s
   ON(loid=id)
) s
) s1;

Résultat:

-[ RECORD 1 ]-----------------+------------------------
% moyen compression LO        | 0.2545161290322581
% moyen compression BYTEA     | 0.0956207233626588
taille post-compression BYTEA | 4771356790
taille post-compression LO    | 4764013877
taille pré-compression BYTEA  | 4771604903
taille pré-compression LO     | 4771604903

Comme prévu, la compression par-dessus JPEG est très faible. Mais celle du bytea l’est encore plus celle des objets larges, avec 0,09% contre 0,25%, soit 7,1 MB de différence cumulée sur la totalité. Donc non seulement ça n’explique pas le surpoids de pg_largeobject, mais ça irait plutôt légèrement dans le sens inverse.

Troisième hypothèse: il y a trop de fragmentation ou espace inutilisé à l’intérieur de pg_largeobject par rapport à celles des tables TOAST. Au fait, quelle est cette taille des “chunks” ou mini-pages du côté TOAST? La doc nous dit encore:

Les valeurs hors-ligne sont divisées (après compression si nécessaire) en morceaux d’au plus TOAST_MAX_CHUNK_SIZE octets (par défaut, cette valeur est choisie pour que quatre morceaux de ligne tiennent sur une page, d’où les 2000 octets)

Pour estimer l’espace inutilisé dans les pages, sans aller jusqu’à les regarder à l’octet près, bien qu’en théorie faisable avec l’extension pg_pageinspect, on peut faire quelques vérifications en SQL de base. Comme le contenu dans notre exemple n’a pas été modifié après import, les données sont a priori séquentielles dans les pages. On peut donc se faire une idée de la relation entre les lignes et les pages les contenant juste en regardant comment évolue la colonne ctid sur des lignes logiquement consécutives.

Par exemple, en prenant une photo au hasard:

# select ctid,pageno,octet_length(data),pg_column_size(data)
  from pg_largeobject where loid=16460;
   ctid   | pageno | octet_length | pg_column_size 
----------+--------+--------------+----------------
 (2855,3) |      0 |         2048 |           1079
 (2855,4) |      1 |         2048 |            132
 (2855,5) |      2 |         2048 |            198
 (2855,6) |      3 |         2048 |           1029
 (2855,7) |      4 |         2048 |            589
 (2856,1) |      5 |         2048 |           2052
 (2856,2) |      6 |         2048 |           2052
 (2856,3) |      7 |         2048 |           2052
 (2857,1) |      8 |         2048 |           2052
 (2857,2) |      9 |         2048 |           2052
 (2857,3) |     10 |         2048 |           2052
 (2858,1) |     11 |         2048 |           2052
 (2858,2) |     12 |         2048 |           2052
 (2858,3) |     13 |         2048 |           2052
 (2859,1) |     14 |         2048 |           2052
 (2859,2) |     15 |         2048 |           2052
 (2859,3) |     16 |         2048 |           2052
... 1900 lignes sautées ...
 (3493,2) |   1917 |         2048 |           2052
 (3493,3) |   1918 |         2048 |           2052
 (3494,1) |   1919 |         2048 |           2052
 (3494,2) |   1920 |         2048 |           2052
 (3494,3) |   1921 |          674 |            678

Dans un ctid comme (2855,3), le premier nombre représente la page et le deuxième le numéro séquentiel de ligne relativement à cette page. On voit dans cet extrait qu’en dehors du début et de la fin, les lignes valent 1,2,3, puis ça passe à la page suivante et ainsi de suite. Très schématiquement, on a le plus souvent 3 lignes par page. C’est logique parce qu’il n’y a pas de place pour 4 lignes. 4*2052 dépasserait déjà 8192 octets, sans même compter les entêtes de ligne et les autres colonnes.

Maintenant regardons l’équivalent dans la table TOAST. C’est trop compliqué de retrouver le chunk_id qui corresponde à la même photo, donc je vais prendre le début de la table, mais on peut vérifier par échantillons aléatoires que le même motif se répète massivement dans toutes ces données.

# select ctid,chunk_id,chunk_seq,octet_length(chunk_data),pg_column_size(chunk_data)
  from pg_toast.pg_toast_18518 limit 20;

 ctid  | chunk_id | chunk_seq | octet_length | pg_column_size 
-------+----------+-----------+--------------+----------------
 (0,1) |    18526 |         0 |         1996 |           2000
 (0,2) |    18526 |         1 |         1996 |           2000
 (0,3) |    18526 |         2 |         1996 |           2000
 (0,4) |    18526 |         3 |         1996 |           2000
 (1,1) |    18526 |         4 |         1996 |           2000
 (1,2) |    18526 |         5 |         1996 |           2000
 (1,3) |    18526 |         6 |         1996 |           2000
 (1,4) |    18526 |         7 |         1996 |           2000
 (2,1) |    18526 |         8 |         1996 |           2000
 (2,2) |    18526 |         9 |         1996 |           2000
 (2,3) |    18526 |        10 |         1996 |           2000
 (2,4) |    18526 |        11 |         1996 |           2000
 (3,1) |    18526 |        12 |         1996 |           2000
 (3,2) |    18526 |        13 |         1996 |           2000
 (3,3) |    18526 |        14 |         1996 |           2000
 (3,4) |    18526 |        15 |         1996 |           2000
 (4,1) |    18526 |        16 |         1996 |           2000
 (4,2) |    18526 |        17 |         1996 |           2000
 (4,3) |    18526 |        18 |         1996 |           2000
 (4,4) |    18526 |        19 |         1996 |           2000

On retrouve la taille de 2000 octets mentionnée dans la doc, et les 4 lignes par page, dans la mesure où les numéros de ligne par page dans ctid sont typiquement 1,2,3,4 avant passage à la page suivante, et ainsi de suite.

4 lignes de chunk_data occupent 4*2000=8000 octets, et les 192 octets restants sur 8192 permettent manifestement de contenir tout le reste, notamment 27 octets d’entête par ligne plus 4+4 octets pour chunk_id et chunk_seq. Ajoutons à ça un entête par page de 24 octets, et il est clair que cette page est occupée presque totalement par des informations utiles: (27 + 4 + 4 + 2000) * 4 = 8140.

Au contraire de ça, nos pages de pg_largeobject semblent être majoritairement occupées par 3 lignes remplies de cette manière: (27 + 4 + 4 + 2052) * 3 = 6261 octets

Compte-tenu de toute ça une estimation grossière du ratio entre les tailles des photos et celle de pg_largeobject pourrait être (2048*3)/8192 = 0,75

Les données “pures” pèsent 4771604903 octets, et pg_largeobject pèse 6402637824 octets.
Le ratio réel d’utilité disque pour les objets larges vaut donc 4771604903 / 6402637824 = 0,745

Du côté TOAST, ce ratio estimé grossièrement est de (2000*4)/8192 = 0,9765625.
Le ratio réel d’utilité est de 4771604903 / 4951490560 = 0,9637

La réalité est remarquablement proche de l’estimation, du fait de la grande taille des objets, de la quasi-absence de compression, et du fait qu’il n’y a pas de désorganisation dans les pages, en l’absence de modifications post-chargement.

Mais cette structuration est assez réaliste par rapport à l’usage qui est souvent fait des contenus binaires, qui sont créés ou effaçés d’un seul tenant, mais dont l’intérieur n’est jamais modifié. Car qui saurait changer des pixels dans une image JPEG ou une phrase dans un PDF avec une requête UPDATE?

Conclusion

Dans ce billet, on a pas mal regardé la structure interne de ces contenus binaires, et observé à travers cet exemple comment une différence de paramétrage de 48 octets a des conséquences finalement non négligeables sur des données de grande taille, ici en faveur du bytea sur l’espace disque.

N’oubliez pas que ce résultat ne s’applique pas forcément à vos données, ça dépend comment elles se compressent et comment ces tables sont mises à jour sur le temps long.

Dans un ou deux autres billets à venir, j’essaierai de détailler d’autres éléments du tableau de comparaison en haut de page, avec d’autres différences assez nettes sur certains points, certaines en faveur des objets larges, d’autres en faveur des bytea.