Wir begannen mit Problemen im Zusammenhang mit der Isolation , machten einen Exkurs über das Organisieren von Daten auf niedriger Ebene und sprachen dann ausführlich über Zeilenversionen und wie Snapshots aus Versionen erhalten werden.

Das letzte Mal haben wir über HOT-Updates und In-Page-Reinigung gesprochen. Heute werfen wir einen Blick auf die bekannte normale Reinigung, Vakuum vulgaris . Ja, es wurde bereits so viel über sie geschrieben, dass ich wahrscheinlich nichts Neues sagen werde, aber die Vollständigkeit des Bildes erfordert Opfer. Sei geduldig.

Normale Reinigung (Vakuum)

Was macht die Reinigung?

Die Intra-Page-Reinigung ist schnell, macht aber nur einen Bruchteil des Speicherplatzes frei. Es funktioniert auf derselben Tabellenseite und wirkt sich nicht auf Indizes aus.

Die "normale" Hauptreinigung wird vom Befehl VACUUM durchgeführt, und wir nennen sie einfach "Reinigung" (und wir werden separat über die automatische Reinigung sprechen).

Die Bereinigung verarbeitet die Tabelle also vollständig. Es werden nicht nur unnötige Versionen von Zeichenfolgen bereinigt, sondern auch Verweise auf sie aus allen Indizes.

Die Verarbeitung erfolgt parallel zu anderen Aktivitäten im System. In diesem Fall können die Tabelle und die Indizes auf die übliche Weise sowohl zum Lesen als auch zum Bearbeiten verwendet werden (eine gleichzeitige Ausführung von Befehlen wie CREATE INDEX, ALTER TABLE und einigen anderen ist jedoch nicht möglich).

In der Tabelle werden nur die Seiten angezeigt, auf denen eine Aktivität aufgetreten ist. Hierzu wird eine Sichtbarkeitskarte verwendet (ich erinnere Sie daran, dass Seiten, die nur ziemlich alte Versionen von Zeilen enthalten, die garantiert in allen Datenschnappschüssen sichtbar sind, darin markiert sind). Es werden nur Seiten verarbeitet, die nicht auf der Karte markiert sind, während die Karte selbst aktualisiert wird.

Dabei wird die Freiraumkarte aktualisiert, um den auf den Seiten angezeigten Freiraum widerzuspiegeln.

Erstellen Sie wie gewohnt eine Tabelle:

=> CREATE TABLE vac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => CREATE INDEX vac_s ON vac(s); => INSERT INTO vac(s) VALUES ('A'); => UPDATE vac SET s = 'B'; => UPDATE vac SET s = 'C';

Mit dem Parameter autovacuum_enabled deaktivieren wir die automatische Reinigung. Wir werden das nächste Mal darüber sprechen, aber für Experimente ist es vorerst wichtig, die Reinigung manuell durchzuführen.

Die Tabelle enthält jetzt drei Versionen der Zeile, von denen jede über einen Index verknüpft ist:

 => SELECT * FROM heap_page('vac',0);

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 4000 (c) | 4001 (c) | | | (0,2) (0,2) | normal | 4001 (c) | 4002 | | | (0,3) (0,3) | normal | 4002 | 0 (a) | | | (0,3) (3 rows)

 => SELECT * FROM index_page('vac_s',1);

  itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) (3 rows)

Nach der Reinigung verschwinden die "toten" Versionen und es gibt nur eine relevante. Und der Index hat noch einen Link übrig:

 => VACUUM vac; => SELECT * FROM heap_page('vac',0);

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | unused | | | | | (0,3) | normal | 4002 (c) | 0 (a) | | | (0,3) (3 rows)

 => SELECT * FROM index_page('vac_s',1);

  itemoffset | ctid ------------+------- 1 | (0,3) (1 row)

Bitte beachten Sie, dass die ersten beiden Zeiger den Status unbenutzt und nicht tot erhalten haben, wie dies bei der Bereinigung innerhalb einer Seite der Fall wäre.

Und noch einmal zum Transaktionshorizont

