Sichten

Sichten (Views) sind gespeicherte SQL-Abfragen, die bei jedem Zugriff ausgeführt werden. Sie können wie Tabellen in SQL-Anweisungen behandelt werden, jedoch kann die Komplexität der SQL-Abfrage hohe Kosten verursachen, was zu einer langen Ausführzeit führt. Dies wird umso dramatischer je öfter die Sicht abgefragt wird.

create view tours_available as
  select 
    *,
    t.places - coalesce(aggr.booked, 0) as available,
    false as invalid
    from organisations
    join tours as t using (oid)
    join events using (tid)
    left join (
      select eid,
       sum(places) as booked
      from bookings 
      group by eid
    ) as aggr using (eid);

Die Sicht master_tours_available berechnet zu allen Events (Tabelle events), wiederkehrender Veranstaltungen (tours) von verschiedenen Anbietern (organisations) die reservierten (booked) und freien (available) Plätze. Diese Sicht vereint alle Informationen, die für die Darstellung und Buchung aller Events notwendig sind. Die Sicht ist nichts weiteres als eine Erweiterung der Tabelle events. Sofern der Zugriff auf diese Sicht effekitv gestaltet werden kann, stellt dies auch eine erhebliche Vereinfachung für die Anwendungsentwicklung dar. Besonders hohe Kosten entstehen wenn in der Unterabfrage aggr alle eine Millionen Buchungen behandelt werden. Das ist der Fall, wenn beispielsweise nach allen noch buchbaren Events von Morgen gefragt wird.

explain analyze select * 
from tours_available
where start_at between current_date + 1 and current_date + 2
and available > 0;

                   QUERY PLAN
--------------------------------------------------------------
Merge Left Join  (cost=31509.46..33448.54 rows=30953 width=323)
  Merge Cond: (events.eid = aggr.eid)
  Filter: ((t.places - COALESCE(aggr.booked, 0::bigint)) > 0)
  ->  Sort  (cost=1351.87..1354.12 rows=899 width=315)
  ...
  ->  Sort  (cost=30157.59..30389.74 rows=92860 width=12)
        Sort Key: aggr.eid
        Sort Method:  quicksort  Memory: 7760kB
        ->  Subquery Scan on aggr  (cost=20406.00..22495.35 rows=92860 width=12)
              ->  HashAggregate  (cost=20406.00..21566.75 rows=92860 width=8)
                    ->  Seq Scan on bookings  (cost=0.00..15406.00 rows=1000000 width=8)
Total runtime: 5473.173 ms

Die möglichen Kosten einer SQL-Abfrage können durch einen Ausführlplan (Query Plan) abgeschätzt werden. In dem Ausschnitt dieses Ausführplans kann abgelesen werden, dass ein Großteil der Kosten (ca. 90%, cost) für das Lesen und Gruppieren der Buchungen benötigt wird. Die tatsächliche Ausführzeit wird abschließend unter Total runtime aufgeführt und beträgt hier 5720 ms. Die hohen Kosten können vermieden werden, indem die Ergebnismenge der SQL-Abfrage aus der Definition der Sicht tours_available in einer Tabelle gespeichert und von da abgefragt werden. Dies leisten Materialisierte Sichten.

Snapshots

Die einfachste Form einer Materialisierten Sicht (Materialized View) ist ein sogenannter Auszug (Snapshot). Dazu wird hier das Ergebnis der Sicht tours_available zugrunde liegenden SQL-Abfrage einmalig in eine Tabelle geschrieben. Dies ist einfach zu erreichen, da eine Tabelle als Ergebnis einer SQL-Abfrage definiert werden kann.

explain analyze create table tours_available_materialized as
  select * from tours_available;

Total runtime: 10012.114 ms

Ein Nachteil von Snapshots sind die zumeist hohen Kosten für die Neuerstellung (maintaince, refresh). Schon die Änderung eines Datensatzes einer in der SQL-Abfrage vorkommenden Tabelle (Basis-Tabelle) kann Datensätze der Materialisierten Sicht ungültig werden lassen. Es ist in der Regel effizienter Änderungen an den Basis-Tabellen inkrementell zu übernehmen, oder aber mit teilweise veralteten Datensätzen weiter zu arbeiten und die ungültigen Datensätze nachträglich zu aktualisieren. In dem Datenbanksystem von Oracle wird die Aktualisierung von Materialisierten Sichten automatisch durchgeführt. Dazu werden in Logdateien die geänderten Datensätze einer Basis-Tabelle aufgenommen. Dieser Mechanismus fehlt in PostgreSQL. Mittels Funktionen und Triggern lässt sich dies aber emulieren.

Trigger

Trigger lösen Aktionen bei Eintreten bestimmter Ereignisse an Datenbanktabellen aus. So kann das Hinzufügen, Ändern oder Löschen eines Datensatzes in einer Tabelle eine Aktion auslösen, das durch einen entsprechenden Trigger definiert wurde. Änderungen an Datensätzen der Basis-Tabellen können mittels Trigger die Aktualisierung der Materialisierten Sicht nachsichziehen.

Aus dem ER-Diagramm des Beispieles können die notwendigen Datenbankoperation bei insert-, update- oder delete-Anweisungen an einer Tabelle abgelesen werden. Die Fremdschlüssel in den abhängigen Tabellen tours, events und bookings setzen ohnehin Löschungen voraus, falls ein dazugehöriger Primärschlüssel gelöscht werden soll.

Die Neuanlage einer Organisation, einer Verantstaltung oder eines Kunden führt in keinem Fall zur Änderung der Materialisierten Sicht, da die dazugehörigen Events fehlen. Gleiches gilt für das Ändern von Kundendaten. Aus dem Diagramm leiten sich folgende Trigger zur Aktualisierung der Materialisierten Sicht ab.

create trigger trig_organisations after update on organisations
  for each row execute procedure trig_organisations_refresh();

create trigger trig_tours after update on tours
  for each row execute procedure trig_tours_refresh();

create trigger trig_events after insert or update or delete on events
  for each row execute procedure trig_events_refresh();

create trigger trig_bookings after insert or update or delete on bookings
  for each row execute procedure trig_events_refresh();

In PostgreSQL ruft jeder Trigger bei Eintreten definierter Ereignisse eine sogenannte Triggerfuntion auf. So wird beispielsweise beim Einfügen, Aktualisieren oder Löschen eines Events die Triggerfunktion trig_events_refresh für jeden veränderten Datensatz (row) einmalig aufgerufen. Die durch Ausführen von Triggerfunktionen verursachten Kosten summieren sich über die Anzahl der Funktionsaufrufe. Diese Folgekosten müssen bei der Implementierung der Funktionen berücksichtigt werden.

Cache Control

Die Aktualisierung eines Datensatzes der Materialisierten Sicht tours_available_materialized wird durch die Funktion tours_available_materialized_refresh_row vorgenommen. Diese löscht zunächst einen Datensatz aus der Materialisierten Sicht, um danach den neu berechneten Datensatz hinzuzufügen. Diese Funktion abstrahiert von den Feldern der Materialisierten Sicht durch Behandlung von Datensätzen.

create function tours_available_materialized_refresh_row(v_eid integer)
returns tours_available_materialized
language plpgsql
as
$$
declare
  v_ret tours_available_materialized;
begin
  delete from tours_available_materialized where eid = v_eid;
  insert into tours_available_materialized
       select * from tours_available where eid = v_eid  returning * into v_ret;
  return v_ret;
end;
$$;

Um das Auffinden eines Events zu Beschleunigen werden folgende Indexe angelegt.

create index idx_eid_bookings on bookings (eid);
create index idx_eid_tours_available_materialied on tours_available_materialized (eid);

Die Kosten für die einmalige Ausführung dieser Funktion sind gering.

explain analyze select tours_available_materialized_refresh_row(10000);

Total runtime: 1.484 ms

Bei Mehrfachausführung summieren sich die Kosten. Sollen Datensätze der Materialisierten Sicht später aktualisiert werden, so werden diese mittels der Funktion tours_available_materialized_invalidate_row als ungültig markiert (invalid=true).

create function tours_available_materialized_invalidate_row(v_eid integer)
returns tours_available_materialized
language plpgsql
as
$$
declare
  v_ret tours_available_materialized;
begin
  update tours_available_materialized
  set invalid = true
  where eid = v_eid
  returning * into v_ret; 

  return v_ret;
end;
$$;

Die Aktualisierung aller veralteten Datensätze kann periodisch oder ereignisgesteuert nachgeholt werden. Folgende Abfrage kann periodisch durchgeführt werden.

select tours_available_materialied_refresh_row(eid)
from tours_available_materialized
where invalid=true; 

Wird als Ergebnis einer SQL-Abfrage nur gültige Datensätze vorausgesetzt, können diese hinzugelesen werden. Die Sicht tours_available_materialized_updater ersetzt die ungültigen Datensätze aus der Materialisierten Sicht mit den gültigen Datensätzen aus der Sicht tours_available. Nebenläufig wird die Materialisierte Sicht durch Verwendung der Funktion tours_available_materialized_refresh_row aktualisiert.

create view tours_available_materialized_updater as
select * 
  from tours_available_materialized 
  where invalid=false
  union all
  select (ro).* from (
    -- (function(arg)).* calls function column-number times
    select tours_available_materialized_refresh_row(eid) as ro
    from tours_available_materialized
    where invalid=true
  ) as subsl;

Folgender Index optimiert die Abfrage der Sicht.

create index idx_invalid_tours_available_materialized on tours_available_materialized (invalid) where invalid=true;

Cache Policy

Um die Mehrkosten durch Verwendung der oben beschriebenen Trigger abzuschätzen, werden die notwendigen Datenbankoperationen zur Aktualisierung der Materialisierten Sicht klassifiziert. So führt beispielsweise die Terminverlegung für einen Event zu einer Aktualisierung in der Materialisierten Sicht (1:1 Update). Die Namensänderung einer Organisation führt zu N-Änderungen an der Sicht (1:N Update). Die Stornierung von N Buchungen zu einem Event führt hingegen zu lediglich einer Aktualisierung eines Datensatzes in der Materialisierten Sicht (N:1 Update).

Mit diesem Wissen können nun die Triggerfunktionen implementieret werden. Das Hinzufügen, Aktualisieren oder Löschen eines Events oder einer Buchung wird direkt auf die Materialisierte Sicht übertragen.

create function trig_events_refresh()
returns trigger
language plpgsql
as
$$
begin
  case TG_OP
    when 'DELETE' then
      perform tours_available_materialized_refresh_row(old.eid);
      return old; 
    when 'UPDATE' then
      if new.eid <> old.eid then
        perform tours_available_materialized_refresh_row(old.eid);
      end if;
      perform tours_available_materialized_refresh_row(new.eid);
      return new;
    else
      perform tours_available_materialized_refresh_row(new.eid);
      return new;
  end case; 
end;
$$;

Änderungen an Organisationen und Veranstaltungen sind seltener und sollen nachträglich in die Materialisierte Sicht übernommen werden. Im Folgenden ist einfachheitshalber nur die Triggerfunktion für Organisationen angegeben.

create function trig_organisations_refresh()
returns trigger
language plpgsql
as
$$
begin
  perform tours_available_materialized_invalidate_row(eid)
    from tours_available_materialized where oid in (new.oid, old.oid);
  return new;
end;
$$;

Ergebnisse

Der Mehraufwand zur Erstellung und Aktualisierung einer Materialisierten Sicht zahlt sich aus, wie folgender Vergleich zeigt. Dazu wird die Menge der noch buchbaren Veranstaltungen von Morgen betrachtet.

explain analyze select * 
from tours_available
where start_at between current_date + 1 and current_date + 2
and available > 0;

Total runtime: 5473.173 ms

Die Laufzeit bei Abfrage der Sicht beträgt 5473ms. Die gleiche Abfrage über die Materialisierte Sicht benötigt hingegen nur 6.787ms.

explain analyze select *
from tours_available_materialized
where start_at between current_date + 1 and current_date + 2 and available > 0;

Total runtime: 6.787 ms

Dieses brilliante Ergebnis setzt allerdings einen Index für den Zeitpunkt des Events voraus.

create index idx_start_at_tours_available_materialied on tours_available_materialized (start_at);

Fazit

In PostgreSQL müssen Materialisierte Sichten bisher emuliert werden. Dazu wird das Ergebnis einer SQL-Abfrage in einer Tabelle gespeichert und bei Bedarf aktualisiert wird. Da eine Neuerstellung meist zu kostenintensiv ist, werden die Aktualisierungen meist inkrementell vorgenommen. Mittels Trigger für Ereignisse an den Basis-Tabellen kann die Aktualisierung vorgenommen oder vorgemerkt werden. Da für die Aktualisierung der Materialisierten Sicht Folgekosten für einen Klienten entstehen können, muss die ereignisgesteuerte Aktualisierung abgewogen werden. Die Datensätze können anstelle dessen auch nachträglich aktualisiert werden.

Der Mehraufwand zur Erstellung Materialisierter Sichten lohnt. Die Laufzeit einer Abfrage konnte auf 1/800 reduziert werden. Bei einer hohen Abfragedichte ist der Einsatz von Materialisierten damit unerlässlich. Dies gilt jedoch nur bei einer effizienten Implementierung, inklusive notwendiger Optimierungen des Vorsystems.