Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # Exemple : récupérer de l'espace en utilisant Autovacuum et VACUUM FULL Par exemple, créons un `emp` tableau de 500 000 lignes, puis mettons à jour les lignes avec de nouvelles valeurs. Autovacuum étant activé, il exécutera à la fois les `ANALYZE` commandes `VACUUM` et les commandes de cette table pour éliminer le gonflement et récupérer de l'espace. L'espace récupéré peut être réutilisé mais il ne sera pas restitué au système d'exploitation. La requête suivante détermine le gonflement de la table : ``` -- WARNING: When run with a non-superuser role, the query inspects only indexes on tables you are granted to read. -- WARNING: Rows with is_na = 't' are known to have bad statistics ("name" type is not supported). -- This query is compatible with PostgreSQL 8.2 and later. SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, bs*(relpages-est_pages)::bigint AS extra_size, 100 * (relpages-est_pages)::float / relpages AS extra_pct, fillfactor, CASE WHEN relpages > est_pages_ff THEN bs*(relpages-est_pages_ff) ELSE 0 END AS bloat_size, 100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct, is_na -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO) FROM ( SELECT coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) ) AS est_pages, coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 ) AS est_pages_ff, bs, nspname, tblname, idxname, relpages, fillfactor, is_na -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) FROM ( SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor, ( index_tuple_hdr_bm + maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign ELSE index_tuple_hdr_bm%maxalign END + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN WHEN nulldatawidth = 0 THEN 0 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) FROM ( SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs, CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS maxalign, /* per page header, fixed size: 20 for 7.X, 24 for others */ 24 AS pagehdr, /* per page btree opaque data */ 16 AS pageopqdata, /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ CASE WHEN max(coalesce(s.null_frac,0)) = 0 THEN 8 -- IndexTupleData size ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) END AS index_tuple_hdr_bm, /* data len: we remove null values save space using it fractionnal part from stats */ sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na FROM ( SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor, coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid, CASE WHEN a1.attnum IS NULL THEN ic.idxname ELSE ct.relname END AS attrelname FROM ( SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey, pg_catalog.generate_series(1,indnatts) AS attpos FROM ( SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid, i.indexrelid AS idxoid, coalesce(substring( array_to_string(ci.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor, i.indnatts, pg_catalog.string_to_array(pg_catalog.textin( pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree') AND ci.relpages > 0 ) AS idx_data ) AS ic JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid LEFT JOIN pg_catalog.pg_attribute a1 ON ic.indkey[ic.attpos] <> 0 AND a1.attrelid = ic.tbloid AND a1.attnum = ic.indkey[ic.attpos] LEFT JOIN pg_catalog.pg_attribute a2 ON ic.indkey[ic.attpos] = 0 AND a2.attrelid = ic.idxoid AND a2.attnum = ic.attpos ) i JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname AND s.tablename = i.attrelname AND s.attname = i.attname GROUP BY 1,2,3,4,5,6,7,8,9,10,11 ) AS rows_data_stats ) AS rows_hdr_pdg_stats ) AS relation_stats ORDER BY nspname, tblname, idxname; ``` Les résultats de la requête montrent que la table présente une surcharge d'environ 51 % : ``` current_database | schemaname | tblname | real_size | extra_size | extra_pct | fillfactor | bloat_size | bloat_pct | is_na ------------------+------------+---------+-----------+------------+-------------------+------------+------------+-------------------+------- apgl | public | emp | 60383232 | 30744576 | 50.91575091575091 | 100 | 30744576 | 50.91575091575091 | f ``` Voici les statistiques tirées de la `pg_stat_all_tables` vue : ``` relid | 914748 schemaname | public relname | emp seq_scan | 5 seq_tup_read | 1500000 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 600000 n_tup_upd | 500000 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 500000 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2023-04-15 11:59:54.957449+00 last_analyze | last_autoanalyze | 2023-04-15 11:59:55.016352+00 vacuum_count | 0 autovacuum_count | 2 analyze_count | 0 autoanalyze_count | 3 ``` Notez qu'autovacuum a mis à jour les `last_autoanalyze` colonnes `last_autovacuum` et après son exécution. Maintenant, insérons quelques lignes dans le tableau et vérifions-le`extra_size(bloat_size)`, car l'espace vide est également considéré comme un excès. ``` apgl=> select count(*) from emp; count | 900000 current_database | schemaname | tblname | real_size | extra_size | extra_pct | fillfactor | bloat_size | bloat_pct | is_na ------------------+------------+---------+-----------+------------+--------------------+------------+------------+--------------------+------- apgl | public | emp | 61349888 | 327680 | 0.5341167044999332 | 100 | 327680 | 0.5341167044999332 | f (1 row) ``` La `bloat_pct` colonne de sortie indique que l'espace nettoyé a été occupé par de nouveaux inserts. Allons courir `VACUUM FULL` : ``` apgl=> vacuum full emp ; VACUUM current_database | schemaname | tblname | real_size | extra_size | extra_pct | fillfactor | bloat_size | bloat_pct | is_na ------------------+------------+---------+-----------+------------+-----------+------------+------------+-----------+------- apgl | public | emp | 60792832 | -229376 | 0 | 100 | 0 | 0 | f (1 row) ``` À partir de cette sortie, vous pouvez voir que l'espace vide et le gonflement ont été supprimés et que l'espace a été renvoyé au système d'exploitation. **Note** Au lieu de cela`VACUUM FULL`, vous pourriez courir `pg_repack` pour obtenir les mêmes résultats.