Aller au contenu

Estimer la taille totale d’une table

Stockage de données sous PostgreSQL

PostgreSQL stocke les données des tables dans ce qu’il appelle en interne la heap (« tas »). Il y a d’autres petits fichiers séparés pour la maintenance de la table (free space map, visibility map), négligés ici.

Les index sont stockés séparément.

Structure d’un enregistrement

Un enregistrement est toujours stocké dans le heap.

Chaque enregistrement a un entête incompressible.

Cet entête stocke des informations de transaction ou de chaînage de versions d’enregistrements. Une partie variable (t_bits) indique quels champs ont pour valeur NULL (s’il y en a dans la ligne), pour éviter de les stocker sur disque.

Pour des raisons d’alignement, l’entête fera donc au moins 24 octets (une estimation suffisante la plupart du temps pour les calculs), mais 32 octets s’il y a 9 champs ou plus, 40 à partir de 73 champs, etc.

Chaque enregistrement fait donc au moins 24 octets + la taille de l’enregistrement.

Référence : Disposition des données dans une page

Structure d’un bloc

Par défaut, la taille de bloc (ou page) est de 8 ko. Il faut recompiler PostgreSQL pour le changer, ce qui est très rarement fait et ouvre de possibles incompatibilités avec nombre d’outils. On gardera donc 8 ko pour la suite.

Dans un bloc, les données sont à la fin :

Structure d'un bloc (documentation officielle)

L’entête (PageHeaderData) fait 24 octets : adresse du journal auquel ce bloc a été modifié pour la dernière fois, numéro de version, somme de contrôle, adresse du début de l’espace libre, etc… (détails)

Après lui, se trouve un tableau de pointeurs, chacun de 4 octets, vers les enregistrements (Item) qui sont à la fin du bloc.

La ligne de ctid (0,2) est donc le 2ᵉ item en partant de la fin du premier bloc de la table.

Place perdue

Le « surcoût total » par ligne de données est donc d’au moins 28 octets (24 d’entête, au moins + 4 de pointeur), plus l’entête partagé entre tous les enregistrements d’un bloc (24 octets, soit 0,3 % d’un bloc plein).

Référence : PostgreSQL - Architecture et notions avancées (Guillaume Lelarge), chapitre 3.2

Fillfactor

De plus, chaque table ou index peut se voir affecté un fillfactor, c’est-à-dire un pourcentage maximum de remplissage du bloc lors des INSERT (cette limite pouvant être dépassée lors d’un UPDATE).

Intérêt d’un fillfactor inférieur à 100%

Les UPDATE ont plus de chance de se réaliser dans le même bloc, améliorant l’effet de cache.

Les mises à jour HOT sont favorisées.

Par défaut le fillfactor d’une table est de 100 %, pour une compacité maximale. On le modifie ainsi :

CREATE TABLE xxx … WITH (fillfactor = 80) ;
ALTER  TABLE xxx … SET  (fillfactor = 90) ;

TOAST

Une ligne doit tenir dans un unique bloc de 8 ko. C’est parfois insuffisant. Pour contourner cette limitation, sur certaines lignes, certaines colonnes sont « toastées », c’est-à-dire stockées compressées dans le champ, ou stockées à l’extérieur de la table, dans une table d’extension.

Seuls les types à longueur variable subissent ce traitement : chaînes (varchar,json, hstore…), binaires (bytea, jsonb…), composites (record)… Les champs numeric peuvent être compressés dans la heap.

Ne sont pas toastés les champs fixes, soit int et float et leurs variantes, char, les divers champs date et heure, uuid, bool, les champs techniques… Dans le doute, voir le champ typstorage de la table des types pg_type.

Avantages du TOAST

Le système est totalement automatique et transparent pour l’utilisateur.

Les données de ces colonnes ne sont lues que si elles sont réellement nécessaires, le reste du temps, elles ne sont pas consultées (c’est une raison supplémentaire d’éviter SELECT *).

Lors d’un UPDATE, les colonnes toastées et non modifiées peuvent être partagées entre les différentes versions de l’enregistrement : la nouvelle version pointe simplement vers la même entrée TOAST.

Les valeurs toastées sont parfois compressées, dans la heap ou la table d’extension.

Le coût supplémentaire en place est proportionnellement très faible.

