Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Trickkiste: conditional TRIGGER

Dieses Thema im Forum "PostgreSQL" wurde erstellt von akretschmer, 23 November 2013.

  1. akretschmer

    akretschmer Datenbank-Guru

    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:

    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 ;-)
     
Die Seite wird geladen...
Ähnliche Themen - Trickkiste conditional TRIGGER
  1. akretschmer
    Antworten:
    2
    Aufrufe:
    1.752
  2. akretschmer
    Antworten:
    3
    Aufrufe:
    2.192

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden