SQL Für Fortgeschrittene Teil 1

Mir ist kein Titel eingefallen und es sollte kein Clickbait sein - gut ich ich habe habe nichts von den Klicks. SQL von Ahnungslosen wäre auch ein guter Titel.

Ausgangspunkt

Eine Webanwendung, welche ein Java Backend hat. Als Datenbank wird PostgreSQL 10 benutzt. Die Anwendung ist im produktiven Einsatz.

CREATE TABLE foo (
    special_id1  BIGINT,
    special_id2  VARCHAR(255),
    date_        DATE,
    time_        TIME WITHOUT TIME ZONE,
    data         TEXT,
    PRIMARY KEY  (special_id1, special_id2, date_, time_)
);
CREATE INDEX idx_foo ON foo (date_);
CREATE INDEX idx_bar ON foo (time_);

Die problematische Anfrage sieht wie folgt aus:

SELECT
    (date_ + time_) AS date_,
    CAST (data AS INTEGER) AS data
FROM
    foo
WHERE
    '2019-10-16 13:45'::timestamp < (date_ + time_) AND
    (date_ + time_) < '2019-10-23 20:15'::timestamp AND
    CAST (data AS INTEGER) <> -2147483648 AND
    special_id2 = 'diese ID ganz toll'
;

Das ganze sieht noch nicht so gefährlich aus, obwohl es mir weh tut, dass data vom Typ text ist. Es werden nur Integer gespeichert. Es gibt auch keinen Grund, weswegen date_ und time_ separat gespeichert werden¹. Die Indizes idx_foo und idx_bar sind vollkommen nutzlos, auch für die anderen Anfragen. Der Primärschlüssel ist extrem groß und bringt keine Vorteile. Er ist ca. 30% größer als die Tabelle. Dabei hat er noch fachliche Fehler, special_id2 muss eindeutig sein.

Die Tabelle foo hat über eine Millarde Zeilen. Es gibt ein paar 100 special_id1 und special_id2 Kombinationen. Für jede Kombination wird in unterschiedlichen Intervallen ein neuer Datenpunkt erzeugt. Das Intervall ist zwischen wenigen Sekunden und unter 10 Minuten. Man hat einen bunten Blumenstrauß an Datensätzen. Es kam der Punkt an dem die Platten der Datenbank voll wurden. Die Anwendung hat zwischen 5 und 30 Minuten für die Anfragen benötigt². Man war sich einig, dass der Datenbankserver das Problem ist. Das ganze lässt sich nur mit Partionierung und mehr Hardware lösen.

Lösung

Ich möchte die Größe der Tabelle reduzieren, zweckmäßige Datentypen verwenden, sinnvolle Indizes einführen und die fachlichen Anforderungen sicherstellen. Das ganze ist relativ einfach, dazu muss man die Tabelle normalisieren.

CREATE TABLE id_table (
    id              INT GENERATED BY DEFAULT AS IDENTITY,
    special_id1     BIGINT,
    special_id2     VARCHAR(40),
    PRIMARY         KEY(id),
    UNIQUE(special_id2)
);

CREATE TABLE better_foo (
    data            INT NULL,
    id_table_id     INT NOT NULL REFERENCES id_table(id),
    date_           TIMESTAMP  NOT NULL,
    PRIMARY KEY(id_table_id, date_)
);

Durch die Aufteilung der Daten in 2 Tabellen werden die Tabellen kleiner. Das gilt auch für die Indizes. Im Zuge der Optimierung wurde festgestellt, dass man nur Aggegate benötigt. Diese habe ich auch in der Datenbank berechnet. Dadurch müssen nicht Tonnen an Daten verschickt werden.

SELECT
    q.date_,
    q.data
FROM (
    SELECT
        width_bucket(
            extract(epoch from date_),
            extract(epoch from '2019-10-16 13:45'::timestamp),
            extract(epoch from '2019-10-23 20:15'::timestamp),
            100                                             -- maximum rows in the result set
        ) AS bucket_no,
        MIN(date_) + ((MAX(date_)-MIN(date_))/2) AS date_,  -- middle of the date range
        AVG(data) as data
    FROM
        better_foo
    WHERE
        '2019-10-16 13:45'::timestamp < date_ AND
        date_ < '2019-10-23 20:15'::timestamp AND
        data  <> -2147483648 AND
        id_table_id = (
            SELECT id FROM id_table WHERE special_id2 = 'diese ID ganz toll'
        )
    GROUP BY
        bucket_no
    ORDER BY
        bucket_no
    ) AS q
;

Erklärung

• extract(epoch from '2019-10-23 20:15'::timestamp) man erhält einen Integer (Unix Timestamp)
• width_bucket(v, min_value, max_value, n) teilt ein Intervall [min_value, max_value] in n äquidistante Teilintervale. Rückgabewert ist das Bucket/Intervall in dem v einsortiert wird.

Die Idee ist, dass man den Datumsbereich z.B. in 100 gleiche Intervalle aufteilt. Für jeden Datensatz, welcher in der Intervall fällt und die passende id_table_id hat, wird eine Bucket-Nummer bestimmt. Alles Datensätze mit der gleichen Nummer werden aggregiert. Die Mitte von dem Intervall ist der neue Zeitpunkt. Das äußere SELECT ist nur nötig, damit die Ergebnismenge gleich aussieht. In der Bedingung wird werden die Spalten id_table_id und date_ verwendet. Dafür haben wir den Primärschlüssel³.

Fazit

Durch den Umbau schrumpfte der Platzbedarf für Indizes und Tabellen auf ca. 25% und die maximale Anfragezeit liegt bei ca. 10 Sekunden. Wenn die Datenbank warm ist, dann sind Zeiten weiter unter einer Sekunde keine Seltenheit. Die Anwendung braucht mit den Daten nichts mehr zu machen. Die Entwickler haben geflucht, weil meine kreative Anfrage nicht vom OR Mapper vorgesehen war.

Seit SQL ‘92 hat sich eine Menge geändert⁴. Jeder, der eine SQL Datenbank nutzt, sollte sich mit seiner Datenbank und den Anfragen auseinander setzen. Die OR Mapper nehmen einen das nachdenken nicht ab. Wenn man eine gute Anfrage hat, dann ist es egal wie groß die Datenbank ist. Ich hätte nicht mit so einer großen Verbesserung gerechnet. Deswegen werde ich mich nicht intensiver mit Partionierung bei PostgreSQL beschäftigen. Es gibt für mich kein Problem⁵, welches ich damit lösen kann.