Un champ est candidat à TOAST si l’enregistrement dépasse le quart de la taille du bloc (2 ko). Les plus gros champs seront toastés jusqu’à ce que l’enregistrement redescende à 2 ko ou moins.

PostgreSQL décide du mode de stockage champ par champ et ligne par ligne.

Taille de chaque type

Types de taille fixe

Ce sont des champs très courants : numériques, date/heure…

Ils n’ont pas d’entête variable (varlena dans la nomenclature PostgreSQL). Ils ont des contraintes d’alignement.

Type Taille (octets) Alignement (octets)
bool 1 1
char 1 1
int8 8 8
int2 2 2
int4 4 4
float4 4 4
float8 8 8
date 4 4
time 8 8
timestamp 8 8
timestamptz 8 8
interval 16 8

Types de taille variable

Ils ont un entête de 4 octets incompressibles indiquant leur taille réelle (jusqu’à 1 Go en théorie), dont 2 bits indiquant s’ils sont inline ou toasté, compressé ou non-compressé (cela peut varier pour chaque enregistrement).

Leur taille est suffisamment importante pour que les contraintes d’alignement soient sans importance.

Types texte

Ne pas confondre caractère et octet

La taille en caractères d’une chaîne est différente de sa taille en octets à cause de l’encodage UTF-8 généralement utilisé (1 à 4 octets selon le caractère).

La taille occupée est :

  • si la chaîne est plus courte que 126 octets : 1 octet + la longueur de la chaîne
  • si elle est fait 126 octets ou plus : 4 octets + la longueur de la chaîne
  • pour les plus grandes chaînes : le mécanisme TOAST compresse ou remplace par un pointeur.

Ces trois types sont identiques du point de vue du stockage :

  • char(N) (ou bpchar) a toujours la taille N caractères (si non précisé : 1 caractère) et au besoin est complété par des espaces à la fin.

  • varchar(N) (ou character varying) est de taille variable, limitée par N (au plus 10 485 760).

  • text (identique à varchar sans limite de taille) a une taille arbitraire, éventuellement plusieurs mégaoctets.

Référence : Character types

Types numériques à précision arbitraire

numeric/numeric (p,s) (identique à decimal) peut avoir une précision allant jusqu’à 147 455 chiffres (131 072 avant la virgule, 16 383 après), hors zéros en début ou fin (pour du numericsans contrainte).

La place est de 2 octets pour chaque groupe de 4 chiffres significatifs, plus 3 à 8 octets d’entête. Au-delà d’environ 2000 octets, le mécanisme TOAST compresse les données.

Référence : Arbitrary Precision Numbers

Types binaires

  • bytea suit la même règle que text : taille de la chaîne + 1 ou 4 octets, éventuellement avec TOAST.

  • bit strings (bit (N) et bit varying(N)) : nombre de bits dans le bit string, aligné à l’octet (tout octet commencé est dû), plus 5 à 8 octets d’entête selon la taille de la chaîne, éventuellement avec TOAST.

Référence : Binary Data Types, Bit String Types

Types composés

Pour les tableaux et types composites, la taille est grosso modo la taille des éléments les composant.

Autres types

Toutes les informations peuvent être retrouvées dans pg_type :

SELECT typname, typlen, typalign FROM pg_type ;
où :

  • typname est le nom du type
  • typlen est sa taille en octets (ou -1 pour un type de longueur variable)
  • typalign est la contrainte d’alignement du type : c pour 1 octet, s pour 2 octets, i pour 4 octets, d pour 8 octets (qui peut différer entre machines 32 et 64 bits).

Alignement

L’alignement des types (ou padding) a surtout un sens pour les types fixes. Il indique sur quelle adresse une donnée peut être stockée. Par exemple :

  • un type char(1) (1 octet) peut être stocké à toute adresse (en octet)
  • un type smallint (ou int2) ne peut être stocké qu’à chaque adresse multiple de 2 octets.

L’ordre de déclaration des champs a un impact : 

CREATE TABLE demopadd0 ( j int8, i int2, a char (1), b char(1) ) ;
CREATE TABLE demopadd2 ( a char (1), j int8, b char(1), i int2 ) ;
INSERT INTO demopadd0 SELECT 255,16, 'A','B'  FROM generate_series (1,100000) ;
INSERT INTO demopadd2 SELECT 'A', 255, 'B', 16 FROM generate_series (1,100000) ;
ANALYZE demopadd0,demopadd2;
SELECT relname, pg_size_pretty(relpages*8192::bigint) FROM pg_class
WHERE relname LIKE 'demopadd%' ORDER BY 1;

  relname  | pg_size_pretty 
