Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich. # Beispiel: Rückgewinnung von Speicherplatz mithilfe von Autovacuum und VACUUM FULL Lassen Sie uns als Beispiel eine `emp` Tabellentabelle mit 500.000 Zeilen erstellen und dann die Zeilen mit neuen Werten aktualisieren. Autovacuum ist aktiviert, sodass sowohl `VACUUM` Befehle als auch `ANALYZE` Befehle für diese Tabelle ausgeführt werden, um Blähungen zu entfernen und Speicherplatz zurückzugewinnen. Der zurückgewonnene Speicherplatz kann wiederverwendet werden, wird aber nicht an das Betriebssystem zurückgegeben. Die folgende Abfrage bestimmt, wie groß die Tabelle ist: ``` -- 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; ``` Die Ergebnisse der Abfrage zeigen, dass die Tabelle eine Aufblähung von etwa 51 Prozent aufweist: ``` 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 ``` Hier sind die Statistiken aus der `pg_stat_all_tables` Ansicht: ``` 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 ``` Beachten Sie, dass Autovacuum die `last_autoanalyze` Spalten `last_autovacuum` und nach der Ausführung aktualisiert hat. Fügen wir nun einige Zeilen in die Tabelle ein und überprüfen wir`extra_size(bloat_size)`, ob der leere Bereich auch als aufgebläht angesehen wird. ``` 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) ``` Die `bloat_pct` Spalte in der Ausgabe gibt an, dass der bereinigte Bereich durch neue Einfügungen belegt wurde. Lass uns laufen`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) ``` Aus dieser Ausgabe können Sie sehen, dass der leere Speicherplatz und die Aufblähung entfernt wurden und der Speicherplatz an das Betriebssystem zurückgegeben wurde. **Anmerkung** Stattdessen könnten Sie rennen`VACUUM FULL`, `pg_repack` um dieselben Ergebnisse zu erzielen.