Vous appréciez mon travail ?
Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

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.
BazinGa's - Tips & tuto IT

Stockage physique, gestion des données et 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 pageOnly visibleOnly frozen
011
101
200

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ètreValeurCommentaire
schemapublic
tabletable_test
tablespacepg_default
oid_table350934
numero_heap_file350934Le numéro du fichier peut être différent de l’oid de la table
heap_file_segment_0_locationC:/…/PG16-data/base/246510/350934
nb_page0La table vient d’être créée et ne contient aucune donnée donc aucune page n’a été créée.
tuple_count0Aucune données.
table_length0La table venant d’être créée, elle ne pèse encore rien
table_length_human0 bytes
tuple_length0
dead_tuple_length0
free_space_length0
toast_oid350937La 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_tablepg_toast_350934
toast_heap_file_segment_0_locationC:/…/PG16-data/base/246510/350937
toast_nb_page0
toast_nb_tuple-1
toast_length0
toast_length_human0 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ètreValeurCommentaire
nb_page1Une page a été créée
tuple_count2
table_length_human8192 bytes
tuple_length2113
free_space_length6036

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ètreValeurCommentaire
page_num0Numéro de page
lsn4/1D111198Identifiant xlog de la dernière opération effectuée sur la page.
nb_pointer2Nombre de pointeurs.
nb_tuple2Nombre de tuple.
nb_tuple_visible2Nombre de tuples visibles.
length_header24Taille des métadonnées de la page.
length_pointer8Taille de l’ensemble des pointeurs.
length_free_space6036Taille de l’espace libre.
length_tuple2120Taille occupée par les tuples.
length_meta48Taille des métadonnées des tuples.
length_data2065Taille des données des tuples.
length_empty7Taille des espaces laissés par l’alignement des tuples.
length_special0Taille des données spécifiques.
offset_pointer24Positionnement du début des pointeurs.
offset_free_space32Positionnement du début de l’espace disponible.
offset_data6072Positionnement du début des données.
offset_special8192Positionnement du début des données spécifique.
offset_page_end8192Positionnement 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).

ctidpage_numpage_indexoffset_startoffset_endoffset_nextlength_tuplelength_metalength_datalength_empty
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 tupleLongueur des données en elle-mêmeEspace vide
(0,1)0161848186819220022419786
(0,2)0260726183618411124871

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');
ctidpage_numpage_indexoffset_startoffset_end
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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)0161848186
(0,2)0260726183
(0,3)0340646066
(0,4)0420564058
(0,5)05482050
(1,1)1161848186

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 :

tableoidtableoidctidxminxmaxidcommentaire_1commentaire_2
table_test350934(0,1)443201Aun commentaire
table_test350934(0,2)443202Bun commentaire vraiment très très très long
table_test350934(0,3)443503Cun commentaire
table_test350934(0,4)443504Dun commentaire
table_test350934(0,4)443505Eun commentaire
table_test350934(1,1)443506Fun 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 :

HoraireTransaction 1Transaction 2
10h00Démarrage transaction
Début UPDATE sur la table X
10h05Démarrage transaction
Début INSERT sur la table Y
10h06Fin INSERT sur la table Y
Fin transaction
10h08Fin UPDATE sur la table X
Début UPDATE sur la table Y
10h09Fin 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');
ctidpage_indexxminxmaxstatevisibleupdatedirectionhotdeletefreeze
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 à jourto : vers un autre tuple.
from : depuis unautre tuple
HOT UPDATETuple suppriméxmin gelé
(0,1)14432 – cnormalOui
(0,2)24432 – cnormalOui
3deadNonOuito another line
(0,4)44435 – c4442 – cnormalNonOui
(0,5)54435 – cnormalOui
(1,1)14435 – cnormalOui
(1,2)24440 – cnormalOuiOuifrom another line
(1,3)
34453 – cnormalOui

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);
ctidpage_indexoffset_startxminxmaxstatevisibleupdatedirectionhotdeletefreeze
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 à jourto : vers un autre tuple.
from : depuis unautre tuple
HOT UPDATETuple suppriméxmin gelé
(1,1)161844435 – cnormalOui
(1,4)241764440 – c4454 – cnormalNonOuifrom and to another lineOui
(1,3)321684453 – cnormalOui
(1,4)41604454 – cnormalOuiOuifrom another lineOui

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 :

ctidpage_indexoffset_startxminxmaxstatevisibleupdatedirectionhotdeletefreeze
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 à jourto : vers un autre tuple.
from : depuis unautre tuple
HOT UPDATETuple suppriméxmin gelé
(1,1)161844435 – cnormalOui
24redirect to 4NonOuifrom and to another line
(1,3)341764453 – cnormalOui
(1,4)421684454 – cnormalOuiOuifrom another lineOui

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 (pt1-2 dans notre exemple) est alors rétabli. La défragmentation nettoiera alors le tuple intermédiaire ainsi que son pointeur (pt1-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 :

  1. Analyse des pages pour identifier les tuples morts (issus d’un UPDATE, d’un DELETE ou d’une transaction abandonnée).
  2. FREEZE des tuples si nécessaire (voir le chapitre VACUUM FREEZE)
  3. Suppression des entrées des index correspondant à des tuples morts.
  4. 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.
  5. Suppression de la dernière page si possible (si aucun tuple vivant dedans).
  6. 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 :

ctidpage_numpage_indexoffset_startxminxmaxstatevisibleupdatedeletefreeze
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 à jourTuple suppriméxmin gelé
(0,1)0161844527 – cnormalOui
(0,2)0260724527 – cnormalOui
030deadNonOui
(0,4)0440644528 – c4530 – cnormalNonOui
(0,5)0520564528 – cnormalOui
(1,1)1161844528 – c4533 – cnormalNonOui
124redirect to 4NonOui
(1,3)1341764531 – cnormalOui
(1,4)1421684532 – c4533 – cnormalNonOuiOui

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_numnb_pointernb_tuplenb_tuple_visiblelength_pointerlength_free_spacelength_tuple
Numéro de pageNombre 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.
05432020126136
14311621286024

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 :

ctidpage_numpage_indexoffset_startxminxmaxstatevisibleupdatedeletefreeze
Identifiant de la ligne : numéro de page, numéro d’index dans la pageNumé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 à jourTuple suppriméxmin gelé
(0,1)0161844527 – fnormalOuiOui
(0,2)0260724527 – fnormalOuiOui
030unusedNonOuiOui
040unusedNonOui
(0,5)0540644528 – fnormalOuiOui
110unusedNonOuiOui
120unusedNonOui
(1,3)1361844531 – fnormalOuiOuiOui

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_numnb_pointeurnb_tuplenb_tuple_visiblelength_pointerlength_free_spacelength_tuple
Numéro de pageNombre 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.
05332040204128
13111261482008

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_numnb_pointeurnb_tuplelength_free_spaceunused_line_pointer
Numéro de pageNombre de pointeur.Nombre de tuple.Taille de l’espace libre.Présence de pointeurs non utilisés.
0534020Oui
1316148Oui

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_numnb_pointeurnb_tuplelength_free_spaceunused_line_pointer
Numéro de pageNombre de pointeur.Nombre de tuple.Taille de l’espace libre.Présence de pointeurs non utilisés.
0442016Non

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 232 = 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 :

HoraireTransaction 1Transaction 2
10h00Démarrage transaction
Début SELECT sur la table X
10h05Démarrage transaction
Début INSERT sur la table Y
=> Assignation d’un txid : 1000
10h06Fin INSERT sur la table Y
Fin transaction
10h08Fin SELECT sur la table X
Début UPDATE sur la table Y
=> Assignation d’un txid : 1001
10h09Fin 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 :

txidTemporalité
1<- Passé
999<- Passé
1000Présent
1001Futur ->
231 + 999
2 147 484 647
Futur ->
231 + 1000
2 147 484 648
<- Passé
232
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 221) 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_txidvacuum_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 txidvacuum_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ètreValeur
schemapublic
tabletable_test
nb_page1
tuple_count4
dead_tuple_count0
max_frozen_txid4644
n_tup_ins7
n_tup_upd2
n_tup_del3
n_mod_since_analyze0
n_ins_since_vacuum0
last_vacuum2024-01-01 01:01:01.000 +0100
last_autovacuum0
last_analyze2024-01-01 01:01:01.000 +0100
last_autoanalyze0

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.

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

BDDPostgreSQL ANALYZEHeapMVCCpageTOASTTransactiontupleVACUUM

50%