-----------+----------------
 demopadd0 | 4328 kB
 demopadd2 | 5096 kB

Dans le deuxième cas, l’alignement des deux entiers est gêné par les char.

Il ne faut pas exagérer ce problème d’alignement lors de la conception

Il est rare d’utiliser les types de petite taille (char, int2, bool). Le plus gros des données est aligné à 4 octets ou plus.

Si on veut éviter de sous-estimer l’espace utilisé par ces types on peut leur attribuer 4 octets en tenant compte de l’alignement.

C’est à garder à l’esprit cependant pour les plus grandes tables.

Noter qu’une modification implique la reconstruction complète de la table.

Règle de calcul pour une table

Pour estimer rapidement la taille d’une table :

Taille estimée d’une table

(28 octets + Somme (tailles(champs)))
  × nb_enregistrements
  × 1,003
  / fillfactor

sachant que nous avons vu que :

  • la taille des champs fixes peut être surévaluée à au moins 4 octet par champ (alignement), les champs plus grands étant indiqués plus haut

  • la taille des chaînes est de 1 ou 4 octets + longueur moyenne de la chaîne

  • la taille des numeric serait de 8 + taille mantisse/2

  • le 1,003 tient compte de l’entête de chaque bloc

  • le fillfactor est ici exprimé en fraction

Ceci ne peut être qu’une approximation pour une table fraîchement chargée ou reconstruite.

Les blocs de 8192 octets ne seront jamais remplis exactement, et encore moins le tout dernier.

Les modifications des lignes génèrent des lignes mortes, et l’autovacuum attendra toujours un peu avant de les netoyer (par défaut, le seuil est de 20 % de lignes mortes).

La taille des chaînes variables est délicate à estimer.

Les valeurs NULL ne prennent pas de place (un bit est positionné dans l’entête).

L’entête de ligne peut faire beaucoup plus de 24 octets s’il y a trop de colonnes.

Les lignes de plus de 2 ko (par défaut) seront toastées. La compression est difficilement estimable, menant à une surestimation. Une valeur déplacée dans la table TOAST associée sera découpée en autant de morceaux de 2 ko, avec le coût pour gérer ces lignes (entêtes, index).

Estimation de taille d’un index B-tree

Nous ne parlerons pas des GIN, GIST… bien plus complexes à évaluer.

  • Une page d’index B-Tree commence comme une table, avec un entête de 24 octets, et 4 octets par enregistrement dans la page.

  • Chaque entrée d’index a un entête de 6 octets (pointeur vers l’enregistrement). Il contient ensuite les données des colonnes de l’index (même taille et contraintes d’alignement que les données de l’enregistrement).

  • Chaque entrée d’index a un pointeur de 4 octets dans l’entête de la page, tout comme une entrée de page heap. Le « coût » d’entêtes pour un enregistrement d’index est donc de 10 octets.

  • Les contraintes d’alignement sont plus importantes pour les index que pour les données (une entrée d’index est bien plus compacte). La partie « données » d’une entrée d’index est alignée sur la taille de pointeur de l’architecture (8 octets en 64 bits)

Pour une taille moyenne d’enregistrement de 19 octets :

Il faudra donc arrondir la taille à 24 octets sur une machine 64 bits, et à 20 octets sur une machine 32 bits.

  • Le fillfactor d’un index est par défaut de 90%.

  • On ignore les pages intermédiaires de l’index, dont le nombre est extrêmement faible par rapport aux feuilles (c’est le principe des B-Tree). L’évolution du nombre de pages intermédiaires est fonction du logarithme du nombre de pages totales.

Un index a donc pour taille théorique :

(10 octets + somme des tailles (champs) alignée à l'architecture) × nb_enregistrements × 1,003 / 0,90

Là encore ce n’est qu’une approximation

Un index se fragment également au long de sa vie.

La déduplication des valeurs indexées identiques peut réduire drastiquement la taille, si les données se répètent. (Du moins depuis PostgreSQL et pour certains types : entiers, textes à collation déterministe…)

Fonctions de calculs de volumétrie

Ces fonctions permettent de connaître les volumétries des différentes parties d’une table : 

SELECT
    oid AS table_oid,
    c.relnamespace::regnamespace || '.' || relname AS TABLE,
    reltoastrelid,
    reltoastrelid::regclass::text AS table_toast,
    reltuples AS nb_lignes_estimees,
    pg_size_pretty(pg_table_size(c.oid)) AS " Table (dont TOAST)",
    pg_size_pretty(pg_relation_size (c.oid,'main')) AS "  Heap",
    pg_size_pretty(pg_relation_size (reltoastrelid,'main')) AS "  Toast",
    pg_size_pretty(pg_indexes_size  (reltoastrelid)) AS  "  Toast (PK)",
    pg_size_pretty(pg_relation_size (c.oid,'vm')) AS "  Vis.map",
    pg_size_pretty(pg_relation_size (c.oid,'fsm')) AS "  FSM",
    pg_size_pretty(pg_indexes_size  (c.oid)) AS " Index",
    pg_size_pretty(pg_total_relation_size(c.oid)) AS "Total"
FROM  pg_class c
WHERE relkind = 'r'
AND   relname = 'textes'
\gx

-[ RECORD 1 ]------+------------------------
table_oid           ║ 220416
table               ║ public.textes
reltoastrelid       ║ 220419
table_toast         ║ pg_toast.pg_toast_220416
nb_lignes_estimees  ║ 2.0985848e+07
 Table (dont TOAST) ║ 3187 MB
  Heap              ║ 3186 MB
  Toast             ║ 24 kB
  Toast (PK)        ║ 16 kB
  Vis.map           ║ 104 kB
  FSM               ║ 824 kB
 Index              ║ 310 MB
Total               ║ 3497 MB
Référence : Fonctions de calcul de taille des objets

Exemples

Exemple 1

Pour la table ci-dessus : 

CREATE TABLE demopadd0 ( j int8, i int2, a char (1), b char(1) ) ;

On calcule : 

( 28  +  8 + 2 + (1+1) + (1+1) ) × 100000 × 1,003 / 1024 = 4113 Mo
ou plutôt 4309 Mo en surestimant l’alignement, ce qui est la valeur obtenue plus haut.

Exemple 2

Cette table possède 3 index :

CREATE TABLE test_taille (a int, b varchar, c int);
INSERT INTO test_taille SELECT i, i::text, i FROM generate_series  (50000,50000+1e6) i;

CREATE INDEX test_taille_idx1 on test_taille (a);
CREATE INDEX test_taille_idx2 on test_taille (a,b);
CREATE INDEX test_taille_idx3 on test_taille (c,a,b);

Le champ texte b fait en moyenne 6 caractères. La table contient 1 million d’enregistrement.

  • La taille de la table est donc de : 

    (28 + 4 + (1+6) + 4) × 10⁶ × 1,003 / 1024 / 1024 = 41,1 Mo

  • La taille de l’index test_taille_idx1 (en 64 bits) est : 

    (4 octets d'entête + ( (6 octets + somme tailles(champs) ) aligné à l'architecture ) ) × nb_enregistrements x 1,003 / 90%

  (4 + (6+4) aligné à 8 ) × 10⁶ × 1,003 / 0,9 / 1024 / 1024
= (4 + 16 ) × 10⁶ × 1,003 / 0,9 / 1024 / 1024
= 21,7 Mo

  • Pour test_taille_idx2 : 

      (4 + (6+4+6+1) aligné à 8) × 10⁶ × 1,003 / 0,9 / 1024 / 1024
= 29,7 Mo

  • Pour test_taille_idx3 :

  (4 + (6+4+6+1+4) aligné à 8) × 10⁶ × 1,003 / 0,9 / 1024 / 1024
= 29,7 Mo
Soit la même taille que l’index précédent, car l’alignement à 8 octets donne 24 dans les deux cas.

  • Vérification : 

    SELECT relname, pg_size_pretty ( pg_relation_size(oid,'main') )
FROM pg_class WHERE relname LIKE 'test_taille%' ;

         relname      | pg_size_pretty 
------------------+----------------
 test_taille      | 42 MB
 test_taille_idx1 | 21 MB
 test_taille_idx2 | 30 MB
 test_taille_idx3 | 30 MB

  • Par contre, si la déduplication peut opérer, ce sera beaucoup moins : 

    TRUNCATE TABLE test_taille ;
INSERT INTO test_taille SELECT 1, '123456', 2 FROM generate_series  (1,+1e6) i;
    La taille de la table reste identique, mais les index descendent à 6 Mo chacun.

