Stockage physique, gestion des données et VACUUM

Rédigé par Bazinga et publié le 3 février 2024
Temps de lecture d'environ 120 min
Niveau d'expertise : 5/5

BDD PostgreSQL

ANALYZE Heap MVCC page TOAST Transaction tuple VACUUM

En SIG, on oppose souvent le stockage sous forme de fichiers plats au stockage en base de données. Mais comment fait PostgreSQL pour stocker réellement les données ? Et bien il utilise des fichiers plats… (le serpent se mort la queue). Nous allons donc démystifier un peu ce fonctionnement.

Autre question que j’ai pu avoir en lien avec le stockage : pourquoi après avoir fait du nettoyage dans une base de données, l’espace disque n’avait pas franchement diminué, y compris après avoir utilisé la commande VACUUM ? Nous allons voir d’où provient ce comportement.

Notez que je prendrais parfois quelques raccourcis pour pour simplifier les explications. Nous ne parlerons également que des tables et non des index.

Fonctions personnalisées

Pour nous aider dans notre quête, nous allons avoir besoin d’extensions spécifiques et de fonctions créées pour l’occasion. Les voici, vous pourrez les décortiquer une fois que vous aurez lu l’article :

-- Ajout des extensions
CREATE EXTENSION pgstattuple;
CREATE EXTENSION pageinspect;
CREATE EXTENSION pg_freespacemap;

-- Fonction de récupération des informations sur les pages d'une table
DROP FUNCTION IF EXISTS public.get_table_info(relname TEXT);

CREATE OR REPLACE FUNCTION public.get_table_info(relname TEXT)
RETURNS TABLE(
  "schema" text,
  "table" text,
  "tablespace" text,
  oid_table oid,
  numero_heap_file oid,
  heap_file_segment_0_location text,
  nb_page bigint,
  tuple_count bigint,
  dead_tuple_count bigint,
  table_length bigint,
  table_length_human text,
  tuple_length bigint,
  tuple_percent numeric,
  dead_tuple_length bigint,
  dead_tuple_percent numeric,
  free_space_length bigint,
  free_space_percent numeric,
  max_frozen_txid xid,
  n_tup_ins bigint,
  n_tup_upd bigint,
  n_tup_del bigint,
  n_mod_since_analyze bigint,
  n_ins_since_vacuum bigint,
  last_vacuum timestamp with time zone, 
  last_autovacuum timestamp with time zone, 
  last_analyze timestamp with time zone,
  last_autoanalyze timestamp with time zone,
  toast_oid oid,
  toast_table text,
  toast_heap_file_segment_0_location text,
  toast_nb_page bigint,
  toast_nb_tuple bigint,
  toast_length bigint,
  toast_length_human text
)
AS $function$
/*
 * FUNCTION table_info(relname TEXT)
 * Renvoi différentes informations sur une table et son stockage
 * 
 * PARAMETERS
 *  relname TEXT : objet à analyser au choix :
 *    - 'schema.Table' : pour analyser une table en particulier.
 *    - 'schema' : pour analyser toutes les tables d'un schéma.
 *    Les noms d'objet n'ont pas besoin d'être quotés.
 * 
 */
DECLARE 
  var_requete TEXT;
  schemaname TEXT;
  table_schema record;
  schema_check boolean DEFAULT FALSE;
  where_clause TEXT;

BEGIN
  
  -- Nettoyage du nom d'objet
  relname := replace(relname, '"', '');
  


  -- L'analyse porte-elle sur un schéma ?
  var_requete := 
    $a$SELECT 
      TRUE
    FROM 
      information_schema.schemata
    WHERE 
      schema_name = $a$ || quote_literal(relname)
  ;

  EXECUTE 
    var_requete
  INTO 
    schema_check
  ;
  

  
  -- Action selon si schema/table
  IF schema_check THEN
  
    RAISE NOTICE USING message = 'ANALYZE for schema';
    
    -- Clause de récupération des données d'un schéma
    where_clause := 
      $a$pgc.relnamespace = $a$ || quote_literal(relname) || $a$::regnamespace $a$
    ;
  
    -- Boucle sur toutes les tables d'un schema
    var_requete := 
      $a$SELECT
        pgc.relname::TEXT AS "table"
      FROM
        pg_class AS pgc
      WHERE 
        $a$ || where_clause
    ;
  
    --RAISE NOTICE USING MESSAGE = var_requete;
  
    FOR table_schema IN EXECUTE var_requete
        
    LOOP
      
      var_requete := 
        $a$ANALYZE $a$ || quote_ident(relname) || '.' || quote_ident(table_schema."table")
      ;
      
      --RAISE NOTICE USING MESSAGE = var_requete;

        -- Analyse de la table
      EXECUTE 
        var_requete
      ;
      
      
    END LOOP;
    
  
  ELSE 
  
    RAISE NOTICE USING message = 'ANALYZE for table';
  
    -- Quotation du schema.table
    relname := 
      quote_ident((regexp_match(relname, '(.*)\..+'))[1]) || 
      '.' || 
      quote_ident((regexp_match(relname, '.*\.(.+)'))[1])
    ;
  
    -- Clause de récuépration d'une table
    where_clause :=
      $a$pgc.oid = $a$ || quote_literal(relname) || $a$::regclass $a$
    ;

    -- Analyse de la table
    EXECUTE 
      $a$ANALYZE $a$ || relname
    ;
  
  END IF;


  
  -- Renvoi des statistiques
  var_requete := 
    $a$SELECT 
      pgc.relnamespace::regnamespace::text AS "schema",
      pgc.relname::text AS "table",
      COALESCE (pgt.spcname, 'pg_default')::TEXT AS "tablespace",
      pgc.oid AS oid_table,
      pg_relation_filenode(pgc.oid) AS numero_heap_file,
      current_setting('data_directory') || '/' || pg_relation_filepath(pgc.oid) AS "heap_file_segment_0_location",
      pgc.relpages::int AS nb_page,
      pst.tuple_count::bigint,
      pst.dead_tuple_count::bigint,
      pst.table_len::bigint AS table_length,
      pg_size_pretty(pst.table_len) AS table_length_human,
      pst.tuple_len::bigint AS tuple_length,
      pst.tuple_percent::numeric,
      pst.dead_tuple_len::bigint AS dead_tuple_length,
      pst.dead_tuple_percent::numeric,
      pst.free_space::bigint AS free_space_length,
      pst.free_percent::NUMERIC AS free_space_percent,
      pgc.relfrozenxid AS max_frozen_txid,
      psut.n_tup_ins::bigint,
      psut.n_tup_upd::bigint,
      psut.n_tup_del::bigint,
      psut.n_mod_since_analyze::bigint,
      psut.n_ins_since_vacuum::bigint,
      psut.last_vacuum, 
      psut.last_autovacuum, 
      psut.last_analyze,
      psut.last_autoanalyze,
      pgc.reltoastrelid AS toast_oid,
      pgct.relname::text AS toast_table,
      current_setting('data_directory') || '/' || pg_relation_filepath(pgct.oid) AS "toast_heap_file_segment_0_location",
      pgct.relpages::int AS toast_nb_page,
      pgct.reltuples::int AS toast_nb_tuple,
      pg_relation_size(pgc.reltoastrelid)::bigint AS toast_length,
      pg_size_pretty(pg_relation_size(pgc.reltoastrelid))::text AS toast_length_human
    FROM 
      pg_class AS pgc
    LEFT JOIN 
      pg_tablespace AS pgt 
      ON pgt.oid = pgc.reltablespace 
    LEFT JOIN 
      pg_class AS pgct 
      ON pgct.oid = pgc.reltoastrelid
    LEFT JOIN
      pg_stat_user_tables AS psut
      ON psut.relid = pgc.oid
    LEFT JOIN LATERAL
      pgstattuple(pgc.oid::regclass::text) AS pst
      ON TRUE
    WHERE 
      $a$ || where_clause
    ;
  
    --RAISE NOTICE USING MESSAGE = var_requete;
    
    RETURN QUERY EXECUTE 
      var_requete
    ;

    RETURN;
END
$function$
LANGUAGE PLPGSQL
;

-- Fonction de récupération des informations sur les pages d'une table
DROP FUNCTION IF EXISTS public.get_table_info(relname TEXT);

CREATE OR REPLACE FUNCTION public.get_table_info(relname TEXT)
RETURNS TABLE(
  "schema" text,
  "table" text,
  "tablespace" text,
  oid_table oid,
  numero_heap_file oid,
  heap_file_segment_0_location text,
  nb_page bigint,
  tuple_count bigint,
  dead_tuple_count bigint,
  table_length bigint,
  table_length_human text,
  tuple_length bigint,
  tuple_percent numeric,
  dead_tuple_length bigint,
  dead_tuple_percent numeric,
  free_space_length bigint,
  free_space_percent numeric,
  max_frozen_txid xid,
  n_tup_ins bigint,
  n_tup_upd bigint,
  n_tup_del bigint,
  n_mod_since_analyze bigint,
  last_vacuum timestamp with time zone, 
  last_autovacuum timestamp with time zone, 
  last_analyze timestamp with time zone,
  last_autoanalyze timestamp with time zone,
  toast_oid oid,
  toast_table text,
  toast_heap_file_segment_0_location text,
  toast_nb_page bigint,
  toast_nb_tuple bigint,
  toast_length bigint,
  toast_length_human text
)
AS $function$
/*
 * FUNCTION table_info(relname TEXT)
 * Renvoi différentes informations sur une table et son stockage
 * 
 * PARAMETERS
 *  relname TEXT : objet à analyser au choix :
 *    - 'schema.Table' : pour analyser une table en particulier.
 *    - 'schema' : pour analyser toutes les tables d'un schéma.
 *    Les noms d'objet n'ont pas besoin d'être quotés.
 * 
 */
DECLARE 
  var_requete TEXT;
  schemaname TEXT;
  table_schema record;
  schema_check boolean DEFAULT FALSE;
  where_clause TEXT;