Wie bestimmt PostgreSQL, welche Zeilenversionen als "tot" angesehen werden können? Wir haben das Konzept eines Transaktionshorizonts bereits in Betracht gezogen, als wir über Datenschnappschüsse gesprochen haben , aber dies ist ein so wichtiges Thema, dass es keine Sünde ist, es zu wiederholen.

Beginnen wir noch einmal mit der vorherigen Erfahrung.

 => TRUNCATE vac; => INSERT INTO vac(s) VALUES ('A'); => UPDATE vac SET s = 'B';

Bevor Sie die Zeile erneut aktualisieren, lassen Sie eine weitere Transaktion beginnen (aber nicht enden). In unserem Beispiel funktioniert es auf der Ebene "Commit lesen", sollte jedoch eine reale (nicht virtuelle) Transaktionsnummer erhalten. Zum Beispiel kann es einige Zeilen in einer beliebigen Tabelle ändern oder sogar nur sperren, nicht unbedingt in vac:

 | => BEGIN; | => SELECT s FROM t FOR UPDATE;

 | s | ----- | FOO | BAR | (2 rows)

 => UPDATE vac SET s = 'C';

Die Tabelle enthält drei Zeilen und den Index drei Links. Was passiert nach der Reinigung?

 => VACUUM vac; => SELECT * FROM heap_page('vac',0);

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | normal | 4005 (c) | 4007 (c) | | | (0,3) (0,3) | normal | 4007 (c) | 0 (a) | | | (0,3) (3 rows)

 => SELECT * FROM index_page('vac_s',1);

  itemoffset | ctid ------------+------- 1 | (0,2) 2 | (0,3) (2 rows)

Die Tabelle enthält noch zwei Versionen der Zeile: Bei der Bereinigung wurde entschieden, dass Version (0.2) noch nicht gelöscht werden konnte. Der Grund liegt natürlich im Datenbanktransaktionshorizont, der in unserem Beispiel durch eine unvollständige Transaktion bestimmt wird:

 | => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid();

 | backend_xmin | -------------- | 4006 | (1 row)

Sie können um Reinigung bitten, um darüber zu sprechen, was passiert:

 => VACUUM VERBOSE vac;

 INFO: vacuuming "public.vac" INFO: index "vac_s" now contains 2 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages DETAIL: 1 dead row versions cannot be removed yet, oldest xmin: 4006 There were 1 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM

Beachten Sie:

2 nicht entfernbare Zeilenversionen - In der Tabelle wurden 2 Versionen gefunden, die nicht gelöscht werden können.
1 Dead Row-Versionen können noch nicht entfernt werden - einschließlich 1 "Dead",
Das älteste xmin zeigt den aktuellen Horizont.

Wir wiederholen die Schlussfolgerung noch einmal: Das Vorhandensein langlebiger Transaktionen in der Datenbank (nicht abgeschlossen oder sehr lange) kann zur Erweiterung von (aufgeblähten) Tabellen führen, unabhängig davon, wie oft die Bereinigung durchgeführt wird. Daher werden in PostgreSQL OLTP- und OLAP-Workloads in einer Datenbank schlecht kombiniert: Berichte, die stundenlang ausgeführt werden, ermöglichen es nicht, häufig aktualisierte Tabellen rechtzeitig zu löschen. Eine mögliche Lösung wäre die Erstellung eines separaten "Berichts" -Replikats.

Nach Abschluss einer offenen Transaktion verschiebt sich der Horizont und die Situation wird korrigiert:

 | => COMMIT;

 => VACUUM VERBOSE vac;

 INFO: vacuuming "public.vac" INFO: scanned index "vac_s" to remove 1 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: "vac": removed 1 row versions in 1 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "vac_s" now contains 1 row versions in 2 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4008 There were 1 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM

Jetzt hat die Seite nur die neueste aktuelle Version der Zeile:

 => SELECT * FROM heap_page('vac',0);

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | unused | | | | | (0,3) | normal | 4007 (c) | 0 (a) | | | (0,3) (3 rows)

Es gibt auch nur einen Eintrag im Index:

 => SELECT * FROM index_page('vac_s',1);

  itemoffset | ctid ------------+------- 1 | (0,3) (1 row)

Was ist drinnen los?

Bei der Bereinigung sollten sowohl die Tabelle als auch die Indizes gleichzeitig verarbeitet werden, und zwar so, dass der Betrieb anderer Prozesse nicht blockiert wird. Wie macht sie das?

Alles beginnt mit einem Tabellenscan (unter Berücksichtigung der Sichtbarkeitskarte, wie bereits erwähnt). Auf den gelesenen Seiten werden unnötige Versionen von Zeichenfolgen ermittelt und ihre Bezeichner (tid) in ein spezielles Array geschrieben. Das Array befindet sich im lokalen Speicher des Reinigungsprozesses. Dafür wird ein Fragment der Größe "tenance_work_mem "zugewiesen. Der Standardwert für diesen Parameter ist 64 MB. Beachten Sie, dass dieser Speicher sofort vollständig und nicht nach Bedarf zugewiesen wird. Richtig, wenn die Tabelle klein ist, wird dem Fragment weniger zugewiesen.

Als nächstes eines von zwei Dingen: Entweder erreichen wir das Ende der Tabelle oder der für das Array zugewiesene Speicher endet. In beiden Fällen beginnt die Indexbereinigungsphase . Zu diesem Zweck wird jeder der in der Tabelle erstellten Indizes vollständig nach Datensätzen durchsucht, die auf gespeicherte Versionen von Zeilen verweisen. Gefundene Datensätze werden von Indexseiten gelöscht.

An dieser Stelle erhalten wir das folgende Bild: In den Indizes gibt es keine Links mehr zu unnötigen Versionen von Zeilen, aber sie sind immer noch in der Tabelle vorhanden. Dies widerspricht nichts: Bei der Ausführung einer Abfrage gelangen wir entweder überhaupt nicht zu toten Versionen von Zeilen (mit Indexzugriff) oder markieren sie bei der Überprüfung der Sichtbarkeit (beim Scannen einer Tabelle).

Danach beginnt die Tischreinigungsphase . Die Tabelle wird erneut gescannt, um die erforderlichen Seiten zu lesen, die gespeicherten Versionen der Zeilen daraus zu entfernen und die Zeiger freizugeben. Wir können dies tun, weil es keine Links mehr aus Indizes gibt.

Wenn die Tabelle im ersten Durchgang nicht vollständig gelesen wurde, wird das Array gelöscht und alles wird an der Stelle wiederholt, an der wir aufgehört haben.

Auf diese Weise:

Die Tabelle wird immer zweimal gescannt.
Wenn während der Bereinigung so viele Zeilenversionen gelöscht werden, dass nicht alle in den Speicher vontenance_work_mem passen, werden alle Indizes so oft wie nötig vollständig gescannt.

Bei großen Tischen kann dies viel Zeit in Anspruch nehmen und das System erheblich belasten. Natürlich werden Anfragen nicht blockiert, aber die "zusätzliche" E / A ist auch unangenehm.

Um den Prozess zu beschleunigen, ist es sinnvoll, entweder häufiger zu bereinigen (damit nicht jedes Mal eine sehr große Anzahl von Zeilenversionen gelöscht wird) oder mehr Speicher zuzuweisen.

Ich stelle in Klammern fest, dass PostgreSQL ab Version 11 Index-Scans überspringen kann, wenn dies nicht unbedingt erforderlich ist. Dies sollte den Besitzern großer Tabellen, in denen Zeilen nur hinzugefügt (aber nicht geändert) werden, das Leben erleichtern.

Überwachung

Wie kann man verstehen, dass die Reinigung nicht mit der Arbeit in einem Durchgang fertig wird?

Wir haben bereits die erste Methode gesehen: Sie können den Befehl VACUUM mit VERBOSE aufrufen. Anschließend werden Informationen zu den Arbeitsphasen auf der Konsole angezeigt.

Zweitens gibt es ab Version 9.6 eine Ansicht pg_stat_progress_vacuum, die auch alle notwendigen Informationen enthält.

