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