Exemple 2

Pour une base pgbench de taille 100 : 

pgbench -i -s 100  pgbench

  • La table pgbench_accounts est définie par : 
    pgbench=# \d pgbench_accounts
                  Table « public.pgbench_accounts »
 Colonne  |     Type      | Collationnement | NULL-able | Par défaut 
----------+---------------+-----------------+-----------+------------
 aid      | integer       |                 | not null  | 
 bid      | integer       |                 |           | 
 abalance | integer       |                 |           | 
 filler   | character(84) |                 |           | 
…

Elle fait 10 000 000 lignes de 28 + 4 + 4 + 4 + (1+84) = 125 octets (arrondi à 128 avec l’alignement).

  • La taille attendue est :

128 × 10 000 000 × 1,003 / 1024 /1024 = 1224 Mo

  • En réalité : 
    SELECT relpages, pg_size_pretty(relpages*8192::numeric)
FROM pg_class where relname='pgbench_accounts' ;

 relpages | pg_size_pretty 
----------+----------------
   163935 | 1281 MB

On va tenter d’expliquer ce petit écart.

  • La requête suivante permet de voir qu’il y a 61 lignes dans la plupart des blocs : 

    SELECT (ctid::text::point)[0] AS bloc, count(*) AS nb_enregistrements
FROM pgbench_accounts
GROUP BY 1 ORDER BY 1 ;

  • La requête suivante dans un bloc au hasard permet de voir la disposition de ces lignes :

SELECT *, length(t_data) AS taille_t_data
FROM heap_page_items (get_raw_page ('pgbench_accounts','main', 1111) )
WHERE lp <= 2 or lp >=61 
ORDER BY lp
\gx
Résultat de la requête 
-[ RECORD 1 ]-+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lp            | 1
lp_off        | 8064
lp_flags      | 1
lp_len        | 121
t_xmin        | 12538435
t_xmax        | 0
t_field3      | 2
t_ctid        | (1111,1)
t_infomask2   | 4
t_infomask    | 2818
t_hoff        | 24
t_bits        | 
t_oid         | 
t_data        | \xbc0801000100000000000000ab202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
taille_t_data | 97
-[ RECORD 2 ]-+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lp            | 2
lp_off        | 7936
lp_flags      | 1
lp_len        | 121
t_xmin        | 12538435
t_xmax        | 0
t_field3      | 2
t_ctid        | (1111,2)
t_infomask2   | 4
t_infomask    | 2818
t_hoff        | 24
t_bits        | 
t_oid         | 
t_data        | \xbd0801000100000000000000ab202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
taille_t_data | 97
-[ RECORD 3 ]-+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lp            | 61
lp_off        | 384
lp_flags      | 1
lp_len        | 121
t_xmin        | 12538435
t_xmax        | 0
t_field3      | 2
t_ctid        | (1111,61)
t_infomask2   | 4
t_infomask    | 2818
t_hoff        | 24
t_bits        | 
t_oid         | 
t_data        | \xf80801000100000000000000ab202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020
taille_t_data | 97

et on y lit :

  • les données de la ligne (t_data) font bien 97 octets
  • le champ lp_off indique que la première ligne commence à l’octet 8064 (en fin de bloc, ce qui nous amène à 8064 + une ligne de 128 = 8192 octets)
  • celui de la deuxième à 7636 (et 7936 + 128 = 8064)

  • celui de la 61ᵉ et dernière à 384.

  • De plus, l’entête du bloc fait 24 + 4×61 = 268 octets. Cela explique qu’entre la fin de cet entête et l’octet 384, il n’y a pas la place de caser une 62ᵉ ligne, et on perd donc 116 octets par bloc.

  • On retombe bien sur : 

    ( ( 268 octets d'entête + 116 perdus ) × 163 935 blocs + 128 octets × 10 millions de lignes ) / 1024 / 1024
= 1281 Mo

Exemple 2 (suite)

Pour cette même table, passer le fillfactor de 100 (défaut) à 80 augmente la taille de la table de 1/0,80 (+25 %), soit progressivement lors des mises à jour, soit d’un coup avec un VACUUM FULL ;

ALTER  TABLE xxx pgbench_accounts SET  (fillfactor = 80) ;
VACUUM FULL ;

La table passe en conséquence de 1281 à 1594 Mo.

Pour aller plus loin