(Es gibt eine dritte Möglichkeit: Informationen im Nachrichtenprotokoll anzuzeigen. Dies funktioniert jedoch nur für die automatische Reinigung, die beim nächsten Mal erläutert wird.)

Wir werden weitere Zeilen in die Tabelle einfügen, damit die Reinigung spürbar lange dauert, und wir werden alle aktualisieren, damit etwas mit der Reinigung zu tun hat.

 => TRUNCATE vac; => INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000); => UPDATE vac SET s = 'B';

Reduzieren Sie die Größe des für das Bezeichnerarray zugewiesenen Speichers:

 => ALTER SYSTEM SET maintenance_work_mem = '1MB'; => SELECT pg_reload_conf();

Wir beginnen mit der Reinigung und wenden uns, während dies funktioniert, mehrmals der Ansicht pg_stat_progress_vacuum zu:

 => VACUUM VERBOSE vac;

 | => SELECT * FROM pg_stat_progress_vacuum \gx

 | -[ RECORD 1 ]------+------------------ | pid | 6715 | datid | 41493 | datname | test | relid | 57383 | phase | vacuuming indexes | heap_blks_total | 16667 | heap_blks_scanned | 2908 | heap_blks_vacuumed | 0 | index_vacuum_count | 0 | max_dead_tuples | 174762 | num_dead_tuples | 174480

 | => SELECT * FROM pg_stat_progress_vacuum \gx

 | -[ RECORD 1 ]------+------------------ | pid | 6715 | datid | 41493 | datname | test | relid | 57383 | phase | vacuuming indexes | heap_blks_total | 16667 | heap_blks_scanned | 5816 | heap_blks_vacuumed | 2907 | index_vacuum_count | 1 | max_dead_tuples | 174762 | num_dead_tuples | 174480

Hier sehen wir insbesondere:

Name der aktuellen Phase (Phase) - wir haben über drei Hauptphasen gesprochen, aber im Allgemeinen gibt es mehr davon;
Gesamtzahl der Tabellenseiten (heap_blks_total);
die Anzahl der gecrawlten Seiten (heap_blks_scanned);
die Anzahl der bereits gelöschten Seiten (heap_blks_vacuumed);
Die Anzahl der Durchgänge pro Index (index_vacuum_count).

Der Gesamtfortschritt wird durch das Verhältnis von heap_blks_vacuumed zu heap_blks_total bestimmt. Beachten Sie jedoch, dass sich dieser Wert nicht reibungslos ändert, sondern aufgrund von Index-Scans „ruckartig“. Das Hauptaugenmerk sollte jedoch auf die Anzahl der Reinigungszyklen gelegt werden - ein Wert größer als 1 bedeutet, dass der zugewiesene Speicher nicht ausreicht, um die Reinigung in einem Durchgang abzuschließen.

Die Ausgabe des Befehls VACUUM VERBOSE, die zu diesem Zeitpunkt abgeschlossen ist, zeigt das Gesamtbild:

 INFO: vacuuming "public.vac"

 INFO: scanned index "vac_s" to remove 174480 row versions DETAIL: CPU: user: 0.50 s, system: 0.07 s, elapsed: 1.36 s INFO: "vac": removed 174480 row versions in 2908 pages DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.13 s

 INFO: scanned index "vac_s" to remove 174480 row versions DETAIL: CPU: user: 0.26 s, system: 0.07 s, elapsed: 0.81 s INFO: "vac": removed 174480 row versions in 2908 pages DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.10 s

 INFO: scanned index "vac_s" to remove 151040 row versions DETAIL: CPU: user: 0.13 s, system: 0.04 s, elapsed: 0.47 s INFO: "vac": removed 151040 row versions in 2518 pages DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.08 s

 INFO: index "vac_s" now contains 500000 row versions in 17821 pages DETAIL: 500000 index row versions were removed. 8778 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 500000 removable, 500000 nonremovable row versions in 16667 out of 16667 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4011 There were 0 unused item pointers. 0 pages are entirely empty. CPU: user: 1.10 s, system: 0.37 s, elapsed: 3.71 s. VACUUM

