As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá. # Exemplo: recuperação de espaço usando autovacuum e VACUUM FULL Como exemplo, vamos criar uma tabela `emp` com 500 mil linhas e, em seguida, atualizar as linhas com novos valores. O Autovacuum está habilitado, então ele executará os comandos `VACUUM` e `ANALYZE` nessa tabela para remover o inchaço e recuperar espaço. O espaço recuperado pode ser reutilizado, mas não será devolvido ao sistema operacional. A consulta a seguir determina o inchaço na tabela: ``` -- 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; ``` Os resultados da consulta mostram que a tabela tem um inchaço de cerca de 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 ``` Aqui estão as estatísticas da visualização `pg_stat_all_tables`: ``` 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 ``` Observe que o autovacuum atualizou as colunas `last_autovacuum` e `last_autoanalyze` após sua execução. Agora, vamos inserir algumas linhas na tabela e verificar `extra_size(bloat_size)`, pois o espaço vazio também é considerado inchaço. ``` 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) ``` A coluna `bloat_pct` na saída indica que o espaço limpo foi ocupado por novas inserções. Vamos executar o `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) ``` Nessa saída, você pode ver que o espaço vazio e o inchaço foram removidos e que o espaço foi retornado ao sistema operacional. **nota** Em vez do `VACUUM FULL`, você pode executar `pg_repack` para obter os mesmos resultados.