BEGIN
  
  -- Nettoyage du nom d'objet
  relname := replace(relname, '"', '');
  


  -- L'analyse porte-elle sur un schéma ?
  var_requete := 
    $a$SELECT 
      TRUE
    FROM 
      information_schema.schemata
    WHERE 
      schema_name = $a$ || quote_literal(relname)
  ;

  EXECUTE 
    var_requete
  INTO 
    schema_check
  ;
  

  
  -- Action selon si schema/table
  IF schema_check THEN
  
    RAISE NOTICE USING message = 'ANALYZE for schema';
    
    -- Clause de récupération des données d'un schéma
    where_clause := 
      $a$pgc.relnamespace = $a$ || quote_literal(relname) || $a$::regnamespace $a$
    ;
  
    -- Boucle sur toutes les tables d'un schema
    var_requete := 
      $a$SELECT
        pgc.relname::TEXT AS "table"
      FROM
        pg_class AS pgc
      WHERE 
        $a$ || where_clause
    ;
  
    --RAISE NOTICE USING MESSAGE = var_requete;
  
    FOR table_schema IN EXECUTE var_requete
        
    LOOP
      
      var_requete := 
        $a$ANALYZE $a$ || quote_ident(relname) || '.' || quote_ident(table_schema."table")
      ;
      
      --RAISE NOTICE USING MESSAGE = var_requete;

        -- Analyse de la table
      EXECUTE 
        var_requete
      ;
      
      
    END LOOP;
    
  
  ELSE 
  
    RAISE NOTICE USING message = 'ANALYZE for table';
  
    -- Quotation du schema.table
    relname := 
      quote_ident((regexp_match(relname, '(.*)\..+'))[1]) || 
      '.' || 
      quote_ident((regexp_match(relname, '.*\.(.+)'))[1])
    ;
  
    -- Clause de récuépration d'une table
    where_clause :=
      $a$pgc.oid = $a$ || quote_literal(relname) || $a$::regclass $a$
    ;

    -- Analyse de la table
    EXECUTE 
      $a$ANALYZE $a$ || relname
    ;
  
  END IF;


  
  -- Renvoi des statistiques
  var_requete := 
    $a$SELECT 
      pgc.relnamespace::regnamespace::text AS "schema",
      pgc.relname::text AS "table",
      COALESCE (pgt.spcname, 'pg_default')::TEXT AS "tablespace",
      pgc.oid AS oid_table,
      pg_relation_filenode(pgc.oid) AS numero_heap_file,
      current_setting('data_directory') || '/' || pg_relation_filepath(pgc.oid) AS "heap_file_segment_0_location",
      pgc.relpages::int AS nb_page,
      pst.tuple_count::bigint,
      pst.dead_tuple_count::bigint,
      pst.table_len::bigint AS table_length,
      pg_size_pretty(pst.table_len) AS table_length_human,
      pst.tuple_len::bigint AS tuple_length,
      pst.tuple_percent::numeric,
      pst.dead_tuple_len::bigint AS dead_tuple_length,
      pst.dead_tuple_percent::numeric,
      pst.free_space::bigint AS free_space_length,
      pst.free_percent::NUMERIC AS free_space_percent,
      pgc.relfrozenxid AS max_frozen_txid,
      psut.n_tup_ins::bigint,
      psut.n_tup_upd::bigint,
      psut.n_tup_del::bigint,
      psut.n_mod_since_analyze::bigint,
      psut.last_vacuum, 
      psut.last_autovacuum, 
      psut.last_analyze,
      psut.last_autoanalyze,
      pgc.reltoastrelid AS toast_oid,
      pgct.relname::text AS toast_table,
      current_setting('data_directory') || '/' || pg_relation_filepath(pgct.oid) AS "toast_heap_file_segment_0_location",
      pgct.relpages::int AS toast_nb_page,
      pgct.reltuples::int AS toast_nb_tuple,
      pg_relation_size(pgc.reltoastrelid)::bigint AS toast_length,
      pg_size_pretty(pg_relation_size(pgc.reltoastrelid))::text AS toast_length_human
    FROM 
      pg_class AS pgc
    LEFT JOIN 
      pg_tablespace AS pgt 
      ON pgt.oid = pgc.reltablespace 
    LEFT JOIN 
      pg_class AS pgct 
      ON pgct.oid = pgc.reltoastrelid
    LEFT JOIN
      pg_stat_user_tables AS psut
      ON psut.relid = pgc.oid
    LEFT JOIN LATERAL
      pgstattuple(pgc.oid::regclass::text) AS pst
      ON TRUE
    WHERE 
      $a$ || where_clause
    ;
  
    --RAISE NOTICE USING MESSAGE = var_requete;
    
    RETURN QUERY EXECUTE 
      var_requete
    ;

    RETURN;
END
$function$
LANGUAGE PLPGSQL
;

-- Fonction de récupération des informations sur les pages d'une table
DROP FUNCTION IF EXISTS public.get_heap_page_info(relname TEXT, page_number INT);

CREATE OR REPLACE FUNCTION public.get_heap_page_info(relname TEXT, page_number INT DEFAULT -1)
RETURNS TABLE(
  page_num int,
  lsn pg_lsn, 
  nb_pointer int,
  nb_tuple int,
  nb_tuple_visible int,
  length_header int,
  length_pointer int,
  length_free_space int,
  length_tuple int,
  length_meta int,
  length_data int,
  length_empty int,
  length_special int,
  offset_pointer int,
  offset_free_space int,
  offset_data int,
  offset_special int,
  offset_page_end int,
  unused_line_pointer bool,
  no_free_space bool,
  all_visible bool
)
AS $function$
/*
 * FUNCTION heap_page_info(relname TEXT, page_number INT)
 * Renvoi différentes informations sur les pages d'une table
 * 
 * PARAMETERS
 *  relname TEXT : table à analyser préfixée par son schéma.
 *    Les noms d'objet n'ont pas besoin d'être quotés.
 *  page_number INT : (facultatif) numéro de page à analyser.
 *    Sans ce paramètre, toutes les pages sont remontées
 * 
 */
BEGIN
  -- Quotation du schema.table
  relname := replace(relname, '"', '');
  relname := 
    quote_ident((regexp_match(relname, '(.*)\..+'))[1]) || 
    '.' || 
    quote_ident((regexp_match(relname, '.*\.(.+)'))[1])
  ;
  
  
  -- Analyse de la table
  EXECUTE 
    $a$ANALYZE $a$ || relname ;


  
  -- Renvoi des statistiques
  RETURN QUERY EXECUTE 
    $a$SELECT
      pages.page_num,
      -- Identifiant xlog de la dernière opération effectuée sur la page.
      ph.lsn,
      tm.count_pointer::int AS nb_pointer,
      tm.count_tuple::int AS nb_tuple,
      tm.count_tuple_visible::int AS nb_tuple_visible,
      24::int AS length_header,
      (ph.lower - 24)::int AS length_pointer,
      (ph.upper - ph.lower)::int AS length_free_space,
      (ph.special - ph.upper)::int AS length_tuple,
      (tm.length_meta)::int,
      (tm.length_data)::int,
      (tm.length_empty)::int,
      (ph.pagesize - ph.special)::int AS length_special,
      24::int AS offset_pointer,
      (ph.lower)::int AS offset_free_space,
      (ph.upper)::int AS offset_data,
      (ph.special)::int AS offset_special,
      (ph.pagesize)::int AS offset_page_end,
      CASE 
        WHEN (ph.flags::int::bit(16) & x'0001') = (x'0001')
          THEN TRUE
        ELSE FALSE
      END AS unused_line_pointer,
      CASE 
        WHEN (ph.flags::int::bit(16) & x'0002') = (x'0002')
          THEN TRUE
        ELSE FALSE
      END AS no_free_space,
      CASE 
        WHEN (ph.flags::int::bit(16) & x'0004') = (x'0004')
          THEN TRUE
        ELSE FALSE
      END AS all_visible
    FROM 
      pg_catalog.pg_class AS pgc
    LEFT JOIN LATERAL 
        generate_series(0, pgc.relpages - 1) AS pages(page_num)
        ON TRUE
    LEFT JOIN LATERAL
        page_header(
          get_raw_page(pgc.oid::regclass::text, pages.page_num)
        ) AS ph
        ON TRUE
    LEFT JOIN LATERAL (
        SELECT
          -- Nombre de pointeur
          count(1) AS count_pointer,
          -- Nombre de tuples
          count(
            CASE 
              WHEN hpi.lp_len > 0 THEN 1
            END
          ) AS count_tuple,
          -- Nombre de tuples visibles
          count(
            CASE 
              WHEN (t_infomask::int::bit(16) & x'0F00') = (x'0100')  
              OR   (t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0800')
              OR   (t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0200' | x'0800')
                THEN 1
            END
          ) AS count_tuple_visible,
          -- Longueur des métadonnées
          sum(hpi.t_hoff::int) AS length_meta,
          -- Longueur des données
          sum((hpi.lp_len - hpi.t_hoff)::int) AS length_data,
          -- Longueur des espaces vides
          sum(((ceil((hpi.lp_off + hpi.lp_len)::numeric / 8) * 8) - (hpi.lp_off + hpi.lp_len))::int) AS length_empty
        FROM
          heap_page_items(
            get_raw_page(pgc.oid::regclass::text, pages.page_num)
          ) AS hpi 
        ) AS tm
        ON TRUE
    WHERE 
      pgc.oid = $a$ || quote_literal(relname) || $a$::regclass 
    AND (
      (
        $a$ || page_number || $a$ <> -1
        AND
        $a$ || page_number || $a$ = pages.page_num
      )OR 
      $a$ || page_number || $a$ = - 1
    ) $a$
    ;

    RETURN;
END
$function$
LANGUAGE PLPGSQL
;

-- Fonction de récupération des informations sur les tuples
DROP FUNCTION IF EXISTS public.get_heap_tuple_info(relname text);

CREATE OR REPLACE FUNCTION public.get_heap_tuple_info(relname TEXT, page_number INT DEFAULT -1)
RETURNS TABLE(
  ctid tid, 
  page_num int,
  page_index int, 
  offset_start int,
  offset_end int,
  offset_next int,
  length_tuple int,
  length_meta int,
  length_data int,
  length_empty int,
  xmin text, 
  xmax text, 
  state text,
  visible bool,
  "update" bool,
  direction text,
  hot bool,
  "delete" bool, 
  "freeze" bool,
  info text[]
)
AS $function$
/*
 * FUNCTION heap_tuple_info(relname TEXT, page_number INT)
 * Renvoi différentes informations sur les tuples présents dans les pages d'une table
 * 
 * PARAMETERS
 *  relname TEXT : table à analyser préfixée par son schéma.
 *    Les noms d'objet n'ont pas besoin d'être quotés.
 *  page_number INT : (facultatif) numéro de page à analyser.
 *    Sans ce paramètre, tous les tuples de toutes les pages sont remontés
 * 
 */
BEGIN
  -- Quotation du schema.table
  relname := replace(relname, '"', '');
  relname := 
    quote_ident((regexp_match(relname, '(.*)\..+'))[1]) || 
    '.' || 
    quote_ident((regexp_match(relname, '.*\.(.+)'))[1])
  ;
  
  -- Analyse de la table
  EXECUTE 
    $a$ANALYZE $a$ || relname ;
  

  
  -- Renvoi des statistiques
  RETURN QUERY EXECUTE 
    $a$SELECT
      -- Indentifiant du tuple
      hpi.t_ctid AS ctid,
      -- Numéro de page
      pages.page_num,
      -- Index dans la page
      hpi.lp::int AS page_index,
      -- Début du tuple dans la page
      hpi.lp_off::int AS offset_start,
      -- Fin du tuple dans la page
      (hpi.lp_off + hpi.lp_len)::int AS offset_end,
      -- Début du tuple suivant dans la page
      (ceil((hpi.lp_off + hpi.lp_len)::numeric / 8) * 8)::int AS offset_next,
      -- Longueur du tuple
      hpi.lp_len::int AS length_tuple,
      -- Longueur des métadonnées
      hpi.t_hoff::int AS length_meta,
      -- Longueur des données
      (hpi.lp_len - hpi.t_hoff)::int AS length_data,
      -- Longueur de l'espacement avant le tuple suivant
      ((ceil((hpi.lp_off + hpi.lp_len)::numeric / 8) * 8) - (hpi.lp_off + hpi.lp_len))::int AS length_empty,
      -- ID de transaction de début de vie
      --    c : transaction commitée
      --    a : transaction annulée
      concat(
        hpi.t_xmin,
        CASE 
          WHEN ((t_infomask::int::bit(16) & x'0F00') & (x'0100' | x'0200')) = (x'0100' | x'0200') THEN ' - f'
          WHEN (t_infomask::int::bit(16) & x'0100') = x'0100' THEN ' - c'
          WHEN (t_infomask::int::bit(16) & x'0200') = x'0200' THEN ' - a'
        END
      ) AS xmin,
      -- ID de transaction de fin de vie
      --    c : transaction commitée
      --    a : transaction annulée
      NULLIF(
        concat(
          hpi.t_xmax,
          CASE 
            WHEN (t_infomask::int::bit(16) & x'0400') = x'0400' THEN ' - c'
            WHEN (t_infomask::int::bit(16) & x'0800') = x'0800' THEN ' - a'
          END
        ),
        '0 - a'
      ) AS xmax,
      -- Etat de la ligne
      CASE lp_flags
        WHEN 0 THEN 'unused'
        WHEN 1 THEN 'normal'
        WHEN 2 THEN 'redirect to '||lp_off
        WHEN 3 THEN 'dead'
      END AS state,
      -- Visibilité actuelle du tuple
      CASE 
        WHEN (t_infomask::int::bit(16) & x'0F00') = (x'0100')  
        OR   (t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0800')
          THEN TRUE
        WHEN (t_infomask::int::bit(16) & x'0F00') = (x'0200') 
        OR   (t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0200' | x'0800')  
        OR   (t_infomask::int::bit(16) & x'0F00') = (x'0000') 
        OR   (t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0400') 
          THEN FALSE
        ELSE FALSE
      END AS visible,
      -- Tuple concerné par un UPDATE
      CASE 
        WHEN (t_infomask::int::bit(16) & x'F000') = (x'2000')
        OR t_infomask IS NULL 
        OR ((t_infomask2::int::bit(16) & x'F000') & (x'4000')) = (x'4000')
        OR ((t_infomask::int::bit(16) & x'0F00') = (x'0100' | x'0400') AND NOT (t_infomask2::int::bit(16) & x'F000') = (x'2000'))
          THEN TRUE
        ELSE FALSE
      END AS "update",
      -- Si concerné par un UPDATE, tuple initial (to) ou final (from)
      NULLIF(
        concat_ws(
          ' and ',
          CASE 
            WHEN (t_infomask::int::bit(16) & x'F000') = (x'2000')
              THEN 'from'
          END,
          CASE 
            WHEN t_infomask IS NULL 
            OR ((t_infomask2::int::bit(16) & x'F000') & (x'4000')) = (x'4000')
              THEN 'to'
          END
        ),
        ''
      ) || ' another line' AS direction,
      -- Tuple hot updated 
      --    le tuple actuel et son ancienne version sont sur la même page
      CASE 
        WHEN (t_infomask2::int::bit(16) & x'F000') = (x'4000')
        OR (t_infomask2::int::bit(16) & x'F000') = (x'8000')
        OR (t_infomask2::int::bit(16) & x'F000') = (x'4000' | x'8000')
          THEN TRUE
        ELSE FALSE
      END AS hot,
      -- Tuple supprimé (ou attribut clé mis à jour)
      CASE 
        WHEN ((t_infomask2::int::bit(16) & x'F000') & (x'2000')) = (x'2000')
          THEN TRUE 
        ELSE FALSE
      END AS "delete",
      -- Identifiant de transaction xmin gelé
      CASE 
        WHEN ((t_infomask::int::bit(16) & x'0F00') & (x'0100' | x'0200')) = (x'0100' | x'0200')
          THEN TRUE
        ELSE FALSE 
      END AS "freeze",
      -- Drapeaux d'information
      ARRAY(
        SELECT
          array_to_string(
            regexp_matches(
              heap_tuple_infomask_flags(t_infomask, t_infomask2)::TEXT,
              '[A-Za-z0-9_\-]+',
              'g'
            ),
            ''
          )
      )::text[] AS info
    FROM 
      pg_catalog.pg_class AS pgc,
      LATERAL 
        generate_series(0, pgc.relpages - 1) AS pages(page_num),
      LATERAL
        heap_page_items(
          get_raw_page(pgc.oid::regclass::text, pages.page_num)
        ) AS hpi
    WHERE 
      pgc.oid = $a$ || quote_literal(relname) || $a$::regclass 
    AND (
      (
        $a$ || page_number || $a$ <> -1
        AND
        $a$ || page_number || $a$ = pages.page_num
      )OR 
      $a$ || page_number || $a$ = - 1
    ) $a$
    ;

    RETURN;
END
$function$
LANGUAGE PLPGSQL
;

Stockage physique

Côté base, les données sont structurées de la façon suivante :

Base de données
Schéma
Table
Ligne
Attribut

Mais dans le système de fichiers, les données sont structurées différemment :

Un répertoire racine par tablespace.
Un répertoire par base de données.
Des fichiers spécifiques pour chaque table.

Tablespace

Le lien entre les données et le système de fichier se matérialise en base via le tablespace. Cet objet permet aux administrateurs de définir l’emplacement des données. Par défaut deux tablespaces existent et pointent vers le répertoire $PGDATA/base du répertoire (sur Windows, il s’agit par défaut de « C:\Program Files\PostgreSQL\16\data\base »). Il s’se nomment :

pg_default : emplacement de stockage par défaut.
pg_global : emplacement de stockage des objets contenant le catalogue système (données sur le cluster).

Il est possible créer un nouveau tablespace pour stocker des données sur un stockage spécifique (un disque dur plus rapide par exemple) :

CREATE TABLESPACE pgraster_data
	OWNER postgres
	LOCATION 'C:\mon_repertoire\de_stockage'
;

Notez que :

Un tablespace est lié à une instance PostgreSQL et ne peut pas être ouvert par une instance autre que celle qui l’a créée.
Un tablespace non trouvable (défaillance du disque dur, déplacement…) peut empêcher le démarrage de l’instance PostgreSQL.
un tablespace peut être utilisé par n’importe qu’elle base de donnée de l’instance à laquelle il appartient.
un tablespace ne peut être supprimé tant qu’un objet est stocké dedans.

Lorsque vous créez un tablespace, un lien symbolique est créé dans le répertoire $PGDATE/pg_tblspc et pointe vers le répertoire définit pour stocker les données du tablespace.

Vous pouvez lister les différents tablespaces ainsi que leurs emplacements de stockage grâce à la commande suivante :

SELECT 
  pt.spcname AS nom,
  pa.rolname AS proprietaire,
  pg_tablespace_location(pt.oid) AS emplacement,
  pg_size_pretty(pg_tablespace_size(pt.oid)) AS poids
FROM 
  pg_tablespace AS pt
LEFT JOIN
  pg_authid AS pa
  ON pa.oid = pt.spcowner
;

Fichiers

Dans le système de fichier, les données sont stockées dans le répertoire du tablespace et s’organise assez simplement :

Un répertoire par base de données, dont le nom correspond à l’OID de la base au sein de l’instance PostgreSQL.
Un ensemble de fichiers par table.

Pour chaque table il existe un ou plusieurs fichiers dont le nom débute toujours par un numéro nommé filenode qui correspond souvent à l’OID de la table (mais pas toujours, par exemple lorsqu’une table est réécrite, un nouveau fichier est créé avec un nouveau numéro ; voir la partir sur les VACUUM).

Vous pourrez trouver les fichiers suivants pour chaque table (exemple pour un filenode = 112233 ) :

Le heap file : ensemble de fichiers contenant les données.
Chaque fichier du heap file est appelé segment et ne peut exéder 1 Go, il y a donc un nombre de segments différent selon la taille de la table.
Le premier segment est nommé filenode sans extension (112233 dans notre exemple), le second se voit ajouter l’extension « 1 » (112233.1), le troisième l’extension « 2 » (11223.2) et ainsi de suite…
La free space map : liste l’espace disponible dans chaque page.
Le fichier est nommé filenode_fsm (112233_fsm).
La visibility map : liste indiquant pour chaque page si toutes les lignes sont visibles pour toutes les transactions et si tous les tuples sont gelés. Cela permet au VACUUM de ne pas avoir à scanner ces pages afin d’aller plus vite.
Le fichier est nommé filenode_vm (112233_vm).

Même si c’est très rare, les fichiers fsm et vm peuvent se voir attribuer des extensions « 1 », « 2 »… s’ils dépassent 1 Go et que d’autres fichiers doivent être créés.

Voici un graphique pour vous représenter la répartition des données :

Exemple d’une carte de visibilité :

Numéro de page	Only visible	Only frozen
0	1	1
1	0	1
2	0	0

La première page ne contient que des tuples visibles, en revanche, les pages d’après contiennent des tuples non visibles : il s’agit de tuples supprimés qui sont encore présents dans la base et en attente de suppression par le VACUUM. Ce dernier n’a donc pas besoin de travailler sur la première page pour le nettoyage. Nous allons voir dans le paragraphe suivant ce qu’est un tuple.

Page

Pour faciliter la gestion des données, leur stockage au sein des segments du heap file se fait sous la forme de paquets de 8 ko de données (paramétrable mais par défaut 8 ko). Un paquet de données est appelé page (ou bloc). Un segment peut donc contenir plusieurs pages.

Les pages sont toujours structurées de la même façon :

différentes métadonnées sur la page (24 octets).
Les pointeurs : il s’agit de données contenant l’emplacement de chaque tuple stocké dans la page (4 octets chacun).
Les index référencent ces pointeurs.
L’espace restant disponible dans la page.
Les tuples : un tuple équivaut à une ligne dans la table.
Il s’agit d’un ensemble de métadonnées associées aux valeurs des attributs de la ligne les unes à la suite des autres.
Les éléments sont positionnés dans l’ordre inverse : du dernier au premier et accolés à la fin de la page.
Des données spécifiques (uniquement pour les table d’index).

La page est considérée comme complète lorsque l’espace disponible vient à manquer : au fur et à mesure du remplissage, les pointeurs rejoignent les tuples et l’espace entre les deux devient insuffisant pour stocker un nouvel élément.

Notez que les tuples sont toujours placés de façon à être alignés sur des multiples de 8 octets. Ce positionnement entraine la présence d’espace entre les tuples lorsque ceux-ci ne possèdent pas une longueur multiple de 8.

Avec une telle structure, vous pouvez donc voir que lorsque PostgreSQL créé une nouvelle page, celle-ci occupe immédiatement 8 ko, même si elle est presque vide car il s’agit d’un conteneur de taille fixe. Ainsi, une base PostgreSQL grossit par multiple de 8 ko.

Pour la lecture des données, il y a deux cas :

Le scan séquentiel : lors d’un parcours complet de la table, PostgreSQL lit chaque pointeur ce qui lui permet ensuite d’aller lire chaque tuple.
Le scan indexé : lors d’un parcours via un index, ce dernier contient le numéro de page ainsi que l’emplacement du pointeur de chaque donnée indexée.
PostgreSQL n’a plus qu’a parcourir les pointeurs pour récupérer la donnée sans avoir à lire tous les tuples (scan séquentiel).

Tuple

Les données de toutes les colonnes d’une ligne sont stockées au sein d’un tuple (en français « uplet »). Pour faire simple, il s’agit d’une liste de données.

Les tuples sont organisés de la façon suivante :

Un en-tête contenant l’identifiant du tuple (ctid), des données de visibilité de transaction (t_xmin et t_xmax) et d’autres éléments que nous verrons plus tard.
Une carte des valeurs NULL au sein de la ligne.
Cette carte n’est présente que si la ligne contient des valeurs NULL.
Il s’agit d’une suite de valeurs booléenne représentant chaque colonne (dans l’ordre donc) avec « 1 » indiquant la présence d’une valeur NULL, « 0 » dans le cas contraire.
Les données les unes à la suite des autres.

L’en-tête d’un tuple mesurant 23 octets, il y a donc 1 octet ajouté pour l’alignement si aucune carte de valeurs NULL n’existe : c’est la taille minimum d’un tuple.

Le placement des données au sein du tuple se fait dans l’ordre de définition des colonnes en respectant deux règles :

L’espace occupé dépend du type :
- L’espace peut être fixe (smallint, integer, bigint…)
- L’espace peut être variable et dépend donc de la donnée (varchar, text…).
L’emplacement d’une donnée par rapport à la précédente dépend du type :
- L’emplacement peut être aligné (smallint, integer, bigint…) (même principe que l’alignement des tuples dans la page sauf qu’ici la longueur est variable).
- L’emplacement peut être non aligné (booléen, varchar…)
  Si ce type de donnée est en première position et qu’aucune carte de valeurs NULL n’est présente, un alignement sur le 24^ème octet est tout de même réalisé.

Voici quelques cas de figure pour les types les plus utilisés :

Booléen :
Pas d’alignement : se place directement après la donnée de la colonne précédente.
Espace occupé de 1 octet.
Smallint – int2 :
Alignement sur un multiple de 2 octets.
Espace occupé de 2 octets.
Attention à ne pas confondre l’alignement et l’espace occupé qui sont les mêmes pour ce type.
Integer – int4 :
Alignement sur un multiple de 4 octets.
Espace occupé de 4 octets.
Attention à ne pas confondre l’alignement et l’espace occupé qui sont les mêmes pour ce type.
Biginteger – int8 :
Alignement sur un multiple de 8 octets.
Espace occupé de 8 octets.
Attention à ne pas confondre l’alignement et l’espace occupé qui sont les mêmes pour ce type.
Text, Varchar :
Alignement variable : aucun alignement en dessous d’une certaine taille ; au dela, les données peuvent être compressée et/ou TOASTées et un header aligné sur un multiple de 4 octets est ajouté.
Espace occupé variable : 1 octet de header plus les données en dessous d’une certaine taille ; au dela, 4 octets de header plus les données.

Voici une requête permettant de connaitre la taille fixée pour les types de données (pour les types variables, l’alignement se fait pour le header éventuellement présent) :

SELECT 
	typname AS type,
	concat(
		typcategory,
		' - ' || 
		CASE typcategory
			WHEN 'A' THEN 'Array types'
			WHEN 'B' THEN 'Boolean types'
			WHEN 'C' THEN 'Composite types'
			WHEN 'D' THEN 'Date/time types'
			WHEN 'E' THEN 'Enum types'
			WHEN 'G' THEN 'Geometric types'
			WHEN 'I' THEN 'Network address types'
			WHEN 'N' THEN 'Numeric types'
			WHEN 'P' THEN 'Pseudo-types'
			WHEN 'R' THEN 'Range types'
			WHEN 'S' THEN 'String types'
			WHEN 'T' THEN 'Timespan types'
			WHEN 'U' THEN 'User-defined types'
			WHEN 'V' THEN 'Bit-string types'
			WHEN 'X' THEN 'unknown type'
			WHEN 'Z' THEN 'Internal-use types'
		END
	) AS category,
	CASE 
		WHEN typlen = -1 THEN 'variable'
		ELSE typlen::text
	END AS longueur,
	concat(
		typalign,
		' - ' || 
		CASE typalign
			WHEN 'c' THEN 'char alignment, i.e., no alignment needed'
			WHEN 's' THEN 'short alignment (2 bytes on most machines)'
			WHEN 'i' THEN 'int alignment (4 bytes on most machines)'
			WHEN 'd' THEN 'double alignment (8 bytes on many machines, but by no means all)'
		END
	) AS alignement,
	concat(
		typstorage,
		' - ' || 
		CASE typstorage
			WHEN 'p' THEN 'plain: Values must always be stored plain (non-varlena types always use this value)'
			WHEN 'e' THEN 'external: Values can be stored in a secondary “TOAST” relation'
			WHEN 'm' THEN 'main: Values can be compressed and stored inline'
			WHEN 'x' THEN 'extended: Values can be compressed and/or moved to a secondary relation'
		END
	) AS stockage
FROM 
	pg_type

Cette valeur d’alignement implique donc une potentielle perte d’espace entre les valeurs de chaque colonne. Ainsi, l’ordre des colonnes a une forte incidence sur le poids d’une table.

Voici un exemple concret pour mieux situer le problème. Voila deux tables avec les mêmes colonnes mais des agencements différents et l’impact que cela a :

CREATE TABLE public.test_non_optimise (
  a int2,
  b int8,
  c int2,
  d int8
)
;

INSERT INTO public.test_non_optimise
VALUES (1, 1, 1, 1)
;

SELECT 
  pg_column_size(test_non_optimise.*) 
FROM 
  public.test_non_optimise
;

La ligne insérée pèse 56 octets que l’on peut décomposer ainsi :

position – longueur : commentaire
0 - 23 o : Métadonnées.
23 - 1 o : Espacement.
24 - 2 o : Données integer (alignée sur un multiple de 2).
26 - 6 o : Espacement.
32 - 8 o : Données biginteger (alignée sur un multiple de 8).
40 - 2 o : Données integer (alignée sur un multiple de 2).
42 - 6 o : Espacement.
48 - 8 o : Données biginteger (alignée sur un multiple de 8).

On note beaucoup d’octets d’espacement.

CREATE TABLE public.test_optimise (
  b int8,
  d int8,
  a int2,
  c int2
)
;

INSERT INTO public.test_optimise 
VALUES (1, 1, 1, 1)
;

SELECT 
  pg_column_size(test_optimise.*) 
FROM 
  public.test_optimise
;

La ligne insérée pèse 44 octets que l’on peut décomposer ainsi :

position – longueur : commentaire
0 - 23 o : Métadonnées.
23 - 1 o : Espacement.
24 - 8 o : Données biginteger (alignée sur un multiple de 8).
32 - 8 o : Données biginteger (alignée sur un multiple de 8).
40 - 2 o : Données integer (alignée sur un multiple de 2).
42 - 2 o : Données integer (alignée sur un multiple de 2).

L’optimisation du placement des colonnes permet de gagner 22% d’espace disque : c’est non négligeable sur des tables de grande taille.

Toast

Les données d’un tuple ne peuvent dépasser une page. Ainsi, pour contourner cette limite avec des données lourdes, un mécanisme spécifique appelé TOAST est utilisé.

TOAST signifie « The Oversized-Attribute Storage Technique » et se déclenche de façon automatisée et totalement transparente pour l’utilisateur lorsqu’une ligne est trop lourde pour le stockage « standard ».

Le fonctionnement est le suivant :

Jusqu’à 2 ko (par défaut, définit via la variable TOAST_TUPLE_THRESHOLD), un tuple est stocké normalement, directement dans la page.
A partir de 2 ko, le système compresse le tuple, puis deux cas de figures se présentent :
- Le poids du tuple compressé est inférieur à 2 ko (par défaut, définit via la variable TOAST_TUPLE_TARGET), alors ce dernier est stocké compressé dans la page.
- Le poids du tuple compressé est supérieur à 2 ko, alors le système transfert les données colonne par colonne dans une table annexe appelée table TOAST.
  Le transfert commence par les colonnes les plus lourdes et s’arrête lorsque les colonnes restantes peuvent être stockées dans la page (lorsqu’il reste moins de 2 ko).

Les tables TOAST sont stockées dans le schéma système pg_toast et sont nommées d’après l’OID de la table d’origine sous la forme pg_toast_oidorigine.

Il s’agit de tables standards (et donc qui peuvent être lues via un simple SELECT) à la structure suivante :

chunk_id : identifiant de bloc (permettant la correspondance avec un tuple spécifique de la table source).
chunk_seq : numéro d’ordre du bloc (une donnée importante peut être découpée en plusieurs blocs qu’il faut pouvoir ordonnancer).
chunk_data : donnée au format binaire.

Les données y sont stockées à raison d’un bloc par ligne, chaque bloc étant un morceau de 2 ko (par défaut, définit via la variable TOAST_MAX_CHUNK_SIZE) de la donnée d’origine qui a donc été découpée.

Cette division permet de répartir le poids des données et de les stocker de façon classique (dans un heap file), sans dépasser la taille limite d’une page. Voici un exemple schématisé du contenu de deux pages d’une table TOAST :

Notez que lorsque vous requêtez une table, la récupération des données TOASTées possède un certain coût car PostgreSQL doit lire la table TOAST, récupérer les morceaux de données, les assembler puis retourner la données consolidée. C’est une des raisons pour lesquelles le sélecteur * est souvent déconseillé.

Autre point, depuis PostgreSQL 14, vous pouvez choisir l’algorithme de compression via la variable default_toast_compression qui permet de choisir entre pglz (format compression par défaut) et lz4 (nouveau format jusqu’à 80% plus rapide pour un taux de compression similaire).

Quelques requêtes

Voici quelques requêtes pour récupérer des informations sur vos tables et mieux appréhender la structure des pages.

Commencez par créer une table :

CREATE TABLE public.table_test (
  id int4,
  commentaire_1 char(1955),
  commentaire_2 text
)
;

Vous pouvez maintenant récupérer quelques données grâce à la première fonction get_table_info() :

SELECT * FROM public.get_table_info('public.table_test');

Voici les résultats (tronqué, je n’ai pas mis toutes les colonnes) avec en gras les valeurs qui m’intéressent :

Paramètre	Valeur	Commentaire
schema	public
table	table_test
tablespace	pg_default
oid_table	350934
numero_heap_file	350934	Le numéro du fichier peut être différent de l’oid de la table
heap_file_segment_0_location	C:/…/PG16-data/base/246510/350934
nb_page	0	La table vient d’être créée et ne contient aucune donnée donc aucune page n’a été créée.
tuple_count	0	Aucune données.
table_length	0	La table venant d’être créée, elle ne pèse encore rien
table_length_human	0 bytes
tuple_length	0
dead_tuple_length	0
free_space_length	0
toast_oid	350937	La table TOAST est déjà créée dans le SGBD bien qu’il n’y ait pas encore eu besoin du système TOAST..
toast_table	pg_toast_350934
toast_heap_file_segment_0_location	C:/…/PG16-data/base/246510/350937
toast_nb_page	0
toast_nb_tuple	-1
toast_length	0
toast_length_human	0 bytes

Ajoutez une ligne de données puis recalculez les statistiques :

-- Insertion de la donnée
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (1, 'A', 'un commentaire'),
  (2, 'B', 'un commentaire vraiment très très très long')
;

SELECT * FROM public.get_table_info('public.table_test');

Voici les résultats (tronqués) :

Paramètre	Valeur	Commentaire
nb_page	1	Une page a été créée
tuple_count	2
table_length_human	8192 bytes
tuple_length	2113
free_space_length	6036

Il est intéressant de constater que les données pèsent 2113 octets (tuple_length) et que la page pèse 8,192 ko. En effet, les pages font toujours la même taille.

Vous pouvez voir qu’il reste 6036 octets d’espace disponible dans la page avant la création d’une seconde.

Regardez maintenant un peu plus en détail le stockage des données grâce à cette seconde fonction get_heap_page_info() qui remonte des statistiques sur le contenu des pages d’une table :

SELECT * FROM public.get_heap_page_info('public.table_test');

Voici les résultats :

Paramètre	Valeur	Commentaire
page_num	0	Numéro de page
lsn	4/1D111198	Identifiant xlog de la dernière opération effectuée sur la page.
nb_pointer	2	Nombre de pointeurs.
nb_tuple	2	Nombre de tuple.
nb_tuple_visible	2	Nombre de tuples visibles.
length_header	24	Taille des métadonnées de la page.
length_pointer	8	Taille de l’ensemble des pointeurs.
length_free_space	6036	Taille de l’espace libre.
length_tuple	2120	Taille occupée par les tuples.
length_meta	48	Taille des métadonnées des tuples.
length_data	2065	Taille des données des tuples.
length_empty	7	Taille des espaces laissés par l’alignement des tuples.
length_special	0	Taille des données spécifiques.
offset_pointer	24	Positionnement du début des pointeurs.
offset_free_space	32	Positionnement du début de l’espace disponible.
offset_data	6072	Positionnement du début des données.
offset_special	8192	Positionnement du début des données spécifique.
offset_page_end	8192	Positionnement de la fin de la page.

Voici un schéma de l’état actuel de la page, vous remarquerez qu’il n’y a pas de bloc spécifique car il s’agit d’une table classique et non pas d’une table d’index :

Bon nous voyons déjà pas mal de chose mais nous ne savons pas comment se répartissent les tuples, leurs métadonnées ainsi que les espaces vides.

Allez donc un peu plus loin (puisque vous êtes là pour ça), grâce à une troisième fonction get_heap_tuple_info() qui permet de récupérer toutes les informations disponibles sur les tuples présents dans la page.

SELECT * FROM public.get_heap_tuple_info('public.table_test');

Voici les résultats (attention, j’ai plusieurs lignes donc mon tableau est maintenant dans l’autre sens).

ctid	page_num	page_index	offset_start	offset_end	offset_next	length_tuple	length_meta	length_data	length_empty
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro de page.	Numéro d’index du tuple dans la page.	Position du début du tuple.	Position de la fin du tuple.	Position du début du tuple précédent.	Longueur du tuple.	Longueur des métadonnées du tuple	Longueur des données en elle-même	Espace vide
(0,1)	0	1	6184	8186	8192	2002	24	1978	6
(0,2)	0	2	6072	6183	6184	111	24	87	1

Voici la représentation exacte de la page (avec en jaune l’espace perdu à cause de l’alignement) :

Vous pouvez voir que le premier tuple possède un peu moins de 2000 octets de données : c’est à cause du champ commentaire_2. En effet, le type de données char(2000) limite les données à 2000 caractères maximum. Cependant, ce type de données est en fait le type bpchar(2000) comme blank padded caractère : PostgreSQL ajoute des espaces au texte jusqu’à ce qu’il atteigne 2000 caractères ce qui force la ligne à prendre plus de place que nécessaire.

Autre élément, le second tuple ne possède que 87 octets de données. C’est à cause du système TOAST qui s’est déclenché car les données pesaient plus que 2 ko, elles ont donc été compressées. Comme le résultat pèse moins de 2 ko, aucune colonne n’a été transférée dans la table TOAST.

Notez également que les pointeurs renseignent sur l’emplacement des tuples.

Ajoutez encore des données :

INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (3, 'C', 'un commentaire'),
  (4, 'D', 'un commentaire'),
  (5, 'E', 'un commentaire'),
  (6, 'F', 'un commentaire')
;

Analysez la table :

SELECT * FROM public.get_heap_tuple_info('public.table_test');

ctid	page_num	page_index	offset_start	offset_end
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro de page.	Numéro d’index du tuple dans la page.	Position du début du tuple.	Position de la fin du tuple.
(0,1)	0	1	6184	8186
(0,2)	0	2	6072	6183
(0,3)	0	3	4064	6066
(0,4)	0	4	2056	4058
(0,5)	0	5	48	2050
(1,1)	1	1	6184	8186

Une page supplémentaire a été créée, la table pèse maintenant 2 x 8 ko : 16 ko (vous pouvez le vérifier avec get_table_info()).

Gestion des données

Colonnes système

Des colonnes système sont présentes dans chacune des tables. Il suffit de les spécifier dans vos requêtes pour y accéder, les voici :

tableoid : l’OID de la table, utilisez tableoid::regclass pour obtenir le nom de la table.
ctid : emplacement physique de la ligne.
xmin : identifiant de la transaction d’insertion.
cmin : identifiant de commande au sein de la transaction d’insertion.
xmax : identifiant de la transaction de supression.
cmax : identifiant de commande au sein de la transaction de suppression.

Voici un exemple avec notre table :

SELECT 
  tableoid::regclass,
  tableoid,
  ctid,
  xmin,
  xmax,
  *
FROM 
  public.table_test
;

Voici le résultat :

tableoid	tableoid	ctid	xmin	id	commentaire_1	commentaire_2
table_test	350934	(0,1)	4432	1	A	un commentaire
table_test	350934	(0,2)	4432	2	B	un commentaire vraiment très très très long
table_test	350934	(0,3)	4435	3	C	un commentaire
table_test	350934	(0,4)	4435	4	D	un commentaire
table_test	350934	(0,4)	4435	5	E	un commentaire
table_test	350934	(1,1)	4435	6	F	un commentaire

Si besoin, n’hésitez pas à utiliser cette requête pour voir à quelles lignes correspondent les tuples que nous allons analyser.

Fonctionnement

Maintenant que nous connaissons un peu mieux la façon dont les données sont stockées physiquement, regardons comment les mouvements sont gérés.

Dans une base PostgreSQL, l’accès aux données peut se faire de façon concurrentielle (en même temps) depuis différents clients. Il faut donc gérer correctement l’ordonnancement des transactions.

Pour cela, il existe le MVCC : Multiversion Concurrency Control ou contrôle d’accès simultané, il s’agit d’un système permettant la conservation des différents états de la donnée afin de permettre à chaque transaction de travailler sur un instantané de la base.

Le fonctionnement repose sur le principe suivant : chaque transaction possède un identifiant unique et incrémentale, lorsqu’elle effectue une opération, les lignes modifiées se voient attribuer son identifiant ce qui permet de ressituer l’état des données par rapport à toute autre transaction.

Par exemple :

Horaire	Transaction 1	Transaction 2
10h00	Démarrage transaction Début UPDATE sur la table X
10h05		Démarrage transaction Début INSERT sur la table Y
10h06		Fin INSERT sur la table Y Fin transaction
10h08	Fin UPDATE sur la table X Début UPDATE sur la table Y
10h09	Fin UPDATE sur la table Y Fin transaction

Lorsque la transaction 1 effectue sa seconde opération, l’état des données doit être celui qui existait lors du démarrage de la transaction. Cependant, pour éviter de bloquer tous les utilisateurs, PostgreSQL a déjà effectué l’INSERT de la transaction 2. Ainsi, pour conserver l’isolation de chaque transaction, une version de la table telle qu’elle était lors de démarrage de la transaction 1 existe toujours après la finalisation de la transaction 2, le temps que la transaction 1 puisse se terminer.

Notez que si la transaction 2 avait effectué une opération modifiant des données de la table Y concernées par la transaction 1, PostgreSQL aurait attendu la fin de la transaction grâce à un système de verrous placés sur les lignes concernées.

Notez que selon le niveau d’isolation, il est possible pour une transaction de « voir » les lignes modifiées par d’autres transactions. Il s’agit du réglage de la variable default_transaction_isolation qui peut prendre l’une des valeurs suivantes :

READ COMMITTED : une requête n’a accès qu’aux lignes commitées avant qu’elle ne commence, c’est donc le démarrage de la requête qui fait foi.
Ainsi, les dernières requêtes d’une transaction peuvent opérer sur des données ayant été modifiées par d’autres transactions par rapport aux premières requêtes.
Il s’agit de la valeur par défaut.
REPEATABLE READ : une transaction ne peut voir que les lignes commitées avant que la première requête de la transaction ne soit exécutée.
Deux transactions concurrentes peuvent modifier des données différentes mais appartenant à la même table ce qui peut conduire à des données inconsistantes (car calculées sur une base qui n’existe plus par exemple).
En revanche, si une seconde transaction modifie les mêmes données que la première, une erreur serialization_failure est remontée.
SERIALIZABLE : une transaction ne peut voir que les lignes commitées avant que la première requête de la transaction ne soit exécutée.
Si deux transactions concurrentes modifient les mêmes données, la première transaction à commiter « gagne » et la seconde est annulée (ROLLBACK) et une erreur serialization_failure est remontée.

Mais comment cela fonctionne en détail ? Pour voir cela, nous allons devoir utiliser un exemple concret.

INSERT – UPDATE – DELETE

Pour démarrer nos test, nous allons repartir de la table précédente (disponible dans l’onglet « initialisation »).

Démarrez une transaction et affichez son identifiant :

BEGIN;

SELECT txid_current();

-- !!!
-- Attention à bien exécuter chaque requête dans une transaction spécifique
-- Ce que permet le COMMIT à la suite de chaque requête si vous lancez tout d'un seul bloc
-- !!!

-- Création d'une table de test
CREATE TABLE public.table_test (
  id int4,
  commentaire_1 char(1955),
  commentaire_2 text
)
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (1, 'A', 'un commentaire'),
  (2, 'B', 'un commentaire vraiment très très très long')
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (3, 'C', 'un commentaire'),
  (4, 'D', 'un commentaire'),
  (5, 'E', 'un commentaire'),
  (6, 'F', 'un commentaire')
;
COMMIT;

Ma transaction porte le numéro d’identifiant 4440.

Modifiez des données :

-- Mise à jour de la ligne 3
UPDATE public.table_test
SET commentaire_2 = 'Un autre commentaire'
WHERE id = 3
;

Validez la transaction :

COMMIT;

Tant qu’a faire, faites la suppression suivante suivit d’une insertion :

DELETE FROM public.table_test
WHERE id = 4
;

-- Ajout d'une ligne
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (7, 'G', 'un commentaire')
;

Analysez les données :

SELECT * FROM public.get_heap_tuple_info('public.table_test');

ctid	page_index	xmin	xmax	state	visible	update	direction	hot	delete	freeze
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro d’index du tuple dans la page.	ID de la transaction à partir de laquelle le tuple est visible.	ID de la transaction au delà de laquelle le tuple n’est plus visible.	État du tuple.	Visibilité du tuple.	Le tuple a-t-il été mis à jour	to : vers un autre tuple. from : depuis unautre tuple	HOT UPDATE	Tuple supprimé	xmin gelé
(0,1)	1	4432 – c		normal	Oui
(0,2)	2	4432 – c		normal	Oui
	3			dead	Non	Oui	to another line
(0,4)	4	4435 – c	4442 – c	normal	Non				Oui
(0,5)	5	4435 – c		normal	Oui
(1,1)	1	4435 – c		normal	Oui
(1,2)	2	4440 – c		normal	Oui	Oui	from another line
(1,3)	3	4453 – c		normal	Oui

Vous découvrez les colonnes xmin et xmax qui indiquent « à partir de » et « jusqu’à » quelle transaction un tuple est visible. J’ai ajouté un petit drapeau à côté de chaque valeur :

c : la transaction a été validée (COMMIT).
a : la transaction a été annulée (ROLLBACK).

Vous noterez donc que PostgreSQL garde en mémoire les données qui ont été modifiées lors des transactions abandonnées.

Vous pouvez voir plusieurs éléments :

Les lignes visibles ne possèdent pas de xmax (en vrai il est égal à 0).
La ligne id = 3 a été mise à jour.
Son ancien emplacement avec les données d’origine est toujours présent et correspond à la troisième ligne. Elle est identifiée comme « morte » et devra être nettoyée.
La nouvelle version est présente à la dernière ligne (ctid = (1,2)).
PostgreSQL a réalisé un INSERT.
La ligne ctid = (0,4) correspond à la ligne id = 4 que nous avons supprimée.
Ici aussi la donnée n’est pas supprimée mais simplement « tagguée » comme non visible.
La ligne ctid = (1,3) correspond à la ligne que nous venons d’ajouter : elle est située en dernière position.

Un UPDATE est en réalité la même opération qu’un DELETE suivi d’un INSERT, d’ailleurs son coût est assez similaire.

Ce fonctionnement nous montre également pourquoi les lignes peuvent paraitre désordonnées lors d’un SELECT. En effet, PostgreSQL remonte les lignes dans l’ordre dans lequel elles sont stockées. Dans notre cas, un simple SELECT sur la table nous aurait donné :

initialement 1, 2, 3, 4, 5, 6.
Après l’UPDATE 1, 2, 4, 5, 6, 3.
Après le DELETE 1, 2, 5, 6, 3.

HOT UPDATE

Le Heap Only Tuple UPDATE (ou simplement HOT UPDATE) un processus particulier de PostgreSQL qui se produit lorsqu’il reste suffisamment de place dans une page pour que l’ancienne et la nouvelle version d’un tuple y soient stockées.

Lancez maintenant la requête suivante :

-- Mise à jour de la ligne 3 (oui encore)
UPDATE public.table_test
SET commentaire_2 = 'Un commentaire'
WHERE id = 3
;

Analysez les données de la deuxième page :

SELECT * FROM public.get_heap_tuple_info('public.table_test', 1);

ctid	page_index	offset_start	xmin	xmax	state	visible	update	direction	hot	delete	freeze
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro d’index du tuple dans la page.	Position du début du tuple.	ID de la transaction à partir de laquelle le tuple est visible.	ID de la transaction au delà de laquelle le tuple n’est plus visible.	État du tuple.	Visibilité du tuple.	Le tuple a-t-il été mis à jour	to : vers un autre tuple. from : depuis unautre tuple	HOT UPDATE	Tuple supprimé	xmin gelé
(1,1)	1	6184	4435 – c		normal	Oui
(1,4)	2	4176	4440 – c	4454 – c	normal	Non	Oui	from and to another line	Oui
(1,3)	3	2168	4453 – c		normal	Oui
(1,4)	4	160	4454 – c		normal	Oui	Oui	from another line	Oui

Cette fois-ci vous pouvez voir :

L’INSERT est fait en fin de données (ctid = (1,2)).
L’UPDATE de la ligne id = 3 est ici un HOT UPDATE.

Voici le processus en image :

Voici la table dans son état initial

Première étape du HOT UPDATE

La nouvelle version du tuple est insérée en dernière position avec le ctid = (1,4) et porte de tag « HOT UPDATE ».

L’ancienne version est « désactivée » : son ctid devient (1,4) et les tags « HOT UPDATE » et « HEAP ONLY TUPLE » sont ajoutés. Ceci permet à PostgreSQL de comprendre, lors de la lecture du tuple 2, qu’il doit aller lire le tuple 4.

Le HOT UPDATE n’est en réalité pas terminé, une seconde opération va être menée par la base.

Faites la requête SELECT suivante puis réanalysez la table :

-- SELECT
SELECT * FROM public.table_test;

-- Analyse
SELECT * FROM public.get_heap_tuple_info('public.table_test', 1);

Voici les données de la 2ème page :

ctid	page_index	offset_start	xmin	xmax	state	visible	update	direction	hot	delete	freeze
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro d’index du tuple dans la page.	Position du début du tuple.	ID de la transaction à partir de laquelle le tuple est visible.	ID de la transaction au delà de laquelle le tuple n’est plus visible.	État du tuple.	Visibilité du tuple.	Le tuple a-t-il été mis à jour	to : vers un autre tuple. from : depuis unautre tuple	HOT UPDATE	Tuple supprimé	xmin gelé
(1,1)	1	6184	4435 – c		normal	Oui
	2	4			redirect to 4	Non	Oui	from and to another line
(1,3)	3	4176	4453 – c		normal	Oui
(1,4)	4	2168	4454 – c		normal	Oui	Oui	from another line	Oui

Le tuple 2, qui contient l’ancienne version de la ligne id = 3, a été « nettoyée » et sont pointeur redirige vers le pointeur 4. L’offset des tuples a été modifié à cause d’une défragmentation de la page : c’est la seconde opération du HOT UPDATE.

Voici le processus imagé :

Pruning : mise à jour du pointeur ce qui tue le tuple 2

Lors de la première requête qui suit l’UPDATE et qui lit les données de la table (un SELECT par exemple), PostgreSQL opère un processus de pruning :
il met à jour le pointeur du tuple 2 et remplace la valeur de position du tuple 2 par celle du pointeur de la nouvelle version (ici le pointeur 4).

Ainsi, lors d’une lecture de la donnée, c’est toujours le pointeur 2 qui sera lu en premier mais PostgreSQL est directement redirigé vers le nouveau pointeur (le 4) qui l’envoi ainsi vers le nouveau tuple (le 4 donc). Ceci permet de ne pas avoir à modifier les index dans ce type de situation.

Notez que dans le cas d’une lecture séquentiel, cette opération (de redirection) est similaire en temps et en coût que de lire directement le pointeur 4.

PostgreSQL effectue ensuite une défragmentation de la page en supprimant physiquement le tuple 2 (ce qui entraine un « décalage » des tuples pour combler l’espace vide). Cette défragmentation ne concerne pas les pointeurs, le 2^ème reste donc présent, référençant toujours le pointeur 4 (toujours pour ne pas avoir à toucher aux index).

Le processus de lecture est donc identique à l’étape précédente mais de l’espace est rendu disponible.

Notez que si un second HOT UPDATE est réalisé sur le même tuple et ceci juste après le premier HOT UPDATE, la nouvelle version est insérée dans l’espace qui aurait du être nettoyé par la défragmentation, le pointeur qui redirigeait vers un autre pointeur (pt_1-2 dans notre exemple) est alors rétabli. La défragmentation nettoiera alors le tuple intermédiaire ainsi que son pointeur (pt_1-4, tuple 4).

Le HOT UPDATE est ainsi plus rapide à executer qu’un UPDATE classique et permet de libérer de l’espace en évitant de conserver l’ancien tuple.

Vous savez maintenant plus précisément comment fonctionne le stockage physique. Vous voyez ainsi que lorsque beaucoup d’opérations sont faites, des lignes mortes ainsi que des pointeurs supplémentaires sont présents dans vos tables ce qui entraine une grande perte d’espace.

Heureusement, un outil spécifique existe pour nettoyer tout cela : le VACUUM.

VACUUM

Passons l’aspirateur ! Ce n’est pas pour rien que le mot VACUUM a été choisi pour cette opération…

Le VACUUM consiste en le nettoyage d’une table afin d’en retirer tous les éléments qui ne sont plus utiles (lignes mortes) ainsi que de réarranger les éléments pour gagner de la place.

Deux types de VACUUM existent :

Le Plain VACUUM (standard) : nettoyage rapide et défragmentation partielle de la table.
Le VACUUM FULL (complet) : nettoyage et défragmentation complets de la table.

L’opération de VACUUM est lancée automatiquement par PostgreSQL via l’autovacuum.

VACUUM standard

Le VACUUM standard (ou VACUUM concurrentiel ou Plain VACUUM) peut être exécuter sans verrouiller la table sur laquelle il porte.

Il opère les actions suivantes :

Analyse des pages pour identifier les tuples morts (issus d’un UPDATE, d’un DELETE ou d’une transaction abandonnée).
FREEZE des tuples si nécessaire (voir le chapitre VACUUM FREEZE)
Suppression des entrées des index correspondant à des tuples morts.
Pour chaque page :
- Suppression des tuples morts
- Défragmentation de la page pour récupérer l’espace des tuples morts.
- Mise à jour de la free space map et de la visibility map.
Suppression de la dernière page si possible (si aucun tuple vivant dedans).
Mise à jour des statistiques de la table dans les tables système.

L’étape 1 commence par un listing des tuples mort, cette lsite est créée en mémoire. C’est pour cette étape que la variable de configuration maintenance_work_mem est importante. En effet, si sa valeur est trop faible, PostgreSQL devra itérer plusieurs fois sur la table jusqu’à ce qu’il ne trouve plus de tuple mort.

L’étape 2 est un FREEZE des valeurs xmin des tuples. Cette opération est détaillée dans le chapitre VACUUM FREEZE.

L’étape 4 se déroule de la façon suivante :

État initial : un tuple est considéré comme mort

Les données sont effacées et le pointeur du tuple est réinitialisé

La défragmentation réorganise les données mais conserve le pointeur 2

Voici un exemple, repartez de notre table d’exemple utilisée précédemment (disponible dans l’onglet « initialisation ») puis supprimez quelques lignes et analysezla :

DELETE FROM public.table_test
WHERE id IN (6, 3)
;

SELECT * FROM public.get_heap_tuple_info('public.table_test');

-- !!!
-- Attention à bien exécuter chaque requête dans une transaction spécifique
-- Ce que permet le COMMIT à la suite de chaque requête si vous lancez tout d'un seul bloc
-- !!!

-- Création de la table
CREATE TABLE public.table_test (
  id int4,
  commentaire_1 char(1955),
  commentaire_2 text
)
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (1, 'A', 'un commentaire'),
  (2, 'B', 'un commentaire vraiment très très très long')
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (3, 'C', 'un commentaire'),
  (4, 'D', 'un commentaire'),
  (5, 'E', 'un commentaire'),
  (6, 'F', 'un commentaire')
;
COMMIT;

-- Mise à jour de la ligne 2
UPDATE public.table_test
SET commentaire_2 = 'Un autre commentaire'
WHERE id = 3
;
COMMIT;

-- Suppression de la ligne 4
DELETE FROM public.table_test
WHERE id = 4
;
COMMIT;

-- Ajout d'une ligne
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (7, 'G', 'un commentaire')
;
COMMIT;

-- Mise à jour de la ligne 3 (oui encore)
UPDATE public.table_test
SET commentaire_2 = 'Un commentaire'
WHERE id = 3
;
COMMIT;

-- Sélection des données pour finalisation HOT UPDATE
SELECT * FROM public.table_test
;
COMMIT;

Vous devriez avoir la structure suivante :

ctid	page_num	page_index	offset_start	xmin	xmax	state	visible	update	delete	freeze
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro de page.	Numéro d’index du tuple dans la page.	Position du début du tuple.	ID de la transaction à partir de laquelle le tuple est visible.	ID de la transaction au delà de laquelle le tuple n’est plus visible.	État du tuple.	Visibilité du tuple.	Le tuple a-t-il été mis à jour	Tuple supprimé	xmin gelé
(0,1)	0	1	6184	4527 – c		normal	Oui
(0,2)	0	2	6072	4527 – c		normal	Oui
	0	3	0			dead	Non	Oui
(0,4)	0	4	4064	4528 – c	4530 – c	normal	Non		Oui
(0,5)	0	5	2056	4528 – c		normal	Oui
(1,1)	1	1	6184	4528 – c	4533 – c	normal	Non		Oui
	1	2	4			redirect to 4	Non	Oui
(1,3)	1	3	4176	4531 – c		normal	Oui
(1,4)	1	4	2168	4532 – c	4533 – c	normal	Non	Oui	Oui

Notez la différence entre les tuples non visibles qui ont été mis à jour et les tuples non visibles qui ont été supprimés :

Les premiers ont déjà été nettoyés des tables, leurs pointeurs sont toujours présents mais ne pointent vers rien ou redirigent vers un autre pointeur.
Les seconds en revanche sont toujours présents mais simplement non visibles.

Regardez également le résultat de la requête suivante :

SELECT * FROM public.get_heap_page_info('public.table_test');

Voici les résultats :

page_num	nb_pointer	nb_tuple	nb_tuple_visible	length_pointer	length_free_space	length_tuple
Numéro de page	Nombre de pointeur.	Nombre de tuple.	Nombre de tuples visibles.	Taille de l’ensemble des pointeurs.	Taille de l’espace libre.	Taille occupée par les tuples.
0	5	4	3	20	2012	6136
1	4	3	1	16	2128	6024

Lancez un VACUUM puis analysez les données :

VACUUM public.table_test;

SELECT * FROM public.get_heap_tuple_info('public.table_test');

Voici les résultats :

ctid	page_num	page_index	offset_start	xmin	xmax	state	visible	update	delete	freeze
Identifiant de la ligne : numéro de page, numéro d’index dans la page	Numéro de page.	Numéro d’index du tuple dans la page.	Position du début du tuple.	ID de la transaction à partir de laquelle le tuple est visible.	ID de la transaction au delà de laquelle le tuple n’est plus visible.	État du tuple.	Visibilité du tuple.	Le tuple a-t-il été mis à jour	Tuple supprimé	xmin gelé
(0,1)	0	1	6184	4527 – f		normal	Oui			Oui
(0,2)	0	2	6072	4527 – f		normal	Oui			Oui
	0	3	0			unused	Non	Oui	Oui
	0	4	0			unused	Non		Oui
(0,5)	0	5	4064	4528 – f		normal	Oui			Oui
	1	1	0			unused	Non	Oui	Oui
	1	2	0			unused	Non		Oui
(1,3)	1	3	6184	4531 – f		normal	Oui	Oui		Oui

Vous pouvez voir que les tuples vivants ont été réorganisés (modification des offsets), quand aux morts, ils ont été supprimés mais leurs pointeurs sont toujours présents dans un état « inutilisé ».

Regardez les statistiques au niveau de chaque page :

SELECT * FROM public.get_heap_page_info('public.table_test');

Voici les résultats :

page_num	nb_pointeur	nb_tuple	nb_tuple_visible	length_pointer	length_free_space	length_tuple
Numéro de page	Nombre de pointeur.	Nombre de tuple.	Nombre de tuples visibles.	Taille de l’ensemble des pointeurs.	Taille de l’espace libre.	Taille occupée par les tuples.
0	5	3	3	20	4020	4128
1	3	1	1	12	6148	2008

Vous pouvez voir immédiatement que l’espace précédemment occupé par les tuples morts a pu être récupéré. Les prochaines insertions se feront alors d’abord dans la première page puis dans la seconde avant de créer une éventuelle troisième page.

En revanche, vous pouvez voir que les pointeurs n’ont pas disparus.

Notez également que les données présentes dans la page 2 pourraient tout à fait être transférées dans la page 1 maintenant qu’il y a suffisamment de place., c’est le VACUUM FULL qui permet de faire cela.

VACUUM FULL

Le VACUUM FULL permet d’aller au delà du VACUUM standard. En effet, ce dernier ne permet pas de réduire la taille des tables.

Le VACUUM FULL réécrit complètement le heap file afin de réorganiser les tuples et d’éliminer les pages en trop. L’avantage est un gain de place qui peut être conséquent.

L’inconvénient est que pendant la réécriture, la table n’est plus accessible. Un autre inconvénient est que deux versions de la table seront présentes sur le disque lors de la phase de réécriture ce qui peut entrainer des problèmes d’espace disque si les données sont très volumineuses.

Reprenez notre exemple (disponible dans l’onglet « initialisation ») et analysez la table avec la fonction get_heap_page_info() :

-- Quelques statistiques
SELECT * FROM public.get_heap_page_info('public.table_test');

-- !!!
-- Attention à bien exécuter chaque requête dans une transaction spécifique
-- Ce que permet le COMMIT à la suite de chaque requête si vous lancez tout d'un seul bloc
-- !!!

-- Création de la table
CREATE TABLE public.table_test (
  id int4,
  commentaire_1 char(1955),
  commentaire_2 text
)
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (1, 'A', 'un commentaire'),
  (2, 'B', 'un commentaire vraiment très très très long')
;
COMMIT;

-- Insertion de données
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (3, 'C', 'un commentaire'),
  (4, 'D', 'un commentaire'),
  (5, 'E', 'un commentaire'),
  (6, 'F', 'un commentaire')
;
COMMIT;

-- Mise à jour de la ligne 2
UPDATE public.table_test
SET commentaire_2 = 'Un autre commentaire'
WHERE id = 3
;
COMMIT;

-- Suppression de la ligne 4
DELETE FROM public.table_test
WHERE id = 4
;
COMMIT;

-- Ajout d'une ligne
INSERT INTO 
  public.table_test (id, commentaire_1, commentaire_2)
VALUES 
  (7, 'G', 'un commentaire')
;
COMMIT;

-- Mise à jour de la ligne 3 (oui encore)
UPDATE public.table_test
SET commentaire_2 = 'Un commentaire'
WHERE id = 3
;
COMMIT;

-- Sélection des données pour finalisation HOT UPDATE
SELECT * FROM public.table_test
;
COMMIT;

-- VACUUM Standard
VACUUM public.table_test
;
COMMIT;

Voici les résultats :

page_num	nb_pointeur	nb_tuple	length_free_space	unused_line_pointer
Numéro de page	Nombre de pointeur.	Nombre de tuple.	Taille de l’espace libre.	Présence de pointeurs non utilisés.
0	5	3	4020	Oui
1	3	1	6148	Oui

Maintenant, réalisez un VACUUM FULL :

-- VACUUM FULL
VACUUM FULL public.table_test
;

Réanalysons la table :

SELECT * FROM public.get_heap_page_info('public.table_test');

Voici le retour :

page_num	nb_pointeur	nb_tuple	length_free_space	unused_line_pointer
Numéro de page	Nombre de pointeur.	Nombre de tuple.	Taille de l’espace libre.	Présence de pointeurs non utilisés.
0	4	4	2016	Non

Les tuples ont été réorganisés sur la première page du nouveau fichier. D’ailleurs dans l’exemple, mon filenode était 350934 (l’OID de la table). Grâce à la fonction get_table_info() je vois maintenant qu’il est égal à 351418 : le fichier heap a bien été réécrit.

Notez que le VACUUM FULL réécrit également les index, la visibility map ainsi que la free space map.

FREEZE des données

Le VACUUM possède une option : FREEZE qui permet de geler les données. Mais à quoi ça sert ?

ID de transaction

Comme vous l’avez vu, le système de contrôle d’accès simultané (MVCC) utilise l’identifiant des transactions pour déterminer la visibilité des tuples. Cet identifiant est nommé txid et est assigné par le gestionnaire de transaction. Il s’agit d’un nombre entier non signé de 32 bit (soit 2³² = 4 294 967 295 valeurs différentes).

Ainsi, lorsqu’une transaction est en cours, tous les éléments portant un txid inférieur à celui de la transaction sont considérés comme dans le passé et donc modifiables par la transaction. Tous ceux qui ont un txid supérieur, sont dans le futur et donc non modifiables par la transaction (cela dépend du mode de transaction utilisé mais ne compliquons pas les choses).

Notez qu’une seule transaction peut contenir plusieurs instructions SQL, chaque instruction se voit alors assigner un identifiant de commande (cid), nombre entier non signé de 32 bits également ; permettant de comparer les résultats des instructions les uns par rapport aux autres, exactement comme avec les txid, au sein de la transaction. Ceci contraint d’ailleurs le système à ne pouvoir exécuter « que » 4,2 milliard d’instructions SQL par transaction (on est large…)

Les txid suivants sont spéciaux :

0 : txid invalide.
1 : txid d’initialisation du cluster.
2 : avant PG 9.4, il s’agit de la valeur xmin gelée, maintenant c’est un drapeau qui est ajouté au tuple.

L’assignement du txid se produit lorsque la transaction commence à écrire et n’indique donc pas l’ordre de démarrage des transactions. Ceci implique qu’une transaction peut « doubler » une autre.

Par exemple :

Horaire	Transaction 1	Transaction 2
10h00	Démarrage transaction Début `SELECT` sur la table X
10h05		Démarrage transaction Début `INSERT` sur la table Y => Assignation d’un txid : 1000
10h06		Fin `INSERT` sur la table Y Fin transaction
10h08	Fin `SELECT` sur la table X Début `UPDATE` sur la table Y => Assignation d’un txid : 1001
10h09	Fin `UPDATE` sur la table Y Fin transaction

Les actions de la transaction 1 sont considérées comme ayant été réalisées après la transaction 2 bien que cette dernière ait démarrée après

Bouclage ou wraparound

Le problème, c’est que 4,2 milliards de valeurs ce n’est pas forcément assez dans la vie d’une base de données. Lorsqu’il y a plusieurs milliers de transactions par minutes, le compteur peut être dépassé en moins de 8 ans (ok on est large mais une base avec plusieurs millions de transactions par jour cela peut arriver).

Pour résoudre ceci, PostgreSQL utilise le bouclage (wraparround) : lorsque les 4,2 milliards de valeurs sont atteintes, le compteur redémarre à 0. Le problème c’est qu’au moment du bouclage, la transaction portant le txid = 4,1 milliards qui était dans le passé se retrouve alors dans le futur (le txid courant étant maintenant inférieur). Pour éviter ce phénomène, PostgreSQL considère que les 2,1 milliards de transactions précédentes sont dans le passé et les 2,1 milliards suivantes dans le futur en tenant compte de ce bouclage.

Par exemple, ma transaction porte le txid = 1000, la « boucle du temps » serait la suivante :

txid	Temporalité
1	<- Passé
999	<- Passé
1000	Présent
1001	Futur ->
2³¹ + 999 2 147 484 647	Futur ->
2³¹ + 1000 2 147 484 648	<- Passé
2³² 4 294 967 295	<- Passé

Voici une requête pour connaitre les limites de votre passé et de votre futur :

SELECT 
  txid_current() AS "txid_current",
  CASE 
    WHEN txid_current() < 2^31 THEN txid_current() + 2^31 - 1
    ELSE txid_current() + 2^31 - 2^32 - 1
  END AS "limite_futur",
  CASE 
    WHEN txid_current() > 2^31 THEN txid_current() - 2^31
    ELSE txid_current() - 2^31 + 2^32
  END AS "limite_passé"

Gel des données : Freeze

Le problème qui peut survenir c’est que lorsqu’il y a eu plus de 2,1 (exactement 2²¹) milliards de transactions, PostgreSQL va considérer d’anciennes transactions comme étant dans le futur. Pas très pratique…

C’est pour résoudre ce problème que le FREEZE a été inventé. Il permet d’ajouter un drapeau sur un tuple indiquant que son xmin est « gelé », c’est à dire que peu importe la valeur du xmin, il doit être considéré comme inférieur au txid actuel, même si ce n’est pas le cas.

Pour cela, le VACUUM se charge de geler les lignes adéquates selon les cas suivants :

VACUUM standard : tous les tuples dont le xmin est inférieur de la valeur à : oldest_current_txid – vacuum_freeze_min_age
Le FREEZE n’est opéré que sur les pages pour lesquelles le VACUUM opère : les pages contenant des tuples morts. On parle alors de lazy FREEZE (FREEZE paresseux).
- La valeur de vacuum_freeze_min_age est définie dans le fichier postgresql.com et est fixée par défaut fixée à 50 000 000.
- oldest_current_txid correspond au plus vieux txid des transaction en cours.
VACUUM FREEZE : tous les tuples dont le xmin est inférieur à oldest_current_txid.
Le FREEZE est opéré sur toutes les pages de la table. On parle de FREEZE aggressif.
VACUUM FULL : équivalent du VACUUM FREEZE, les opérations de nettoyage sont différentes mais les opérations de FREEZE intégrée sont équivalentes.

Si l’age de la table (age du plus vieux tuple) dépasse la valeur de la variable vacuum_freeze_table_age (par défaut 150 000 000) alors le prochain VACUUM lancé s’accompagnera d’un FREEZE aggressif.

Attention car le gel des tuples d’une page implique de réécrire entièrement cette dernière et a donc un coût important. Si le VACUUM prend trop de temps, il est possible de le stopper, le travail déjà accompli en sera pas perdu (notamment sur le gel des données).

A la fin du VACUUM, des statistiques sont calculées et les valeurs suivantes renseignées :

pg_catalog.pg_class.relfrozenxid : txid le plus récent ayant été gelé dans la table.
Il s’agit de la valeur ayant été utilisée pour geler les tuples (txid ou txid – vacuum_freeze_min_age), pas forcément du txid gelé le plus récent ; ce dernier est en effet plus ancien ou égal à cette valeur.
pg_catalog.pg_database.datfrozenxid : txid le plus récent ayant été gelé dans toute la base.
Il s’agit du pg_catalog.pg_class.relfrozenxid le plus ancien.

Dans le cas de notre exemple, la colonne max_frozen_txid de la fonction get_table_info() vaut pg_catalog.pg_class.relfrozenxid.

AUTOVACUUM

Vacuuming is like exercising. If it hurts, you’re not doing it enough!
Robert Haas, PGConf.EU 2023, Prague, 13 décembre 2023

Un VACUUM cela peut être très rapide mais cela peut aussi être très lourd et c’est pour cela qu’il faut le lancer régulièrement. C’est justement le rôle de l’autovacuum.

Toutes les minutes (interval paramétrable via le paramètre autovacuum_naptime), l’autovacuum se lance, analyse les données de la vue pg_stat_all_tables afin de déterminer s’il doit lancer un VACUUM et ou un ANALYSE sur les différentes tables de la base.

Plusieurs tables peuvent être nettoyées en même temps : un processus de VACUUM sur une table utilise un worker et par défaut jusqu’à trois peuvent être lancés (variable autovacuum_max_workers).

Le déclenchement de l’autovacuum sur une table se fait si au moins l’une des conditions suivantes est remplie :

Le txid courant est plus récent que :
pg_catalog.pg_class.relfrozenxid + autovacuum_freeze_max_age
Le tuple le plus vieux de la table est plus ancien que la valeur de la variable autovacuum_freeze_max_age (par défaut 200 000 000).
Le nombre de tuples morts est plus important que :
autovacuum_vacuum_threshold + ( autovacuum_vacuum_scale_factor × nombre_tuples_relation )
Avec les valeurs par défaut cela correspond à 250 tuples morts pour 1000 tuples dans la table ou 2050 pour 10 000.
Le nombre de tuples insérés est plus important que :
autovacuum_vacuum_insert_threshold + ( autovacuum_vacuum_insert_scale_factor × nombre_tuples_relation )
Avec les valeurs par défaut cela correspond à 3000 tuples insérés pour 10 000 tuples dans la table.

La valeur par défaut de autovacuum_vacuum_scale_factor est trop importante pour les grosses tables. Par exemple, une table qui contient 1 000 000 d’enregistrements ne se verra nettoyée qu’après 200 050 mouvements ce qui est très important. D’autant plus que le VACUUM qui sera déclenché risque d’être très lourds en raison de la taille importante de la table.

Les paramètres suivants sont intéressants :

maintenance_work_mem / autovacuum_work_mem : quantité de mémoire allouée pour les processus de maintenance. N’hésitez pas à le pousser jusqu’à 1 Go (au delà, le VACUUM n’en a pas l’utilité).
autovacuum_vacuum_scale_factor : pourcentage de tuples modifiés au delà duquel l’autovacuum se lance. Il est intéressant de le définir spécifiquement pour les tables de grande taille.
Par exemple pour le passer à 5% : ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.05);
autovacuum_vacuum_insert_scale_factor : pourcentage de tuples insérés au delà duquel l’autovacuum se lance. Il est intéressant de le définir spécifiquement pour les tables de grande taille.
Par exemple pour le passer à 5% : ALTER TABLE table_name SET (autovacuum_vacuum_insert_scale_factor = 0.05);
autovacuum_max_workers : nombre de workers pouvant être lancés simultanément durant le processus d’autovacuum.

Voici les statistiques que nous avons actuellement pour la table de notre exemple :

SELECT * FROM public.get_table_info('public.table_test');

Paramètre	Valeur
schema	public
table	table_test
nb_page	1
tuple_count	4
dead_tuple_count	0
max_frozen_txid	4644
n_tup_ins	7
n_tup_upd	2
n_tup_del	3
n_mod_since_analyze	0
n_ins_since_vacuum	0
last_vacuum	2024-01-01 01:01:01.000 +0100
last_autovacuum	0
last_analyze	2024-01-01 01:01:01.000 +0100
last_autoanalyze	0

Pour en savoir plus

Voila un très gros résumé de comment PostgreSQL gère les données physiquement. Vous pouvez pousser le sujet en consultant les ressources suivantes.

Sur le stockage physique :

Sur le système de contrôle concurrentiel :

Sur les TOAST :

Sur le VACUUM :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Partagez Tweetez Partagez

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDD PostgreSQL ANALYZE Heap MVCC page TOAST Transaction tuple VACUUM

L'auteur

Arthur Bazin

🗺 Expert SIG (PostgreSQL/PostGIS - GDAL/OGR - GEO)
🏆 Certifié FME Professionnal
👨‍💻 Programmeur (PL/PGSQL - Python - PHP - JS)

💼 arx iT

Contactez-moi LinkedIn

Un thé ?

Vous appréciez ces articles ? Soutenez-mon travail en cliquant ici.

A propos

Ce blog est une compilation de tips (astuces) et de tuto (tutoriels) sur l'informatique rédigés au fil du temps lors de l'approfondissement de mes connaissances en informatique.

A l'origine, l'idée était de pouvoir centraliser des informations sur les sujets en lien avec mes projets pro. et perso. pour les retrouver plus facilement. Maintenant, le but est de partager cette connaissance avec le plus grand nombre pour que chacun puisse en bénéficier.

Profitez-en !

Made with in by Arthur Bazin | | Mentions légales

50%

1 café	Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés	Génial, ça couvre mes frais de serveur mensuels.
10 cafés	Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.

Stockage physique, gestion des données et VACUUM

Fonctions personnalisées

Stockage physique

Tablespace

Fichiers

Page

Tuple

Toast

Quelques requêtes

Gestion des données

Colonnes système

Fonctionnement

INSERT – UPDATE – DELETE

HOT UPDATE

VACUUM

VACUUM standard

VACUUM FULL

FREEZE des données

ID de transaction

Bouclage ou wraparound

Gel des données : Freeze

AUTOVACUUM

Pour en savoir plus

Cet article vous a plu ?

#ezw_tco-2 .ez-toc-title{ font-size: 120%; ; ; } #ezw_tco-2 .ez-toc-widget-container ul.ez-toc-list li a{ ; ; ; } #ezw_tco-2 .ez-toc-widget-container ul.ez-toc-list li.active{ background-color: #ededed; } Table des matières

L'auteur

Un thé ?

A propos

Table des matières