Hier können Sie sehen, dass es insgesamt drei Durchgänge durch die Indizes gab, von denen jeder 174.480 Zeiger auf tote Versionen von Zeichenfolgen löschte. Woher kommt diese Nummer? Ein Link (tid) benötigt 6 Bytes und 1024 * 1024/6 = 174762 ist die Nummer, die wir in pg_stat_progress_vacuum.max_dead_tuples sehen. Tatsächlich kann es etwas weniger verwendet werden: Es ist garantiert, dass beim Lesen der nächsten Seite alle Zeiger auf die "toten" Versionen genau in den Speicher passen.

Analyse

Die Analyse oder mit anderen Worten die Erfassung statistischer Informationen für den Abfrageplaner ist formal nicht mit der Bereinigung verbunden. Trotzdem können wir die Analyse nicht nur mit dem ANALYZE-Team durchführen, sondern auch die Reinigung mit der Analyse kombinieren: VACUUM ANALYZE. In diesem Fall wird zuerst die Reinigung und dann die Analyse durchgeführt - es werden keine Einsparungen erzielt.

Wie wir später sehen werden, werden die automatische Reinigung und die automatische Analyse in einem Prozess durchgeführt und auf ähnliche Weise verwaltet.

Vollreinigung (Vakuum voll)

Wie wir gesehen haben, gibt die herkömmliche Reinigung mehr Platz frei als die Reinigung innerhalb einer Seite, aber selbst sie löst das Problem nicht immer vollständig.

Wenn eine Tabelle oder ein Index aus irgendeinem Grund erheblich größer geworden ist, wird durch regelmäßige Bereinigung Speicherplatz auf vorhandenen Seiten frei: Sie haben Löcher, die dann zum Einfügen neuer Versionen von Zeilen verwendet werden. Die Anzahl der Seiten ändert sich jedoch nicht. Aus Sicht des Betriebssystems belegen die Dateien daher genau so viel Speicherplatz wie vor dem Bereinigen. Und das ist schlecht, weil:

Das vollständige Scannen einer Tabelle (oder eines Index) wird verlangsamt.
Möglicherweise ist ein größerer Puffercache erforderlich (da Seiten gespeichert werden und die Dichte nützlicher Informationen abnimmt).
Im Indexbaum wird möglicherweise eine zusätzliche Ebene angezeigt, die den Indexzugriff verlangsamt.
Dateien belegen zusätzlichen Speicherplatz und Backups.

(Die einzige Ausnahme sind die vollständig bereinigten Seiten am Ende der Datei. Diese Seiten „beißen“ die Datei ab und kehren zum Betriebssystem zurück.)

Wenn der Anteil nützlicher Informationen in den Dateien einen angemessenen Grenzwert unterschreitet, kann der Administrator eine vollständige Tabellenbereinigung durchführen. Gleichzeitig werden die Tabelle und alle ihre Indizes vollständig von Grund auf neu erstellt, und die Daten werden so kompakt wie möglich gepackt (natürlich unter Berücksichtigung des Füllfaktorparameters). Bei der Neuerstellung erstellt PostgreSQL zuerst die Tabelle und dann jeden ihrer Indizes nacheinander neu. Für jedes Objekt werden neue Dateien erstellt, und am Ende der Neuerstellung werden alte Dateien gelöscht. Bitte beachten Sie, dass während der Arbeit an der Festplatte zusätzlicher Speicherplatz benötigt wird.

Fügen Sie zur Veranschaulichung erneut eine Reihe von Zeilen in die Tabelle ein:

 => TRUNCATE vac; => INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000);

Wie bewertet man die Informationsdichte? Verwenden Sie dazu bequem die spezielle Erweiterung:

 => CREATE EXTENSION pgstattuple; => SELECT * FROM pgstattuple('vac') \gx

 -[ RECORD 1 ]------+--------- table_len | 68272128 tuple_count | 500000 tuple_len | 64500000 tuple_percent | 94.47 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 38776 free_percent | 0.06

Die Funktion liest die gesamte Tabelle und zeigt Statistiken darüber an, wie viel Speicherplatz welche Daten in den Dateien belegen. Die Hauptinformation, die uns jetzt interessiert, ist das Feld tuple_percent: der Prozentsatz, der von nützlichen Daten belegt wird. Es ist weniger als 100 aufgrund des unvermeidlichen Overheads an Serviceinformationen auf der Seite, aber dennoch ziemlich hoch.

Für den Index werden andere Informationen angezeigt, aber das Feld avg_leaf_density hat dieselbe Bedeutung: den Prozentsatz nützlicher Informationen (auf Blattseiten).

 => SELECT * FROM pgstatindex('vac_s') \gx

 -[ RECORD 1 ]------+--------- version | 3 tree_level | 3 index_size | 72802304 root_block_no | 2722 internal_pages | 241 leaf_pages | 8645 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 83.77 leaf_fragmentation | 64.25

Und hier ist die Größe der Tabelle und des Index:

 => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size;

  table_size | index_size ------------+------------ 65 MB | 69 MB (1 row)

Löschen Sie jetzt 90% aller Zeilen. Wir wählen die zu löschenden Zeilen zufällig aus, so dass auf jeder Seite mit hoher Wahrscheinlichkeit mindestens eine Zeile übrig bleibt:

 => DELETE FROM vac WHERE random() < 0.9;

 DELETE 450189

Welche Größe haben Objekte nach der normalen Reinigung?

 => VACUUM vac; => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size;

  table_size | index_size ------------+------------ 65 MB | 69 MB (1 row)

Wir sehen, dass sich die Größe nicht geändert hat: Durch regelmäßige Reinigung kann die Größe der Dateien in keiner Weise verringert werden. Obwohl die Informationsdichte offensichtlich um das Zehnfache abgenommen hat:

 => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;

  tuple_percent | avg_leaf_density ---------------+------------------ 9.41 | 9.73 (1 row)

Überprüfen Sie nun, was nach einer vollständigen Bereinigung passiert. Hier sind die Dateien, die jetzt von der Tabelle und den Indizes verwendet werden:

 => SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');

  pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/41493/57392 | base/41493/57393 (1 row)

 => VACUUM FULL vac; => SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s');

  pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/41493/57404 | base/41493/57407 (1 row)

Jetzt werden die Dateien durch neue ersetzt. Die Größe der Tabelle und des Index hat erheblich abgenommen, und die Informationsdichte hat entsprechend zugenommen:

 => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size;

  table_size | index_size ------------+------------ 6648 kB | 6480 kB (1 row)

 => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s;

  tuple_percent | avg_leaf_density ---------------+------------------ 94.39 | 91.08 (1 row)

Bitte beachten Sie, dass die Informationsdichte im Index im Vergleich zum Original sogar gestiegen ist. Das erneute Erstellen eines Index (B-Baum) aus den verfügbaren Daten ist rentabler als das zeilenweise Einfügen von Daten in einen vorhandenen Index.

Die von uns verwendeten pgstattuple- Erweiterungsfunktionen lesen die gesamte Tabelle. Wenn die Tabelle groß ist, ist dies unpraktisch, und daher gibt es dort eine Funktion pgstattuple_approx, die die in der Sichtbarkeitskarte markierten Seiten überspringt und ungefähre Zahlen anzeigt.

Eine noch schnellere, aber noch weniger genaue Methode besteht darin, das Verhältnis von Datenvolumen zu Dateigröße im Systemverzeichnis zu schätzen. Optionen für solche Abfragen finden Sie im Wiki .

Eine vollständige Bereinigung erfordert keine regelmäßige Verwendung, da alle Arbeiten mit der Tabelle (einschließlich der Abfrage) für die gesamte Dauer ihrer Arbeit vollständig blockiert werden. Es ist klar, dass dies auf einem aktiv genutzten System möglicherweise nicht akzeptabel ist. Sperren werden separat betrachtet, aber wir beschränken uns vorerst darauf , die Erweiterung pg_repack zu erwähnen, die die Tabelle am Ende der Arbeit nur für kurze Zeit sperrt.

MVCC-6. Reinigung