akretschmer
Datenbank-Guru
- Beiträge
- 10.367
Angenommen, ihr habt eine mehr oder weniger große Tabelle und eine nur mittelmäßig intelligent programmierte Anwendung.
Diese holt immer sehr viele / alle Datensätze, um einige zu verändern und macht dann über alle ein UPDATE. Das Update wird für alle Rows ausgeführt, auch wenn nur wenige / keine Änderungen real erfolgen.
Wo ist das Problem:
Ein UPDATE ist intern ein DELETE und ein INSERT. Der alte Datensatz bleibt dabei bestehen, er wird nur als gelöscht markiert (nicht einmal das, beim Update wird nur xmax, ein verstecktes Feld, auf die aktuelle Transaktions-ID gesetzt. Das als gelöscht markieren macht später das VACUUM.)
Damit bleibt erst einmal der Datensatz also als 100% Kopie bestehen. Bei großen Tabellen kann das enorm Speicherplatz kosten, außerdem ist jede Menge IO-Traffic da (Schreiben des WAL, beim Checkpoint schreiben der eigentlichen Tabelle), VACUUM hat zu tun etc.
Hier kann ein conditional TRIGGER helfen. Kurze Demo mit Kommentaren:
Gut zu sehen, die Tabelle ist nach dem Update etwa doppelt so groß, obwohl rein logisch die Daten sich nicht geändert haben.
Jetzt mit einem TRIGGER:
Der interessante Teil ist hier "when (new is not distinct from old)", dieser prüft NEW und OLD, ob diese unterschiedlich sind, falls nein, ruft es die Triggerfunktion auf, welche NULL liefert. Da es ein Before-Trigger ist, ist der Trigger an dieser Stelle zu ende - es erfolgt kein Update, wenn NEW und OLD gleich sind.
Das Update braucht etwas länger, aber die Größe der Tabelle hat sich nicht geändert, wir haben keine toten Tupel in der Tabelle.
Im übrigen: wenn nur ein Datensatz verändert wird, braucht die TRIGGER-Lösung etwa genau so lange bzw. ist sogar schneller:
Die Laufzeiten waren mir aber erst einmal egal, wesentlich war mir das Verhindern von Table-Bloat ;-)
Diese holt immer sehr viele / alle Datensätze, um einige zu verändern und macht dann über alle ein UPDATE. Das Update wird für alle Rows ausgeführt, auch wenn nur wenige / keine Änderungen real erfolgen.
Wo ist das Problem:
Ein UPDATE ist intern ein DELETE und ein INSERT. Der alte Datensatz bleibt dabei bestehen, er wird nur als gelöscht markiert (nicht einmal das, beim Update wird nur xmax, ein verstecktes Feld, auf die aktuelle Transaktions-ID gesetzt. Das als gelöscht markieren macht später das VACUUM.)
Damit bleibt erst einmal der Datensatz also als 100% Kopie bestehen. Bei großen Tabellen kann das enorm Speicherplatz kosten, außerdem ist jede Menge IO-Traffic da (Schreiben des WAL, beim Checkpoint schreiben der eigentlichen Tabelle), VACUUM hat zu tun etc.
Hier kann ein conditional TRIGGER helfen. Kurze Demo mit Kommentaren:
Code:
test=# create table demo (id int primary key, value text);
CREATE TABLE
Time: 5,532 ms
test=*# insert into demo select s, md5(s::text) from generate_series(1,10000) s;
INSERT 0 10000
Time: 57,994 ms
test=*# select pg_size_pretty(pg_relation_size('demo'));
pg_size_pretty
----------------
672 kB
(1 row)
Time: 0,284 ms
test=*# update demo set id=id, value=value;
UPDATE 10000
Time: 72,413 ms
test=*# select pg_size_pretty(pg_relation_size('demo'));
pg_size_pretty
----------------
1336 kB
(1 row)
Time: 0,267 ms
test=*# rollback;
ROLLBACK
Gut zu sehen, die Tabelle ist nach dem Update etwa doppelt so groß, obwohl rein logisch die Daten sich nicht geändert haben.
Jetzt mit einem TRIGGER:
Code:
test=# create table demo (id int primary key, value text);
CREATE TABLE
Time: 14,632 ms
test=*# insert into demo select s, md5(s::text) from generate_series(1,10000) s;
INSERT 0 10000
Time: 72,565 ms
test=*# create or replace function trigger_function() returns trigger as $$begin return NULL; end;$$ language plpgsql;
CREATE FUNCTION
Time: 0,481 ms
test=*# create trigger trg_demo before update on demo for each row when (new is not distinct from old) execute procedure trigger_function();
CREATE TRIGGER
Time: 0,791 ms
test=*# select pg_size_pretty(pg_relation_size('demo'));
pg_size_pretty
----------------
672 kB
(1 row)
Time: 0,278 ms
test=*# update demo set id=id, value=value;
UPDATE 0
Time: 96,690 ms
test=*# select pg_size_pretty(pg_relation_size('demo'));
pg_size_pretty
----------------
672 kB
(1 row)
Der interessante Teil ist hier "when (new is not distinct from old)", dieser prüft NEW und OLD, ob diese unterschiedlich sind, falls nein, ruft es die Triggerfunktion auf, welche NULL liefert. Da es ein Before-Trigger ist, ist der Trigger an dieser Stelle zu ende - es erfolgt kein Update, wenn NEW und OLD gleich sind.
Das Update braucht etwas länger, aber die Größe der Tabelle hat sich nicht geändert, wir haben keine toten Tupel in der Tabelle.
Im übrigen: wenn nur ein Datensatz verändert wird, braucht die TRIGGER-Lösung etwa genau so lange bzw. ist sogar schneller:
Code:
-- hier noch mit TRIGGER
test=*# update demo set id=id, value=value where id = 4711;
UPDATE 0
Time: 0,353 ms
test=*# rollback;
ROLLBACK
Time: 1,180 ms
-- Tabelle neu erstellen und füllen
test=# create table demo (id int primary key, value text);
CREATE TABLE
Time: 7,999 ms
test=*# insert into demo select s, md5(s::text) from generate_series(1,10000) s;
INSERT 0 10000
Time: 57,079 ms
-- jetzt ohne TRIGGER
test=*# update demo set id=id, value=value where id = 4711;
UPDATE 1
Time: 0,366 ms
Die Laufzeiten waren mir aber erst einmal egal, wesentlich war mir das Verhindern von Table-Bloat ;-)