PostgreSQL Datenbankpraxis für Anwender, Administratoren und Entwickler

Kapitel 8 - VACUUM und ANALYZE

Seite 256

Beispieltabelle für VACUUM

CREATE TABLE vacuum_test (id SERIAL, daten TEXT);
INSERT INTO vacuum_test (daten) SELECT generate_series(1, 5000);
DELETE FROM vacuum_test WHERE id <= 1000;
SELECT relname,n_live_tup,n_dead_tup
         FROM pg_stat_user_tables
        WHERE relname = 'vacuum_test';

Seite 257

Tabelle aufräumen

VACUUM VERBOSE vacuum_test;

Seite 258

Tabelle aufräumen

VACUUM FULL VERBOSE vacuum_test;

Seite 260

Beispieltabelle für XID-Überlauf

CREATE TABLE xid_test (daten TEXT);
INSERT INTO xid_test (daten) VALUES ('meine Daten');
SELECT xmin, daten FROM xid_test;
SELECT relfrozenxid
         FROM pg_class
        WHERE oid = 'xid_test'::regclass;
SELECT txid_current();

Seite 261

VACUUM passt xid an

SELECT txid_current();
SELECT xmin, daten FROM xid_test;
VACUUM xid_test;
SELECT xmin, daten FROM xid_test;
SELECT relfrozenxid
         FROM pg_class
        WHERE oid = 'xid_test'::regclass;

Seite 262

Alter einer Tabelle ermitteln

SELECT AGE(relfrozenxid) AS alter
         FROM pg_class
        WHERE oid = 'xid_test'::regclass;


Anzahl Transaktionen für alle Tabellen ermitteln

SELECT relname, AGE(relfrozenxid) AS alter
         FROM pg_class
        WHERE relkind = 'r' ORDER BY relname;
SELECT datname, AGE(datfrozenxid) AS alter
         FROM pg_database
     ORDER BY datname;

Seite 264

Statistiken für HOT auslesen

SELECT schemaname AS "Schema",
              relname AS "Tabelle",
              n_tup_upd AS "Tuples geupdated",
              n_tup_hot_upd AS "Tuples mit HOT geupdated",
              CASE WHEN n_tup_upd > 0
                   THEN ((n_tup_hot_upd::numeric /
                          n_tup_upd::numeric)*100.0)::numeric(5,2)
                   ELSE NULL
              END AS "HOT Ratio"
        FROM pg_stat_user_tables;

Danke an dim aus dem IRC-Channel #postgresql für die Vorlage in seinem Blog.


fillfactor für eine Tabelle angeben

CREATE TABLE ... (
  ...
) WITH (fillfactor = 80);


fillfactor ändern

ALTER TABLE ...
         SET (fillfactor = 60);

Seite 265

Voreinstellung für fillfactor ermitteln

SELECT t.schemaname AS "Schema",
              t.relname AS "Tabelle",
              c.reloptions AS "Optionen"
         FROM pg_stat_user_tables t
         JOIN (pg_class c
         JOIN  pg_namespace n ON c.relnamespace = n.oid)
           ON n.nspname = t.schemaname
          AND c.relname = t.relname;

Seite 268

VACUUM für eine Tabelle ausschalten

INSERT INTO pg_autovacuum
  VALUES ('vacuum_test'::regclass, 'f', -1, -1, -1, -1, -1, -1, -1, -1);


Letzter Autovacuum-Aufruf

SELECT relname,last_autovacuum,last_autoanalyze
         FROM pg_stat_user_tables
        WHERE relname='vacuum_test';


Storage-Parameter für eine Tabelle angeben

CREATE TABLE ... (
  ...
) WITH (autovacuum_enabled = off);


Storage-Parameter ändern

ALTER TABLE ...
         SET (autovacuum_enabled = on);