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';

Tabelle aufräumen

VACUUM VERBOSE vacuum_test;

Tabelle aufräumen

VACUUM FULL VERBOSE vacuum_test;

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();

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;

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;

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);

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;

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);