PostgreSQL Wie man Katzen oder die Geschichte einer Migration speichert

    Die Geschichte stammt aus einem echten Projekt. Da das eigentliche Projekt jedoch zu langweilig ist (und der NDA unterliegt), wird in diesem Artikel ein vereinfachtes Beispiel verwendet.

    Es war einmal ein Projekt. Und er hatte eine Datenbank. In der Datenbank befand sich eine Tabelle, in der beispielsweise Katzen aufbewahrt wurden. Hier ist einer:
    CREATE TABLE cats (
        id serial,
        cname varchar(20),
        ctype varchar(20),
        primary key(id)
    );
    

    Alles ist ganz einfach: Jede Katze hat eine ID, einen Namen und einen bestimmten Typ.

    Natürlich hatten wir geschäftliche Anforderungen an Katzen und ihre Typen. Zum Beispiel wussten wir mit Sicherheit, dass wir Typen mit großem Fell , Neko und plötzlicher Gefahr haben . Es wurde vermutet, dass die Typen Langschwanz und Schläferfresser auftreten könnten . Wir haben aber damit gerechnet, dass sich die Anforderungen ändern werden. Und es ist noch nicht bekannt, welche Typen irgendwann benötigt werden. Daher wurde der Datentyp varchar verwendet (20) .

    Nach langer Zeit und mehreren Veröffentlichungen haben wir endlich eine genaue Liste der Katzentypen zusammengestellt. Zu diesem Zeitpunkt hatte der Tisch bereits mehrere zehn Millionen Katzen mit einer Vielzahl von Typen, von denen viele veraltet sind. Es war notwendig aufzuräumen, alle Werte in der Tabelle in Übereinstimmung mit den neuen Anforderungen zu bringen.



    Deshalb wenden wir den Engineering-Ansatz an:
    • eine Theorie aufbauen
    • wir werden es mit Experimenten testen,
    • auf der Grundlage der Theorie eine praktische Lösung entwickeln,
    • Wir wenden die Lösung an und bewerten das Ergebnis.

    Eine Theorie aufbauen



    Erstellen Sie einen ENUM-Datentyp und listen Sie die gültigen Werte darin auf. Dann werden wir die Migration durchführen:
    CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
                                  'crafty hunter', 'sudden danger', 'sleeper-eater');
    ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
    

    Wir wissen noch nicht, dass die Migration in dieser Form nicht funktionieren wird. Ungültige Werte wurden vergessen, die bereits in der Tabelle vorhanden sind. Wir werden später darüber erfahren, wenn wir versuchen, Migration anzuwenden =)

    Wir werden daher die Erstellung von Katzen mit einem ungültigen Typwert verbieten. Reduzieren Sie außerdem die Größe der Tabelle und den Index für das Feld ctype. Die Größe der Tabelle ist nicht so wichtig, aber die Reduzierung des Index ist gut. Wir haben uns bereits mit Indizes befasst, die nicht in den Arbeitsspeicher passen. Und das sind, gelinde gesagt, nicht sehr nützliche Indizes.

    Lassen Sie uns abschätzen, mit welchem ​​Speichergewinn zu rechnen ist.

    Um einen Wert vom Typ varchar zu speichern, werden 1-4 Bytes pro Zeichen (abhängig von der Kodierung) und weitere 1 oder 4 Bytes zum Speichern der Länge der Zeichenfolge zugewiesen (weitere Einzelheiten hier www.postgresql.org/docs/current/static/datatype-character.html)) In unserem Fall ist es 1 Byte pro Zeichen (utf8, lateinische Buchstaben) und 1 Byte pro Zeilenlänge. Zeichenfolgen haben eine Länge von 9-14 Zeichen. Wir gehen davon aus, dass wir durchschnittlich 12 Bytes pro Wert haben.
    > select pg_column_size('big furry');
    10
    > select pg_column_size('sleeper-eater');
    14
    

    Es ist bekannt, dass Aufzählungswerte unabhängig von ihrer Länge 4 Byte belegen.
    > select pg_column_size('big furry'::cat_type);
    4
    > select pg_column_size('sleeper-eater'::cat_type);
    4
    

    Eine Zeile in der Tabelle lautet:
    • 27 Bytes pro Kopfzeile,
    • 8 bytes id
    • 21 Bytes cname (wir nehmen an, dass alle Katzen jeweils 20 Zeichen haben),
    • 12 Bytes ctype

    Gesamt: 68 Bytes.

    Nach der Migration sind 27 + 8 + 21 + 4 = 60 Bytes vorhanden. Der Unterschied ist gering, aber für 50 Millionen Leitungen sollte der Gesamtgewinn erheblich sein.
    Wir haben 2 Indizes, nach ID und nach C-Typ. Der Index für die ID ändert sich nicht. Der ctype-Index sollte abnehmen. Wir wissen nicht, wie der Indexspeicher angeordnet ist, aber wir gehen davon aus, dass sich der Index 2-3 mal verringert, wenn ein Wert dreimal verringert wird.

    Versuch Nr. 1


    Erstellen Sie für das Experiment zwei Tabellen:
    CREATE TABLE cats1 (
        id serial,
        name varchar(20),
        type varchar(20),
        primary key(id)
    );
    

    CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
                                  'crafty hunter', 'sudden danger', 'sleeper eater');
    CREATE TABLE cats2 (
        id serial,
        name varchar(20),
        type cat_type,
        primary key(id)
    );
    

    Füllen Sie sie mit Testdaten:
    CREATE SEQUENCE ss;
    INSERT INTO cats1 (name, type)
        SELECT
            substring(md5(random()::text), 0, 20),
            (ARRAY['big furry', 'small red', 'long tail',
                   'crafty hunter', 'sudden danger', 'sleeper eater'])
                [nextval('ss') % 5 + 1]
        FROM
            generate_series(1, 500000);
    INSERT INTO cats2 (name, type)
        SELECT
            substring(md5(random()::text), 0, 20),
            ((ARRAY['big furry', 'small red', 'long tail',
                    'crafty hunter', 'sudden danger', 'sleeper eater'])
                [nextval('ss') % 5 + 1])::cat_type
        FROM
            generate_series(1, 500000);
    

    Ja, die Namen unserer Katzen sind ziemlich seltsam. Aber für das Experiment geeignet.

    Erstellen Sie die Indizes:
    CREATE INDEX cats1_index ON cats1(type);
    CREATE INDEX cats2_index ON cats2(type);
    

    Und mal sehen, wie viel Speicher sie in Anspruch genommen haben:
    SELECT pg_relation_size('cats1') AS table_size,
           pg_indexes_size('cats1') AS indexes_size;
    SELECT pg_relation_size('cats2') AS table_size,
           pg_indexes_size('cats2') AS indexes_size;
    

    Theoretisch belegen die Zeilen in der ersten Tabelle 68 * 500.000 = 34.000.000 Bytes, in der zweiten Tabelle 60 * 500.000 = 30.000.000 Bytes. In der Praxis sehen wir 34.136.064 und 30.121.984 Bytes. Die Zahlen sind nah.

    Es ist klar, dass eine Tabelle nacheinander komplexer ist als nur 500.000 Zeilen. Es sind Speicherseiten von 8 KB zugeordnet. Seiten haben eigene Überschriften und andere Metainformationen. Und die Werte in den Zeilen stimmen irgendwie überein (weitere Details hier www.postgresql.org/docs/9.5/static/storage-page-layout.html ).

    Aber was ist mit Indizes? Pg_indexes_size-
    FunktionZeigt den gesamten Speicherverbrauch für alle mit der Tabelle verknüpften Indizes und nicht für jeden einzeln an. Dies spielt jedoch keine Rolle, wir können es vor dem Erstellen des Index nach ctype und danach aufrufen. Und dann werden wir sehen, dass der Index nach ID 11.255.808 Bytes beträgt, und die C-Typ-Indizes für die erste Tabelle sind 15.794.176 Bytes, und für die zweite Tabelle sind es 11.255.808 Bytes.
    Merklich weniger, aber nicht 2-3 mal, wie wir erwartet hatten. Warum so?

    Versuch 2


    Lassen Sie uns einige einfache Tabellen erstellen, die nur eine Spalte enthalten:
    CREATE TABLE t_bool (f bool);
    CREATE TABLE t_sint (f smallint);
    CREATE TABLE t_int (f int);
    CREATE TABLE t_bint (f bigint);
    CREATE TABLE t_c7 (f char(7));
    CREATE TABLE t_c8 (f char(8));
    CREATE TABLE t_c9 (f char(9));
    CREATE TABLE t_c15 (f char(15));
    CREATE TABLE t_c16 (f char(16));
    CREATE TABLE t_c20 (f char(20));
    

    Füllen Sie sie mit Daten:
    INSERT INTO t_bool (f)
        SELECT true FROM generate_series(1, 500000);
    INSERT INTO t_sint (f)
        SELECT 1 FROM generate_series(1, 500000);
    ...
    INSERT INTO t_c7 (f)
        SELECT 'abcdefg' FROM generate_series(1, 500000);
    ...
    INSERT INTO t_c20 (f)
        SELECT 'abcd efgh abcd efgh ' FROM generate_series(1, 500000);
    

    Erstellen Sie die Indizes:
    CREATE INDEX ON t_bool(f);
    CREATE INDEX ON t_sint(f);
    ...
    CREATE INDEX ON t_c20(f);
    

    Und mal sehen, wie viel Platz die Tabelle und der Index einnehmen:
    Datentyp
    Einzelbyte
    Tischgröße
    Indexgröße
    Scheiße
    1
    18,128,896
    11,255,808
    smallint
    2
    18,128,896
    11,255,808
    int
    4
    18,128,896
    11,255,808
    bigint
    8
    18,128,896
    11,255,808
    char (7)
    8
    18,128,896
    11,255,808
    char (8)
    9
    22,142,976
    15,794,176
    char (9)
    10
    22,142,976
    15,794,176
    char (15)
    16
    22,142,976
    15,794,176
    char (16)
    17
    26.091.520
    20,332,544
    char (20)
    21
    26.091.520
    20,332,544

    Wir sehen, dass die Größe der Tabelle und des Index in den Bereichen von 1-8 Bytes, 9-16 Bytes und mehr als 16 Bytes gleich ist.
    Kleinere Optimierungen wie das Ersetzen von int durch smallint scheinen wenig zu nützen. Nun, außer in einigen Fällen, in denen eine Tabelle viele Spalten enthält, die auf diese Weise optimiert werden können.
    Das Ersetzen von varchar durch enum ergibt eine Verstärkung, wenn der varchar-Wert im Durchschnitt mehr als 8 Byte (länger als 7 Zeichen) beträgt.

    Wir entwickeln eine praktische Lösung


    Jetzt wissen wir, was uns in der Praxis erwartet, und wir sind bereit, unsere Migration zu realisieren.
    Wir kehren zu unseren Katzen zurück:
    CREATE TABLE cats (
        id serial,
        cname varchar(20),
        ctype varchar(20),
        primary key(id)
    );
    CREATE INDEX c1 ON cats(ctype);
    

    Wir füllen die Tabelle mit Daten, sodass sie ungültige und NULL-Werte enthält.
    CREATE SEQUENCE ss;
    INSERT INTO cats (cname, ctype)
        SELECT
            substring(md5(random()::text), 0, 20),
            (ARRAY['big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger',
                   'sleeper-eater', 'black eye', 'sharp claw', 'neko', NULL])
                [nextval('ss') % 10 + 1]
        FROM
            generate_series(1, 500000);
    

    Migrationsversuch:
    CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
                                  'crafty hunter', 'sudden danger', 'sleeper-eater');
    ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
    

    Und wir stellen fest, dass unser naiver ALTER TABLE nicht funktioniert:
    ERROR:  invalid input value for enum cat_type: "black eye"
    

    Und Sie müssen eine Funktion schreiben, um den Typ zu konvertieren:
    CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS
    $$
    DECLARE res cat_type;
    BEGIN
        CASE ctype
            WHEN 'big furry' THEN res := 'big furry';
            WHEN 'small red' THEN res := 'small red';
            WHEN 'long tail' THEN res := 'long tail';
            WHEN 'crafty hunter' THEN res := 'crafty hunter';
            WHEN 'sudden danger' THEN res := 'sudden danger';
            WHEN 'sleeper-eater' THEN res := 'sleeper-eater';
            ELSE res := NULL;
        END CASE;
        RETURN res;
    END
    $$
    LANGUAGE plpgsql;
    

    Und versuchen Sie es noch einmal:
    ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING cast_to_cat_type(ctype);
    

    Diesmal hat es geklappt. Nur jemandem eine solche Funktion zu zeigen, ist peinlich. Oh, anscheinend habe ich gerade meine heimliche Vorliebe für Copy-Paste verraten =) Shh, tun wir mal so, als hätte ich das nicht geschrieben, aber du hast es nicht gesehen, ok? Und ich werde auf andere Weise schreiben:
    CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS
    $$
    DECLARE res cat_type;
    BEGIN
        BEGIN
            res := ctype::cat_type;
        EXCEPTION WHEN others THEN
            res := NULL;
        END;
        RETURN res;
    END
    $$
    LANGUAGE plpgsql;
    

    Dies kann sicher zur Codeüberprüfung gesendet werden.

    Bewerten Sie das Ergebnis


    Was haben wir als Ergebnis bekommen? Tabellengrößen und Indizes vor der Migration: 33.038.336 und 26.140.672 Byte. Nach der Migration: 28.581.888 und 22.511.616 Byte. Angesichts der Tatsache, dass wir in einer realen Tabelle nicht 500.000 Datensätze, sondern 50 Millionen haben, wird der Gewinn erheblich sein.
    Aber unter bestimmten Bedingungen können Sie noch mehr gewinnen. Angenommen, ein Unternehmen ist nicht an Katzen des falschen oder unbekannten Typs interessiert, sie werden in Anfragen ausgeschlossen. Dann können Sie sie aus dem Index ausschließen.

    Wir verwenden den Teilindex :
    CREATE index CONCURRENTLY c2 ON cats(ctype) WHERE ctype IS NOT NULL;
    DROP index c1;
    

    Und jetzt ist die Indexgröße 18.014.208 Bytes. Hier hängt natürlich alles davon ab, wie viele Katzen sich als falsch erwiesen haben.
    Eine merkwürdige Frage ist, was als nächstes mit den falschen Katzen zu tun ist. Dies ist jedoch eine geschäftliche Frage und keine Frage für den Entwickler.

    Es muss weiterhin sichergestellt werden, dass die richtigen Werte in die Tabelle eingefügt werden und dass keine falschen eingefügt werden:
    > INSERT INTO cats (cname, ctype) VALUES ('Murzik', 'crafty hunter'), ('Vasjka', 'sleeper-eater');
    INSERT 0 2
    > INSERT INTO cats (cname, ctype) VALUES ('Sharik', 'big dog');
    ERROR:  invalid input value for enum cat_type: "big dog"
    

    Alles funktioniert wie es sollte.

    Wir haben noch ein paar interessante Geschichten über Migration und wie schwierig es ist, mit großen Tischen zu leben. Lass uns das nächste Mal darüber reden.

    Yura Zhloba,
    Webentwickler.

    Jetzt auch beliebt: