Im Zuge der Anwendungsentwicklung sind Datenbanken das Herzstück der meisten Anwendungen. Sie speichern und verwalten Daten, die für digitale Unternehmen wichtig sind. Da diese Daten wachsen und immer komplexer werden, ist es wichtig, dass du die Effizienz deiner Datenbank sicherstellst, um die Anforderungen deiner Anwendung zu erfüllen.

Hier kommt die Idee der Datenbankwartung ins Spiel. Zur Datenbankwartung gehören Aufgaben wie Reinigung, Backups und die Optimierung von Indizes, um die Leistung zu steigern.

Dieser Artikel bietet wertvolle Einblicke in die Wartungstrigger und stellt praktische Anleitungen zur Einrichtung vor. Er erklärt, wie man verschiedene Datenbankwartungsaufgaben wie Datensicherung, Neuaufbau von Indizes, Archivierung und Datenbereinigung mit PostgreSQL, integriert mit einem API-Trigger in einer Node.js-Anwendung, durchführt.

Trigger verstehen

Bevor du Wartungsvorgänge für deine Datenbank erstellst, ist es wichtig, die verschiedenen Möglichkeiten, wie sie ausgelöst werden können, zu verstehen. Jeder Trigger dient einem bestimmten Zweck, um die Wartungsaufgaben zu erleichtern. Die drei wichtigsten Trigger, die häufig verwendet werden, sind:

  • Manuell, API-basiert: Dieser Trigger ermöglicht es dir, einmalige Vorgänge über einen API-Aufruf auszuführen. Er ist z. B. nützlich, um ein Datenbank-Backup wiederherzustellen oder Indizes neu aufzubauen, wenn die Leistung plötzlich abfällt.
  • Zeitplan (wie CRON): Mit diesem Trigger kannst du geplante Wartungsaktivitäten in Zeiten mit geringem Nutzeraufkommen automatisieren. Er ist ideal für ressourcenintensive Vorgänge wie Archivierung und Bereinigung. Mit Paketen wie node-schedule kannst du Zeitpläne in Node.js einrichten, die die Vorgänge bei Bedarf automatisch auslösen.
  • Datenbank-Benachrichtigungen: Dieser Trigger ermöglicht es dir, Wartungsarbeiten als Reaktion auf Datenbankänderungen durchzuführen. Wenn zum Beispiel ein Nutzer einen Kommentar auf einer Plattform postet, können die gespeicherten Daten sofort eine Überprüfung auf unregelmäßige Zeichen, anstößige Sprache oder Emojis auslösen. Diese Funktion lässt sich in Node.js mit Paketen wie pg-listen implementieren.

Voraussetzungen

Um diesem Leitfaden folgen zu können, solltest du die folgenden Tools auf deinem lokalen Computer haben:

  • Git: Um die Versionskontrolle für den Quellcode deiner Anwendung zu verwalten
  • Node.js: Für die Erstellung deiner Backend-Anwendung
  • psql: Für die Interaktion mit deiner PostgreSQL-Datenbank über dein Terminal
  • PGAdmin (optional): Für die Interaktion mit deiner PostgreSQL-Datenbank über eine grafische Benutzeroberfläche (GUI).

Erstellen und Hosten einer Node.js-Anwendung

Richte ein Node.js-Projekt ein, übertrage es auf GitHub und richte eine Auto-Deploy-Pipeline zu Kinsta ein. Du musst auch eine PostgreSQL-Datenbank auf Kinsta einrichten, um deine Wartungsroutinen darauf zu testen.

Beginne damit, ein neues Verzeichnis auf deinem lokalen System mit dem folgenden Befehl zu erstellen:

mkdir node-db-maintenance

Wechsle dann in das neu erstellte Verzeichnis und führe den folgenden Befehl aus, um ein neues Projekt zu erstellen:

cd node-db-maintenance
yarn init -y # or npm init -y

Dadurch wird ein Node.js-Projekt mit der Standardkonfiguration für dich initialisiert. Du kannst nun die notwendigen Abhängigkeiten installieren, indem du den folgenden Befehl ausführst:

yarn add express pg nodemon dotenv

Hier ist eine kurze Beschreibung der einzelnen Pakete:

  • express: ermöglicht es dir, eine Express-basierte REST-API einzurichten.
  • pgmit diesem Paket kannst du über deine Node.js-Anwendung mit einer PostgreSQL-Datenbank interagieren.
  • nodemonmit diesem Paket kannst du deinen Dev-Build während der Entwicklung deiner Anwendung aktualisieren, sodass du deine Anwendung nicht jedes Mal stoppen und starten musst, wenn du eine Änderung vornimmst.
  • dotenv: ermöglicht es dir, Umgebungsvariablen aus einer .env-Datei in dein process.env Objekt zu laden.

Als Nächstes fügst du die folgenden Skripte in deine package.json-Datei ein, damit du deinen Entwicklungsserver einfach starten und deinen Server auch in der Produktion betreiben kannst:

{
    // ... 
    "scripts": {
      "start-dev": "nodemon index.js",
      "start": "NODE_ENV=production node index.js"
  },
  // …
}

Du kannst nun eine index.js-Datei erstellen, die den Quellcode deiner Anwendung enthält. Füge den folgenden Code in diese Datei ein:

const express = require("express")
const dotenv = require('dotenv');

if (process.env.NODE_ENV !== 'production') dotenv.config();
const app = express()
const port = process.env.PORT || 3000

app.get("/health", (req, res) => res.json({status: "UP"}))

app.listen(port, () => {
    console.log(`Server running at port: ${port}`);
});

Der obige Code initialisiert einen Express-Server und konfiguriert die Umgebungsvariablen mit Hilfe des dotenv Pakets, wenn es sich nicht im Produktionsmodus befindet. Er richtet auch eine /health Route ein, die ein JSON-Objekt {status: "UP"} zurückgibt. Schließlich wird die Anwendung mit der Funktion app.listen() gestartet, um auf dem angegebenen Port zu lauschen. Wenn kein Port über die Umgebungsvariable angegeben wird, wird standardmäßig 3000 verwendet.

Nachdem du nun eine einfache Anwendung erstellt hast, initialisiere ein neues Git-Repository mit deinem bevorzugten Git-Anbieter (BitBucket, GitHub oder GitLab) und veröffentliche deinen Code. Kinsta unterstützt die Bereitstellung von Anwendungen über all diese Git-Anbieter. Für diesen Artikel verwenden wir GitHub.

Wenn dein Repository fertig ist, befolge die folgenden Schritte, um deine Anwendung auf Kinsta bereitzustellen:

  1. Melde dich an oder erstelle ein Konto, um dein MyKinsta-Dashboard zu sehen.
  2. Autorisiere Kinsta mit deinem Git-Anbieter.
  3. Klicke in der linken Seitenleiste auf Anwendungen und dann auf Anwendung hinzufügen.
  4. Wähle das Repository und den Zweig aus, von dem aus du bereitstellen möchtest.
  5. Wähle einen der verfügbaren Rechenzentrumsstandorte aus der Liste der 37 Optionen aus. Kinsta erkennt automatisch die Build-Einstellungen für deine Anwendungen über Nixpacks.
  6. Wähle die Ressourcen für deine Anwendung, z. B. RAM und Festplattenplatz.
  7. Klicke auf Anwendung erstellen.

Sobald die Bereitstellung abgeschlossen ist, kopierst du den Link der bereitgestellten Anwendung und navigierst zu /health. In deinem Browser sollte das folgende JSON angezeigt werden:

{status: "UP"}

Dies zeigt an, dass die Anwendung korrekt eingerichtet wurde.

Einrichten einer PostgreSQL-Instanz auf Kinsta

Kinsta bietet eine einfache Schnittstelle zur Bereitstellung von Datenbankinstanzen. Beginne damit, ein neues Kinsta-Konto zu erstellen, falls du noch keines hast. Befolge dann die folgenden Schritte:

  1. Logge dich in dein MyKinsta-Dashboard ein.
  2. Klicke in der linken Seitenleiste auf Datenbanken und dann auf Datenbank hinzufügen.
  3. Wähle PostgreSQL als Datenbanktyp und wähle deine bevorzugte Version. Wähle einen Namen für deine Datenbank und ändere den Benutzernamen und das Passwort, wenn du möchtest.
  4. Wähle einen Standort für das Rechenzentrum aus der Liste von 37 aus.
  5. Wähle die Größe deiner Datenbank.
  6. Klicke auf Datenbank erstellen.

Sobald die Datenbank erstellt ist, stelle sicher, dass du den Datenbank-Host, den Port, den Benutzernamen und das Passwort abrufst.

Der Bildschirm zeigt den externen Hostnamen, den Port, den Benutzernamen, das Passwort, den Datenbanknamen und einen externen Verbindungsstring für die Datenbank an, die auf Kinsta erstellt wurde.
Von Kinsta generierte Datenbank-Anmeldedaten

Du kannst diese Werte dann in dein psql CLI (oder PGAdmin GUI) eingeben, um die Datenbank zu verwalten. Um deinen Code lokal zu testen, erstelle eine .env-Datei im Hauptverzeichnis deines Projekts und speichere darin die folgenden Secrets:

DB_USER_NAME=your database user name
DB_HOST=your database host
DB_DATABASE_NAME=your database’s name
DB_PORT=your database port
PGPASS=your database password

Wenn du auf Kinsta bereitstellst, musst du diese Werte als Umgebungsvariablen zu deiner Anwendungsimplementierung hinzufügen.

Um die Datenbankoperationen vorzubereiten, lade dieses SQL-Skript herunter und führe es aus, um Tabellen (Benutzer, Beiträge, Kommentare) zu erstellen und Beispieldaten einzufügen. Verwende den folgenden Befehl, indem du die Platzhalter durch deine eigenen Angaben ersetzt, um die Daten zu deiner neu erstellten PostgreSQL-Datenbank hinzuzufügen:

psql -h <host> -p <port> -U <username> -d <db_name> -a -f <sql file e.g. test-data.sql>

Achte darauf, dass du den genauen Dateinamen und den Pfad im obigen Befehl eingibst. Bei der Ausführung dieses Befehls wirst du aufgefordert, dein Datenbankpasswort zur Autorisierung einzugeben.

Sobald dieser Befehl ausgeführt wurde, kannst du mit dem Schreiben von Operationen für die Wartung deiner Datenbank beginnen. Du kannst deinen Code in dein Git-Repository stellen, wenn du mit jeder Operation fertig bist, um ihn auf der Kinsta-Plattform in Aktion zu sehen.

Wartungsroutinen schreiben

In diesem Abschnitt werden mehrere häufig verwendete Operationen für die Wartung von PostgreSQL-Datenbanken erklärt.

1. Backups erstellen

Regelmäßige Backups von Datenbanken sind ein gängiger und wichtiger Vorgang. Dabei wird eine Kopie des gesamten Datenbankinhalts erstellt, die an einem sicheren Ort gespeichert wird. Diese Backups sind wichtig für die Wiederherstellung von Daten im Falle eines versehentlichen Verlusts oder von Fehlern, die die Datenintegrität beeinträchtigen.

Auch wenn Plattformen wie Kinsta automatische Backups als Teil ihrer Dienste anbieten, ist es wichtig zu wissen, wie man bei Bedarf eine eigene Backup-Routine einrichtet.

PostgreSQL bietet das Tool pg_dump für die Erstellung von Datenbanksicherungen. Es muss jedoch direkt von der Kommandozeile aus ausgeführt werden und es gibt kein npm-Paket dafür. Du musst also das Paket @getvim/execute verwenden, um den Befehl pg_dump in der lokalen Umgebung deiner Node-Anwendung auszuführen.

Installiere das Paket, indem du den folgenden Befehl ausführst:

yarn add @getvim/execute

Als Nächstes importierst du das Paket in deine index.js-Datei , indem du diese Codezeile am Anfang einfügst:

const {execute} = require('@getvim/execute');

Die Backups werden als Dateien im lokalen Dateisystem deiner Node-Anwendung erstellt. Daher ist es am besten, wenn du im Stammverzeichnis des Projekts ein eigenes Verzeichnis mit dem Namen backup anlegst.

Jetzt kannst du die folgende Route verwenden, um bei Bedarf Backups deiner Datenbank zu erstellen und herunterzuladen:

app.get('/backup', async (req, res) => {

    // Create a name for the backup file
    const fileName = "database-backup-" + new Date().valueOf() + ".tar";

    // Execute the pg_dump command to generate the backup file
    execute("PGPASSWORD=" + process.env.PGPASS  + " pg_dump -U " + process.env.DB_USER_NAME 
    + " -d " + process.env.DB_DATABASE_NAME 
    + " -h " + process.env.DB_HOST
    + " -p " + process.env.DB_PORT
    + " -f backup/" + fileName + " -F t"

).then(async () => {
        console.log("Backup created");
        res.redirect("/backup/" + fileName)
    }).catch(err => {
        console.log(err);
        res.json({message: "Something went wrong"})
    })

})

Außerdem musst du die folgende Zeile am Anfang deiner index.js-Datei einfügen, nachdem die Express-Anwendung initialisiert wurde:

app.use('/backup', express.static('backup'))

Dadurch kann der Backup-Ordner statisch mit der Middleware-Funktion express.static bedient werden, so dass der Nutzer die erzeugten Backup-Dateien von der Node-Anwendung herunterladen kann.

2. Wiederherstellung aus einem Backup

Postgres ermöglicht die Wiederherstellung von Backups mit dem Kommandozeilentool pg_restore. Allerdings musst du es über das Paket execute verwenden, ähnlich wie du es mit dem Befehl pg_dump gemacht hast. Hier ist der Code für die Route:

app.get('/restore', async (req, res) => {

    const dir = 'backup'

    // Sort the backup files according to when they were created
    const files = fs.readdirSync(dir)
        .filter((file) => fs.lstatSync(path.join(dir, file)).isFile())
        .map((file) => ({ file, mtime: fs.lstatSync(path.join(dir, file)).mtime }))
        .sort((a, b) => b.mtime.getTime() - a.mtime.getTime());

    if (!files.length){
        res.json({message: "No backups available to restore from"})
    }

    const fileName = files[0].file

    // Restore the database from the chosen backup file
    execute("PGPASSWORD=" + process.env.PGPASS  + " pg_restore -cC "
    + "-U " + process.env.DB_USER_NAME
    + " -h " + process.env.DB_HOST
    + " -p " + process.env.DB_PORT
    + " -d postgres backup/" + fileName
)

        .then(async ()=> {
            console.log("Restored");
            res.json({message: "Backup restored"})
        }).catch(err=> {
        console.log(err);
        res.json({message: "Something went wrong"})
    })
})

Das obige Codeschnipsel sucht zunächst nach Dateien, die im lokalen Sicherungsverzeichnis gespeichert sind. Dann sortiert er sie nach dem Datum, an dem sie erstellt wurden, um die neueste Sicherungsdatei zu finden. Schließlich verwendet er das Paket execute, um die ausgewählte Sicherungsdatei wiederherzustellen.

Achte darauf, dass du die folgenden Importe in deine index.js-Datei einfügst, damit die notwendigen Module für den Zugriff auf das lokale Dateisystem importiert werden und die Funktion korrekt ausgeführt werden kann:

const fs = require('fs')
const path = require('path')

3. Einen Index wiederherstellen

Die Indizes von Postgres-Tabellen werden manchmal beschädigt und die Leistung der Datenbank wird beeinträchtigt. Das kann an Software-Bugs oder Fehlern liegen. Manchmal können Indizes auch durch zu viele leere oder fast leere Seiten aufgebläht werden.

In solchen Fällen musst du den Index neu erstellen, um sicherzustellen, dass du die beste Leistung aus deiner Postgres-Instanz herausholst.

Postgres bietet zu diesem Zweck den Befehl REINDEX an. Du kannst das Paket node-postgres verwenden, um diesen Befehl auszuführen (und später auch einige andere Operationen), also installiere es, indem du zuerst den folgenden Befehl ausführst:

yarn add pg

Als Nächstes fügst du die folgenden Zeilen am Anfang der Datei index.js unter den Importen ein, um die Datenbankverbindung korrekt zu initialisieren:

const {Client} = require('pg')
const client = new Client({
    user: process.env.DB_USER_NAME,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE_NAME,
    password: process.env.PGPASS,
    port: process.env.DB_PORT
})



client.connect(err => {
    if (err) throw err;
    console.log("Connected!")
})

Die Implementierung dieses Vorgangs ist recht einfach:

app.get("/reindex", async (req, res) => {

    // Run the REINDEX command as needed
    await client.query("REINDEX TABLE Users;")

    res.json({message: "Reindexed table successfully"})
})

Der oben gezeigte Befehl indiziert die gesamte Tabelle Users neu. Du kannst den Befehl an deine Bedürfnisse anpassen, um einen bestimmten Index neu zu erstellen oder sogar die gesamte Datenbank neu zu indizieren.

4. Archivierung und Bereinigung von Daten

Für Datenbanken, die im Laufe der Zeit sehr groß werden (und auf historische Daten wird nur selten zugegriffen), kann es sinnvoll sein, Routinen einzurichten, die die alten Daten in einen Data Lake auslagern, wo sie bequemer gespeichert und verarbeitet werden können.

Parquet-Dateien sind ein gängiger Standard für die Speicherung und Übertragung von Daten in vielen Data Lakes. Mit der ParquetJS-Bibliothek kannst du Parquet-Dateien aus deinen Postgres-Daten erstellen und Dienste wie AWS Athena nutzen, um sie direkt zu lesen, ohne sie in Zukunft wieder in die Datenbank laden zu müssen.

Installiere die ParquetJS-Bibliothek, indem du den folgenden Befehl ausführst:

yarn add parquetjs

Wenn du Archive erstellst, musst du eine große Anzahl von Datensätzen aus deinen Tabellen abfragen. Das Speichern einer so großen Datenmenge im Speicher deiner Anwendung kann ressourcenintensiv, kostspielig und fehleranfällig sein.

Deshalb ist es sinnvoll, Cursors zu verwenden, um Daten aus der Datenbank zu laden und zu verarbeiten. Installiere das Modul cursors aus dem Paket node-postgres, indem du den folgenden Befehl ausführst:

yarn add pg-cursor

Als nächstes musst du beide Bibliotheken in deine index.js-Datei importieren:

const Cursor = require('pg-cursor')
const parquet = require('parquetjs')

Jetzt kannst du den folgenden Codeschnipsel verwenden, um Parkettdateien aus deiner Datenbank zu erstellen:

app.get('/archive', async (req, res) => {

    // Query all comments through a cursor, reading only 10 at a time
    // You can change the query here to meet your requirements, such as archiving records older than at least a month, or only archiving records from inactive users, etc.
    const queryString = "SELECT * FROM COMMENTS;"

    const cursor = client.query(new Cursor(queryString))

    // Define the schema for the parquet file
    let schema = new parquet.ParquetSchema({
        comment_id: { type: 'INT64' },
        post_id: { type: 'INT64' },
        user_id: { type: 'INT64' },
        comment_text: { type: 'UTF8' },
        timestamp: { type: 'TIMESTAMP_MILLIS' }
    });



    // Open a parquet file writer
    let writer = await parquet.ParquetWriter.openFile(schema, 'archive/archive.parquet');

    let rows = await cursor.read(10)

    while (rows.length > 0) {

        for (let i = 0; i < rows.length; i++) {
            // Write each row from table to the parquet file
            await writer.appendRow(rows[i])
        }

        rows = await cursor.read(10)
    }

    await writer.close()
    
    // Once the parquet file is generated, you can consider deleting the records from the table at this point to free up some space

    // Redirect user to the file path to allow them to download the file
    res.redirect("/archive/archive.parquet")
})

Als Nächstes fügst du den folgenden Code am Anfang deiner index.js-Datei ein, nachdem die Express-Anwendung initialisiert wurde:

app.use('/archive', express.static('archive'))

Damit kann der Archivordner statisch bedient werden, so dass du die erzeugten Parkettdateien vom Server herunterladen kannst.

Vergiss nicht, ein Archivverzeichnis im Projektverzeichnis anzulegen, um die Archivdateien zu speichern.

Du kannst dieses Codeschnipsel weiter anpassen, um die Parquet-Dateien automatisch in einen AWS S3-Bucket hochzuladen und CRON-Jobs zu verwenden, um den Vorgang automatisch in einer Routine auszulösen.

5. Datenbereinigung

Ein häufiger Grund für die Durchführung von Datenbankwartungsoperationen ist die Bereinigung von Daten, die mit der Zeit veraltet oder irrelevant geworden sind. In diesem Abschnitt geht es um zwei häufige Fälle, in denen Datenbereinigungen als Teil der Wartung durchgeführt werden.

In Wirklichkeit kannst du deine eigene Datenbereinigungsroutine einrichten, je nachdem, was die Datenmodelle deiner Anwendung erfordern. Die folgenden Beispiele dienen nur als Referenz.

Löschen von Datensätzen nach Alter (letzte Änderung oder letzter Zugriff)

Das Bereinigen von Datensätzen anhand des Alters ist im Vergleich zu den anderen Vorgängen auf dieser Liste relativ einfach. Du kannst eine Löschabfrage schreiben, die Datensätze löscht, die älter als ein bestimmtes Datum sind.

Hier ist ein Beispiel für das Löschen von Kommentaren, die vor dem 9. Oktober 2023 gemacht wurden:

app.get("/clean-by-age", async (req, res) => {

    // Filter and delete all comments that were made on or before 9th October, 2023
    const result = await client.query("DELETE FROM COMMENTS WHERE timestamp < '09-10-2023 00:00:00'")

    if (result.rowCount > 0) {
        res.json({message: "Cleaned up " + result.rowCount + " rows successfully!"})
    } else {
        res.json({message: "Nothing to clean up!"})
    }
})

Du kannst es ausprobieren, indem du eine GET-Anfrage an die Route /clean-by-age sendest.

Löschen von Datensätzen basierend auf benutzerdefinierten Bedingungen

Du kannst auch Bereinigungen basierend auf anderen Bedingungen einrichten, z. B. das Entfernen von Datensätzen, die nicht mit anderen aktiven Datensätzen im System verknüpft sind (wodurch eine verwaiste Situation entsteht).

Du kannst zum Beispiel einen Bereinigungsvorgang einrichten, der nach Kommentaren sucht, die mit gelöschten Beiträgen verknüpft sind, und diese löscht, da sie wahrscheinlich nie wieder in der Anwendung auftauchen:

app.get('/conditional',  async (req, res) => {

    // Filter and delete all comments that are not linked to any active posts
    const result = await client.query("DELETE FROM COMMENTS WHERE post_id NOT IN (SELECT post_id from Posts);")

    if (result.rowCount > 0) {
        res.json({message: "Cleaned up " + result.rowCount + " rows successfully!"})
    } else {
        res.json({message: "Nothing to clean up!"})
    }
})

Du kannst dir deine eigenen Bedingungen für deinen Anwendungsfall ausdenken.

6. Datenmanipulation

Datenbankpflegeoperationen werden auch zur Datenmanipulation und -umwandlung verwendet, z. B. zum Zensieren obszöner Sprache oder zum Umwandeln von Textkombinationen in Emoji.

Im Gegensatz zu den meisten anderen Operationen sollten diese Operationen am besten dann ausgeführt werden, wenn die Datenbank aktualisiert wird (und nicht zu einem bestimmten Zeitpunkt in der Woche oder im Monat für alle Zeilen).

In diesem Abschnitt werden zwei dieser Operationen aufgeführt, aber die Implementierung für alle anderen benutzerdefinierten Manipulationsoperationen ist diesen recht ähnlich.

Text in Emoji umwandeln

Du kannst Textkombinationen wie „:)“ und „xD“ in echte Emojis umwandeln, um ein besseres Nutzererlebnis zu bieten und die Konsistenz der Informationen zu wahren. Hier ist ein Codeschnipsel, der dir dabei hilft:

app.get("/emoji", async (req, res) => {
    // Define a list of emojis that need to be converted
    const emojiMap = {
        xD: '😁',
        ':)': '😊',
        ':-)': '😄',
        ':jack_o_lantern:': '🎃',
        ':ghost:': '👻',
        ':santa:': '🎅',
        ':christmas_tree:': '🎄',
        ':gift:': '🎁',
        ':bell:': '🔔',
        ':no_bell:': '🔕',
        ':tanabata_tree:': '🎋',
        ':tada:': '🎉',
        ':confetti_ball:': '🎊',
        ':balloon:': '🎈'
    }

    // Build the SQL query adding conditional checks for all emojis from the map
    let queryString = "SELECT * FROM COMMENTS WHERE"

    queryString += " COMMENT_TEXT LIKE '%" + Object.keys(emojiMap)[0] + "%' "

    if (Object.keys(emojiMap).length > 1) {
        for (let i = 1; i < Object.keys(emojiMap).length; i++) {
            queryString += " OR COMMENT_TEXT LIKE '%" + Object.keys(emojiMap)[i] + "%' "
        }
    }

    queryString += ";"

    const result = await client.query(queryString)

    if (result.rowCount === 0) {
        res.json({message: "No rows to clean up!"})
    } else {
        for (let i = 0; i < result.rows.length; i++) {

            const currentRow = result.rows[i]
            let emoji

            // Identify each row that contains an emoji along with which emoji it contains
            for (let j = 0; j < Object.keys(emojiMap).length; j++) {
                if (currentRow.comment_text.includes(Object.keys(emojiMap)[j])) {
                    emoji = Object.keys(emojiMap)[j]
                    break
                }
            }

            // Replace the emoji in the text and update the row before moving on to the next row
            const updateQuery = "UPDATE COMMENTS SET COMMENT_TEXT = '" + currentRow.comment_text.replace(emoji, emojiMap[emoji]) + "' WHERE COMMENT_ID = " + currentRow.comment_id + ";"

            await client.query(updateQuery)
        }

        res.json({message: "All emojis cleaned up successfully!"})
    }

})

In diesem Codeschnipsel musst du zunächst eine Liste von Emojis und ihren textuellen Darstellungen definieren. Dann wird die Datenbank nach diesen Textkombinationen durchsucht und sie werden durch Emojis ersetzt.

Obszöne Sprache zensieren

In Anwendungen, die nutzergenerierte Inhalte zulassen, wird häufig versucht, anstößige Sprache zu zensieren. Die Herangehensweise ist hier ähnlich – identifiziere die Stellen mit obszöner Sprache und ersetze sie durch Sternchen. Mit dem bad-words-Paket kannst du ganz einfach nach Schimpfwörtern suchen und sie zensieren.

Installiere das Paket, indem du den folgenden Befehl ausführst:

yarn add bad-words

Dann initialisiere das Paket in deiner index.js-Datei:

const Filter = require('bad-words');
filter = new Filter();

Verwende nun den folgenden Codeschnipsel, um obszöne Inhalte in deiner Kommentartabelle zu zensieren:

app.get('/obscene', async (req, res) => {

    // Query all comments using a cursor, reading only 10 at a time
    const queryString = "SELECT * FROM COMMENTS;"

    const cursor = client.query(new Cursor(queryString))

    let rows = await cursor.read(10)

    const affectedRows = []

    while (rows.length > 0) {

        for (let i = 0; i < rows.length; i++) {
            // Check each comment for profane content
            if (filter.isProfane(rows[i].comment_text)) {
                affectedRows.push(rows[i])
            }
        }

        rows = await cursor.read(10)
    }

    cursor.close()

    // Update each comment that has profane content with a censored version of the text
    for (let i = 0; i < affectedRows.length; i++) {
        const row = affectedRows[i]
        const updateQuery = "UPDATE COMMENTS SET COMMENT_TEXT = '" + filter.clean(row.comment_text) + "' WHERE COMMENT_ID = " + row.comment_id + ";"
        await client.query(updateQuery)
    }

    res.json({message: "Cleanup complete"})

})

Den vollständigen Code für dieses Tutorial findest du in diesem GitHub Repo.

Das Vakuumieren von PostgreSQL und sein Zweck verstehen

Neben der Einrichtung von benutzerdefinierten Wartungsroutinen, wie sie oben beschrieben wurden, kannst du auch eine der nativen Wartungsfunktionen von PostgreSQL nutzen, um den Zustand und die Leistung deiner Datenbank zu erhalten: den Vakuum-Prozess.

Der Vakuum-Prozess hilft dabei, die Leistung der Datenbank zu optimieren und Speicherplatz zurückzugewinnen. PostgreSQL führt die Vakuumoperationen nach einem Zeitplan mit seinem Auto-Vacuum-Daemon aus, aber du kannst sie bei Bedarf auch manuell auslösen. Hier sind ein paar Möglichkeiten, wie häufiges Vakuumieren hilft:

  • Wiederherstellung von blockiertem Speicherplatz: Eines der Hauptziele von Vacuum ist es, blockierten Speicherplatz in der Datenbank wiederherzustellen. Da ständig Daten eingefügt, aktualisiert und gelöscht werden, kann PostgreSQL mit „toten“ oder veralteten Zeilen überladen werden, die noch Platz auf der Festplatte belegen. Vacuum identifiziert und entfernt diese toten Zeilen und macht den Platz für neue Daten verfügbar. Ohne Vacuum würde der Speicherplatz auf der Festplatte nach und nach erschöpft sein, was zu Leistungseinbußen und sogar zu Systemabstürzen führen kann.
  • Aktualisieren der Query Planner Metriken: Das Vakuumieren hilft PostgreSQL auch dabei, die vom Query Planner verwendeten Statistiken und Metriken auf dem neuesten Stand zu halten. Der Query Planner ist auf eine genaue Datenverteilung und statistische Informationen angewiesen, um effiziente Ausführungspläne zu erstellen. Durch regelmäßiges Ausführen von Vacuum stellt PostgreSQL sicher, dass diese Metriken auf dem neuesten Stand sind und kann so bessere Entscheidungen darüber treffen, wie Daten abgerufen und Abfragen optimiert werden.
  • Visibility Map aktualisieren: Die Visibility Map ist ein weiterer wichtiger Aspekt des PostgreSQL Vacuum-Prozesses. Sie hilft dabei, herauszufinden, welche Datenblöcke in einer Tabelle für alle Transaktionen vollständig sichtbar sind, damit Vacuum nur die notwendigen Datenblöcke bereinigen kann. Dies erhöht die Effizienz des Vakuum-Prozesses, da unnötige E/A-Operationen, die kostspielig und zeitaufwändig wären, minimiert werden.
  • Verhinderung von Transaktions-ID-Wraparound-Fehlern: Vacuum spielt auch eine wichtige Rolle bei der Vermeidung von Transaktions-ID-Wraparound-Fehlern. PostgreSQL verwendet einen 32-Bit Transaktions-ID-Zähler, der zu einem Wraparound führen kann, wenn er seinen Maximalwert erreicht. Vacuum markiert alte Transaktionen als „eingefroren“ und verhindert so, dass der ID-Zähler einen Wraparound auslöst und Daten beschädigt. Wenn du diesen Aspekt vernachlässigst, kann das zu katastrophalen Datenbankausfällen führen.

Wie bereits erwähnt, bietet PostgreSQL zwei Optionen für die Ausführung von Vacuum: Autovacuum und Manual Vacuum.

Autovacuum ist die empfohlene Wahl für die meisten Szenarien, da es den Vakuumprozess automatisch anhand der vordefinierten Einstellungen und der Datenbankaktivität verwaltet. Manuelles Vakuum hingegen bietet mehr Kontrolle, erfordert aber ein tieferes Verständnis der Datenbankwartung.

Die Entscheidung zwischen den beiden Varianten hängt von Faktoren wie der Größe der Datenbank, der Arbeitslast und den verfügbaren Ressourcen ab. Kleine bis mittelgroße Datenbanken können sich oft auf Autovacuum verlassen, während größere oder komplexere Datenbanken möglicherweise einen manuellen Eingriff erfordern.

Zusammenfassung

Datenbankwartung ist nicht nur eine Frage der Routine, sondern die Grundlage für eine gesunde und leistungsfähige Anwendung. Indem du deine Daten regelmäßig optimierst, bereinigst und organisierst, stellst du sicher, dass deine PostgreSQL-Datenbank weiterhin Spitzenleistungen erbringt, nicht beschädigt wird und effizient arbeitet, auch wenn deine Anwendung skaliert.

In diesem umfassenden Leitfaden haben wir untersucht, wie wichtig es ist, gut strukturierte Datenbankwartungspläne für PostgreSQL zu erstellen, wenn du mit Node.js und Express arbeitest.

Haben wir irgendwelche routinemäßigen Datenbankwartungsvorgänge übersehen, die du für deine Datenbank implementiert hast? Oder kennst du einen besseren Weg, um eine der oben genannten Maßnahmen umzusetzen? Lass es uns in den Kommentaren wissen!

Kumar Harsh

Kumar ist Softwareentwickler und technischer Autor in Indien. Er hat sich auf JavaScript und DevOps spezialisiert. Auf seiner Website kannst du mehr über seine Arbeit erfahren.